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?

PhpMyAdmin divide your queries and execute them separately

Your Common Sense
– Your Common Sense

2010-09-10 17:47:29 +00:00
Commented Sep 10, 2010 at 17:47 — Your Common Sense
– Your Common Sense, Commented Sep 10, 2010 at 17:47

Mischa · Accepted Answer · 2010-09-10 17:31:20Z

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());
}

answered Sep 10, 2010 at 17:31

Mischa

43.4k9 gold badges101 silver badges111 bronze badges

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

2 Comments

MacMac Over a year ago

Aw... so how can I execute multiple queries?

Mischa Over a year ago

By executing each query separately. Or use sled's suggestion.

sled · Accepted Answer · 2010-09-10 17:32:06Z

1

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

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

answered Sep 10, 2010 at 17:32

sled

14.6k3 gold badges46 silver badges70 bronze badges

Comments

Your Common Sense · Accepted Answer · 2010-09-10 17:44:54Z

1

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.

answered Sep 10, 2010 at 17:44

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:30:24Z

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: Overwriting data in a MySQL table. 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.

Collectives™ on Stack Overflow

Executing MySQL Query from PHP error

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related