₦airaland Forum

Hello

i have a table below with the following columns sn, pos,league.

I want to number the pos serially as 1, 2, 3 etc like the second image

I used php to call the league, cound the number of rows with brazil, and tried using for loop to update the pos but unfortunately it replace the pos column with the total number of rows of brazil which is 18.

this is my full code

$league = "brazil" ;
include 'mergecos.php';

$sql = "SELECT count(*) FROM sortable where league = '$league'";
$result = $dbh->prepare($sql);
$result->execute();
$number_of_rows = $result->fetchColumn();

echo $number_of_rows." ".$league." </br>";///exit;


//embed code in for loop
for ($x=1; $x <= $number_of_rows; $x++)
{
echo "postion of $league".$x." </br>";//exit;

$stmt = $dbh->prepare("UPDATE sortable SET pos='$x' where league='$league'";
$stmt->execute();

}

thanks

edit

The thirds image shows the result of running the above code i.e column pos is showing 20 instead of sequential number 1,2,3,4,5....18,19,20

Normal stuff

IceColdVeins:
Normal stuff

any suggestion will be helpful thanks

jimmynauty:
Hello

i have a table below with the following columns sn, pos,league.

I want to number the pos serially as 1, 2, 3 etc like the second image

I used php to call the league, cound the number of rows with brazil, and tried using for loop to update the pos but unfortunately it replace the pos column with the total number of rows of brazil which is 18.

this is my full code

$league = "brazil" ;
include 'mergecos.php';

$sql = "SELECT count(*) FROM sortable where league = '$league'";
$result = $dbh->prepare($sql);
$result->execute();
$number_of_rows = $result->fetchColumn();

echo $number_of_rows." ".$league." </br>";///exit;


//embed code in for loop
for ($x=1; $x <= $number_of_rows; $x++)
{
echo "postion of $league".$x." </br>";//exit;

$stmt = $dbh->prepare("UPDATE sortable SET pos='$x' where league='$league'";
$stmt->execute();

}

thanks

maybe I dont understand your question, but why didn't you just make the column an autoincrementing primary key?

Capnd143:
maybe I dont understand your question, but why didn't you just make the column an autoincrementing primary key?

the sn is auto incremental and i dont want that . i want pos column to have a sequential numbering which i tried using update statement and it gave 20 in pos column.

let me try and put it in words.

The table consist of various countries, using brazil as an example.

Brazil has 20 rows (as shown above)
greece has 10 rows
nigeria has 10 rows

so what i want to do is that using php i determine the total rows of brazil which is 20.
then i use a counter (in my case is for loop) to update pos column from 1,2,3 ...20.
Once it reaches 20 loops, it ejects from the loop and count the next country - greece and repeat same.

hope it makes sense

If this code

echo "postion of $league".$x." </br>";//exit;

Returns normal 1,2,3,...20 them it's the prepare statement

$stmt = $dbh->prepare("UPDATE sortable SET pos='$x' where league='$league'"wink;

That is causing problem for you.

Just execute the sql without the prepare() method and see

ANTONINEUTRON:
If this code

Returns normal 1,2,3,...20 them it's the prepare statement

$stmt = $dbh->prepare("UPDATE sortable SET pos='$x' where league='$league'"wink;

That is causing problem for you.

Just execute the sql without the prepare() method and see

Thanks for the suggestion i tried this code from stackoverflow and it worked

SET @rank:=0;
update sortable
set pos=@rank:=@rank+1 where league='brazil'

jimmynauty:
Thanks for the suggestion i tried this code from stackoverflow and it worked
SET @rank:=0; update sortable set pos=@rank:=@rank+1 where league='brazil'

ok.