MYSQL :: Information From Existing Db

Table Joins On Existing/Non-Existing Data
I have a question that involves a joining tables on potentially non-existing data, but we will know the expected values whether they exist or not.

Say we're keeping statistics on any given number of sports for attendances over weekly time periods. There's two tables to hold data.

------------------------------
TABLE: StatisticTypes
id
name
------------------------------
TABLE: Statistics
id
statisticTypeId
value
date
------------------------------

Then, say, we have three entries in the StatisticTypes table for types of sports we're keeping data on...

MYSQL User Information: Error While Storing The User Information
I have a proplem creating a new user and/or adding additional
information for root user in MySQL Administrator 1.0.19. When I do I
get this message: error while storing the user information.

Never the less, when I close the Admin program and open it again, the
changes I tryed to make are saved, (althoug the program asked me if I
wanted to save changes and I said no)

Is this a know bug, should I be alarmed, any solutions ?

I´m running
MySQL 4.3.11
Admin 1.0.19
Query 1.1.5
Apache2
php5

Replace Information In One Table With Information From Another Table
I've got two tables in one DB they have various columns; what I need to know is how I can take the information from a column in one table and put it in to a column in another table.

table1 - characters
table2 - characters_jackedup

I want to take the information from column maxMp in table1 and put the information in table2 under maxMp the column exists in both; but the columns are in a different order.

Mysql Information
My company is currently investigating in several database, projects
tracking software.

Being a beginner, I would like to know if you could provide me some
support concerning the capabilities of MYSQL.

We are looking in a software that could achieve the following:

- Enter text related to specific subjects (text can be pretty long and
should be searchable by key words)
- linking tables and extract specific information from those tables
(no problem with that).
- The ability to assign author privileges and to change them easily)
- The ability to assign task to some users.
- the ability to access the database and modify the information on the
web.
- the ability to automatically send the information by email or at
least notify one of the user that a project has been updated.
- the ability to point at other document (word, pdf etc) and to easily
access those documents from the database.

Duplicate Information
How the simple form that I can run query and drop duplicate information on table.

Displaying Information
I have inserted all of my information into my data base. Now I want to print it onto my website. I know how to get it to print onto my website, but I don't know how to organize it. I want to print the information into an table, and I want the table rows to alternate colors. Also I want to adjust font, how would I do this?

Retrieving Information As It Is
I have a mulitline input field, going to a database, when i view the inputted information in the database "browse" i looks exactly like it does when i typed it in for example

Line one
Line Two

When i get the information back out using a SQL statement it come out in one lone line thus Line one Line two, with two boxes in between where the line break should be.

I need to to come out as it went in

Is this possible?

Sql Headers Information
I have a scSi disk 36.4GB, I lost information from it and tried to recover with other recovery softwares to no avail finally I got winhex & got the professional licence.

I can now be able to see all the information on the 1 whole NTFS partition, but the problem is that I can't be able to recover it. whenever I open the logical drive I can see all the information and most of the information is stack up in a "free space" which is around 33gb which I can be able to see has all the information that I need.

I have tried to recover the free space but its only copied to the location that I specify and no recoveries are made. recovering the files by type/ name has bore no fruits.

A freind told me I have to curve out that area and use data extractors to get the information out of that area (freee space).

The information in that drive are sql data bases and zip files. whats the header for sql.

what techniques do I use to curve out that space and be abele to extract the information, for I can even read the information in text format.

which are the data extractors available in the market.

Transfering Information Between 2 Different DBs
i have an old DB done for a forum in the mambo system ( now called joomla ), but i am changing now to vbulletin system + vbadvanced. The DB i use is MySQL in both forums, but when i open the backup file from mambo and compare it to the vbulleting DB using my phpdev system, i see that they are tottally different.

would you have any tip for me so i can save at least my users and their information from mambo to vbulletin? Can someone help me? ItÂ´s a very seriou forum, itÂ´s from Alias ( 3D Maya Software productor), and so, could please somebody give me a tip? A help? A force to make me win my MySQL battle?

Selecting Old Information
hello, i have a table called messages and i have a column in which the date of the message was created called dateSet, how can i select all of the messages that are older than 6months?

Get Information About An Error
I'm running MySQL 5.0.48 and would like to be able to retrieve an error number or error message when an error has occurred.

I know that I can do:
SELECT @@ERROR_COUNT;

But since I mostly know that an error has occurred that is of little use to me.

When connected to MySQL I can do things like:
SHOW ERRORS;
SHOW WARNINGS;

But I can not do a SELECT so that is not of much use either.

