Why is my SQL insert statement failing to execute through my php script?

Question

In a php script, I've attempted an insert statement several different ways:

$query = "INSERT INTO table (charfield,charfield,intfield,decimalfield) VALUES ('$stringvar','$stringvar',$intvar,$decimalvar)";
$query = "INSERT INTO table (charfield,charfield,intfield,decimalfield) VALUES ('".$stringvar."','".$stringvar."',".$intvar.",".$decimalvar.")";
$query = 'INSERT INTO table (charfield,charfield,intfield,decimalfield) VALUES ("'.$stringvar.'","'.$stringvar.'",'.$intvar.','.$decimalvar.')';
$query = 'INSERT INTO table (charfield,charfield,intfield,decimalfield) VALUES ("'.$stringvar.'","'.$stringvar.'","'.$intvar.'","'.$decimalvar.'")';

I've executed it several different ways too using combinations of these:

mysql_real_escape_string($query);
mysql_query($query);
$result = @mysql_query($query);

I've echo'd out the statement that is being concatenated and it looks fine. Even if I copy and paste that into phpmyadmin sql editor, it executes fine. The database is MySQL and the user has the correct permissions.

What am I doing wrong?

EDIT:

Error message using or die:

Access denied for user 'schaffins'@'localhost' (using password: NO)

I've added a user with the rights to select, insert, and update and I'm connecting using that user in the php before executing anything.

Also, I was able to insert into another table I made in the same script.

Is error reporting enabled? also is there anything returned from mysql_error()? — Scuzzy
– Scuzzy, Commented May 14, 2012 at 3:05
When you say it fails, can you give us more information - Do you mean it executes but nothing is inserted, or do you get an error and if so what is the error message — FluffyKitten
– FluffyKitten, Commented May 14, 2012 at 3:05
mysql_query($query) or die(mysql_error()) and it will tell you what's wrong. — deceze
– deceze ♦, Commented May 14, 2012 at 3:05
Do other queries work? Have you tried reducing this insert to a smaller one? Have you tried terminating the query with a semicolon? "Table" is the actual name of the table in your actual code, I assume. Seeing the table definition might be helpful, as would actual values you are putting into strfield. I also recommend using PDO instead of manually concatenating SQL (php.net/manual/en/book.pdo.php) — earth_tom
– earth_tom, Commented May 14, 2012 at 3:11
This may be a silly question, but you have specfiied the password in mysql_connect() haven't you? Because its saying you are not using the password... FYI the "@" infront of "@mysql_query($query);" is supressing the error message that would have been displayed. Fine for production, but not so helpful for testing! — FluffyKitten
– FluffyKitten, Commented May 14, 2012 at 3:12

FluffyKitten · Accepted Answer · 2012-05-14 03:19:00Z

2

This issue isn't with your insert - the error is connecting to the database so its not even getting as far as running the insert statement.

Double-check the credentials you are passing into mysql_connect()... from the error message "Access denied for user 'schaffins'@'localhost' (using password: NO)" it appears the problem is with your password.

answered May 14, 2012 at 3:19

FluffyKitten

14.4k10 gold badges44 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Explosion Pills · Accepted Answer · 2012-05-14 03:09:19Z

1

You can't use mysql_real_escape_string on the entire query. That will escape quotes that are part of the query syntax. You need to use it on the values to insert in the query.

answered May 14, 2012 at 3:09

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

1 Comment

WhoIsMaryLou Over a year ago

I commented it out on a couple runs and it made no difference

CheeseConQueso · Accepted Answer · 2012-05-14 18:44:16Z

0

Sometimes the database connection happens only after certain actions &| validations take place. Make sure you have an open connection before attempting to execute any sql. If the connection is nested within an if/else code block, the conditions may not be satisfied and the db connection will not be established.

Try finding the db connection and moving (or copying) to a more 'globalized' location within the php - aka somewhere in the php that ensures the db connection is made before any if statements that might exclude the connection from being established based on conditionals that aren't allowing

answered May 14, 2012 at 18:44

CheeseConQueso

6,07129 gold badges98 silver badges127 bronze badges

Collectives™ on Stack Overflow

Why is my SQL insert statement failing to execute through my php script?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related