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);
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);
>>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.
"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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...
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...