Jump to content


Photo

Update multiple rows


Best Answer Barand, 05 May 2013 - 05:54 PM

I meant to post this - you could do the whole table with a single query with

mysql_query("UPDATE `oc_product`
        SET image = CONCAT('data/Products/' , model, '.jpg')
        WHERE image = '' ");
Go to the full post


  • Please log in to reply
3 replies to this topic

#1 Fearpig

Fearpig

    Advanced Member

  • Members
  • PipPipPip
  • 195 posts

Posted 05 May 2013 - 05:00 PM

Hello,
I need help updating multiple rows at once
 
Table Columns:
Model_ID, Image

 

I want to update all rows where the image field is empty to have a value of:

'data/Products/' . $model . '.jpg'

I can update one row at a time but I want to upload things in a batch!

I've written a select page

<html>
<body>

<?php

//Conntect to Database
include 'Database_Connection.php';

if ($db_found) { 
	//print "<i>Database Found (" . $db_handle.")</i></br></br>"; 

	$SQL = "SELECT * FROM `oc_product` WHERE image = ''";
	$result = mysql_query($SQL);

	$x = 1;
	
	echo "<table class='Data'><thead><tr><th>Model</th><th>Image</th><th>Image Should Be...</th></tr></thead><tbody>";
	
	while ($db_field = mysql_fetch_assoc($result)){
		
		$model = $db_field['model'];
		$image = $db_field['image'];
		$image_should_be = 'data/Products/' . $model . '.jpg';

		if ($x < 0){ $Table_Row = 'Row_Style1'; }
		else { $Table_Row = 'Row_Style2'; }
			
		print "
		<tr class='$Table_Row'>
		<td>$model</td>
		<td>$image</td>
		<td>$image_should_be</td>
		</tr>";

		$x = $x * (-1);

		}

	echo "</table>";
	
	}
	
else { echo "Whoops!"; }

echo "<a href=Update_Images.php>Update</a>";

?>

</body>
</html> 

My database connection is fine as the user has the correct permissions but when I try to update the images I'm getting something wrong and the records are not updating or giving me an error message.

My idea was to loop through the records using a select query and then update each record.

<html>
<body>

<?php

//Conntect to Database
include 'Database_Connection.php';

if ($db_found) { 
	//print "<i>Database Found (" . $db_handle.")</i></br></br>"; 

	$SQL = "SELECT * FROM `oc_product` WHERE image = ''";
	$result = mysql_query($SQL);
	
	while ($db_field = mysql_fetch_assoc($result)){
		
		$model = $db_field['model'];
		echo "$model<br>";
		
		$image_should_be = 'data/Products/' . $model . '.jpg';	
		$SQL_UPDATE = "UPDATE `oc_product` SET image = '$image_should_be' WHERE model = $model";
		$result_update = mysql_query($SQL_UPDATE);
		}
	
	}
	
else { echo "Whoops!"; }

echo "<a href=test.php>Test</a>";

?>

</body>
</html> 

I added in the...

echo "$model<br>";

...just to see if the page was doing anything and I now get a list of model numbers but no updates in the table.


"Whats wrong with the cat?" - Mrs Schrödinger

#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,885 posts
  • LocationCheshire, UK

Posted 05 May 2013 - 05:13 PM

$image_should_be = 'data/Products/' . $model . '.jpg';

mysql_query("UPDATE `oc_product` SET image = '$image_should_be' WHERE image = '' ");

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#3 Fearpig

Fearpig

    Advanced Member

  • Members
  • PipPipPip
  • 195 posts

Posted 05 May 2013 - 05:43 PM

Thank you Barand! I've been looking at that for hours scratching my head, tested and all working.


"Whats wrong with the cat?" - Mrs Schrödinger

#4 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,885 posts
  • LocationCheshire, UK

Posted 05 May 2013 - 05:54 PM   Best Answer

I meant to post this - you could do the whole table with a single query with

mysql_query("UPDATE `oc_product`
        SET image = CONCAT('data/Products/' , model, '.jpg')
        WHERE image = '' ");

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com