Executing MySQL Query from PHP error

Question

This part of my code creates a multiple query by this:

$sql = "";

$sql .= "INSERT INTO projects
        (project_id, project_name, project_description, project_deadline, project_status, project_priority)
        VALUES ('" . $project_id . "', '" . $name . "', '" . $description . "', '" . $final_deadline . "', '" . $status . "', '" . $priority . "');";

foreach($assignments as $assigned_user)
{
    $sql .= "INSERT INTO assignments 
             (user_id, project_id, assigned_date) 
             VALUES ('" . $assigned_user . "', '" . $project_id . "', '" . time() . "');";
}

$result = mysql_query($sql) or die(mysql_error());

However, I get an error:

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 assignments (user_id, project_id, assigned_date) ' at line 3

However, if I echo $sql it turns out something like this:

INSERT INTO projects (project_id, project_name, project_description, project_deadline, project_status, project_priority) VALUES ('7090', 'Sup :D', 'OMG!', '716770800', '1', '1');
INSERT INTO assignments (user_id, project_id, assigned_date) VALUES ('12', '7090', '1284139311');
INSERT INTO assignments (user_id, project_id, assigned_date) VALUES ('11', '7090', '1284139311');

THEN I manually execute the SQL into PhpMyAdmin, it works fine, but how come it doesn't work when PHP executes it and outputs an error?

Answer 1

mysql_query can only execute one query at a time

Try this:

$sql = "INSERT INTO projects
        (project_id, project_name, project_description, project_deadline, project_status, project_priority)
        VALUES ('" . $project_id . "', '" . $name . "', '" . $description . "', '" . $final_deadline . "', '" . $status . "', '" . $priority . "');";
$result = mysql_query($sql) or die(mysql_error());

foreach($assignments as $assigned_user)
{
    $sql = "INSERT INTO assignments 
             (user_id, project_id, assigned_date) 
             VALUES ('" . $assigned_user . "', '" . $project_id . "', '" . time() . "');";
    $result = mysql_query($sql) or die(mysql_error());
}

Answer 2

Hi,

multiple queries is not supported by the mysql_query function. Check mysqli::multi_query

http://php.net/manual/en/mysqli.multi-query.php

Answer 3

My guess is that mysql_query function won't allow execution of multiple statements, each statement will need to be executed individually.

Looking at the bigger picture though, please consider the following posting: http://stackoverflow.com/questions/3228714/overwriting-data-in-a-mysql-table/3228839#3228839. In summary, building up queries by concatenating strings as you are doing is a recipe for disaster, you will be vulnerable to SQL injection attacks and other bugs. Consider using the PDO abstraction layer instead of calling mysql specific functions and look into using parameterised queries instead of building strings as you are doing.

This is a standard mistake that beginners make (not helped by the copious examples online and in books giving poor guidance) so don't feel bad about it if you're a beginner, just bear in mind that you should not use this approach in production systems.

Answer 4

learn SQL bro, it rules ;)

$sql = "INSERT INTO assignments 
             (user_id, project_id, assigned_date) ";

foreach($assignments as $assigned_user)
{
  $sql.= " VALUES ('" . $assigned_user . "', '" . $project_id . "', '" . time() . "') ";
}

I hope you have all your variables escaped.