Simplifying this query?

Question

Consider this query:

SELECT table1.id, 
       table1.review, 
       table1.time, 
       table2.author, 
       table2.title 
FROM 
table1, table2 
WHERE table1.id = table2.id 
AND table1.reviewer = '{$username}'
ORDER BY table1.id

I'm using the above quite a lot around my site's code. I find that adding the table prefixes etc. before the column names can make the query very long and take up quite a lot of lines.

Is there a way to make the above query simpler/easier?

Answer 1

You query looks pretty optimal aside from the potential risks with your "{$username}" portion. I could very easily see some SQL injection issues if the overall query is being rendered as a straight string and not via an abstract layer of some sort.

Answer 2

You can use table aliases

SELECT t1.id, t1.review, t1.time, t2.author, t2.title 
FROM 
table1 AS t1, table2 AS t2
WHERE t1.id = t2.id AND t1.reviewer = '{$username}'
ORDER BY t1.id

Answer 3

First of all, you may want to give shorter aliases to your tables. In addition, you are using the implicit join syntax which complicates the WHERE clause, and is not recommended in general. You may want to use the more modern explicit syntax instead:

SELECT    t1.id, t1.review, t1.time, t2.author, t2.title 
FROM      table1 AS t1
JOIN      table2 AS t2 ON (t2.id = t1.id)
WHERE     t1.reviewer = '{$username}'
ORDER BY  t1.id

Note that JOIN is a synonym for INNER JOIN, and the AS keyword is optional when defining table aliases. You can simply use ... FROM table1 t1 ... instead of ... FROM table1 AS t1 ....

Answer 4

The format I find most readable is:

SELECT 
   t1.id, 
   t1.review, 
   t1.time, 
   t2.author, 
   t2.title 
FROM 
   table1 t1

   JOIN table2 t2
      on ( t2.id = t1.id )

WHERE 
   t1.reviewer = '{$username}'

ORDER BY 
   t1.id

Answer 5

Why dont you make a function , pass the table name and other parameter and return either values or sql query.

Your query is already simplified, i think you are worried about doing this multiple times. so better will be to create a small function in that case.

I had similar problem, which i got rid using function.

Answer 6

It's not clear if you're "using the above code all over the site" verbatim, or the style of SQL without aliases.

If this specific piece of code is being copy/pasted, consider encapsulating this logic in a function in your PHP modules.

function GetReviewerTitles($reviewer)
{
    //your select statement.
    $reviewerSQL = sprintf("SELECT t1.id,  t1.review, t1.time, t2.author, t2.title FROM  table1 as t1 INNER JOIN table2 AS t2 ON t1.id=t2.id WHERE t1.reviewer = '%s' ORDER BY t1.id",
                   mysql_real_escape_string({$username}));

    // Perform Query
    $result = mysql_query($reviewerSQL);

    //return if/when necessary
}

Answer 7

I suggest using a view:

CREATE VIEW ReviewInfo(id, review, time, author, title, reviewer) AS
    SELECT t1.id,     t1.review, t1.time, 
           t2.author, t2.title,  t1.reviewer
      FROM table1 AS t1 JOIN table2 AS t2 ON t1.id = t2.id 

Now you can write your query as:

SELECT id, review, time, author, title
  FROM ReviewInfo
 WHERE reviewer = '{$username}'
 ORDER BY id;

Note the use of abbreviations for the table names in the view definition - that is a technique you could use in your query even if you don't use a view. And the notation using an explicit JOIN operator in the FROM clause with the ON condition is preferred to the old-style FROM table1, table2.