MYSQL UPDATE by looping through

PHP
1

OK guys, I have a problem.

I am used to getting data from a table using mysql_fetch_array() and then iterating through the loop to get what I want from each row etc.

Anyway I now have an array of values and I want to update an entry in every row using data from this array. What is the best way to do this?

I can’t use mysql_fetch_array because I want to alter the DB entries, not a clientside array and I can’t use a for loop because the DB table is not indexed?

Help would be much appreciated.

Thanks

2

Think I need a little bit more information from this.

You want to query the database for the records, modify each, and update them? What adjustment are you making?

3

Not sure what you exactly want, but i think foeach is the answer: PHP: foreach - Manual . Do the update queries inside foreach then for every row. Also might be wise to use transactions if u want to make sure all the rows gets updated at once.

4

Thanks guys,

I don’t really know what I 'm trying to do myself!

Basically in my DB I have an id, an image path and an index.

I am using php to dynamically render the images when the page loads. These images are sortable(jquery) and I also have a button which, using JQuery sortable(serialize), sends the re-organized index information (GET variable array) to the script(calls itself again).

So basically when the script runs on the server, I want to use the new GET variable array to re-order the indexes in the DB before they are displayed again on the page.

I think my problem is that I have only previously worked with scalar GET/POST variables, I have never had to do this type of thing with a vector.

Does that help?

Thanks

Silversurfer

5

No need to reindex the data in the database. Just use an ORDER BY clause in your select query.

6

I have to re-index the db in order to preserve the new image order when the page is visited again. That is why index is one of the table entries.

7

I have been trying things like:


                if (isset($_GET['index'])){
		 $size = sizeof($_GET['index']);
		 $indices = array();
		
		 for($i=0;$i<$size;$i++)
		 {
		  $indices[$i] = $_GET['index'][$i];
	      $sql_default = mysql_query("UPDATE pix SET pic_index ='$indices[$i]'") or die (mysql_error());		
		 }
	 }
8

Ok, I think I understand.
First of all, the easiest way to loop through an array is using foreach (check out the link Tendolla posted).

And you’ll need to know what index is related to what image, don’t you? Right now, you’re updating pic_index for all rows in pix every time. So in the end all pictures will have the last index from the array.

9

Hey yes! That’s it! So how do I stop that from happening?

10

You could send two arrays: one with the pic ids, and the other with the indexes. As long as pics and indexes are in the same order in the arrays, you can then loop through one, and get the corresponding element from the other, and update only the index for that picture id.

11

OK that sounds like a good solution, let me have a quick go and let you know if I can get it working. :wink: