Mysql inner join/ multiple selects in one query?

Question

Hi all!

I'm scripting a guestbook (personalized for each user). I have one table for users and a different one for the guestbook. Now, the way I'm currently displaying the name of the author of a post is not optimal. I simply have a row in the DB for "fromname" i.e the authors name. I would like to select the authors name based on the authors ID and matching that to their name in the "users" table.

So... This is my mysql query right now:

$sql = " SELECT 
             pid,toid,fromid,message,fromname,name,pdate,flag 
         FROM gbook INNER JOIN users 
         ON id='{$_GET['id']}' 
         WHERE toid='{$_GET['id']}' 
         ORDER BY pdate DESC";

I guess I need to like... Select the name on a different condition but in the same query. But I don't know how.

I hope you understand my problem. Help will be greatly appreciated!

/Jafool

Can you show us your table structure?

SchmitzIT
– SchmitzIT

2013-11-30 14:09:56 +00:00
Commented Nov 30, 2013 at 14:09 — SchmitzIT
– SchmitzIT, Commented Nov 30, 2013 at 14:09

SchmitzIT · Accepted Answer · 2013-11-30 14:11:59Z

1

Assuming your users table has a column called username, the following should do what you want:

SELECT 
    pid,
    toid,
    fromid,
    message,
    u.username, 
    name,
    pdate,
    flag 
FROM gbook INNER JOIN users u
  ON id='{$_GET['id']}' 
WHERE toid='{$_GET['id']}' 
ORDER BY pdate DESC"

All I did was alias the user table (as u), and refered to u.username instead of the fromname you had before.

answered Nov 30, 2013 at 14:11

SchmitzIT

9,74912 gold badges71 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jafool Over a year ago

Allright, thanks for your answer! So I made it to this:

$sql = "SELECT pid,toid,fromid,message,fromname,u.name,pdate,flag FROM gbook INNER JOIN users u ON id='{$_GET['id']}' WHERE toid='{$_GET['id']}' ORDER BY pdate DESC";

And when I echo it out like so: echo "{$row['fromname']} | {$row['u.name']}"; The "fromname" works but I get "Notice: Undefined index: u.name in D:\xampp\htdocs\fbook\gb.php on line 129" and u.name does not.

SchmitzIT Over a year ago

When you reference it in PHP, you wouldn't use the u. prefix. Try using: $row['name']

Jafool Over a year ago

I think the problem is resolved! To share with you all, this is the final result:

$sql = "SELECT pid,         toid,         fromid,         message,         fromname,         u.name,         pdate,         flag FROM gbook INNER JOIN users u ON u.id=fromid WHERE toid='{$_GET['id']}' ORDER BY pdate DESC";

And to echo it out all I needed was e.g: echo "From: {$row['name']}"; I'd like to thank every single one of you that posted an answer, because they ALL helped in different ways. Hopefully this fix won't screw something else up, haha. Thank you again! /Jafool

Linger · Accepted Answer · 2013-11-30 14:47:53Z

1

From what I am seeing it looks like you need to link to the users table twice since you have a fromid and a toid. If you have a users table, why would you have a fromname field in the gbook table? Anyway if my assumption is correct then you may be interested in the following query:

SELECT g.*, u1.username AS ToUser, u2.username AS FromUser
  FROM gbook AS g
  INNER JOIN users AS u1 ON u1.id = g.toid
  INNER JOIN users AS u2 ON u2.id = g.fromid
WHERE g.toid = '{$_GET['id']}' 
ORDER BY g.pdate DESC

edited Nov 30, 2013 at 14:47

answered Nov 30, 2013 at 14:19

Linger

15.1k23 gold badges54 silver badges79 bronze badges

Comments

Xtophe · Accepted Answer · 2013-11-30 14:15:16Z

0

Hard coding the name in the guestbook table is indeed dirty. Assuming your table structure is something like this:

users( id, name ) gbook( pid, toid, fromid, message, fromname, name, pdate, flag )

your query is already almost ok. Just change it as follows to select all columns of both tables:

$sql = " SELECT *
         FROM gbook g INNER JOIN users u
         ON u.id=g.fromid 
         WHERE toid='{$_GET['id']}' 
         ORDER BY pdate DESC";

Then you can display the name with something like

$result = mysql_query($sql);
while( $val = mysql_fetch_array($result) )
{ 
  $username = $val["u.name"];
}

answered Nov 30, 2013 at 14:15

Xtophe

2,3171 gold badge21 silver badges41 bronze badges

Collectives™ on Stack Overflow

Mysql inner join/ multiple selects in one query?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related