MortHub MortHub - 2 years ago 88
SQL Question

Using two where clauses in MYSQL UPDATE query using PHP

Working on a lightweight invoicing system and I'm trying to update my 'items' table correctly without fully succeeding.

Once an invoice is saved, I need to mark the items as paid that were purchased so far that day for that buyer.

Right now this is my query:

$markitemspaidquery = "UPDATE solditems SET paidstatus='paid', paidtime='$date' WHERE buyerid='$buyer_id'";

This updates the records for the buyer correctly, but the problem is it will mark EVERY item in the table for that buyer 'paid'; even if it is from 3 days ago.

How can I use a query kind of like this one or achieve this affect?

$markitemspaidquery = "UPDATE solditems SET paidstatus='paid', paidtime='$date' WHERE buyerid='$buyer_id' AND DATE(timesold)=CURDATE() AND paidstatus='unpaid'";

In all reality, everything should be paid by the end of the day anyway because of the way the company works, but I would like to know for future reference since it's just using up unnecessary resources to update every item for the buyer.

Answer Source

Here is an example with order by and limit

update questions
set prepStatus=1,status_bef_change=status,cv_bef_change=closeVotes,max_cv_reached=greatest(max_cv_reached,closeVotes)
where status='O' and prepStatus = 0
order by qId desc
limit 900;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download