The reason I want to do this is to log errors that occur in a Stored Procedure.

Entering Information
I have looked threw many books and the nety but cant find how I would enter more attributes into a table that already exists in a database in MySql

All I seem to find is for to insert values into a table.

Group Information
I have a database with a date field in it, and I am trying to figure out how I can pull information for a calendar week at a time.I am sure that some type of loop is needed, but I am stuck trying to figure out the logic.

Select Information
my current sql query is

SELECT * FROM entries WHERE approved='1' ORDER BY rating DESC,hits DESC,votes DESC

which works. but i want to implement multiple pages to my site. so i only want it to return for instance the first 15 rows, or only row 16-30 or only 31-45 etc

Fetching Information
an example:

$dbl->query($query);
$res = $dbl->get_result();

how do i pull information from a row in $res without automatically moving the pointer ahead.with: $myrow = $res->fetch_array() i pull the correct information but i don't want the pointer to move ahead in $res.

Passing Information
I'm having trouble passing an item from a text box to another screen. Whenever I enter an address into the box like 123 First St. the only thing that gets passed is the 123 on the URL. Because of the %20 spaces it cuts it off. How do I remedy that?

MySql Information
I've installed apache , PHP and MySQL in phpinfo page information about is MySQL is not present. MySQL is working properly in DOS mode. any solution to display proper information about mysql in phpinfo.

Most Common Information In DB
I have a database with the following fields

id, system, reviewer

an example would be

0, 5, 9
1, 5, 9
2, 4, 9

is there a way that i can search for all of reviewer 9's reviews, and find out what system is reviewed the most...?? so reviewer 9, it would be system 5 according to the example above.

Sortting Information
Here is a makeshift page I have made for sorting information regarding some car's and their respective times in drag racing.

http://www.1320video.com/times.php

As you may notice, when sorting some columns in a ASC to DESC fashion, it places the zero's before everything else.
Without actually not selecting the rows with 0's in the SQL statement, is there a way to sort these so they show at the end?

Getting Table Information
In MySQL How do you query MySQL for the list of column names for a specific table?

Selecting Information
I need to select fields from one column, but this column contains duplicates on many rows. How do I write a query that only selects the info once and ignore it if it exists again.Pretty much I want to build an optionbox with every field from this column that is different. Since some fields in this column have the same value I don't want to list it twice so the standard SELECT all will not work.

My.cnf Not Existing
this is my first posting here so plz do not flame me too much if my quest=
ion=20
marks me as a total noob.

My problem is, i play around a bit with mysql (4.0.13) and want to test t=
he=20
Replication. The Documentation at http://www.mysql.com says i have to do =
the=20
following:
Stop databases - done.
Build tarball of data and bring it on slave - done.
Modify my.cnf - ???

Here is my problem. There is no my.cnf existing on my machine. Maybe its=20
called different with the newer version? Or can it be considered empty an=
d i=20
have to build a new one? But if i have to, in which directory would i do=20
that?

Separating Text And Information?
Is it worth it to store an article's information and text separately in a database?

I've been doing this for some time now because that's what phpBB does. But today, I was looking at the Wordpress database structure, and they store the info and text in the same table.

Figuring Out Pagination Information
mysql
SELECT producten.slug, SUM( maten_producten.voorraad ) AS huidigevoorraad
FROM producten
JOIN categorieen_producten ON ( categorieen_producten.product_id = producten.id )
JOIN categorieen ON ( categorie_id = categorieen.id )
JOIN maten_producten ON ( producten.id = maten_producten.product_id )
JOIN maten ON ( maten_producten.maat_id = maten.id )
WHERE 1 =1
AND categorieen.parentid = ཈'
AND producten.status = 'active'
GROUP BY (
producten.id
)
HAVING SUM( maten_producten.voorraad ) > 0
ORDER BY producten.created

And I would like to retrieve some pagination information on that (the total number of records and the number of pages, when showing max 12 items per page).

I've tried this:

mysql
SELECT CEIL( count( * ) / 12 ) AS numpages, COUNT( * ) AS totalitems
FROM producten
JOIN categorieen_producten ON ( categorieen_producten.product_id = producten.id )
JOIN categorieen ON ( categorie_id = categorieen.id )
JOIN maten_producten ON ( producten.id = maten_producten.product_id )
JOIN maten ON ( maten_producten.maat_id = maten.id )
WHERE 1 =1
AND categorieen.parentid = ཈'
AND producten.status = 'active'
GROUP BY (
producten.id
)
HAVING SUM( maten_producten.voorraad ) > 0
ORDER BY producten.created

