The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 23, 2019, 02:36:44 AM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: in_array and mysql  (Read 3683 times)
deregular
Expert
****
Offline Offline

Posts: 172


View Profile
« on: September 23, 2008, 03:47:28 AM »

I dont believe ive had to do this before.. well not in any efficient manner anyways.
Code:
$sql=mysql_query("SELECT title FROM events WHERE username='".in_array($names)."' LIMIT 10");
^^Doesnt work just to demonstrate what i need to do ^^

I want to do something like this as I dont like the idea of running a foreach() on the array and then
a subsequent mysql call for each as my array may end up being quite large.

Any ideas guys?
Logged
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #1 on: September 23, 2008, 07:32:59 AM »

Code:
$sql=mysql_query("SELECT title FROM events WHERE username IN ('".implode("','",$names)."') LIMIT 10");

you were almost there.
you needed to use IN instead of =
and you needed to implode the array to make it a string of quoted words.

edit: fixed the $names part... duh
« Last Edit: September 23, 2008, 09:24:52 AM by nutballs » Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #2 on: September 23, 2008, 08:51:34 AM »

That's really cool NBs, of course that makes complete sense, but I've never used IN with an array of values rather than a subquery.

I'm curious what the in_array is for though... in fact, I don't think this is the correct way to phrase this query because in_array returns a boolean based on the second parameter being something you are looking for to see if it is "in the array."

I think for what you are trying to do you simply need to get rid of the in_array:

$nameStr = "'" . implode("', '", $names) . "'";
$sql = mysql_query("select title from events where username in ($nameStr)");

/*
For those following along, given 3 names, Nutballs, Perk and Deregular in the array, this would create a string that looks like:
'Nutballs', 'Perk', 'Deregular'
*/

which would be a correct query, if I am not mistaken (just tried it, worked great).

Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #3 on: September 23, 2008, 09:24:20 AM »

bah i didnt even notice the in_array. lol

editing my post...


i use IN all the time, since I tend to end up doing alot of wierd shit with data from multiple sources.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
deregular
Expert
****
Offline Offline

Posts: 172


View Profile
« Reply #4 on: September 23, 2008, 08:46:43 PM »

When i saw nb's post last night  iwent ahead and tried it and yeh it threw errors, I probably should have posted, but figured instead of being a nagging bitch id sleep on it and have another go this morning to get it to work before replying.

Am going to try it out again now.


Logged
deregular
Expert
****
Offline Offline

Posts: 172


View Profile
« Reply #5 on: September 23, 2008, 08:51:24 PM »

yep works beeeyootiful. thanks fellas.
Im liking this "IN" thing. Most useful.

 
Logged
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #6 on: September 23, 2008, 10:37:52 PM »

IN is often used in subselects like this oversimplified example:

select * from books where fk_userid in (select userid from authors where authorbirthdate>'1/1/2000')

people will say to use a join, I say they haven't seen my crazy ass SQL statements.

IN is your bestest buddy sometimes. And NOT IN is your cheap whore.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
deregular
Expert
****
Offline Offline

Posts: 172


View Profile
« Reply #7 on: September 23, 2008, 11:08:54 PM »

thanks m8

Looks like I have a lot of research and playing to do.
I never bothered with joins as most of my projects have been fairly small and could always
run an extra mysql call or loop without it being disastrous and to be honest they always confused
me, but Im now looking at more efficient ways of doing things, as the projects begin getting larger and more complex.

Time to quit the procrastinating and step myself up another notch.
Logged
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #8 on: September 23, 2008, 11:12:53 PM »

That was my deal Nuts, only used IN and NOT IN with subqueries. I REALLY like this, rather than spontaneously creating huge frigging OR statements.

Of course I never did that, really, it's the truth.  Roll Eyes
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #9 on: September 24, 2008, 07:26:13 AM »

ok now that I have had a sip of coffee...

here is why you would use an IN (array)
when you get a dataset from an xml feed for example.
or a multiphrase search entered by a user
or calculated results

in my case those are the IN times. and the situation where I have to use IN instead of JOIN occasionally.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #10 on: September 24, 2008, 10:29:12 AM »

JOINs are faster than nested queries in MySQL a lot of times.

If it's just one nested query, go to town. If you start going 3-4 levels deep, you need to either switch to using some JOINs, or be ready to go out to dinner while your query finishes executing.
Logged

hai
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!