Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,383
    Thanks
    264
    Thanked 32 Times in 31 Posts

    hard time with double array filter

    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


    Code:
    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

  • #2
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    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.

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    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.

  • #4
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,383
    Thanks
    264
    Thanked 32 Times in 31 Posts
    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..

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    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.

  • #6
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Code:
    $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.

  • #7
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,383
    Thanks
    264
    Thanked 32 Times in 31 Posts
    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

  • #8
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    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):

    Code:
    $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:

    Code:
    $start = 0;
    $quantity = 10;
    and page two would be:

    Code:
    $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.

  • #9
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,383
    Thanks
    264
    Thanked 32 Times in 31 Posts
    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.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •