How to compare 2 values of each row from 2 tables?

Question

how to compare 2 values from 2 table?

i have using 2 while (nested while) but the table check one by one and the result in second table is from each row in first table. how to make the result printed once?

i want the result like this result's image

and here is my currently code

<?php               
                    $query1 = mysql_query("SELECT * FROM riwayat_training_detail AS s JOIN materi AS t JOIN riwayat_training AS u JOIN karyawn AS v WHERE v.id_karyawan = $id1 AND s.nik LIKE v.nik AND u.subject no LIKE t.subject no AND s.id_riwayat_training LIKE u.id_riwayat_training ");
                    $i=1;
                    while($row1 = mysql_fetch_array($query1))
                    {
                        $date = $row1['date'];
                        $subject1 = $row1['subject name'];
                ?>
            <table class="table table-bordered">
                <tr>
                    Training yang diikuti
                    <td class="table-bordered">No</td>
                    <td class="table-bordered">date</td>
                    <td class="table-bordered">subject date</td>
                    <td class="table-bordered">No SOP</td>
                    <td class="table-bordered">revision no</td>
                    <td class="table-bordered">Trainer</td>
                    <td class="table-bordered">institution</td>
                </tr>

                <tr>
                    <td class="table-bordered"><?php echo $i; ?></td>
                    <td class="table-bordered"><?php echo date("j/F/Y", strtotime($date)); ?></td>
                    <td class="table-bordered"><?php echo $subject1; ?></td>
                    <td class="table-bordered"><?php echo $row1['no_sop']; ?></td>
                    <td class="table-bordered"><?php echo $row1['revision_no']; ?></td>
                    <td class="table-bordered"><?php echo $row1['trainer']; ?></td>
                    <td class="table-bordered"><?php echo $row1['institution']; ?></td> 
                </tr>
            </br>
            <table class="table table-bordered">
                <tr>
                    Training yang belum diikuti
                    <td class="table-bordered">No</td>
                    <td class="table-bordered">subject no</td>
                    <td class="table-bordered">subject date</td>
                    <td class="table-bordered">No SOP</td>
                </tr>
                <?php
                    $query2 = mysql_query("SELECT * FROM header_jabatan AS r JOIN materi AS q JOIN karyawn AS p WHERE q.subject no LIKE r.id_header AND r.id_jabatan LIKE  p.id_jabatan AND p.id_karyawan = $id1 ORDER BY q.subject no ASC");
                    $x=1;
                    while($row2 = mysql_fetch_array($query2))
                    {
                        $subject2 = $row2['subject name'];
                        if (strcasecmp($materi1, $materi2) != 0)
                        {
                ?>
                <tr>
                    <td class="table-bordered"><?php echo $i; ?></td>
                    <td class="table-bordered"><?php echo $row2['subject no'] ?></td>
                    <td class="table-bordered"><?php echo $subject22; ?></td>
                    <td class="table-bordered"><?php echo $row2['no_sop']; ?></td>
                </tr>     
                <?php  
                        }
                    $x++;       
                    }
                    $i++;
                }

                ?>
            </table>
        </table>

please help me what should i do?

it's my second question with same topic, i really need help im stuck with this..

Please do not use mysql_* functions http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — n00bstacker, Mar 09 '16 at 13:51
How is your query running...? The syntax is completely broken... — Siyual, Mar 09 '16 at 13:54

score 0 · Answer 1 · answered Mar 09 '16 at 14:10

0

you can use CASE Statement Check this link for more detail how to use it.

check this link

answered Mar 09 '16 at 14:10

Pankaj Monga

179
2
8

score 0 · Answer 2 · answered Mar 09 '16 at 14:35

0

Is this something along the lines of what you want?

UPDATE table2 SET Status = "YES" WHERE SubjectName IN (SELECT SubjectName FROM table1)

Or do you want it to also use the revision? Your picture only talks about SubjectName.

answered Mar 09 '16 at 14:35

CodyMR

415
4
17

Thanks for the answer. But if there have new input, it will be update automatically? – Mochammad Helmi Mar 09 '16 at 17:08
@MochammadHelmi If new information is added to table1 then the query would have to be reran. If you did an update and only added one new column then you can use the query I used in the answer but you can add a WHERE clause to the nested SELECT to make it search only for the specific piece of information that was added. Example of this would be if subject YYY is inserted into table1 then you could just do *** UPDATE table2 SET Status = "YES" WHERE SubjectName IN (SELECT SubjectName FROM table1 WHERE SubjectName = "YYY")*** . Or rerun the update if more than one was added. – CodyMR Mar 10 '16 at 14:40
@MochammadHelmi Also if you were to save it as a stored procedure I believe you could run the update everytime something is inserted. Just make a stored procedure that uses both insert and the update. I am not 100% sure on that but I would try it. – CodyMR Mar 10 '16 at 14:46

score 0 · Answer 3 · answered Mar 09 '16 at 14:50

In your case on the result's images above, it's better to use LEFT JOIN or RIGHT JOIN

See below example:

> CREATE TABLE table1 (subject VARCHAR(10), value INT);
> CREATE TABLE table2 (id INT, subject VARCHAR(10));

> INSERT INTO table1 VALUES ('XXX', 23),('ZZZ', 24);
> INSERT INTO table2 VALUES (1, 'XXX'),(2, 'YYY'), (3, 'ZZZ');

> SELECT * FROM table1;
+---------+-------+
| subject | value |
+---------+-------+
| XXX     |    23 |
| ZZZ     |    24 |
+---------+-------+

> SELECT * FROM table2;
+------+---------+
| id   | subject |
+------+---------+
|    1 | XXX     |
|    2 | YYY     |
|    3 | ZZZ     |
+------+---------+
> SELECT a.*, IF(b.value IS NULL, 'No', 'Yes') as status FROM table2 as a LEFT JOIN table1 as b ON b.subject = a.subject;
+------+---------+--------+
| id   | subject | status |
+------+---------+--------+
|    1 | XXX     | Yes    |
|    3 | ZZZ     | Yes    |
|    2 | YYY     | No     |
+------+---------+--------+

Thanks for help, i wanna ask how about i have 2 different query with so many JOIN table? and i think i cant use IF clause in query.. — Mochammad Helmi, Mar 09 '16 at 19:14

How to compare 2 values of each row from 2 tables?

3 Answers3