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