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,520 points
968 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,840 points

Related Questions In PHP

0 votes
0 answers

How to convert an array to a string in PHP?

What can I do to get the ...READ MORE

May 30, 2022 in PHP by Kichu
• 19,040 points
629 views
0 votes
0 answers

How to convert a string to an array in php

I want to convert a string into ...READ MORE

Jun 1, 2022 in PHP by Kichu
• 19,040 points
562 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 8, 2020 in PHP by Niroj
• 82,840 points
12,433 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
5,153 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,840 points
23,063 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,840 points
3,013 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,840 points
2,940 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,840 points
44,858 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,840 points
3,986 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,840 points
3,087 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP