MySQL- IF Statement error

Question

I am having a problem with this SQL Statement:

$sql="SELECT b.FrameNumber, b.BikeCode, IF(b.ProductID = 0, p.FrameNumber) 
   AS FrameNumber 
 FROM BikeStock b LEFT JOIN Purchase p ON b.FrameNumber = p.FrameNumber 
 WHERE b.bikecode = '$bikecode'";

I want the SQL statement to work in this way:

Select FrameNumber & BikeCode from BikeStock 
THEN IF ProductID(BikeStock) is equal to 0 
     then it will grab the framenumber from the purchase table where bikecode is equal to $bikecode

Any Ideas?

Thanks in advance.

knittl · Accepted Answer · 2012-12-09 19:09:02Z

1

The MySQL IF statement has the following syntax: IF(condition, expression if true, expression if false).

As far as I can see from your question you are actually looking for a WHERE clause (remember, you can have multiple conditions in WHERE – use boolean operators to combine them):

SELECT b.FrameNumber, b.BikeCode, p.FrameNumber
FROM BikeStock b
LEFT JOIN Purchase p ON b.FrameNumber = p.FrameNumber
WHERE b.bikecode = :bikecode AND b.ProductID = 0

answered Dec 9, 2012 at 19:09

knittl

269k59 gold badges339 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hii Over a year ago

Thank you very much, this worked however if it enters 0 is it possible to display a message saying "Out of Stock to the user"

knittl Over a year ago

You're probably best of replacing the value 0 with the error string in your frontend. (otherwise: IF(p.FrameNumber = 0, 'Out of stock', p.FrameNumber) FrameNumber

Olaf Dietsche · Accepted Answer · 2012-12-09 19:07:57Z

1

You must add an else part to your if()

SELECT b.FrameNumber, b.BikeCode,
       IF(b.ProductID = 0, p.FrameNumber, NULL) AS FrameNumber
FROM BikeStock b
LEFT JOIN Purchase p ON b.FrameNumber = p.FrameNumber
WHERE b.bikecode = '$bikecode'

answered Dec 9, 2012 at 19:07

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Comments

Benjamin Paap · Accepted Answer · 2012-12-09 19:08:17Z

0

You forgot an else part for that IF statement:

$sql = "SELECT b.FrameNumber, b.BikeCode, IF(b.ProductID = 0, p.FrameNumber, NULL /* ELSE HERE */) AS FrameNumber 
        FROM BikeStock b 
        LEFT JOIN Purchase p 
            ON b.FrameNumber = p.FrameNumber 
        WHERE b.bikecode = '$bikecode'";

answered Dec 9, 2012 at 19:08

Benjamin Paap

2,7442 gold badges24 silver badges33 bronze badges

Comments

echo_Me · Accepted Answer · 2012-12-09 19:13:49Z

0

try this

    $sql="SELECT b.FrameNumber, b.BikeCode, p.FrameNumber (
          CASE WHEN b.ProductID = 0 THEN p.FrameNumber ELSE NULL END AS Framenumber)  
          FROM BikeStock b 
          LEFT JOIN Purchase p ON b.FrameNumber = p.FrameNumber 
          WHERE b.bikecode = '$bikecode'";

answered Dec 9, 2012 at 19:13

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Collectives™ on Stack Overflow

MySQL- IF Statement error

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related