CODEWITHSUNDEEP
phpsql
Dude with a pc
Dude with a pc

Reputation: 23

How can I increment over a field using sql?

Here's my DB

enter image description here

All I want to do is to reset all of the fields with a specific stranica_id to 1 and increment them as I go down the list.

This would be the desired output.

enter image description here

so that every column with a specific value gets incremented if it occurs more than once.

I tried this:

SET @count = 0; UPDATE `events` SET `events`.`tablica_id` = @count:= @count + 1;

but it only works if I execute it in phpmyadmin and doesn't work if I execute it with php. Does anyone know what I'm missing here?

NOTE: The type of field that I'm trying to change is INT (integer), and it doesn't auto-increment.

Upvotes: 2

Views: 54

Answers (1)

Chirag Chhuchha
Chirag Chhuchha

Reputation: 395

If you are using mysqli connection for this than you should use multi_query to achieve your task

<?php
$mysqli = new mysqli('localhost', 'dbUser', 'dbPass', 'dbName');

$sql = 'SET @count = 0;';
$sql .= 'UPDATE `events` SET `events`.`tablica_id` = @count:= @count + 1;';

$mysqli->multi_query($sql);

Upvotes: 1

Related Questions