How to relate two column in PHP MYSQL

Question

I have created one request table that contain all request information.

The table containing these columns:

requestid , itemrequest1, itemrequest2, itemrequest3, quantity1, quantity2, quantity3

How to make a relation so that if the itemrequest has a value in column itemrequest2, it will take the quantity from column quantity2.

My query is like this:

$query2=mysql_query("select * from tbl_request WHERE unit='$unit' AND  (itemrequest1='$itemrequest' or itemrequest2='$itemrequest' or  itemrequest3='$itemrequest')");
$record_num=mysql_num_rows($query2);

while ($data1 = mysql_fetch_array($query2))

The problem is that when the $itemrequest has a value in column itemrequest3, the quantity will always show the quantity from column 1.

Answer 1

You can use conditions in your query in order to select the right column depending on another column's value.

SELECT requestid, 
    (CASE WHEN itemrequest1 = '$itemrequest' THEN quantity1
        ELSE (
            CASE WHEN itemrequest2 = '$itemrequest' THEN quantity2
            ELSE (
                CASE WHEN itemrequest3 = '$itemrequest' THEN quantity3
            END)
        END)
    END)
FROM tbl_request

You should have a close look to your database structure though, if you have to do things like this, it might be better to improve your structure and have better tables/fields.