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, 2020 in PHP by kartik
• 37,490 points
110 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, 2020 by Niroj
• 82,680 points

Related Questions In PHP

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, 2020 in PHP by Niroj
• 82,680 points
6,008 views
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, 2020 in PHP by I Navin
• 220 points
1,430 views
0 votes
1 answer

How to Insert multiple rows in a single SQL query?

Hello @kartik, In SQL Server you can insert ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,680 points
1,706 views
0 votes
1 answer

How can I do an UPDATE statement with JOIN in SQL Server?

Hello @kartik, This should work in SQL Server: update ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,680 points
130 views
+1 vote
1 answer

How to make anchor tag with routing using Laravel?

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

answered Mar 18, 2020 in Laravel by Niroj
• 82,680 points
6,795 views
0 votes
1 answer

What is redirection in Laravel?

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

answered Mar 18, 2020 in Laravel by Niroj
• 82,680 points
790 views
0 votes
1 answer

How to install Laravel via composer?

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

answered Mar 23, 2020 in Laravel by Niroj
• 82,680 points
592 views
+1 vote
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, 2020 in Laravel by Niroj
• 82,680 points
7,192 views
0 votes
1 answer

How to check if a string is an email address in PHP?

Hello, Try this without regular expressions: <?php ...READ MORE

answered Nov 3, 2020 in PHP by Niroj
• 82,680 points
155 views
0 votes
1 answer

How to concatenate text from multiple rows into a single text string in SQL server?

Hello @kartik, Use COALESCE: DECLARE @Names VARCHAR(8000) SELECT @Names = ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,680 points
1,306 views