/    Sign up×
Community /Pin to ProfileBookmark

[RESOLVED] UPDATE MySQL db

Copy linkTweet thisAlerts:
Jun 22.2009

Hi all,

I have a basic knowledge of php and MySql so bare with me.

Basically i have it so i can update my product in db so its a “featured product”, this works fine. But if i go back and update the db again but with a different product, this will now also be a featured product and i then have two products that are “featured products”. What i want to happen is when i click a product to be a featured product it updates the db, but if i then choose a diff product to be the featured product then the db will be updated so that product is the featured product and the other product now isn’t.

Any help would be great.

Cheers

to post a comment
PHP

6 Comments(s) ↴

Copy linkTweet thisAlerts:
@SyCoJun 23.2009 — You could use a column on the details table to track the featured product in 2 ways. You could set a flag and update the new product to 1 and set all others to 0 so it's the only product or use a time stamp. When ever a product is made a featured product update its timestamp, then to get the featured product, select and order by timestamp and limit 1. You have the advantage of keeping the history of featured products using the timestamp way of doing it.
Copy linkTweet thisAlerts:
@W444YN3-31authorJun 23.2009 — Hi SyCo,

Thanks for your reply.

I think the "flag" solution sounds perfect to me, as long as say when i choose a diff product each time to be the featured product that the other products will have a value of "0" and the new one will be "1". And each time i choose a new product to be the featured product it will make the other one "0" and the new one "1".

If this is possible with the flag solution, is there any chance you could give me some advice on how this is done as i've not heard of this before.

Cheers
Copy linkTweet thisAlerts:
@kurbyJun 23.2009 — [CODE]Update products set Featured = 0;[/CODE]

Sets all products to not be featured.

[CODE]Update products set Featured = 1 where ID = NEWID;[/CODE]

Pick the featured one.
Copy linkTweet thisAlerts:
@SyCoJun 23.2009 — Exactly as you said. Just run 2 update statements one to reset all products to 0 then update the new featured flag to 1. Ideally this would be in a transaction. I don't use MySQL and transactions are a little different to MSSQL so you'd have to look that up to be sure this is correct.

//cant post code grrr.
Copy linkTweet thisAlerts:
@SyCoJun 23.2009 — Sorry can't post the code and tried to PM you too. You need to wrap this in a transaction

mysql_query('begin')

then test the queries ran correctly. then commit them or roll back.
Copy linkTweet thisAlerts:
@W444YN3-31authorJun 24.2009 — Hi people,

Cheers for all the help, got it all working now!

Kinda logical solution really, sometimes you just need someone to say it first.

thanks again.
×

Success!

Help @W444YN3-31 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.19,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...