Welcome, Guest: Register On Nairaland / LOGIN! / Trending / Recent / New
Stats: 3,143,383 members, 7,781,089 topics. Date: Friday, 29 March 2024 at 08:42 AM

Php Mysql: Use Php To Serialize Mysql Table - Programming - Nairaland

Nairaland Forum / Science/Technology / Programming / Php Mysql: Use Php To Serialize Mysql Table (833 Views)

You Can Use Php For That Chat Application Instead Of Expensive Node.js / 8 Reasons To Use Php In 2020 / Learn PHP & MYSQL The Fast Way. (2) (3) (4)

(1) (Reply) (Go Down)

Php Mysql: Use Php To Serialize Mysql Table by jimmynauty: 5:00pm On Dec 11, 2019
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'"wink;
$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
Re: Php Mysql: Use Php To Serialize Mysql Table by IceColdVeins(m): 5:12pm On Dec 11, 2019
Normal stuff wink
Re: Php Mysql: Use Php To Serialize Mysql Table by jimmynauty: 5:22pm On Dec 11, 2019
IceColdVeins:
Normal stuff wink

any suggestion will be helpful thanks
Re: Php Mysql: Use Php To Serialize Mysql Table by Capnd143(m): 5:39pm On Dec 11, 2019
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'"wink;
$stmt->execute();

}

thanks



maybe I dont understand your question, but why didn't you just make the column an autoincrementing primary key?
Re: Php Mysql: Use Php To Serialize Mysql Table by jimmynauty: 5:49pm On Dec 11, 2019
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.
Re: Php Mysql: Use Php To Serialize Mysql Table by jimmynauty: 6:10pm On Dec 11, 2019
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
Re: Php Mysql: Use Php To Serialize Mysql Table by ANTONINEUTRON(m): 6:22pm On Dec 11, 2019
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
Re: Php Mysql: Use Php To Serialize Mysql Table by jimmynauty: 6:55pm On Dec 11, 2019
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'
Re: Php Mysql: Use Php To Serialize Mysql Table by ANTONINEUTRON(m): 8:40pm On Dec 11, 2019
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.

(1) (Reply)

Are These Specs Good Enough For Python? / Java Implementation Of Binomial Regression Model In A Soccer Predictn App / Neco, Waec And Nabteb Api

(Go Up)

Sections: politics (1) business autos (1) jobs (1) career education (1) romance computers phones travel sports fashion health
religion celebs tv-movies music-radio literature webmasters programming techmarket

Links: (1) (2) (3) (4) (5) (6) (7) (8) (9) (10)

Nairaland - Copyright © 2005 - 2024 Oluwaseun Osewa. All rights reserved. See How To Advertise. 20
Disclaimer: Every Nairaland member is solely responsible for anything that he/she posts or uploads on Nairaland.