PHP MySQL optimal way to perform multiple dependent queries

Question

I need to query (read) some data, and analyze it before performing another query (read) on a different table.

I've looked at mysqli multiple statements but they don't have documentation for situations where the second query depends on the result of the first query.

It seems my current code of doing two queries might not be optimal. Is there a more optimal way to do this?

//FIRST QUERY
$query1= "SELECT color FROM products WHERE type = '$productType';";
$result = $conn->query($query1);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $colorProduct = $row["color"];
    }
} 


//ANALYZE SEARCH RESULT FROM FIRST QUERY
if ($colorProduct == "green") {
    $colorType = "greenColorType";
}   
//a lot more analysis


//SECOND QUERY
$query2 = "SELECT price FROM Vendors WHERE color = '$colorType';";
$result2 = $conn->query($query2);

if ($result2->num_rows > 0) {
    while($row = $result2->fetch_assoc()) {
        $priceOfProduct = priceOfProduct . $row["price"];
    }
} 

$conn->close();

Thank you in advance.

I'd be tempted to create a stored procedure for this - especially if the processing is a regular task. The last query is using . ~ presumably this is for addition so you would probably want + instead? — Professor Abronsius
– Professor Abronsius, Commented Dec 5, 2017 at 22:06
I'd agree that this can be done with a single query. The thing that I guess stumps me about this is that $colorType is only set if $colorProduct == "green". Are you sure that this is what you want? Perhaps state explicitly what you're trying to achieve, as your code is difficult to interpret in it's current state. — e_i_pi
– e_i_pi, Commented Dec 5, 2017 at 22:14

Professor Abronsius · Accepted Answer · 2017-12-05 22:24:19Z

1

Why don't you join those 2 queries into one and analyse later if you need to. For example:

"SELECT price FROM products
JOIN Vendors ON Vendors.color = products.color
WHERE type = $productType
AND color = 'green';"

MySQL is declarative language so it is usually better to "tell it" what you want and than modify data afterwards (if needed).

edited Dec 5, 2017 at 22:24

Professor Abronsius

33.9k5 gold badges36 silver badges49 bronze badges

answered Dec 5, 2017 at 22:22

boroboris

1,6201 gold badge20 silver badges33 bronze badges

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

Comments

Fiber Optic · Accepted Answer · 2017-12-05 22:34:49Z

0

First, you have to add a "and" to the "where" clause : SELECT color FROM products WHERE type = '$productType' and color = 'green'

Then you have to join or to add a sub query in SQL.

In fact, you should do only one SQL request.

answered Dec 5, 2017 at 22:34

Fiber Optic

1945 bronze badges

Comments

miknik · Accepted Answer · 2017-12-05 22:58:56Z

Looks like your color values in the vendors table all have ColorType added after the actual colour name, which you are dealing with in PHP, before making a second query.

Mysql can do this for you. Here's an example:

"SELECT a.color, b.price FROM products a, vendors b WHERE a.type = '$productType' AND a.color = SUBSTR(b.color,1,LENGTH(a.color);"

The WHERE clause specifies a match on your $productType variable with the type column of table a (products) and a match between the color value in table a and the beginning of the color value in table b. It uses the LENGTH statement to match regardless of the length of the color in table a. So if color in table a is darkblue and table b is darkblueColorType it will match, and if the values are red and redColorType it will still match.

Collectives™ on Stack Overflow

PHP MySQL optimal way to perform multiple dependent queries

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related