CODEWITHSUNDEEP
phpmysqlsql
asotshia
asotshia

Reputation: 121

Better way to write these 2 SQL queries?

It gets the name of the url, finds the ID and use it to find the products associated with this ID.

Is there a better way of writing this? By saying better I mean performance. I do not know if the two queries can be merged for a better performance.

$cname = mysql_real_escape_string($_GET["c"]);

$row = mysql_fetch_assoc(mysql_query("SELECT id FROM companies WHERE name = '$cname' LIMIT 1"));
$cID = $row['id'];

$products = mysql_query("SELECT name FROM products WHERE company_id = '$cID' " );
while($product = mysql_fetch_assoc($products)) {
                echo $product['name'].'<br>';
            }

Upvotes: 0

Views: 107

Answers (5)

Ben
Ben

Reputation: 11188

For security you could (should) consider parameterized queries. See these results

Upvotes: 1

Flukey
Flukey

Reputation: 6555

Just use a simple join:

SELECT p.name FROM products p, companies c
WHERE c.id = p.company_id
AND c.name = '<COMPANY NAME>';

As long as the company name is unique then you won't run into any problems.

Upvotes: 1

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

You can do this:

SELECT p.name
  FROM products p
  JOIN companies c
    ON (p.company_id = c.id)
  WHERE c.name = '$cname'

Upvotes: 0

WWW
WWW

Reputation: 9860

SELECT c.id, p.name
FROM companies c
INNER JOIN products p ON p.company_id = c.id
WHERE c.name = '$cname'

Upvotes: 4

Rup
Rup

Reputation: 34408

Sure, you can join them into one:

SELECT products.name
  FROM products
  JOIN companies
    ON products.company_id = companies.id
  WHERE companies.name = '$cname'

however this loses the LIMIT in the first query - would you ever expect to hit that?

Upvotes: 0

Related Questions