How to change sql query with my requirement?

问题: In my sql database,I have two tables one is Invoices and another table called invoiceitems.So am fetching customer details from invoices(invoices table) along with the invo...

问题:

In my sql database,I have two tables one is Invoices and another table called invoiceitems.So am fetching customer details from invoices(invoices table) along with the invoice items(from invoice item table). I am facing one problem in my sql code.Datas are not fetching if invoice have no items in the invoiceitems table.

I want to fetch all datas including if invoices have no items in the invoice item table. Below sql code only generating the invoices have the invoiceitems. So can anyone help me to do this??

$sql = "SELECT 
        a.cusName, 
        a.cusMob, 
        a.invoiceNo,
        a.invoiceDate, 
        a.total_VAT, 
        a.bill_tot,
        b.itemsName ,
        b.rate, 
        b.amt_vat,
        ROUND(b.amt_vat + b.amount, 2) as amount
    FROM 
        invoices a, 
        invoice_items b 
    where 
        a.invoiceID=b.invoiceid
        and a.invoiceDate between '$getfromdate' and '$gettodate' 
        and a.status IS NULL 
    order by 
        a.invoiceID desc";

回答1:

You should try and change your SQL to the newer JOIN notation as it will make this sort of thing a lot simpler, then you can use a LEFT JOIN to indicate this is an optional table...

SELECT a.cusName, a.cusMob, a.invoiceNo,
       a.invoiceDate, a.total_VAT, a.bill_tot,
       b.itemsName ,b.rate, b.amt_vat,
       ROUND(b.amt_vat + b.amount, 2) as amount
  FROM invoices a
  LEFT JOIN invoice_items b ON a.invoiceID=b.invoiceid
  WHERE a.invoiceDate between '$getfromdate' and '$gettodate' 
      and a.status IS NULL 
  order by a.invoiceID desc

I would also recommend that you look at using prepared statements as they have a lot of advantages.


回答2:

If you want result also when invoice_items have no value yous hould use left join and if null

$sql    = "SELECT a.cusName
                , a.cusMob
                , a.invoiceNo
                , a.invoiceDate
                , a.total_VAT
                , a.bill_tot
                , b.itemsName 
                , b.rate
                , b.amt_vat
                , ROUND(b.amt_vat + inull(b.amount,0), 2) as amount
            FROM invoices a
            LEFT JOIN invoice_items b ON a.invoiceID=b.invoiceid
            WHERE a.invoiceDate between '$getfromdate' 
                and '$gettodate' and a.status IS NULL 
            order by a.invoiceID desc";

You should also avoid old implicit join sinta based on where and use explicit join sintax the also let you use left join (outer join)

you should also avoid the use of php var in you sql code .. you are at risk for sqlinjection .. for this take a look at your db driver for prpared statement and bindig param

  • 发表于 2019-03-28 15:56
  • 阅读 ( 160 )
  • 分类:sof

条评论

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

篇文章

作家榜 »

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