Search code examples
mysqldatabasejoomlavirtuemart

Is there a more efficient way to write this MySQL query?

I'm a newbie to mysql, I managed to scrape this together to get the result I wanted. Can it be coded better? Are there any security risks? Its being output in php.

$qwe = $product->virtuemart_product_id;    
$id = mysql_real_escape_string($qwe);

$result = mysql_query('SELECT * FROM virtuemart_product_medias where virtuemart_product_id = ' . $id . ' LIMIT 1');

$row = mysql_fetch_assoc($result);    
$matched = $row['virtuemart_media_id'];

$result2 = mysql_query('SELECT * FROM virtuemart_medias where virtuemart_media_id = ' . $matched . ' LIMIT 1');

$row2 = mysql_fetch_assoc($result2);    
$matched2 = $row2['file_url_thumb'];

echo $matched2;
Solution

    1. Firstly, never use the mysql_* functions. They are deprecated and relying on them is highly discouraged. Use either MySQLi or PDO

    2. The above query could be rewritten as

      SELECT file_url_thumb FROM virtuemart_medias where virtuemart_media_id = (SELECT virtuemart_media_id FROM virtuemart_product_medias where virtuemart_product_id = ' . $id . ' LIMIT 1) LIMIT 1

    3. Never do a SELECT *. Include only those fields in your query which you need in your code.