Numbering mysql results from 1-6

[nextman]

hi, i need some help with numbering rows in a mysql table after i select them. ill be brief, im going to run a mysql query that selects the 6 latest articles from my database , id like to assign a number to each of the rows it selects. ie, it pulls 6 rows and the first row is $1, the second row is $2 and the third row is $3 etc. any help would be appreciated

[JAY6390]

run the query, then use

$i = 0;
$res = array();
while($row = mysql_fetch_assoc($result)) {
    $res[++$i] = $row;
}

Then to use a row, just use

echo $row[3]['id] for the third result's id value etc

[roopurt18]

You can get MySQL to number them for you:

 

set @seq = 0;

select @seq := @seq + 1 as thecount, login from wv_user;

[JAY6390]

Could you explain what is going on at the start of that please roopurt18. I've never seen anything like that before. Also, I understand the incrementer but what is the : for in the := ???

 

Thanks

Jay

[roopurt18]

You can use variables in MySQL.

 

Before you run the query, you create a variable named @seq and initialize it to 0.

set @seq = 0;

 

Then you issue the query:

select @seq := @seq + 1 as thecount, login from wv_user;

 

For each row, MySQL will add 1 to the current value of @seq.  It will return this value in the dataset.  It also updates @seq variable to this incremented value, so the next row will increment one further.

 

It's essentially the same as:

 

<?php
$inc = 0;
$items = array( 'a', 'b', 'c', 'd' );
foreach( $items as $item ) {
  echo ($inc = $inc + 1) . ' ' . $item . "\n";
}

[JAY6390]

I see, so the @ is to define the variable to begin with (seq in this example), and then the : means it executes it each iteration of getting a row?

[nextman]

thanks guys, im trying to use the first bit of code you suggested. heres what i got:

 

//	Get latest articles
$sql = "SELECT * FROM art_articles	WHERE status = '1' ORDER BY date DESC LIMIT 6";

$result = mysql_query($sql);


$i = 0;
$res = array();
while($row = mysql_fetch_assoc($result)) 
{
    $res[++$i] = $row;
}

$row1id = $row[1]['id'];
$row2id = $row[2]['id'];
$row3id = $row[3]['id'];
$row4id = $row[4]['id'];
$row5id = $row[5]['id'];
$row6id = $row[6]['id'];

 

but i cant seem to get it to work. is there something wrong with my code?

[JAY6390]

You don't need to assign them, they're already assigned. What is it you are trying to do with the data?

[nextman]

im trying to display the 6 articles in dofferent locations on the same page. im trying this now but its not displaying the article ids:

 

	$sql = "SELECT * FROM art_articles WHERE status = '1' ORDER BY date DESC LIMIT 6";

$result = mysql_query($sql);


$i = 0;
$res = array();
while($row = mysql_fetch_assoc($result)) 
{
    $res[++$i] = $row;
}


echo $row[1]['id'];
echo $row[2]['id'];
echo $row[3]['id'];
echo $row[4]['id'];
echo $row[5]['id'];
echo $row[3]['id'];

 

:s

[Felex]

i saw the usage error for variables $row[1]['id'] strip ['number'] from variable name. change them like $row[1], $row[2],...

[JAY6390]

use

echo $row[number]['columnname'];

wherever in your code you want it. So if you want the address column of the third record you would use

echo $row[3]['address'];

 

[JAY6390]

oops it should be $res not $row for the echos