code question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
deras
Forum Newbie
Posts: 24
Joined: Sun Nov 02, 2003 10:26 am

code question

Post 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.
User avatar
dull1554
Forum Regular
Posts: 680
Joined: Sat Nov 22, 2003 11:26 am
Location: 42:21:35.359N, 76:02:20.688W

Post 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
deras
Forum Newbie
Posts: 24
Joined: Sun Nov 02, 2003 10:26 am

Post by deras »

what is the sql query that would select the 8 highest numbered mem_id(s)?
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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.
deras
Forum Newbie
Posts: 24
Joined: Sun Nov 02, 2003 10:26 am

Post 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?
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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
deras
Forum Newbie
Posts: 24
Joined: Sun Nov 02, 2003 10:26 am

Post 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>
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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
}
?>
deras
Forum Newbie
Posts: 24
Joined: Sun Nov 02, 2003 10:26 am

Post 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))
User avatar
dull1554
Forum Regular
Posts: 680
Joined: Sat Nov 22, 2003 11:26 am
Location: 42:21:35.359N, 76:02:20.688W

Post by dull1554 »

yea that should work just fine
Post Reply