Question 300193 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Display info from database only on condition

Working with PHP and SQL here.

I have a bridging table in my database that has columns with separate options (option 1, option 2, etc) and another table with the information of each option. This is because the result may produce one or more of these different options, or none, so I want to pull the options only if it applies.

(To explain some context, basically its about travel and theres different travel options depending on the journey for the user to choose from and thats what I want to display.)

$sql = "SELECT *
        FROM route,
             option
        WHERE (
               (route.Option_1 > 0) OR (route.Option_2 > 0)
              )
              AND
              (
               (route.Option_1 == option.No) OR (route.Option_2 == option.No)
              )";
$stmt = $pdo->prepare($sql);
$rows = $db->executeSQL($stmt);
foreach ($rows AS $row): 
    $Option_Description = $row["Description"];
    ?> 
    <p><?php echo $Option_Description ?></p> 
    <?php
endforeach;

So Option_1 and Option_2 would have the numbers 1,2,3,4 etc depending on the corresponding option ID number from the options table (some journeys will display the same information but the combination may be different for example 2 and 4 for one journey and 2 and 3 for another, or just 2). But if its 0, there is no option and no information will display. So I tried to make the query so that if the number entered is > 0 then it would pull the rows of that ID and display the information otherwise returns false. But this code caused an error when I added two == marks though it was also not working when I wrote just one = mark either (was just assigning route.Option1/2 to option.No I’m pretty sure). What might be a better way to write this query to get the result I’m after?

Tags:

2

Answers


  1. 0

    Your SQL query has issues, for example == is not the correct operator to compare in SQL; it should be =. Here is the modified SQL, try it.

    SELECT route.*, option.*
FROM route
LEFT JOIN option AS option1 ON route.Option_1 = option1.No AND route.Option_1 > 0
LEFT JOIN option AS option2 ON route.Option_2 = option2.No AND route.Option_2 > 0
WHERE route.Option_1 > 0 OR route.Option_2 > 0;
    Login or Signup to reply.
  2. 0 
    $sql = "SELECT opt.Description
        FROM route r
        LEFT JOIN option opt ON (r.Option_1 = opt.No OR r.Option_2 = opt.No)
        WHERE r.Option_1 > 0 OR r.Option_2 > 0";

    Can you try this query?

    Update

    • LEFT JOIN is used. This ensures that all records from the route table are included, even if there’s no matching record in the option table. If a route doesn’t have a valid option (i.e., Option_1 or Option_2 is 0), it still appears in the result set, but with a NULL Description.
    • Condition in JOIN: The condition (r.Option_1 = opt.No OR r.Option_2 = opt.No) ensures that the options are joined correctly based on the No field.
    • WHERE Clause: This filters out routes where both options are 0.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search