join

[lloydsbackyard]

<?php





$query = "SELECT 

        studinfos.lname, 
        studinfos.fname,
        studinfos.mname,
        studinfos.suffix,
        studenroinfos.yearLevel,
        studenroinfos.sy
        FROM studinfos
        INNER JOIN studenroinfos ON studinfos.idNumber=studenroinfos.idNumber
        WHERE 
        (studinfos.idNumber='$searchString') OR (studinfos.lname='$searchString') OR (studinfos.fname='$searchString')";
    $result = mysql_query($query);
    echo "<table border=0 style='border-collapse: collapse' width=95%>";


    while ($record = mysql_fetch_array($result)){
        $name= $record['lname'].', '.$record['fname'].' '.$record['mname'].' '.$record['suffix'];
        /* when sql is modified already i can insert this code here
        if ($record['hasPaid'])==1{
            $remarks="PAID";
            }
          else
          {
          $remarks="...";
            }
       */
        echo "<tr>
              <td><font size=2><a href=payment.php?page=22?&idNo={$record['idNumber']}>{$record['idNumber']}</a>
              <td><font size=2>{$name}</td>           
              <td><font size=2>{$record['yearLevel']}</td>
              <td><font size=2>{$record['sy']}</td>
              //display here the $remarks
              </tr>";
            }        
        echo "</table>";
}

?>

the above sql code consist of 2 tables...heres my question...I want to add another table named payment which has the idNumber and hasPaid fields.

how do i modify my sql and php code so that it returns that all the search idNumber and if the hasPaid=1 it displays "PAID" else it displays "..."

[C#Jaap]

When you're working with MySQL the query below will work, but will only return rows that has an payment record. When you alter the INNER JOIN into LEFT JOIN you'll get the original result, extended with the hasPaid column that contains a null value when no paymend record with idNumber == studinfo.idNumber can be found.

$query = "SELECT 

        studinfos.lname, 
        studinfos.fname,
        studinfos.mname,
        studinfos.suffix,
        studenroinfos.yearLevel,
        studenroinfos.sy,
        payment.hasPaid
        FROM studinfos
        INNER JOIN studenroinfos ON studinfos.idNumber=studenroinfos.idNumber
        INNER JOIN payment on payment.idNumber = studinfos.idNumber
        WHERE 
        (studinfos.idNumber='$searchString') OR (studinfos.lname='$searchString') OR (studinfos.fname='$searchString')";

I suppose you can fetch and use the result a proposed by your commented out code.

[lloydsbackyard]

thanks