Page 1 of 1

Maintaining order

Posted: Mon Aug 29, 2005 3:01 am
by s.dot

Code: Select all

$getlistofnamesquery = mysql_query("SELECT whoviewed FROM viewedprofile WHERE whosprofile = '$u' ORDER BY id desc LIMIT 25");
$namesnumber2 = mysql_num_rows($getlistofnamesquery);
if($namesnumber2 < 1)
{
	echo "Not Available";
} ELSE 
{
	while($getnamesarray17 = mysql_fetch_assoc($getlistofnamesquery))
	{
		$names[] = $getnamesarray17['whoviewed'];
	}
	$namelist = implode("','",$names);
	$online = mysql_query("SELECT username, user_last_visit FROM users WHERE username IN ('$namelist')") or die(mysql_error());
	while($onlinearray = mysql_fetch_assoc($online))
	{
		$timeonline[$onlinearray['username']] = $onlinearray['user_last_visit'];
	}
	$timediff = time()-300;
	$i=1;
	foreach($timeonline AS $username => $time)
	{
		if($time > $timediff)
		{
			echo "<small>$i.</small> <a href=\"showme.php?u=$username\"><font color=\"#FF0000\">$username</font></a>, ";
		} ELSE
		{
			echo "<small>$i.</small> <a href=\"showme.php?u=$username\">$username</a>, ";
		}
		$i++;
	}
}
When I make the $timeonline array ($username => $time), I need the keys to be sorted in order of the original query, which is "order by id desc limit 25"

how do I go about maintaining that order?

Posted: Mon Aug 29, 2005 7:09 am
by feyd
usort(). Create your own function that compares the element you want to sort on and return like the sorting system wants: -1, 0, 1 (less than, equal to, greater than; respectively)

Posted: Mon Aug 29, 2005 7:10 am
by s.dot
Okay, so I finally figured it out, but SURELY there must be a better way.

Basically what I'm trying to do is have the ouput result (in the foreach) have the same order as the first query

Code: Select all

// This is the original query to select the id, and whoviewed
$getlistofnamesquery = mysql_query("SELECT id,whoviewed FROM viewedprofile WHERE whosprofile = '$u' ORDER BY id desc LIMIT 25");
$namesnumber2 = mysql_num_rows($getlistofnamesquery);
if($namesnumber2 < 1)
{
	echo "Not Available";
} ELSE 
{
                // Here I'm storing the username (whoviewed) and id into an array
                // with the username as the KEY and the id as the VALUE

	while($getnamesarray17 = mysql_fetch_assoc($getlistofnamesquery))
	{
		$names[$getnamesarray17['whoviewed']] = $getnamesarray17['id'];
	}

                // $names2 is simply selecting the usernames (the keys) for use in the sql query
	$names2 = array_keys($names);
	$namelist = implode("','",$names2);
	$online = mysql_query("SELECT username, user_last_visit FROM users WHERE username IN ('$namelist')") or die(mysql_error());

                // Now I'll store the results of the above query in an array with the id from $names as the KEY and the time_last_active as the VALUE
	while($onlinearray = mysql_fetch_assoc($online))
	{
		$timeonline[$names[$onlinearray['username']]] = $onlinearray['user_last_visit'];
	}

                // Now I want to sort the keys from highest to lowest
                // ksort will do the opposite
	ksort($timeonline, SORT_NUMERIC);

                // so reverse it, and I'll have it in the order I want
	$highesttolowestkeys = array_reverse($timeonline, TRUE);

                // now I want the ID as KEYS and usernames as VALUES
	$names3 = array_flip($names); 
	$timediff = time()-300;
	$i=1;

                // in the foreach I will select the value of the ID in $names3 (which is the username)
	foreach($highesttolowestkeys AS $id => $time)
	{
		if($time > $timediff)
		{
			echo "<small>$i.</small> <a href=\"showme.php?u=".$names3[$id]."\"><font color=\"#FF0000\">".$names3[$id]."</font></a>, ";
		} ELSE
		{
			echo "<small>$i.</small> <a href=\"showme.php?u=".$names3[$id]."\">".$names3[$id]."</a>, ";
		}
		$i++;
	}
The whole purpose of doing this this way was to avoid having a query in a while loop. Is there a better way to go about this? (It gave me a headache figuring it out this way) Would I be better off using a query inside a while loop (that will iterate 25 times) ? (ideally this is saving 24 queries)

Currently, it works pretty good. I just feel like I did it buttbackwards.

*sigh* hope I explained this good.

Posted: Mon Aug 29, 2005 7:30 am
by feyd
you could combine the two queries you have, using a join in some fashion. That would directly tie the two tables together.

Posted: Mon Aug 29, 2005 7:32 am
by s.dot
I've never used a JOIN before. Perhaps I should read some documentation on it.

Posted: Mon Aug 29, 2005 7:34 am
by Chris Corbyn
Yep... if you post the SQL structure we can help further ;)

