Mysql error but query still working

[padams]

The code below is generating a number of new entries in my tries table, inserting 4 values. When $val contained an integer it worked fine, but I changed it to contain text, and suddenly I'm getting an error. Despite the error, it still inserts the correct amount of new entries in the table. What's going on?

 

Error is:

INSERT into tries (tryOpponent, tryTeam, trySeason, playerID) VALUES ('Wildcats', 'Red', '2006', 'Justin Conway'), ('Wildcats', 'Red', '2006', 'Justin Conway'), ('Wildcats', 'Red', '2006', 'Hamish McGill'),You have an error in your SQL syntax near 'Conway)' at line 1

 

Code is:

$qry = "INSERT into tries (tryOpponent, tryTeam, trySeason, playerID) VALUES ";

$counter =1;

foreach ($_POST['try'] as $key => $val){

$qry .= "('" . $_SESSION['creatematch']['opposition'] . "',

              '" . $_SESSION['creatematch']['ottersteam'] . "',

              '" . $_SESSION['creatematch']['season'] . "',

              '" . $val."'), ";

}

[jaymc]

Is the field in INT? Make sure the mysql field will accept a varchar. I cant see any obvious problems though

[padams]

Yes, I changed it to varchar.

 

I've been playing around with the offending code, and now that I've gone back to the original it's stopped working at all! Can anyone see what is going wrong? It's not even inserting new records anymore.

 

$qry = "INSERT into tries (tryOpponent, tryTeam, trySeason, playerID) VALUES ";

$counter =1;

foreach ($_POST['try'] as $key => $val){

$qry .= "('" . $_SESSION['creatematch']['opposition'] . "',

              '" . $_SESSION['creatematch']['ottersteam'] . "',

              '" . $_SESSION['creatematch']['season'] . "',

              '" . $val . "'), ";

}

 

$qry = substr($qry,0,-1);

echo $qry;

$qry = mysql_query($qry);

[jaymc]

When you echo $query what do you get, paste it so we can see exactly what MYSQL see's

[jaymc]

Also, how many fields exist in the table?

[padams]

Here's one I just tried:

INSERT into tries (tryOpponent, tryTeam, trySeason, playerID) VALUES ('Rhinos', 'Red', '2006', 'Phil Adams'), ('Rhinos', 'Red', '2006', 'Phil Adams'), ('Rhinos', 'Red', '2006', 'Raechel Adams'), ('Rhinos', 'Red', '2006', 'Nick Adams'), ('Rhinos', 'Red', '2006', 'Suzie OSullivan'),

 

It ends in a comma - is that a problem? However, the error message says the error is near 'Adams' so I assume that's after the first Phil Adams entry.

 

There are 5 fields in the tries table, the first one is tryID - an auto-incrementing primary key field.

[padams]

Additionally, when I change the value being entered for the playerID to an integer rather than text, the error doesn't appear, although the query still doesn't actually insert any new entries into the table.

[padams]

I just changed the substr function so that it doesn't include the final comma and the query is now correctly inserting records. However, I'm still getting the error appearing.

 

New code:

$qry = substr($qry,0,-2);

 

Query and error:

INSERT into tries (tryOpponent, tryTeam, trySeason, playerID) VALUES ('Rhinos', 'Red', '2006', 'Phil Adams'), ('Rhinos', 'Red', '2006', 'Phil Adams'), ('Rhinos', 'Red', '2006', 'Raechel Adams'), ('Rhinos', 'Red', '2006', 'Raechel Adams'), ('Rhinos', 'Red', '2006', 'Nick Adams')

You have an error in your SQL syntax near 'Adams)' at line 1

 

When I copy and paste the query into phpmyadmin it works fine too.