vForums: Message Board Support - Fantastic MySQL Trick

Topic Rating:	Printable View

Marc
vChat Developer

I <3 Rossy

Posts: 3,388
Status: Offline
Gender: Male
Location: Ontario, Canada
Age: 32
Joined:

Additional Groups:
Coding Team

Reputation: 40%

Fantastic MySQL Trick (10th Jun 10 at 2:09am UTC)
A few months ago, I came across this little MySQL function while working on a site, and I assumed that everyone knew about it and I'd just been ignorant, but since my discovery I've yet to find someone else who knew about it. So, I thought I'd share! Say you have a messaging system that allows for multiple users, and stores the message participants in a CSV in the column `users` (Ex: 1,4,16,18). How would you usually see if the current user is in that list? You'd grab the `users` list, explode() it, and loop through the array to see if it matched the cookie...well not anymore! SELECT * FROM messages WHERE FIND_IN_SET($_COOKIE['user'], users) For those of you who didn't know about this gem, enjoy! For those of you who did know, shame on you for not sharing.
Back to Top - Link to Post
rroll.to— Shorten a link, rickroll your friends.

dog199200
Guest

Re: Fantastic MySQL Trick (10th Jun 10 at 3:29am UTC)
interesting, well this might actually helps solve a problem I've been having.
Back to Top - Link to Post

Michael
Moderator

Recoding the future
Posts: 4,043
Status: Offline
Gender: Male
Location: UK
Joined:

Additional Groups:
Coding Team

Reputation: 58%

Re: Fantastic MySQL Trick (10th Jun 10 at 7:50am UTC)
Seems handy! I might have a use for it in my current job!
Back to Top - Link to Post

dog199200
Guest

Re: Fantastic MySQL Trick (10th Jun 10 at 3:38pm UTC)
speaking of jobs, marc, or michael, either of you wanting to make a little money XD Na I think I can manage, just getting bored of coding XD
Back to Top - Link to Post

Michael
Moderator

Recoding the future
Posts: 4,043
Status: Offline
Gender: Male
Location: UK
Joined:

Additional Groups:
Coding Team

Reputation: 58%

Re: Fantastic MySQL Trick (10th Jun 10 at 5:19pm UTC)
I code 9-5 every day. I have a freelance client lined up, a project for someone, and improvements to do to wImg... I'm fully booked!
Back to Top - Link to Post

Ross
Administrator

Posts: 3,709
Status: Offline
Gender: Male
Age: 8 ¹⁄₁
Joined:

Additional Groups:
Support Team

Reputation: 45%

Re: Fantastic MySQL Trick (10th Jun 10 at 5:20pm UTC)
I probably wouldn't explode, I'd use some kind of RegEx SELECT * FROM messages WHERE `users` REGEXP '(^\|,)ID_HERE(,\|$)' Handy to know though. I have used a MySQL function (which I forget the name of now) to do the opposite - check if the value in the database is one of a set list (in fact I think vForums uses it in a couple of places)
Back to Top - Link to Post

dog199200
Guest

Re: Fantastic MySQL Trick (10th Jun 10 at 6:25pm UTC)
XD yea well i tend not to use a lot of fancy coding, which is why i dont mind highering someone to look over my coding and do some editing to make it more secure and faster XD as for calling information, i think the most extreme i've done is: "SELECT * FROM random WHERE random_type='random'" or using escape() within an sql query XD
Back to Top - Link to Post

	Printable View