Update multiple records in a loop problem...

[handle87]

Hi,

I am having a problem updateing multiple records at a time in a loop... in a MySQL database. I am trying to run an UPDATE query about 5 times in a while loop... I have printed the strings to the screen and am able to run thm directly on the MySQL database... but they do not work in code?

Has anyone had this problem before?

 

[kenrbnsn]

Without seeing your code, it's hard to debug your problem.

Ken

 

[handle87]

Here is the code:

	//MySQL DB CONNECTION
    $DB_MYSQL2 = new DB_MYSQL(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB);
    $DB_MYSQL2->connect();

	//GET ALL THE ACTIVE TESTIMONIALS AND UPDATE THE TESTIMONIAL ORDER
	$strSQL = "SELECT * FROM WebsiteTestimonials WHERE CustomerID = " . $_SESSION['CustomerID'] . " AND WebsiteID = " . $_SESSION['WebsiteID'] . " AND StatusID = 1 ORDER BY OrderID";
	$DB_MYSQL2->open_query("sort", $strSQL);
	while($row_order = $DB_MYSQL2->get_row("sort")){
		if($row_order['OrderID'] < $position){
			$Order = $row_order['OrderID'];
		}elseif($row_order['OrderID'] >= $position){
			$Order = $row_order['OrderID'] + $beforeAfter;
		}
		$strT .= "UPDATE WebsiteTestimonials SET OrderID = " . $Order . " WHERE CustomerID = " . $_SESSION['CustomerID'] . " AND WebsiteID = " . $_SESSION['WebsiteID'] . " AND TestimonialID = " . $row_order['TestimonialID'] . ";";
	}
	$DB_MYSQL2->open_query("update_testimonial", $strT);

A class has been created to to handle the db connection and recordets etc... it works fine for selecting and insert/updateing... but i get get this to work in a loop... if i print out $strT and run it on the MySQL server directly it runs as it should.. any ideas?

 

[kenrbnsn]

If you're using the mysql_query() function, it looks like the query_string can not contain more than one query.

From

http://www.php.net/mysql_query

query

A SQL query

The query string should not end with a semicolon.

Do you get any errors when executing your query?

Ken

 

[handle87]

Thank you for your time... I found out that my coworker had made changes to the class, that I didn't notice and decided not to inform anyone. Sorry to bother for nothing.

Have a good one!