Search code examples
phpmysqlparameterized-query

Suggestion needed for optimizing a MySQL query

I'm using parameterized queries with PHP I have the following two queries:

SELECT username,question_text 
FROM questions,users 
WHERE questions.question_id = 4 AND questions.user_id = users.user_id

and

SELECT username, post_text 
FROM posts,users WHERE posts.question_id = 4 
AND posts.user_id = users.user_id ORDER BY posts.post_id ASC

In short, the questions are being discussed in the "posts" table and are being referenced by their question_id.

I have some redundant code in my PHP code because I don't know enough mysql to query the database once to get the results I need out of these two queries.

Is the way I'm doing it fine and deal with the redundant parts of my PHP code or is there a better way of accomplishing this?

Solution

  • use join instead of matching columns in where clause 

    SELECT username,question_text 
FROM questions INNER JOIN users on questions.user_id = users.user_id
WHERE questions.question_id = 4

    And

    SELECT username, post_text 
FROM posts INNER JOIN users on posts.user_id = users.user_id 
WHERE posts.question_id = 4 
ORDER BY posts.post_id ASC