How to join my mysql table

Normally I do this way to g开发者_JAVA技巧et 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?

---

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, ...

---

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

---

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

---

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

---

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

---

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.