Joins are something you will need to get to grips with in MySQL to design the best databases (and to greatly cut down on code).

Posted: Mon Aug 29, 2005 7:40 am
by feyd
this should give some nice results:

Code: Select all

SELECT
  v.id AS `viewedprofile_id`,
  v.whoviewed,
  u.user_last_visit
FROM
  viewedprofile v
INNER JOIN
  users u
  ON
  (
    u.username = v.whoviewed
  )
WHERE
  v.whosprofile = '{$u}'
ORDER BY
  v.id DESC
LIMIT 25

Posted: Mon Aug 29, 2005 7:47 am
by s.dot
The current script execution time for that particular part of the code (that I wrote) is 0.026699066162109
Is this bad?

I will give the join query a try and test it's execution time as well.

Posted: Mon Aug 29, 2005 7:47 am
by feyd
that seems a bit slow, but hinges very heavily on how php has been installed, RAM, HD speed, yadda yadda.

Posted: Mon Aug 29, 2005 7:49 am
by Chris Corbyn
scrotaye wrote:The current script execution time for that particular part of the code (that I wrote) is 0.026699066162109
Is this bad?

I will give the join query a try and test it's execution time as well.
I'd guess you can get that much much lower (more than twenty times - Spec dependant)

Posted: Tue Aug 30, 2005 12:44 am
by s.dot
Hmm, I executed the code I wrote and the script execution time was

0.026699066162109

Then I wrote this code using the JOIN

Code: Select all

$result = mysql_query("SELECT v.id AS `viewedprofile_id`, v.whoviewed, u.user_last_visit FROM viewedprofile v INNER JOIN users u ON ( u.username = v.whoviewed )WHERE v.whosprofile = '{$u}' ORDER BY v.id DESC LIMIT 25") OR die(mysql_error());
$timediff = time()-300;
$i=1;
while($array = mysql_fetch_assoc($result))
{
	if($array['user_last_visit'] > $timediff)
	{
		echo "<small>$i.</small> <a href=\"showme.php?u=".$array['whoviewed']."\"><font color=\"#FF0000\">".$array['whoviewed']."</font></a>, ";
	} ELSE
	{
		echo "<small>$i.</small> <a href=\"showme.php?u=".$array['whoviewed']."\">".$array['whoviewed']."</a>, ";
	}
	$i++;
}
And that script execution time is: 0.45380616188049

Perhaps I'm doing something wrong :P I was under the impression that the JOIN would be faster than my code in my previous posts. So is my code using the join a bit messed up in some way? Could it be server conditions slowing the code down? Or should it be slower?

Thanks =)

Posted: Tue Aug 30, 2005 12:55 am
by feyd
join's aren't all that fast, as they require a lot of computation usually, but table structure, server settings and such are all play into the speed as well.

The join here is more to simplify your end work that you wanted to do.

Posted: Tue Aug 30, 2005 12:58 am
by s.dot
Ah, well in the end, I guess what I'm after is speed (code readability is great, so I'll make sure to make good comments)

The simplicity using the JOIN was great though :-D

but 0.02 vs. 0.4, I'll have to stick with the one that is 20 times faster :-D

Thanks a lot for all of your help!

Posted: Tue Aug 30, 2005 8:44 am
by nielsene
Try use "EXPLAIN" on the query in the database. The JOIN method should be faster due to a reduction in query setup/transfer overhead. Perhaps its not using an index on the join, etc.