SQL Query doesn't get executed

Question

I'm trying to make a very basic comment system in PHP. The problem is that when I submit the form, the new row doesn't get inserted in my MySQL table. This is my code (, could someone please check what's wrong?):

<?php
$act = $_POST['act'];
if($act == 1) {
    $m = $_POST['message'];
    $m = strip_tags($m);
    $message = mysql_real_escape_string($m);
    $name = "Anonymous"; //Static username for demonstration purposes
    $date = "2012-7-28"; //Static date for demonstration purposes

    $con = mysql_connect("localhost","username","password");
    if (!$con){die('Could not connect: ' . mysql_error());}

    mysql_query("INSERT INTO comments (name, message, date) VALUES ('$name', '$message', '$date')");
    mysql_close($con);
}
?>

<form action="comments.php" method="post"> 
    <input type="text" name="message">
    <input type="hidden" name="act" value="1">
    <input type="submit" name="submit" value="Submit">
</form>

Please stop writing new code with the ancient mysql_* functions. They are no longer maintained and community has begun the deprecation process. Instead you should learn about prepared statements and use either PDO or MySQLi. If you care to learn, here is a quite good PDO-related tutorial. — user1233508, Commented Jul 28, 2012 at 14:48
what's the error? I guess there's some issue with your table name or field names... — Mr. Alien, Commented Jul 28, 2012 at 14:49
@DCoder This project is just a small one, and it's more of a personal project, so I'm fine with mysql_* functions for now. Didn't know they where deprecating, since most tutorials still use them.. Anyway, I'm might look into that PDO more deeply, thanks! — Bastiaan ten Klooster, Commented Jul 28, 2012 at 14:55
Say, can you tell us how the table you're trying to write to is structured? With variable types, if possible. — ATaylor, Commented Jul 28, 2012 at 15:01

ATaylor · Accepted Answer · 2012-07-28 15:13:17Z

1

I think your problem rests with the escaping, or rather the 'non-escaping' of the column names. Did you know that 'date' is a function name in mySQL?

Try putting all table and column names in backticks.

mysql_query("INSERT INTO `comments` (`name`, `message`, `date`) VALUES ('$name', '$message', '$date')");

Also, for further reference, posting the error message never hurts looking for the answer. Other than that, I can't find anything particularly wrong with your query.

Edit: DUH! I missed something obvious. Please execute 'mysql_select_db('name_of_database'); prior to the query. Otherwise it won't know where to look for the table you're specifying.

For the sake of completeness (as Michael Besteck pointed out), it is necessary to execute 'mysql_real_escape_string' only AFTER the connection has been established.

That is, because the 'escape_string' relies on the encoding of the connection to determine which characters need to be escaped and how.

edited Jul 28, 2012 at 15:13

answered Jul 28, 2012 at 14:53

ATaylor

2,5982 gold badges19 silver badges25 bronze badges

Column names do not necessarily need to be escaped, unless they contain reserved words or weird characters, which does not seem to be the case here.
– nico
Commented Jul 28, 2012 at 14:57
Might be a stupid question, but is there a way to retrieve the error manually? I didn't an error when posting the form.
– Bastiaan ten Klooster
Commented Jul 28, 2012 at 14:58
Date is a function name, and it's never a bad idea to 'escape' the column and table names.
– ATaylor
Commented Jul 28, 2012 at 14:58
@ATaylor: it's never a bad idea, but it won't solve the issue. Date can be used with no problem: dev.mysql.com/doc/refman/5.5/en/reserved-words.html "MySQL permits some keywords to be used as unquoted identifiers because many people previously used them."
– nico
Commented Jul 28, 2012 at 14:59
@BazzyTK use 'mysql_error()' to get the error after the query executes. Like usersomenumber down already proposed, the 'or die(mysql_error())' syntax may help you.
– ATaylor
Commented Jul 28, 2012 at 14:59

| Show 7 more comments

Michael Besteck · Accepted Answer · 2012-07-28 15:08:39Z

1

It is neccessary to first establish the database connection because the escape function is executed my mysql.

$con = mysql_connect("localhost","username","password");
$message = mysql_real_escape_string($m);

answered Jul 28, 2012 at 15:08

Michael Besteck

2,42318 silver badges11 bronze badges

Ah yes, because it uses the encoding...my, my, I must really be getting sloppy.
– ATaylor
Commented Jul 28, 2012 at 15:10
Sadly I can't set 2 comments as the right one.. Would like to upvote this one, but can't since I haven't got nuff resp :(
– Bastiaan ten Klooster
Commented Jul 28, 2012 at 15:12
@BazzyTK Allow me to do the honors. I included it in my answer too, but attributed Michael for pointing it out.
– ATaylor
Commented Jul 28, 2012 at 15:13

Add a comment |

rsz · Accepted Answer · 2012-07-28 15:09:01Z

Run the script with this code and post mysql_error

<?php
$act = $_POST['act'];
if($act == 1) {
$m = $_POST['message'];
$m = strip_tags($m);
$message = mysql_real_escape_string($m);
$name = "Anonymous"; //Static username for demonstration purposes
$date = "2012-7-28"; //Static date for demonstration purposes

$con = mysql_connect("localhost","username","password");
if (!$con){die('Could not connect: ' . mysql_error());}

mysql_query("INSERT INTO comments (name, message, date) VALUES ('$name', '$message', '$date')") or die(mysql_error());
mysql_close($con);
}
?>

<form action="comments.php" method="post"> 
<input type="text" name="message">
<input type="hidden" name="act" value="1">
<input type="submit" name="submit" value="Submit">
</form>

UODATE> The working code is follows:

<?php
$act = $_POST['act'];
if($act == 1) {
$m = $_POST['message'];
$m = strip_tags($m);
$message = mysql_real_escape_string($m);
$name = "Anonymous"; //Static username for demonstration purposes
$date = "2012-7-28"; //Static date for demonstration purposes

$con = mysql_connect("localhost","username","password");
mysql_select_db('databasename');
if (!$con){die('Could not connect: ' . mysql_error());}
mysql_query("INSERT INTO comments (name, message, date) VALUES ('$name', '$message', '$date')") or die(mysql_error());
 mysql_close($con);
}
?>

<form action="comments.php" method="post"> 
<input type="text" name="message">
<input type="hidden" name="act" value="1">
<input type="submit" name="submit" value="Submit">
</form>

@BazzyTK: doesn't that error sound fairly self-explanatory? (Another bonus, PDO can be configured to throw exceptions on errors, no need to sprinkle or die(mysql_error()) everywhere.) — user1233508, Commented Jul 28, 2012 at 15:03
@BazzyTK: right, you're not selecting the DB. Use mysql_select_db. Also, as DCoder suggests you should refrain to use mysql_* functions from new code. Use PDO instead. — nico, Commented Jul 28, 2012 at 15:03

Collectives™ on Stack Overflow

SQL Query doesn't get executed

3 Answers 3

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Related