How can I turn these multiple mysql_querys into 1 query?

Question

I have two tables: users and threads. When a thread is created, it will store the user_id in the table as author_id. I want to display the thread name and the username of its author with the same query. I am currently using two querys as shown:

$query2 = mysql_query("SELECT author_id FROM threads WHERE id = $threadId") or die(mysql_error());
$result2 = mysql_fetch_array($query2);
$author_id = $result2['author_id'];
$query3 = mysql_query("SELECT username FROM users WHERE id = $author_id") or die(mysql_error());
$result3 = mysql_fetch_array($query3);
$author_name = $result3['username'];

Follow a SQL tutorial. It will cover basic RA constructs such as joins (which must already be known about, as it was a added as a tag..). — user2864740
– user2864740, Commented Dec 16, 2015 at 3:19

userlond · Accepted Answer · 2015-12-16 03:30:06Z

4

<?php
$sql = '
    SELECT t.name, u.username
    FROM threads t
    JOIN users u ON t.author_id = u.id
    WHERE t.id = ' . (int)$threadId . '
';
list($thread_name, $author_name) = mysql_fetch_row(mysql_query($sql));

P.S. Mysql php extension is deprecated.

edited Dec 16, 2015 at 3:30

answered Dec 16, 2015 at 3:20

userlond

3,8442 gold badges41 silver badges56 bronze badges

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

3 Comments

userlond Over a year ago

(int) before variable casts it to the integer type. It prevents sql-injection attacks.

Tom Oborne Over a year ago

ah ok thanks! So anytime I have a GET I should use a variable casts on it when receiving it?

userlond Over a year ago

If u expect int or float, you may use type casting. if string - use mysql_real_escape_string (php.net/manual/en/function.mysql-real-escape-string.php). Alternative is not to compose sql directly via string concat, but use prepared statements (for example with mysqli extension: php.net/manual/en/mysqli.quickstart.prepared-statements.php).

Arulkumar · Accepted Answer · 2015-12-16 08:04:33Z

1

Try this query:

SELECT username 
FROM users 
WHERE id = (SELECT author_id 
            FROM threads 
            WHERE id = $threadId 
            LIMIT 1)

Note: Limit 1 is not mandatory as id is unique.

edited Dec 16, 2015 at 8:04

Arulkumar

13.3k15 gold badges51 silver badges76 bronze badges

answered Dec 16, 2015 at 4:27

louk

976 bronze badges

Collectives™ on Stack Overflow

How can I turn these multiple mysql_querys into 1 query?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related