But that gives me multiple rows back, while I only need one with two integers.
I'm not a very advanced SQL coder, some pointers in the right direction would be great!

Getting Number And Information About Connections
How can I get the number of connections that are currently connected to my database?

It would be great if I could know other details about those connections, too, like what SQL is being thrown at the database and the time the connection has been established.

The reason, once or twice a year one of my sites gets a bit busy and takes the shared server down. A simple fix might be to find the database connection info and do something else when the number of connections or the connection times are above certain limits.

The connections are not persistent, btw.

Information About Hacking Query
Yesterday i found some hacking attempt on my machine.
( I found some unknown queries on binary log)

But i don't get any information about hacking query connection on binary log file.. ( username, host.. i don't know anything )

only thing that i know is "thread_id".
------

How can i know other information about connection. is there any
option???

------
one more..

hacking connection's thread_id has sequence with web connection:

User Information Changed
The other day someone changed every user's password on my site. Then they changed every user's email address to theirs - I assume the reason was that when the user used the password reset function, they would never see the information.

I'm trying to research what/where the vulnerability is in the script that I'm suing, but I don't really know where to start - not being a hacker, I don't even know the terms to search for here or on google.

Is this what is meant by mysql injection?

MySQL Server Information
I want to get information about all users who are connected to my database. I am using MySQL 5.0.18 on windows 2000 professional.

Returning A Column With Row Information
I have two tables, customer and customer_phone. The customer table has an id col, cust_id, which links the two tables. The phone table has a number col, a type col, and a few more.

What I need is to be able to return a recordset which comprises of the following:
c.firstname
c.lastname
c.title
cp.number (where cp.type=1)
cp.number (where cp.type=2)
cp.number (where cp.type=3)

I currently have this as a left join query, with a left join for each instance of the phone number eg:

SELECT c.first, c.last, c.title, c1.number, c2.number, c3.number from customer c left join customer_phone c1 on c.cust_id = c1.cust_id left join cust...........
where c1.type ='1' and c2.type = '2' and c3.type= '3'

Is there an easier way which doesn't involve so many left joins? My concern is that I'm looking at returning 100 rows per page for a report, and currently I need to return 6 number types, and other relevant information for each phone number.

Changing The Information Location
I'm running mysql version 4.1.21 locally for my Java program to connect to. I would like to alter my system in such a way that the service could still be running on my actual local machine ... but the actual information will be stored on a mapped drive somewhere else.

Placing Information In Two Databases
How can I get data that always goes into one db column also write to a second db. Is it possible that what ever is written to one column to also be written to another column in another db? Shared or parsed somehow.

Storing File Information
I have a website that I am designing that has a bunch of images in it.. instead of writing a bunch of html code to put these images into a table I wanted to use MYSQL and PHP to do so.

I know that storing the images themselves is a bad idea so can anyone give me a hint on how to store the file information (i.e path and filename) into the database.. More like what information i need. also is it possible to have all the filenames in a text file and read the info from there to populate the table??

Deleting Information On Tables
I know this is a basic question, and I should be able to figure it out, BUT I have run out of ideas on this. have a table Person with the following columns:

ssn
firstname
lastname
zip_code
And i have the table Zip with the following:
zip_code
city
state

I want to be able to delete an entire record both off of Person and Zip using the ssn.
i've tried the following:

delete from Person where ssn="111-11-1111" AND Zip.zip_code=Person.zip_code;
Any suggesstions?

Information On Differences Between MySQL4 And 5
I'm trying to track down information that will tell me all the differences between MySQL4 and MySQL5, that includes things like depreciated commands/functions/etc and new commands/functions/etc. And any other changes that may affect a move from everything that runs on a MySQL4 database that will need to be re-written to work fine on MySQL5,

Writing Information To A Database.
I have a server setup to run MySQL and php and I was wondering how to write information from a site to the db. So far ive tried with .asp and dreamweaver, and to no avail. And well, seem to find PHP abit easier to code with.

At least for me haha. Could anyone show an example piece of code that shows how the write command is written to direct it to the database? or a reference as to where I can read up more on it. Ive searched for abit and have not found anything fruitful. Ive found w3schools, which has helped me to understand it alil bit more, but not enough to be able to write to the database.

Retrieving Information From MySQL
I have created a an online shop and it works fine on localhost and on the test server, however we want to intregate it with an online bank and its not working properly.

When purchasing a single product the product can be purchased successfully however multiple products cannot purchased successfully.

