PHP mysql table Join problems

[sford999]

Hi,

 

I'm trying to do the following:

 

Get the last 3 visitors from the "Visits" table as defined by the unix timestamp,  and display some member info from the members table. However, its doesn't seem to be displaying correctly as the last 3 "visitor_uid" are say "55,56,57" but the page is displaying random ID's and not the last 3

 

$uid is defined previously and is the logged in users ID from the members table.

 

<?php

$sql = "SELECT v.visited_uid, v.time, m.username, m.age, m.sex, m.country, m.img_id, m.last_login FROM visits as v, members as m WHERE v.visited_uid = '$uid' ORDER BY v.time DESC LIMIT 3";

		$res = mysql_query($sql) or die(sql_error(mysql_error(), $sql));
		while($row = mysql_fetch_array($res))
		{
		echo '<table width="100%" border="0">
		<tr>
		<td width="10%" class="none"><img src="users/'.$row['img_id'].'" alt="" /></td>
		<td width="90%" valign="top" class="none">';

		if($row['time'] <= time()-86400)
		{
		echo '<img src="img/new.gif" />';
		}
		elseif($row['time'] <= time()-1800)
		{
		echo '<img src="img/online.gif" alt="Member Online" />';
		}

		echo '<br />
		<span class="inter"><a href="profile.php?uid='.$row['id'].'"><strong>'.$row['username'].'</strong></a><br />
		<strong>'.$row['age'].', '.$row['sex'].'Sex</strong> from <strong>'.$row['country'].'Country</strong><br />
		<sub>';

		echo format_time($row['time']);

		echo '</sub></span></td>
		</tr>
		</table>';
		}
?>

[Mardoxx]

http://stackoverflow.com/questions/991198/how-to-select-an-item-the-one-below-and-the-one-above-in-mysql

 

looks quite difficult...

[avvllvva]

Why you are using the where condition

 WHERE v.visited_uid = '$uid'

??

 

You want to display the last 3 records from visit table and its corresponding member details , correct?

 

if  correct , then

WHERE v.visited_uid = m.user_id 

.

 

 

[sford999]

$uid is the member who is viewing their own page which this is displayed on.

 

eg, my $uid = 5 and I only want to see the last 3 people who have viewed my profile.

 

If I use m.user_id then it will show me people who have viewed other peoples profiles and not those that have viewed my profile.

[avvllvva]

okay.... then what's the type of time-field ?

[sford999]

Heres the visits table

 

CREATE TABLE IF NOT EXISTS `visits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visited_uid` varchar(25) NOT NULL,
  `visitor_uid` varchar(25) NOT NULL,
  `time` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=61 ;

 

And the members table

 

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(25) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `salt` varchar(20) NOT NULL,
  `sex` varchar(1) NOT NULL,
  `age` varchar(20) NOT NULL,
  `country` varchar(2) NOT NULL,
  `city` varchar(255) NOT NULL,
  `has_img` varchar(1) NOT NULL DEFAULT '0',
  `img_id` varchar(255) NOT NULL DEFAULT 'no_image.gif',
  `last_login` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=134 ;

[avvllvva]

change from varchar to bigint (`time` varchar(25) NOT NULL ) .... maybe of that !!

[sford999]

Nope no change

[avvllvva]

Try to order by autoincrement field (primary key). if it will order properly, then your timestamp value have some errors. Else your query may incorrect.

 

And i can suggest that field as DATETIME datatype.