0

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.

Oldskool
  • 34,211
  • 7
  • 53
  • 66
john
  • 7
  • 3
  • you can split the table i mean table 1: requestid table 2: requestid itemrequest quantity you can easily get the record – Ramki Jun 26 '15 at 09:20
  • you mean i need to create new table? – john Jun 26 '15 at 09:22
  • 2
    Please be aware that the `mysql_` functions are now no longer just discouraged (as it was over the last years), but officially [deprecated](http://php.net/manual/en/migration55.deprecated.php). You should really use [MySQLi](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/ref.pdo-mysql.php), as this code will stop working very soon. Also see [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). Additionally, read up on SQL injection. Your code is currently wide open to attackers. – Oldskool Jun 26 '15 at 09:22
  • you restrcture like this means useful for take the vaue – Ramki Jun 26 '15 at 09:23

1 Answers1

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.

  • thank you for the solution. I will try use the CASE solution. How to improve the database structure?. does i need to combine all the itemrequest1, itemrequest2 and item request3 in one column only? – john Jun 26 '15 at 09:36
  • It depends on the context, on what you are trying to do. You might want to combine all in one column if it is possible (and maybe have to add a "type" field), or you might need to create different tables... I can't tell you without knowing what are these items and itemrequests etc. What I mean is... what happens the day you have an itemrequest4? Will you have to add new fields, change all your queries etc.? If so, your current structure is not that good. –  Jun 26 '15 at 09:47
  • Thanks sis, the solution is working. loop using for condition. (y) – john Jun 27 '15 at 14:12