This site is no longer active and is available for archival purposes only. Registration and login is disabled.

MySQL help


MySQL help

Postby Dan East » Oct 17, 2005 @ 2:04pm

I've got two tables, items and categories. Each item has a reference to the category it belongs to. Now for performance reasons I want each category to store a count of the number of items it contains.

I have a query that deactivates all expired items. When an item is deactivated I also need to decrement the appropriate category's item count. I can do this by iterating the items programatically outside of SQL, but I would like to do it the "proper" way with a single query making MySQL do all the work.

Here's the query that deactivates expired items:
UPDATE items SET status=3 WHERE status=1 AND NOW()>enddate

So for each item that is deactivated I also need to do this:
UPDATE categories SET count=count+1 WHERE id=$targetid LIMIT 1

$targetid would be the category id of the item that was deactivated.

Now I've done work in selecting values from multiple tables, via joins and the like, but I'm not sure how to go about updating values in multiple tables at the same time. Hopefully someone can save me some trial and error. :)

Thanks.

Dan East
User avatar
Dan East
Site Admin
 
Posts: 5264
Joined: Jan 25, 2001 @ 5:19pm
Location: Virginia, USA


Postby David Horn » Oct 17, 2005 @ 2:34pm

Shouldn't it be SET count=count-1, or have I got the wrong end of the stick here? ;)
Crosswind technique: "Using your peripheral vision, react to body movements, gasps, groans, and shouts from the other side of the cockpit, and always remember that it's better to be lucky than good."
User avatar
David Horn
<b>The Boss</b>
 
Posts: 1867
Joined: Feb 17, 2002 @ 2:10pm
Location: PocketGamer Towers (New York, Paris, Leeds)


Postby Dan East » Oct 17, 2005 @ 2:43pm

No, you're right. :)

Dan East
User avatar
Dan East
Site Admin
 
Posts: 5264
Joined: Jan 25, 2001 @ 5:19pm
Location: Virginia, USA


Postby refractor » Oct 17, 2005 @ 3:51pm

User avatar
refractor
pm Insider
 
Posts: 2304
Joined: Feb 5, 2002 @ 1:12pm
Location: Luxembourg


Postby David Horn » Oct 18, 2005 @ 11:01am

Crosswind technique: "Using your peripheral vision, react to body movements, gasps, groans, and shouts from the other side of the cockpit, and always remember that it's better to be lucky than good."
User avatar
David Horn
<b>The Boss</b>
 
Posts: 1867
Joined: Feb 17, 2002 @ 2:10pm
Location: PocketGamer Towers (New York, Paris, Leeds)


Postby David Horn » Oct 21, 2005 @ 7:57pm

Crosswind technique: "Using your peripheral vision, react to body movements, gasps, groans, and shouts from the other side of the cockpit, and always remember that it's better to be lucky than good."
User avatar
David Horn
<b>The Boss</b>
 
Posts: 1867
Joined: Feb 17, 2002 @ 2:10pm
Location: PocketGamer Towers (New York, Paris, Leeds)


Postby Dan East » Oct 21, 2005 @ 9:17pm

Thanks for the assistance guys. I'm going to just retotal them all anytime a change is made, since changes will be relatively seldom.

The version of MySQL we're using doesn't support nested queries, so I'm iterating the categories in php for now.

Dan East
User avatar
Dan East
Site Admin
 
Posts: 5264
Joined: Jan 25, 2001 @ 5:19pm
Location: Virginia, USA


Return to Technology Discussion


Sort


Forum Description

Discussion on technology that isn't related to mobile devices.

Moderator:

Kevin Gelso

Forum permissions

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

cron