How to put an 'if clause' in an SQL string?

0 votes

Hii team,

Here's what I want to do on my MySQL database:

SELECT *
    FROM itemsOrdered
    WHERE purchaseOrder_ID = '@purchaseOrdered_ID'
        AND status = 'PENDING'

If that would not return any rows, which is possible through if(dr.HasRows == false), I would now create an UPDATE in the purchaseOrder database:

UPDATE purchaseOrder
    SET purchaseOrder_status = 'COMPLETED'
    WHERE purchaseOrder_ID = '@purchaseOrder_ID'

How would I able to make this process a little shorter?

Jun 1 in PHP by kartik
• 15,480 points
39 views

1 answer to this question.

0 votes

Hello @kartik,

For your specific query, you can do:

UPDATE purchaseOrder
    SET purchaseOrder_status = 'COMPLETED'
    WHERE purchaseOrder_ID = '@purchaseOrder_ID' and
          not exists (SELECT *
                      FROM itemsOrdered WHERE purchaseOrder_ID = '@purchaseOrdered_ID' AND status = 'PENDING'
                     )

However, I might guess that you are looping at a higher level.  so to set all such values, try this:

UPDATE purchaseOrder
    SET purchaseOrder_status = 'COMPLETED'
    WHERE not exists (SELECT 1
                      FROM itemsOrdered
                      WHERE itemsOrdered.purchaseOrder_ID = purchaseOrder.purchaseOrdered_ID AND
                            status = 'PENDING'
                      limit 1
                     )

Hope this is helpfull!!

answered Jun 1 by Niroj
• 32,280 points

Related Questions In PHP

0 votes
1 answer

How to resolve “must be an instance of string, string given” prior to PHP 7?

Hello, Prior to PHP 7 type hinting can only be ...READ MORE

answered Apr 20 in PHP by I Navin
• 220 points
72 views
0 votes
1 answer

How to convert an image to base64 encoding?

Hello @kartik, You can also do this via ...READ MORE

answered Apr 1 in PHP by Niroj
• 32,280 points
38 views
0 votes
1 answer

How to merge two arrays while keeping keys instead of reindexing in php?

Hello, Considering that you have $replaced = array('1' => ...READ MORE

answered Apr 1 in PHP by Niroj
• 32,280 points
46 views
0 votes
1 answer

How to get the client IP address in PHP ?

Hello, Here is a code sample of a good ...READ MORE

answered Apr 8 in PHP by Niroj
• 32,280 points
467 views
0 votes
1 answer

How to make anchor tag with routing using Laravel?

Hey @kartik, First you have to go to ...READ MORE

answered Mar 18 in Laravel by Niroj
• 32,280 points
813 views
0 votes
1 answer

What is redirection in Laravel?

Named route is used to give specific ...READ MORE

answered Mar 18 in Laravel by Niroj
• 32,280 points
82 views
0 votes
1 answer

How to install Laravel via composer?

Hello, This is simple you just need to ...READ MORE

answered Mar 23 in Laravel by Niroj
• 32,280 points
94 views
0 votes
1 answer

What are named routes in Laravel and How can specify route names for controller actions?

Hey @kartik, Named routing is another amazing feature of ...READ MORE

answered Mar 23 in Laravel by Niroj
• 32,280 points
434 views
0 votes
1 answer

How to pass an array within a query string?

Hello, Submitting multi-value form fields, i.e. submitting arrays ...READ MORE

answered Apr 7 in PHP by Niroj
• 32,280 points
163 views
0 votes
1 answer

How to check if php session is already started or not?

Hello kartik, Use session_id(), it returns an empty string ...READ MORE

answered Apr 1 in PHP by Niroj
• 32,280 points
92 views