Check if Strings is in the same row in MySQL

Question

This is an excerpt from an activity that I'm doing:

//checkpoint A
        $allowed_area = array('MarketA', 'MarketB');
        $valid_area = in_array($area,$allowed_area) === true ? TRUE : FALSE;        
//checkpoint B      
        $crop_check = $db->query("SELECT Crop FROM crops");
        $allowed_product = $crop_check->fetchall(PDO::FETCH_COLUMN);
        $valid_product = in_array($product,$allowed_product) === true ? TRUE : FALSE;}

In which both will result to '1' if TRUE.

This is my sample Message: Eggplant MarketB (These two strings are split using preg_split as $product and $area, respectively.)

And this is my IF Statement:

if($valid_area == $valid_product){
   $crop_query = $db->query("SELECT Crop, Price, Area FROM crops WHERE Crop = '{$product}' AND Area = '{$area}' LIMIT 1");
            $result = $crop_query->fetch(PDO::FETCH_OBJ); 
            $message = "The current price of {$result->Crop} is P{$result->Price}/kg in {$result->Area}.  }

And this is my sample SQL table:

Crop     Price  Area
Eggplant 23     MarketA
Rice     45     MarketB

As shown above, the code will execute the IF statement because both statements returned a TRUE value. However, the result would then be an error because Eggplant and MarketB does not exists on the same row.

Any suggestions on how to merge both checkpoints, so that they both have to check if $product and $area exists in the same row before executing the IF statement?

---

I've only remedied the problem with if the result is false, it will echo that "Not enough Data", however the solution would be greatly appreciated.

Answer 1

You could use a prepared statement and get rid of the client side matching. Please have a look at the PHP manual here:

http://php.net/manual/de/mysqli.prepare.php

or look at the accepted answer here:

how to bind multiple parameters to MySQLi query

Answer 2

It is a little hard to understand what you're trying to do. I will guess.

It seems your user has furnished values for both $area and $product. It seems like you are using the following logic.

1. make sure $area has a valid value: valid in the sense that it's mentioned in your table.
2. make sure $product has a valid value: ditto.
3. look up the price of $product in $area.

You're running into trouble when the particular product isn't available in the particular area, even though both the area and the product are both valid.

Your software needs some more specifications.

1. What do you want to do when the user asks for rice on Mars? (invalid area).
2. What do you want to do when the user asks for unicorns in MarketA (invalid product).
3. What do you want to do when the user asks for Rice in MarketA (both valid, but not together).
4. Finally, what do you want do to when the user asks for Rice in MarketB? (valid query)

Clarity in specifications like this is very helpful. In fact, usable code will flow easily from a clear specification.

There are three failure modes and one success mode in this list. If you want to treat all the failure modes the same, that's easy, in fact much easier than what you have. This code will make the query and yield an appropriate result.

$crop_query = $db->prepare("SELECT Crop, Price, Area 
                              FROM crops 
                             WHERE Crop = ? 
                               AND Area = ? 
                             LIMIT 1");
$message = "$product is not available for sale in $area";
if ($crop_query->execute(array($product,$area))) {
    while ($result = $crop_query->fetch(PDO::FETCH_OBJ)) {
        $message = "The current price of {$result->Crop} is P{$result->Price}/kg in {$result->Area}"
    }
}

Notice the use of if and while to handle the basic validity of the query and the availability of rows in the result set. You end up with a usable $message in both the success and the failure case.

Obviously, your result set is limited to a single row by the LIMIT clause, but it is still wise to construct your code using while to iterate over the result set.