...

View Full Version : hard time with double array filter



durangod
11-09-2010, 11:49 PM
Hi i am having a hard time getting this to work and not sure why. what im trying to do is this..

i have two tables, one has the value "Yes" that i want to grab all those records with the value "Yes"

then i want to take that array and use that result as a filter and grab all the user id's info out of my member table from that array...

but i cant get my brain around it.. best i have done is get it to bring up one record and i know there are 13 total..

here is what i have so far





elseif(isset($_REQUEST['Couples']))
{
$query="select adv_userid from adverts_dropdown where adv_areyouacouple='Yes'";
$retvalc=mysql_query($query, $link) or die(mysql_error());
$sql_arrayc = mysql_fetch_object($retvalc);

while(mysql_fetch_array($sql_arrayc))
{
//grab the user id of each record out of the result above
$cplid=$sql_arrayc->adv_userid;

//now use that userid to pull the member info from the member table into its own array

$mem = "select mem_userid, mem_username, mem_email, mem_lastvisit, mem_ip from members where mem_userid='$cplid'";
}

}


any help would be greatly appreciated, thanks

MattF
11-09-2010, 11:52 PM
Use an INNER JOIN and make those two queries into one.


p.s: Who thought of that naming convention for tables/columns? It's absolutely horrid to read and work with.

Keleth
11-09-2010, 11:56 PM
The loop doesn't do anything but overwrite $mem over and over... is there more code you're not showing in the loop? And any reason you pull it as an object instead of an array?

Also, I'm pretty sure you can't use mysql_fetch_array on an object... it has to be used on a mysql result.

durangod
11-10-2010, 12:21 AM
yeah later down the line it grabs $mem and processes it into a list, i thought if i preloaded $mem with what i needed it would work.

$mem is actually the array for all the members that will be printed on the list.


object instead of an array? , good question, i was thinking i could pull the userid as an object and then use that as the select filter.

ok thanks all for the help, ill look into the inner join (reading it now) first, then try to load it as an array and try that...

thanks so much at least i have a clearer direction now... thanks again..

Keleth
11-10-2010, 12:26 AM
Another thing to bare in mind, if you're just getting a bunch of ids.... use IN and one query. There's no need to do multiple identical queries.

MattF
11-10-2010, 12:57 AM
$query="select a.adv_userid, m.mem_userid, m.mem_username,
m.mem_email, m.mem_lastvisit, m.mem_ip FROM
adverts_dropdown AS a INNER JOIN members AS m
ON m.mem_userid=a.adv_userid WHERE a.adv_areyouacouple='Yes'";


Providing I've understood the structure correctly, that query should work.

durangod
11-10-2010, 02:29 AM
thank you so much matt, from the examples i found online it would have taken me forever, never thought about using the a. and m., its working great other than when i go to view the next page of records after 10 records

works fine on normal member list (119 records all records)selection but when i have couples it hickups when i go to next page, it shows 10, then i click next buttom and it goes back to normal member list, then i click couples option again and it shows the last three that it didnt show before,

this was the hardest part getting this join to work im sure the list next is just a small mod to make in the next button code.

thank you so very much, you saved me tons of time, your awesome

MattF
11-10-2010, 04:26 AM
If you're using pagination, you would want something like, (I believe this is the correct syntax for MySQL, off the top of my head):



$query='select m.mem_userid, m.mem_username,
m.mem_email, m.mem_lastvisit, m.mem_ip FROM
adverts_dropdown AS a INNER JOIN members AS m
ON m.mem_userid=a.adv_userid WHERE a.adv_areyouacouple=\'Yes\'
LIMIT '.$start.', '.$quantity;


Start is the row you want to start from and quantity is the amount of rows you want returning per query, so if you're working on a ten result per page setup, page one would be:



$start = 0;
$quantity = 10;


and page two would be:



$start = 10;
$quantity = 10;


etc, etc. Btw, I removed the adv_userid from the query because the mem_userid is giving you the same id? No point extracting duplicate information.

durangod
11-10-2010, 06:57 PM
thanks matt im using

http://phpsense.com/php/php-pagination-script.html

i think its a pagination problem there not with what you wrote, for some reason even when i make all the var for my pagination totally unique to itself its still pulling up and mixing the original output list that loads when you load the page with my new list data, i wrote a whole new pagination script and set it a new var for new pagination and still mixs the data so ill have to tweek it more to get it totally seperate, maybe even just open a new window so i have a fresh template to work with, so its not you at all bud or what you wrote,

you did good bud.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum