Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1248
  • Last Modified:

PHP MySQL Multiple SQL statements

I have the below PHP code which dynamically generates a SQL statement which I then want to execute using 'mysql_query' function. For some reason no results are recorded in the database, however the SQL syntax is correct. If I copy and pate the SQL output into mySQL it executes fine. No errors are shown on the PHP page.

Can anyone explain the problem and suggest a possbile solution.

Many Thanks In Advance.

$sql = "INSERT INTO customerorder VALUES(".$OrderId.", ".$customerid.", ".$postageid.", '".date("Y/m/d")."', ".$total.");";

while (list($key, $val) = each($cart)){
      $sql .= " INSERT INTO filmorder VALUES('".$key."', ".$OrderId.", ".$val.");";      
}

mysql_query($sql, $db_connection);
0
inghfs
Asked:
inghfs
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
try to run the mysql_error() function to see any error displayed in the output.
...
mysql_query($sql, $db_connection);
print mysql_error();

now, in regards to your INSERT statements, you should always put the column list of the destination table:


$sql = "INSERT INTO customerorder (OrderID, CustomerID, PostageID, dateField, Total ) VALUES(".$OrderId.", ".$customerid.", ".$postageid.", '".date("Y/m/d")."', ".$total.");";
while (list($key, $val) = each($cart))
{
      $sql .= " INSERT INTO filmorder (KeyField, OrderID, valueField ) VALUES('".$key."', ".$OrderId.", ".$val.");";      
}



you might also print out the generated SQL string, and try that manually in the database (using mysql command line for example).
note however that I am not sure if mysql_query is able to run multiple sql statements without a special connection option set...
0
 
rdivilbissCommented:
>>I am not sure if mysql_query is able to run multiple sql statements without a special connection option set...

"mysql_query() sends an unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier."

No you should execute each statement, then execute the next and so on. You do not have to close and reopen the connection, however.
0
 
rdivilbissCommented:
mysqli_multi_query,

(PHP 5 ONLY) Executes one or multiple queries which are concatenated by a semicolon.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now