MySql PHP error I cannot figure out 48 hours later

PHP
1 
//$lottery=array(123-XYZ, 234-ABC, 456-DEF, ... , 678-GHI..., 400,000 random 123-ABC combinations)
// Set up the array
$final = $lottery;

// Connect to the database
$DB = mysql_connect("localhost",  $db_user, $db_pass);
mysql_select_db($db_name, $DB);

// Create the query string
$query = '"INSERT INTO CustomerID (uniqueCustomerID) VALUES (';

// Loop through the array
for($i = 1, $c = count($final); $i < ($c-1); $i += 1) {
// Add the next batch of values to the query string
$query .= "'{$final[$i]}'";

// Add a comma if this is not the last batch
//make sure the correct php code is inserted here for the "if" loop condition {...}...else...
$query .= ", ";
}

$query .="'{$final[$c-1]}')".'"';

// Execute the query
$RESULT = mysql_query($query, $DB);

// Check the results
if(!!$RESULT) {
echo "Query was successfull";
}
else {
echo "Query failed
<blockquote>". $query ."</blockquote>
<blockquote>". mysql_error($DB) ."</blockquote>";
} 

mysql_close($conn);
}

Query failed

    "INSERT INTO CustomerID (uniqueCustomerID) VALUES ('434-BMJ', '670-XKR', '606-NYX',
 '096-KUX', '143-XYJ', '681-YLV', '715-XZM', '822-UXN', '922-GIW', '958-ULQ', 
'004-ICY', '102-CZO', '877-TJF', '681-CPQ', '498-LTS', '150-MOA', '113-DHB', ... ,
 '237-QIS', '244-KOG', '241-TKK', '087-GNN', '298-DQX', '492-YGR', '454-PCQ',
'065-WIB', '235-AKW', '973-ZQD', '640-DUI', '248-YYW', '575-OSN', 134-XUQ',
 '087-EEL', '037-HVN', '111-QHY')"

    You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near '"INSERT INTO
 CustomerID (uniqueCustomerID) VALUES ('434-BMJ', '670-XKR', '606-NY' at line 1

Above shows my response from the snippet of code I have been working with over the past two days. I cannot figure this one out, I keep receiving an error when attempting to insert my array full of data into my already initiated MySql database. It’s telling me that there is a syntax error within MySql code I have… I have not been able to figure it out after two days…

Could someone lend me a hand?

2

the error message is telling you ~exactly~ where to look for the source of the problem

it’s the doublequote in front of the word INSERT

in fact you’ve got your entire query wrapped in a pair of doublequotes, in effect turning the entire query into a string value

mysql doesn’t know what to do with a string value, it’s looking for a command like INSERT

3

I have tried fixing the quote error and it keeps coming up the same thing on line one. I know what you are saying and I can read too, believe me, I have been trying everything, but it’s not as easy as fixing the simple quote. I’m sure you have received whacko errors that have been fixed by altering a segment of code completely irrelevant to the error you get, right? This is one of those errors, I believe.

4

Take out the first and last " in the code that builds your query. If that doesn’t fix the problem then post the exact query that is being run. You can see the exact query by inserting

echo $query;

die();

just above where you run the query. The first " definitely should not be there. You could have a mismatched quote somewhere.

5

if you’ve removed the quotes, you will now be faced with another, different error

the query you generated, without the quotes, looks like this –

INSERT INTO CustomerID (uniqueCustomerID) VALUES ('434-BMJ', '670-XKR', '606-NYX',
 '096-KUX', ...)

this will fail (and if you’re interested in the exact error message, run it directly in mysql, not from php)

what you need to generate instead is this –

INSERT INTO CustomerID (uniqueCustomerID) VALUES ('434-BMJ'), ('670-XKR'), ('606-NYX'),
 ('096-KUX'), ...

see the difference?

:slight_smile:

6

I’m confused though, when you look at the first part, it outputs correctly as $query= "INSERT INTO table when it is echoed in the ‘query failed’ section, but I still receive the error.

you see my dilemma?

7

When you echo the query generated by php, the first " should not be there and there shouldn’t be one at the end either. Post exactly what is displayed on the screen when you run echo $query;

8

I see. I’ll reformat the output, had no idea each VALUES needed it’s own ()'s.

9

sorry, no i don’t (i don’t do php)

but i know how to fix mysql errors

have you tried running your query outside of php?

10

@r937 - Thank you for your speedy response and help. I will make the changes accordingly.

@wwwAngel - Thank you for pointing that out. I will test it out now.

11

@r937 - not yet, I have just ran in Firefox 4 beta browser to receive the error. It generates the $query, displays all 400,000 entries correctly thereafter, and spits out that MySql syntax error. When the $query prints out, it looked like it was displaying correctly according to PHP code and MySql sytax references so I’ll make those changes now.

12 
$sql  = "CREATE TABLE CustomerID (personID int NOT NULL 
AUTO_INCREMENT,PRIMARY KEY(personID),uniqueCustomerID varchar(15))";

Unknown column 'uniqueCustomerID' in 'field list'

One more piece of code is above to help finish this problem, along with its error. Any help is appreciated.

–edit-- this is my new error after taking the advice from angel and r937, nothing else was changed, I simply added a little more code from the script I am running that I have made to help store these 400,000 values in my database for later use for a company lottery.