MySQL Join where matched columns contain different values

问题: The below Select used to work perfectly using Mysql_Select with a for loop however since updating to mysqli_connect and using a while loop I've hit a snag. $query =...

问题:

The below Select used to work perfectly using Mysql_Select with a for loop however since updating to mysqli_connect and using a while loop I've hit a snag.

$query = 
    "SELECT * 
    FROM 
        jobs
        LEFT JOIN invoices ON jobs.jobID=invoices.jobID
    WHERE jobs.customerID = '$selectedcustID' 
    ORDER BY date_auto DESC";

$result = mysqli_query($connection, $query) or die(mysqli_error());

if(mysqli_num_rows($result) > 0) {          
    while($row = mysqli_fetch_assoc($result)) {
        $selectedjobID = $row["jobID"];
        $date = $row["date_auto"];
        $formatdate =  date("d/m/Y", $date);
        $status = $row["status"];
        $notes = $row["notes"];
        $invoiceStatus = $row["invoiceStatus"];
        $invoicePaid = $row["paid"];
        $invoiceNumber = $row["invoiceID"];
        $formatinvoiceNumber = sprintf("%05d", $invoiceNumber);

The common parameter is the jobID column in the jobs and invoices tables but, as the invoice will not exist until raised, the display of the jobID in my php output list of jobs is now blank. I think the reason is that it is using the invoices version of jobID (being NULL) rather than the jobs version.

I've tried all kinds of ways of working around this but am running round in circles. I'm thinking that it will be a case of saying SELECT * from jobs and SELECT jobID AS but I'm not sure on the correct syntax.

Any help would be greatly appreciated.


回答1:

As the invoice will not exist until raised, the display of the jobID in my php output list of jobs is now blank

Don't use SELECT * : you have a jobID field on both ends of the LEFT JOIN, and you end up with the wrong field being picked (ie invoices.jobID, which is NULL when the invoice was not yet raised).

You want to explicitely select the fields you need, like :

SELECT  
    jobs.jobID,
    ...
FROM 
    jobs 
    LEFT JOIN invoices ON jobs.jobID = invoices.jobID
 WHERE jobs.customerID = '$selectedcustID'
ORDER BY date_auto DESC
  • 发表于 2019-01-09 23:46
  • 阅读 ( 144 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除