Solved

PHP MySQL Multiple SQL statements

Posted on 2007-04-01
3
1,233 Views
Last Modified: 2010-05-18
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
Comment
Question by:inghfs
  • 2
3 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18832815
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
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 18832871
>>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
 
LVL 29

Accepted Solution

by:
rdivilbiss earned 500 total points
ID: 18832876
mysqli_multi_query,

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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now