Fetch and echo data from second table based on value inside first table

[bcherdak]

Hello, I am trying to create a table/chart that has customers information in it.

I am setting a "work order status" status_id value for a customer when he/she checks in her computer with us as a numerical value.

In another table, I am storing statuses with the status_id and a status_text of the type of current statuses. So I can add more statuses based on type of work.

I was originally using Case switches like this and the case number would be corresponding to the value inside of the row.

$sql    = "SELECT * FROM workorder";
$result = $conn->query($sql);
   while($row = mysqli_fetch_array($result))  {
	//case switch
 $repair_status_color = "";
        $repair_status_text = "";
        switch ($row["status_id"]) {
            case 0:
                $color = "red";
                $repair_status_text = "Open";    
                break;
            case 1:
                $color = "blue";
                $repair_status_text = "Waiting";
                break;
            case 2:
                $color = "blue";
                $repair_status_text = "Waiting Parts";
                break;
        }
	        echo "<td>" . $repair_status_text . "</td>";
	}

    I am trying to get the value from workorder, check it against status and then echo the text from status_text from the table Status based on the status_id from the table workorder
    I have tried this code 

$sql = "SELECT * FROM workorder INNER JOIN status ON workorder.status_id = status.status_id";
echo $row["status_text"]

 

But I am not sure I am doing it right. Anyone notice what I am doing wrong?

Edited October 27, 2018 by bcherdak

[Barand]

That is why Codd gave us JOINs.

You join your work order table to the status table matching in the status id

SELECT wo.pay_type
     , wo.pay
     , st.status_name
FROM work_order as wo
       JOIN
     work_order_status as st 
         USING (status_id)

 

[bcherdak]

5 minutes ago, Barand said:

That is why Codd gave us JOINs.

You join your work order table to the status table matching in the status id

SELECT wo.pay_type
     , wo.pay
     , st.status_name
FROM work_order as wo
       JOIN
     work_order_status as st 
         USING (status_id)

 

I am trying to build off of what you said, but the workorder table has many rows in it, but i am specifically trying to use status_id as a value to grab from status table.

 

$sql    = "SELECT *, STATUS.status_name FROM WORKORDER as wo JOIN STATUS as st USING (status_id)";

So I am selecting ALL from Workorder table (since the chart will have all their information) and also grabbing status_name column from Status table, but the code isnt making much sense to me as how am i able to grab status.status_name FROM workorder? 

[bcherdak]

36 minutes ago, Barand said:

That is why Codd gave us JOINs.

You join your work order table to the status table matching in the status id

SELECT wo.pay_type
     , wo.pay
     , st.status_name
FROM work_order as wo
       JOIN
     work_order_status as st 
         USING (status_id)

 

I have figured it out from reading yours!

$sql    = "SELECT w.*, s.status_text FROM WORKORDER w INNER JOIN STATUS s ON w.status_id = s.status_id";
 echo "<td>" . $row["status_text"] . "</td>";

 

[Barand]

if you have two tables like these

workorder table                                                     workorder_status table
+-------+----------+------+-----------+---------------------+       +-----------+-----------------+
| wo_id | pay_type | pay  | status_id | created             |       | status_id | status_name     |
+-------+----------+------+-----------+---------------------+       +-----------+-----------------+
|     1 |        1 |    2 |         1 | 2018-10-27 18:59:59 |       |         1 | Open            |
|     2 |        1 |    3 |         4 | 2018-10-27 18:59:59 |       |         4 | Waitng approval |
|     3 |        1 |    4 |         5 | 2018-10-27 18:59:59 |       |         5 | Waiting parts   |
|     4 |        2 |   12 |         6 | 2018-10-27 18:59:59 |       |         6 | In progress     |
|     5 |        2 |   15 |         7 | 2018-10-27 18:59:59 |       |         7 | Finished        |
+-------+----------+------+-----------+---------------------+       +-----------+-----------------+

Then a query with a join gives

SELECT wo.wo_id as workorder
     , wo.pay_type
     , wo.pay
     , wo.status_id
     , st.status_name
FROM workorder wo
        JOIN
     workorder_status st 
        USING (status_id);
        
+-----------+----------+------+-----------+-----------------+
| workorder | pay_type | pay  | status_id | status_name     |
+-----------+----------+------+-----------+-----------------+
|         1 |        1 |    2 |         1 | Open            |
|         2 |        1 |    3 |         4 | Waitng approval |
|         3 |        1 |    4 |         5 | Waiting parts   |
|         4 |        2 |   12 |         6 | In progress     |
|         5 |        2 |   15 |         7 | Finished        |
+-----------+----------+------+-----------+-----------------+

 

[ginerjm]

And assign the color in the status table would eliminate the need for a switch statement later on?