Convert memberID to Username

[Bm!]

I did try searching for an answer to this but couldnt find one so sorry if it is a repeat of another thread:

i have made a new page which displays everything in a database, one of the fields is the memberID, what code do i need to connect to the user database and convert the number into the username/name?

Thanks.

[Minalien]

How are you getting the memberID? The method could be anywhere from an SQL Query (such as SELECT memberName FROM `smf_members` WHERE memberID = <member id without the less & greater than signs>), or if you do the smart thing and do your queries properly, and have them returned to an array (ie: $memberData['id'] being whatever the member's ID is), then you can use $memberData['memberName'] or something similar.

[Dragooon]

Hmmmm....
Something like this

Code Select Expand

require('/Path/To/SSI.php');
global $db_prefix, $scripturl;
$result = db_query("SELECT ID_MEMBER, realName
FROM {$db_prefix}members
ORDER BY ID_MEMBER DESC
LIMIT 30",__FILE__,__LINE__);
$members = array();
while($row = mysql_fetch_assoc($result))
{
$members[] = array(
     'name' => $row['realName'],
     'id' => $row['ID_MEMBER'],
);
}
mysql_free_result($result)
foreach($members as $m)
echo '<a href="',$scripturl,'?action=profile;u=',$m['id'],'">',$m['name'],'</a>';

Just wrote this while repleying though :p

[Bm!]

il show you what im trying to do:
http://forum.sycoradio.com/?action=mixes
I have that but want to change where it says "view artist" to the artist name....

which option ya think would work best?

[Kays]

If it's a seperate table, you can do a  LEFT JOIN in your present query to pull that info. No need for another query.

Or easier yet, why not just add another column to the present table and store the name there.

[Bm!]

Or easier yet, why not just add another column to the present table and store the name there.

i considered that, but this way it shows their current name, not the one they had then they uploaded.

how would i apply a left join to convert ID to name/username? never used one before 

[Kays]

I'm not that great with mySQL and providing a proper explination. Take a look at the SMF souce code to see how they do it.

Here's one which might be close to what you're trying to do.

Code Select Expand

// Any calendar information for this topic?
$request = db_query("
SELECT cal.ID_EVENT, cal.startDate, cal.endDate, cal.title, cal.ID_MEMBER, mem.realName
FROM {$db_prefix}calendar AS cal
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = cal.ID_MEMBER)
WHERE cal.ID_TOPIC = $topic
ORDER BY startDate", __FILE__, __LINE__);
$context['linked_calendar_events'] = array();


[Bm!]

left join seems might complex, its really confused me! it seems like least coding needed but i cant get me head around it...
anyone know how i might display the left join, its all displayed in an array 
so frustrating, im usually good at working things out &gt;

[Kays]

It's not really that confusing. ON is the same as WHERE. So here it's grabbing "realName" from the members table. I got that from Display.php. You can take a look at it to see how the array is formed

Why not post what you've got and I'll see if I can help.

[Bm!]

$query="SELECT * FROM smf_mixes ";
                         $result=mysql_query($query);
                         $num = mysql_num_rows ($result);
                         

                         while($row = mysql_fetch_array($result))
                        {
                         echo "
<tr>
    <td colspan='6'><HR></td>
</tr>
                         <tr>
                            <td width='75' valign='top' class='style15'><u><a href='?action=profile;u=" . $row['artist_id'] . "'>View Artist</a></u></td>
                            <td width='85' valign='top'><span class='style15'>" . $row['genre'] . "</span></td>
                            <td width='140' valign='top'><span class='style15'>" . $row['title'] . "</span></td>
                            <td width='301' valign='top'><div align='justify'><span class='style15'>" . $row['description'] . "</span></div></td>
                            <td width='40' valign='top'><div align='center' class='style6'><a href='http://www.sycoradio.com/mediaplayer/?song=" . $row['url'] . "' target='_blank'>listen&#8482;</a></div></td>
                            <td width='58' valign='top'><div align='center' class='style6'><a href='" . $row['url'] . "'>D/L&#8482;</a></div></td>
                          </tr>";
}
mysql_close();   

[Bm!]

just normal array, but some how i need to send $row['artist_id'] to members table, and find out what name/username is associated with that, but im unsure how to call the table inside an array.... if that makes sence?

[Kays]

I "think" this might work. Use $row['realName'] to display the name.

Code Select Expand

$query="SELECT (*), realName FROM smf_mixes
LEFT JOIN smf_members ON (ID_MEMBER = artist_id ";

                         $result=mysql_query($query);
                         $num = mysql_num_rows ($result);
                         


[Bm!]

now it just doesnt display anything 

[Kays]

Ooops, forgot to close a bracket. Sorry.

Code Select Expand

LEFT JOIN smf_members ON (ID_MEMBER = artist_id) ";


[Bm!]

still nada   

[Kays]

I warned you that I wasn't the greatest with MySQL.

Try this:

Code Select Expand

$query="SELECT artist_id, genre, title, description, url, realName FROM smf_mixes
LEFT JOIN smf_members ON (ID_MEMBER = artist_id)";


What LEFT JOIN allows you to do is to pull data from more than one table in a single query. With Dragoon's suggestion, you will need a separate query for each row which will drive your database queries to a very high level.

Google LEFT JOIN and see if you can find a better explanation on how to use it since it is the best way to do this.

[Bm!]

you might not be the greatest, but ur better than me!
i tried googlein it earlier and it just fried my mind tryin 2 work it all out, so far your explanation has beaten them all!

[Bm!]

SPOT ON MATE!!!!!!!!


Cheers dude! owe ya one.

[Kays]

Cool.

It was the "*", I didn't know to add to it and I thought that I saw something like what I had originally posted used somewhere like that.