Update all rows with different values

[dan2684]

Hello all,

 

I'm going to update the display order of a bunch of products in a category (by the way, if I'm doing this wrong, please tell me!).

 

I have an array which is consists of all the "position" fields in the correct order. I want to update all the relevant rows in one statement... something like this:

 

"UPDATE products SET position = $positions[CURRENT_INCREMENT] WHERE catId = $catId"

 

CURRENT_INCREMENT being the amount of rows it's currently updated... which obviously doesn't exist because I just made it up! I can't seem to find anything like this online. Any ideas?

 

Thanks in advance,

 

Dan

[dan2684]

I'm sort of  getting somewhere... although I realise now that this probably isn't the right approach, as I'm never going to be able to call a different part of the array using a SQL variable... damn it! Any help would be greatly appreciated...

 

$sql = "SELECT @i:=0";

if(!mysql_query($sql)) die(mysql_error());

$sql = "UPDATE products SET position = $positions[@i:=@i+1] WHERE catId = $catId";

if(!mysql_query($sql)) die(mysql_error());

[Pikachu2000]

It isn't completely clear what you're after, so if this isn't it, please clarify the question. If you want to update values in the database to values in an array, here's one way to handle it. Use the record's PK ID as the array key, and the value as the array's value (you may need to restructure the array). Then loop the array to do the UPDATE query. I haven't added any logic to check for or handle errors.

 

$array = (245 => 'value for 245', 1313 => 'value for 1313', 4687 => 'value for 4687',  219 => 'value for 1313');

foreach( $array as $k => $v ) {
     $query = "UPDATE table SET field = '$v' WHERE pk_id = $k";
     $result = mysql_query( $query );
}

[dan2684]

I do want to update the values in a database using the values in an array. The thing is, it's going to be hundreds of records at a time and I don't want to query the database hundreds of times in a loop like you have in your example. Is there a way to use an incrementing value in one query?

[Pikachu2000]

I can't think of another way to do it ATM, but maybe one of the SQL gurus like Fenway will pop in and have a suggestion.

[dan2684]

Cool, well thank you for your time - I appreciate it! :-)

[PFMaBiSmAd]

There are two ways of performing a mult row update -

 

1) Use an UPDATE query with CASE ... ELSE ... END logic to supply the SET column = value for each different row,

 

2) Use a multi-value REPLACE query.

 

Do you have a specific example of what you are trying to accomplish (I don't believe your sample query with a single catId value.)

[dan2684]

Hi, thanks for your response.

 

I want to take these values...

 

$positions = array(3,1,2);

 

...and put them into the position field...

 

$sql = "UPDATE products SET position = ??????? WHERE catId = $catId";

 

...but I don't want to smash the database with hundreds of queries in a for loop. (PS the array will be much larger than the example above).

[PFMaBiSmAd]

The example query you are posting in an attempt to show us what you are trying to do is meaningless as an example to show us what you are trying to do because the WHERE clause only refers to ONE row. You would be updating the same row over and over. You would only have the last value in the row after the code executes.

 

Do you have an actual example of what you are trying to accomplish that shows which piece of data gets updated into which row?

[dan2684]

OK, I understand where the confusion lies now.

 

The catId field is NOT a unique id for each row. catId refers to the id of the category that the product sits in. So, for example, that query might be, "update all products in the 'mens' category".

 

This is not a made up example, this is my code!! The only part I made up is the array because it comes from a query string and there's hundreds of products in there... but it will look exactly like that, just larger.

[dan2684]

I mean, the answer might simply be to loop through the array and run a different query each time - I don't actually know, hence the reason I am asking! Is it that bad to do that? I always thought it was better to try and get everything into one query - please tell me if I'm wrong...

[PFMaBiSmAd]

According to your thread title and this -

I'm going to update the display order of a bunch of products

 

You are trying to UPDATE multiple rows with new values. In order to do that, you must identify in the update query which row to update with which value.

 

For an UPDATE query inside of a loop, the WHERE clause would indicate which row each UPDATE query operates on (see the code that Pikachu2000 posted.) For my suggested method #1 (I'm not even going to attempt to show a query using suggested method #2), you would dynamically build a query that looks like -

 

UPDATE products SET position = CASE WHEN id = 245 THEN 2 WHEN id = 1313 THEN 1 ... repeat for each id/value ... ELSE position END WHERE catId = $catId

 

Each WHEN id = x THEN y term associates the id of the row with the new value to put into that row. The ELSE position term is so that row(s) that you don't supply a new value for get updated with their existing value. The WHERE term is not actually needed, but I left it in the query so that the query does not evaluate every row in your table.

[dan2684]

I think my question is being completely misunderstood - I realise this is probably due to a bad explanation on my part.

 

Thank you for your advice, I'll mark this as solved and go with building a dynamic query string using the products' PK to determine which row to update. In this instance, in your opinion, is there any point in even appending the WHERE clause? It seems irrelevant because we already know exactly which rows are going to be updated (using the PK).

 

Just in case there's the possibility of anyone making sense of my ramblings, I'll just have one last attempt in explaining...

 

- The array holds values that I intend to update in specific "position" fields in my "products" table.

- The arrays' values appear in the exact same order as their respective rows in the "products" table.

- When using the WHERE clause, the amount of affected rows will be the exact same length as the values in the array (I know this because the array only holds values for that specific group of products).

 

The idea is to get a query running through the table updating the "position" fields in the table using the each value from the array. I am basically putting my trust in the fact that the current amount of affected rows (while the SQL query is executing) will be the exact key to use in the array, due to it being the exact same length and the values being in the exact order that the "products" appear in the table.

 

Thanks again for your time! :-)