For some reason the page on the online banks server cannot access one our database that stores the information in the customers shopping cart but it can retrieve the information for a single product order. Im not sure if its some kind of issue on there server?

MySQL Basic Information
I'm now using Access w/ VB6 and I'm thinking of switching to MySQL.

What are the costs involved?
What do I need to download?

Information In Zencart Table
Is there any way to retrieve information that I deleted in a database that I failed to backup?I deleted all my categories and products from a ZenCart database.

Missing Server Information
I've been using MySQL for a couple of months now in developing ColdFusion applications. Everything was working great until I tried to change a password. I'm not really sure if that had anything to do with my problem or not, but here is what I think I remember doing: I stopped the MySQL service and then using a command prompt, I flushed the priviliges and reset the password (following the directions from a book). I have had it working a couple of times since then, but not consistently and....

Now I keep getting an Error 2003: Can't connect to MySQL server on 'localhost' (10061). When i look at WinMySQLAdmin, all of the boxes in the Server section of the Environment tab are empty. I know this isn't right.

Retrieving Progress Information
I am trying to implement a progress bar in my application and provide the end user the information of how long the task (retrieving db info) will take.

I wonder if there is no way to get back progress information from the database server. Does mySQL have such mechanisms for retrieving progress information?

Change ALL Information In A Column
In a mySQL table, I have a column labeled "accept". The choice for the column is either: accept or waiting.
When visitors add information, the default is "waiting"

Is it possible to write a query so that I can change ALL the "waiting" entries to "accept" at one time?

ON EXISTING Clause
What I'm trying to do is update a table of mine in mySQL. However, some of the values may not exist in the table yet, while most will already be there.

I have done some searching and I think I need help. I'm assuming UPDATE won't simply act as an INSERT if the existing value is not found. From my searching it seems I need to use the ON EXISTING clause with an INSERT. However, it seems that ON EXISTING can only be used when there is a primary key. In my case, thats not the case here.

However, for me this is query confusing at the moment. Can someone provide a hand?

My table is called "rankings" and has the following structure:
comp_id - int (and an index)
uid - int
networth - int

How To Add A New Row To An Existing Table?
I am able to create database, tables, inserting values, and so forth. My question is this:

Let's say I have a table on the list of items I owned, and some time later, I need to add a new item between after row 12 and before 13. How do I do that? I did look at MySQL 5.0 Manual and didn't find anything similar to what I wanted to do.

Check For Existing Row ?
How do I determine if a row already exists ?

// see if a row has id=100

$id=100;
$query="SELECT * FROM mytable WHERE id=$id";
$result=mysql_query($query);

...what do I check now ?

Using Existing Database
I am setting up a new dev. machine and need to get an old database into the new installation of MySql. There are two databases showing on the database list 'mysql' and 'test'.
Can I drop the old database (contained in a folder) straight in with the existing databases and get any sense from it?

Existing Database
I'm using MySQL 4.1.9-nt & VC++ 6.0 .
how to get all the database names and table names?

I'm using the VC++ syntax,

CRecordset rs(&db); //here db is object of CDatabase class.
rs.Open(AFX_DB_USE_DEFAULT_TYPE,"show databases ");

Innodb Corrupt? > Incorrect Information In .frm
I have a database from shared hosting that is corrupted. It is a mysql database. It feds out that the corrupted tables are of innodb type. The hoster didn't make backups, so I really need to repair this database.
I have given some hints to my hoster. It feds out that the hoster has upgraded the mysql-server to 5.0.27. I conclude from my internet research that the repairing of innnodb tables should be quite feasible. I directed my hoster to http://dev.mysql.com/doc/refman/5.0/...-recovery.html, but according to him this doesn't work.
The error message that I see when accessing the corrupted tables looks like

#1033 - Incorrect information in file: './foo/bar.frm'

Query For Member Information And Photo
member
member_photo

The member_photo has a record for each photo they upload. They can then choose which photo is their primary photo, which is flagged with a field called photomain. Im trying to get the list of all members along with their primary photo but Im using 2 queries which I feel is slowing down the page. Is there a subquery I should be using?

PHP

$query1 = $db->Execute("SELECT id,firstname,lastname FROM members ORDER BY lastname ASC"); //loop through the recordswhile(!query1->EOF){$memberid = $query1->fields['id'];$firstname = $query1->fields['firstname'];$lastname = $query1->fields['lastnamename'];$query2 = $db->Execute("SELECT photothumb FROM member_photo WHERE memberid = $memberid AND photomain = 1 LIMIT 1"); $memberphoto = $query2->fields['photothumb']; $query1->MoveNext();