Page 1 of 1
code question
Posted: Tue Jan 20, 2004 5:00 pm
by deras
i need to code the following in php.
find the 8 highest mem_id(s) in 'tests' table which have info in the local_icon field in the 'members' table
i basically want to display the most 8 recent users (that have user icons) on one of my pages (there name and icon). each field is in a different table, and i am unaccustomed to grabbing more than one piece of data from a db.
any suggestions would be appreciated.
Posted: Tue Jan 20, 2004 5:03 pm
by dull1554
rather than grabbing one piece of data(i'm assuming you talking about......query(SELECT blah FROM blah)) you do this to select all fields
.query(SELECT * FROM blah) the * tells php to grap all fields...
if thats not what you mean pleas clearify
Posted: Tue Jan 20, 2004 5:17 pm
by deras
what is the sql query that would select the 8 highest numbered mem_id(s)?
Posted: Tue Jan 20, 2004 5:18 pm
by markl999
find the 8 highest mem_id(s) in 'tests' table
That bit would be along the lines of :
SELECT mem_id FROM tests ORDER BY mem_id DESC LIMIT 8
...the rest of it depends on which field links the 2 tables.
Posted: Tue Jan 20, 2004 5:42 pm
by deras
this is what i have now....
$result="SELECT mem_id FROM tests ORDER BY mem_id DESC LIMIT 8";
$icon="select local_icon from members where mem_id='$result[mem_id]'";
$username="select username from tests where mem_id='$result[mem_id]'";
is $icon[0] the first icon result of the $icon array?
if i wanted to only select the top 8 mem_id(s) in 'tests' of people who have a local_icon in 'members', how would i change things?
Posted: Tue Jan 20, 2004 5:47 pm
by markl999
Try :
SELECT t.username, m.local_icon FROM tests t, members m WHERE t.mem_id=m.mem_id ORDER BY t.mem_id DESC LIMIT 8
Posted: Tue Jan 20, 2004 7:31 pm
by deras
assuming that works and returns 8 usernames, mem ids, and icon locations then how can i access those values to show up in my dynamic php page, i.e. how do i code in the varibles to my html printout?
here is the html i want the variables to appear in...
<td align=center width=125 height=45 valign=bottom><a href='userview.php?id=mem_id?>'><IMG src='<local_icon>' border=0><br>
<B><user_name></b></a>
Posted: Tue Jan 20, 2004 7:41 pm
by markl999
Something like ...
Code: Select all
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res)){
?>
<tr>
<td align="center" width="125" height="45" valign="bottom">
<a href="userview.php?id=<?php echo $row['mem_id'] ?>"><IMG src="<?php echo $row['local_icon'] ?>" border="0"><br />
<B><?php echo $row['username'] ?></b></a>
</td>
</tr>
<?php
}
?>
Posted: Tue Jan 20, 2004 9:49 pm
by deras
is this right then
$sql="SELECT t.username, m.local_icon, mem_id FROM tests t, members m WHERE t.mem_id=m.mem_id ORDER BY t.mem_id DESC LIMIT 8";
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res))
Posted: Tue Jan 20, 2004 10:24 pm
by dull1554
yea that should work just fine