I've been creating new sections of my forum using php and I'm in the stage of perfecting my design. What I have is a new table in my database that includes fields about songs.
Eg 'title' 'artist' 'album'
I use a form to submit new entries to the table and it also records the userid (uid) of the user who submitted the new entry in a new column.
Eg 'title' 'artist' 'album' 'userid'
Using the uid, how can I extract the user's username? I don't want to enter the username into the new table because members can change their username at any point. It sounds so simple but I can't figure it out on my own.
Thank you for any help you can give.
In the query to get the information from the database, use a left join on the users table:
$query = $db->query("
SELECT a.*, u.username
FROM ".TABLE_PREFIX."albums a
LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=a.userid)
WHERE a.userid = '".intval($mybb->input['uid'])."'
");
You learn new things everyday
Thanks for the very quick response, I'll try this new code out immediately.
My db access coding is a little more simpler. Can you show me how to put the new code into it?
$result = $db->query("SELECT * FROM all_songs");
Bump.
Bump!
You can only bump once every 24 hours - anything else is merged...
What do you by "new code"?
I meant "new code for me" since I'm learning php as I use it. I've been teaching myself for the past week.
Um. OK. Well. Your code
looks like it should work but for me it
looks complicated. Do you mind explaining it or making it a tiny bit simpler?
$result = $db->query("SELECT * FROM all_songs");
This is my current db access code. I see how you've modified it to allow me to...do something...uh...so can you please explain some of it? Thanks
$result = $db->query("SELECT a.*, u.username FROM all_songs a LEFT JOIN mybb_users u ON a.uid=u.uid");
Can't get much simpler than that.
If you don't get it, I suggest searching and learning about SQL joins and how they work.
$query = $db->query("
SELECT a.*, u.username
FROM ".TABLE_PREFIX."albums a
LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=a.userid)
WHERE a.userid = '".intval($mybb->input['uid'])."'
");
If you're coding in MyBB, always perform queries under the $query variable - this way it reassigns it and frees up some memory. Each table you're "joining" is assigned a letter. You'll see the albums is "a" and users is "u".
In the select part, we're selecting everything from the albums table ("a.*") and just the username from the users table ("u.username").
The LEFT JOIN part is where we're adding an extra table into the query. The brackets on this line ("u.uid=a.userid") tell the query what to match in the table. In this example, we're looking for the same user ID in the users table as in the albums table. If there isn't a match, then the table is not joined and you don't get the information in it.
The where line is used as normal, just remember to use the table letters we've assigned when selecting what to search for.
Hope that helps, I'm not the best at explaining things!...
That's a good explanation. I understand it now
Yumi Wrote:I suggest searching and learning about SQL joins and how they work.
I tend to search up php and MySQL codes when I need to learn more about them
In this case it was much easier for me to learn from a person's personal explanation
I'll try and implement this code into my current one and report if it works, or if I've stuffed it up in some way
It happens...
Hm. It didn't work. I'm not an expert in MySQL or php so please understand where I'm coming from. I
replaced my current $query with your one and replaced the table and field names to suit my database. It didn't produce any errors.
Here is the page...
http://www.gamingzion.net/forum/showsong.php
It pulls each row from a table in my database and displays it as new rows in a HTML table using the while php function. I pull each row by this code...
while ($r = mybb->query($query))
if I can remember correctly
{
$title = $r["title"];
the var $title is equal to the title field
etc
$username = $r["username"];
is this extra required for the new "left join" function?
Is it possible to cut off the a. and u. prefixs and make the new query simpler for me? This is a good learning experience for me