Order Results from queries

[drooh]

Say I want use mysql_num_rows to get a number for a variable that is inside an looping array. then I want to output that in an order. so something like this

$result = mysql_query($sql);

while($row = mysql_fetch_array($result)){

$id = $row['id'];
$sql = "SELECT * FROM `table` WHERE `id` = '$id'";
$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);

echo in order from least to greatest "number = $num_rows";
}

[mikhaill]

What you'd want to do is create an array in which you store the results of the $num_rows so even as simple as

replacing

$num_rows = mysql_num_rows($result);

with

$num_rows[] = mysql_num_rows($result);

then once outside the while loop sort the $num_rows in any fashion you please and then echo it out.

Also, think about using SELECT count(*) FROM `table` WHERE `id` = '$id'"; which will get you the number minus one call....

[drooh]

then once outside the while loop sort the $num_rows in any fashion you please and then echo it out.

Ok, this is what I was thinking but can you give me an example of how to do this?

[mikhaill]

$result = mysql_query($sql);

while($row = mysql_fetch_array($result)){
$id = $row['id'];
$sql = "SELECT * FROM `table` WHERE `id` = '$id'";
$result = mysql_query($sql);
$num_rows[] = mysql_num_rows($result);
}
sort($num_rows);

foreach($num_rows as $item)
{
echo $item;
}

Enjoy..

[drooh]

ok, i think i need more help on this, here is what I am doing. I am creating a way to track page hits using php and mysql. Ultimately I want to be able to see what pages get the most hits and order them with the highest at the top. I'd also like to be able to see page hits in a given time frame, such as jan 08-feb 08.

The pages Im tracking contain video clips. The info I am wanting to store is show number, show name, date accessed, user ip.

I need to know how write this out so that I can show the show number and show name and how many hits it has, also I need it to be ordered with highest at top and the option to show hits through a given time frame. Kinda like the google video accounts do.

[drooh]

this is my db structure
id ¦ num ¦ name ¦ date ¦ time ¦ ip

I would like to be able to output to a table like this

Show-Num Show-Name Plays
384 Silly Show 34
382 Stupid Show 29
375 Smart Show 21
389 New Show 14

Here is my code

$sql = "SELECT DISTINCT `num`,`name` FROM `show`";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
$num = $row['num'];
$name = $row['name'];
$znum[] = $row['num'];
$zname[] = $row['name'];

$sql = "SELECT * FROM `show` WHERE `num` = '$num' && `name` = '$name'";
$result1 = mysql_query($sql);
$hits = mysql_num_rows($result1);
}

rsort($zname);
foreach($zname as $item) {
echo $item;
}

But I have no idea what to do?

I need to get these 3 variables $znum $zname & $hits into an array and then order them by hits.

echo <table>
loop here
<tr><td>$zname</td><td>$znum</td><td>$hits</td></tr>
end loop
</table>

[mikhaill]

D.,

You're complicating the matter, you should really let MySQL figure this out. Why can't you to this:

select count(*), id, num, name from show group by id order by count(*) desc

and then just echo the result of this query. What this query does is return the number of times any give id is in your table along with it's num and name ordered with the id that's repeated the most at the top in descending order. If you want to add a date restriction to it make it

select count(*), id, num, name from show where date >'starting date' and date < 'ending date' group by id order by count(*) desc

[drooh]

can you show me how to echo the output of that query?

man its been a long freakin day !

[drooh]

also im not sure that is what im looking for. I need the total from how entries there are that have name & num in common. for instance if there are two entries for show num 345 with name Silly Show I need that to echo 2 next to it?

I almost had it with the other way using
$a = array($hits,$name,$num);

but I couldnt figure out how to make it add onto the array and then assign the key to be hits!

Man by this point I'm so confused I don't know what to do. This seems like it should be so simple!

[mikhaill]

I'm going to try re-use the loop code you provided above to not confuse the matter ... I haven't used the raw php mysql functions in years (check out Pear DB2 class sometime) so here comes a little guessing.

$result = mysql_query("select count(*), id, num, name from show group by id order by count(*) desc");

while($row = mysql_fetch_array($result)){
//at this point each $row has one row of the results
//if i am not mistaken the key is the name of the column so...

echo $row['id'];
echo $row['name'];
echo $row['count(*)'];
echo $row['num'];
}

you may want to var_dump($row) one time to verify the names of the keys...

[drooh]

i changed the group by to either name or num and it seems to work

select count(*), id, num, name FROM `show` GROUP BY `num` ORDER BY count(*) desc

[drooh]

ohh, and THANKS A TON!