Using two where clauses in MYSQL UPDATE query using PHP

Question

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.

mysql supports update with order by and limit

Drew
– Drew

2016-04-24 14:49:33 +00:00
Commented Apr 24, 2016 at 14:49 — Drew
– Drew, Commented Apr 24, 2016 at 14:49

Drew · Accepted Answer · 2016-04-24 14:52:02Z

1

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;

answered Apr 24, 2016 at 14:52

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DMort Over a year ago

Drew, thanks for the example. When I had 2 where clauses separated by AND I what getting an error on the page. Something similar to 'unexpected clause: prepStatus = 0 on line blah blah blah' Do I have to change something else?

Drew Over a year ago

You will need to work on the model, the shopping cart, etc. Plus, unless you like concatenating strings (and who does), shoot for mysqli with parameter binding php.net/manual/en/mysqli-stmt.bind-param.php

Thorsten Kettner · Accepted Answer · 2016-04-24 16:47:15Z

0

There is a flaw in your datamodel. You have items sold and invoices but no connection between them.

So you muddle your way through be saying: when there is an invoice on a day for a customer it must be an invoice exactly covering all items on that very day bought by that customer. This is a rule you are kind of making up - the database doesn't tell you this.

So have a reference in your sold items table to the invoice table. Once an invoice is entered in the system it must be linked to all items sold it includes. Thus the update is easy, or better not necessary, as it is not the sigle item sold which is paid, but the invoice. So the sold items shouldn't have columns paidstatus and paidtime, but the invoice should.

UPDATE: Here is an example for a working data model. Each item has an invoice number which is null at first. Once the customer checks out an invoice is written and all the customer's items without an invoice number get this new invoice number. The invoice's total amount is the sum of its items. I.e. you don't store the sum redundantly.

auction_item

auction_item_no - primary key
customer_no - not null
description - not null
price - not null
invoice_no - nullalble (null as long as not checked out)

invoice

invoice_no - composite primary key part 1
customer_no - composite primary key part 2
date_time - not null

edited Apr 24, 2016 at 16:47

answered Apr 24, 2016 at 14:52

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

4 Comments

DMort Over a year ago

I get what your saying. Ultimately, what you said is what I planned to do but I couldn't really figure out how my data model should have been, particularly how I save all the items the customer bought. A customer could buy 1 item, or 300 items.

DMort Over a year ago

The program is for an auction service, so people will ultimately be bidding for items, and when they checkout they will be buying every single item they bid on, regardless. Maybe they will check out 1 time, leave, then come back and check out 1 more time; but each time they check out they will be paying for every single item that isn't marked as paid. Is the data model still horrendous in this case?

Thorsten Kettner Over a year ago

I don't say it's "horrendous" :-) But still, either you have invoices in your database, then they should have a connection to the items (with the invoice getting paid explicitly and its items only implicitly), or you don't, then simply mark items as paid on checkout. As to how to change the datamodel for an invoice connected to the items: Every item_sold would have an invoice_id.

DMort Over a year ago

Thank you very much for the detailed answer. I think i'm on the right track now. In the meantime between your edit I did the reverse of what your edit said :-/ . I made an itemids column in the invoice table and saved all corresponding item numbers in an imploded string using commas as separators. Your way is much more efficient, so I'll make that update soon. Thanks.

Collectives™ on Stack Overflow

Using two where clauses in MYSQL UPDATE query using PHP

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related