Update table with increment value

Question

Im trying to update a value in a table, using while loop, but i want this value to be like using auto_inc key.

My table: ID / CAR_ID / IMG_KEY / IMAGE

i want to take 12 rows of the same CAR_ID and give the IMG_KEY values from 1-12.

I tried the below loops, but the result is giving the IMG_KEY the value 1

$getImages = mysql_query("SELECT * FROM more_images WHERE car_id = '$car_id'") or die(mysql_error());  

$img_key = 0;
    for ($img_key = 1; $img_key <= mysql_num_rows($getImages); $img_key ++) {
        while ($selectedImages = mysql_fetch_assoc($getImages)) {
            $update = mysql_query("UPDATE `more_images` SET `img_key` = '$img_key' WHERE `car_id` = '$car_id'") or die(mysql_error());
        }
    }

The goal is give to the following 12 rows img_key values from 1 to 12 and all the other values as they are.

enter image description here

Do you mean this? http://stackoverflow.com/questions/973380/sql-how-to-increase-or-decrease-one-for-a-int-column-in-one-command — Marijke Luttekes, Jan 01 '14 at 15:12
As `$car_id` doesn't changes, you are updating always the same row. — MillaresRoo, Jan 01 '14 at 15:15
You seem to be looping through selected rows to update them based on data in each row. Please post the SQL you use to select these rows as it looks like it can be done with the one SQL call and no looping over results. — Popnoodles, Jan 01 '14 at 15:15
Ooh, in your while-loop you're reading all the records returned, which in no way does anything to advance your for-loop. Also, `mysql_*` functions are deprecated, you're best to switch to `mysqli_*` or `PDO`. (Explanation here: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Marijke Luttekes, Jan 01 '14 at 15:19
i updated the question for better explanation. i know about `mysql` decleration. My PHP is old. When i'll update it,i'll replace the `mysql` functions.Not now — John Priestakos, Jan 01 '14 at 15:22
$getImages = mysql_query("SELECT * FROM `more_images` WHERE `car_id` = '$car_id'") or die(mysql_error()); — John Priestakos, Jan 01 '14 at 15:31
@JohnPriestakos would you place that in your question? It allows people reading your question to understand it without having to sift through the comments. — Marijke Luttekes, Jan 01 '14 at 15:34
I've been wondering, how come you get `$img_key = 1` in your PHP, while your database results (in your screenshot) show `img_key = 2`? — Marijke Luttekes, Jan 01 '14 at 15:56

score 1 · Answer 1 · edited May 23 '17 at 12:11

1

Ok, I'm still not 100% sure what you want, but my guess is that you are looking for something like this:

$imgKey = 0;
while ($selectedImages = mysql_fetch_assoc($getImages))
{
   $imgKey++;
   $update = mysql_query("UPDATE `more_images` SET `img_key` = '{$imgKey}' WHERE `car_id` = '{$car_id}'") or die(mysql_error());
}

In your question, your for loop isn't doing anything other than looping, in your case it iterates twelve times.

Since mysql_fetch_assoc($getImages) is a function that loops through all rows in a set of results. So for each iteration of your for loop, it updates all records to have the same $img_key.

Also, really do refrain from using mysql_* functions, they're deprecated. Read this thread: Why shouldn't I use mysql_* functions in PHP?

edited May 23 '17 at 12:11

Community

1
1

answered Jan 01 '14 at 15:32

Marijke Luttekes

1,253
1
12
27

This still returns `img_key` to 1 at all 12 rows. I tried it before posting the question – John Priestakos Jan 01 '14 at 15:43
Ok, I'm at a loss here at the moment. At this point I would use `var_dump()` to determine where my code goes wrong. I'd start with using `var_dump($img_key)` inside your `while` loop, to determine whether that value is what you expect it to be and debug forth until you've found the problem. Sometimes small issues like this are caused by things you expect to go right. – Marijke Luttekes Jan 01 '14 at 15:51
Nothing with var_dump($img_key) – John Priestakos Jan 01 '14 at 16:37

Update table with increment value

1 Answers1