CODEWITHSUNDEEP
phpmysql
Rashad
Rashad

Reputation: 1344

Select IF EXISTS THEN ELSE on Mysql

I have tried to solve this issue a lot, checked a lot of sites, tried more examples, but did not get what I want yet. My sql logic on my php Page like at below:

IF (EXISTS (SELECT * FROM suborders WHERE subId = 1))
THEN SELECT * FROM suborders WHERE subId = 1     //if exists just do this sql again
ELSE SELECT id FROM orders WHERE id = 1 LIMIT 1;

Mysql Gives an error on syntax at the first query. So, I also tried to use another way at below:

delimiter //
create procedure test()
begin
IF EXISTS (SELECT * FROM suborders WHERE subId = 1) THEN
SELECT * FROM suborders WHERE subId = 1;
ELSE
SELECT id FROM orders WHERE id = 1 LIMIT 1;
END IF;
end //
delimiter ;

call test();

Using 2nd query mysql returns empty result, without error. Actually, there should be something to fetch...

Upvotes: 4

Views: 20750

Answers (3)

Michael Berkowski
Michael Berkowski

Reputation: 270599

Since suborders.subId is a foreign key referencing orders.id, you can approach your query logic with a single LEFT JOIN query. I would prefer this solution because it means you do not have to maintain a stored procedure separately. All the query logic is bundled with your application code.

By doing a LEFT JOIN from orders against suborders, columns from suborders may return NULL. In your application code, you may then test if they are null, and if they are, use the id from orders instead. This method essentially transfers the flow logic to your PHP code, while stuffing all the database actions into one query, which will either return one row, or many rows (or none, if the requested id doesn't exist in orders)

SELECT
  orders.id AS orders_id,
  suborders.*
FROM
  orders
  LEFT JOIN suborders ON orders.id = suborders.subId
WHERE orders.id = ?

In the PHP logic, execute that query with an appropriate value in place of ?. When fetching, you will be able to discern immediately if suborders has no return, as all the suborders columns will be NULL.

// Assume fetched into $orders
foreach ($orders as $order) {
  if ($order['subId'] === null) {
    // Use orders.id...
    // If it is a unique id, this will be the only row
    // and the loop will terminate here
    echo $order['orders_id'];
  }
  else {
    // subId has a value, so you can use the other suborders cols
    echo $order['subId'];
    echo $order['other_suborder_column'];
  }

This way, if suborders does match and populate columns, you would be able to loop over the entire returned rowset, equivalent to SELECT * FROM suborders. Otherwise, you can break the loop right away (or if orders.id is unique as I suspect it is, it will only ever return one row anyway with an orders_id and null suborders).

Upvotes: 1

meda
meda

Reputation: 45490

IF EXISTS (SELECT * FROM suborders WHERE subId = 1 )
BEGIN
   SELECT * FROM suborders WHERE subId = 1
END
ELSE
BEGIN
    SELECT id FROM orders WHERE id = 1 LIMIT 1
END

Full stored Procedure:

DELIMITER //
 CREATE PROCEDURE sp_select_suborders(IN `p_subId`)
    IF EXISTS (SELECT * FROM suborders WHERE subId = p_subId )
    BEGIN
       SELECT * FROM suborders WHERE subId = p_subId
    END
    ELSE
    BEGIN
        SELECT id FROM orders WHERE id = p_subId LIMIT 1
    END //
 DELIMITER ;

Usage:

$stmt = $db->prepare('CALL sp_select_suborders(?)');

Upvotes: 3

dognose
dognose

Reputation: 20889

Why don't you simple handle the case of no results in php?

$yourDBUsage->query("SELECT * FROM suborders WHERE subId = 1"); 

if ($yourDBUsage->count == 0){
   $yourDBUsage->query("SELECT id FROM orders WHERE id = 1 LIMIT 1;");
}

Upvotes: 2

Related Questions