CODEWITHSUNDEEP
phpmysql
wow
wow

Reputation: 553

How to join my mysql table

Normally I do this way to get 2 tables

<?php 
$select = "SELECT * FROM question ORDER BY id DESC LIMIT 0,20";
$query  = $db->query($select);

while ($fetch = $db->fetch($query)) {
    $uselect = "SELECT * FROM user WHERE id='".intval($fetch['q_user'])."' ";
    $uquery  = $db->query($uselect);
    $ufetch = $db->fetch($uquery);
    ?>    

    Title : <?php echo $fetch['q_title']; ?>
    User : <?php echo $ufetch['u_id']; ?>

    <?php 
} 
?>

There have a simple way to join this one?

Upvotes: 2

Views: 117

Answers (6)

diagonalbatman
diagonalbatman

Reputation: 18002

Yes, i am assuming you are wanting to return the user that asked a question, because you store the q_user then you need to do some SQL like:

"SELECT q.q_title, u.u_id FROM question q JOIN user u ON (q.q_user=u.u_id)"

You can apply your limits etc to this query.

Upvotes: 0

Yoram de Langen
Yoram de Langen

Reputation: 5499

This query should be right:

create table questions
 (
    question_id bigint unsigned auto_increment primary key,
    user_id bigint unsigned not null,
    title varchar(255),
 )engine=innodb;

 create table users
 (
    user_id bigint unsigned auto_increment primary key,
    name varchar(255) not null
 )engine=innodb;

 // your query
 select q.* from questions q
 inner join users u where u.user_id = q.user_id
 order by q.question_id desc

Upvotes: 0

Alex Pliutau
Alex Pliutau

Reputation: 21957

SELECT * FROM question JOIN user ON question.q_user = user.id ORDER BY question.id DESC LIMIT 0, 20

Useful link: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Upvotes: 1

ircmaxell
ircmaxell

Reputation: 165201

Well, you could do:

SELECT *
FROM question
JOIN user ON question.q_user = user.id
ORDER BY question.id DESC
LIMIT 0, 20

One thing I'd suggest, is explicitly listing the columns, since any overlap (Question.id and user.id) will be ambiguous. Simply do SELECT question.id, user.id AS uid, ...

Upvotes: 1

Ovi
Ovi

Reputation: 2750

SELECT * FROM question 
LEFT JOIN user ON user.id = question.q_user
ORDER BY question.id DESC LIMIT 0,20

Upvotes: 0

Tyler Eaves
Tyler Eaves

Reputation: 13121

select * from question left join user on user.id = question.q_user order by question.id desc limit 0,20;

Upvotes: 0

Related Questions