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 12 of 12
  1. #1
    Banned
    Join Date
    Mar 2007
    Posts
    1,523
    Thanks
    116
    Thanked 0 Times in 0 Posts

    Question 2 tables not joining

    Happy New Year to you all!

    I have tblMembers and tblPics, I have 23 members and 6 pictures for 2 members I want to JOIN the two tables and show all members and the 6 pictures for the 2 members, my code at the moment just shows all members. When I tried a join i think the $offset, $rowsPerPage where messing things up?!

    thanks

    Code:
    <?php
    include_once("config.php");
    doCSS(); ?>
    <?php
    ##### PAGINATION ###########################################
    // how many rows to show per page
    $rowsPerPage = 10;
    
    // by default we show first page
    $pageNum = 1;
    
    $self = $_SERVER['PHP_SELF'];
    
    // if $_GET['page'] defined, use it as page number
    if(isset($_GET['page']))
    {
    	$pageNum = $_GET['page'];
    }
    
    // counting the offset
    $offset = ($pageNum - 1) * $rowsPerPage;
    
    // how many rows we have in database
    $query  = "SELECT COUNT(UserID) AS numrows FROM wmb_members";
    $row  = mysql_fetch_assoc(mysql_query($query));
    $numrows = $row['numrows'];
    
    // how many pages we have when using paging?
    $maxPage = ceil($numrows/$rowsPerPage);
    
    // creating 'previous' and 'next' link
    // plus 'first page' and 'last page' link
    
    // print 'previous' link only if we're not
    // on page one
    if ($pageNum > 1)
    {
    	$page = $pageNum - 1;
    	$prev = "<a href=\"$self?page=$page\">[Prev]</a>";
    	$first = "<a href=\"$self?page=1\">[First Page]</a>";
    } 
    else
    {
    	$prev  = '[Prev]';       // we're on page one, don't enable 'previous' link
    	$first = '[First Page]'; // nor 'first page' link
    }
    
    // print 'next' link only if we're not
    // on the last page
    if ($pageNum < $maxPage)
    {
    	$page = $pageNum + 1;
    	$next = " <a href=\"$self?page=$page\">[Next]</a> ";
    	
    	$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
    } 
    else
    {
    	$next = '[Next]';      // we're on the last page, don't enable 'next' link
    	$last = '[Last Page]'; // nor 'last page' link
    }
    
    ##### / PAGINATION ##############################################
    // check if form has been submitted
    
    
    $jq_add_div_strings = '';
    $jq_div_classes_csv = '';
    $counter = 0;
    
    //startarray
    
    $bikearray = array();
    
    $query  = "SELECT * FROM wmb_members ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

    the join I tried, brought back 2 members (the ones with the pictures)
    Code:
    $query  = "SELECT * FROM wmb_members INNER JOIN wmb_pics ON wmb_members.UserID=wmb_pics.UserID ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

  • #2
    Banned
    Join Date
    Mar 2007
    Posts
    1,523
    Thanks
    116
    Thanked 0 Times in 0 Posts

    Question

    When i try a LEFT JOIN, i only get 2 results per page and not 10?!
    Code:
    $query  = "SELECT * FROM wmb_members LEFT JOIN wmb_pics ON wmb_pics.UserID=wmb_members.UserID ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

  • #3
    Banned
    Join Date
    Mar 2007
    Posts
    1,523
    Thanks
    116
    Thanked 0 Times in 0 Posts
    please help!

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by jarv View Post
    When i try a LEFT JOIN, i only get 2 results per page and not 10?!
    Code:
    $query  = "SELECT * FROM wmb_members LEFT JOIN wmb_pics ON wmb_pics.UserID=wmb_members.UserID ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";
    that's because you restrict the results set to members what match the condition wmb_pics.UserID = wmb_members.UserID

    best regards

  • #5
    Banned
    Join Date
    Mar 2007
    Posts
    1,523
    Thanks
    116
    Thanked 0 Times in 0 Posts
    so what do i do now?

  • #6
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by jarv View Post
    so what do i do now?
    first I would make the script to to work without pics, only members.

    best regards

  • #7
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by jarv View Post
    so what do i do now?
    I want to make clear something, The reason I didn't give a usefull answer in my previous post is because I read your other thread about this join and I didn't realy understand what happend. The solution in this thread depend of this join therefore I suggested to make the script to work only with members query.

    happy new years and best regards

  • #8
    Banned
    Join Date
    Mar 2007
    Posts
    1,523
    Thanks
    116
    Thanked 0 Times in 0 Posts
    the script works! I am already showing members!

    Code:
    <?php
    include_once("config.php");
    doCSS(); ?>
    <?php
    ##### PAGINATION ###########################################
    // how many rows to show per page
    $rowsPerPage = 10;
    
    // by default we show first page
    $pageNum = 1;
    
    $self = $_SERVER['PHP_SELF'];
    
    // if $_GET['page'] defined, use it as page number
    if(isset($_GET['page']))
    {
    	$pageNum = $_GET['page'];
    }
    
    // counting the offset
    $offset = ($pageNum - 1) * $rowsPerPage;
    
    // how many rows we have in database
    $query  = "SELECT COUNT(UserID) AS numrows FROM wmb_members";
    $row  = mysql_fetch_assoc(mysql_query($query));
    $numrows = $row['numrows'];
    
    // how many pages we have when using paging?
    $maxPage = ceil($numrows/$rowsPerPage);
    
    // creating 'previous' and 'next' link
    // plus 'first page' and 'last page' link
    
    // print 'previous' link only if we're not
    // on page one
    if ($pageNum > 1)
    {
    	$page = $pageNum - 1;
    	$prev = "<a href=\"$self?page=$page\">[Prev]</a>";
    	$first = "<a href=\"$self?page=1\">[First Page]</a>";
    } 
    else
    {
    	$prev  = '[Prev]';       // we're on page one, don't enable 'previous' link
    	$first = '[First Page]'; // nor 'first page' link
    }
    
    // print 'next' link only if we're not
    // on the last page
    if ($pageNum < $maxPage)
    {
    	$page = $pageNum + 1;
    	$next = " <a href=\"$self?page=$page\">[Next]</a> ";
    	
    	$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
    } 
    else
    {
    	$next = '[Next]';      // we're on the last page, don't enable 'next' link
    	$last = '[Last Page]'; // nor 'last page' link
    }
    
    ##### / PAGINATION ##############################################
    // check if form has been submitted
    
    
    $jq_add_div_strings = '';
    $jq_div_classes_csv = '';
    $counter = 0;
    
    //startarray
    
    $bikearray = array();
    
    $query  = "SELECT * FROM wmb_members ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";
    it's just when i change the $query to show the wmb_pics table, then it breaks and i require help!

  • #9
    Banned
    Join Date
    Mar 2007
    Posts
    1,523
    Thanks
    116
    Thanked 0 Times in 0 Posts
    please help!

  • #10
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    this is a improvisation not a solution. I hope will work until you find a solution:
    PHP Code:
    $query  "SELECT * FROM wmb_members ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";
    $result mysql_query($query);
    $members = array();
    if(
    $result){
       while(
    $rows mysql_fetch_assoc($result)){
            
    $members[$rows['UserID']] = $rows;
       }
       
    $secondquery "select * from wmb_pics where UserID in (".join(','array_keys($members)).")";
       
    $result mysql_query($secondquery);
       if(
    $result){
           while(
    $row mysql_fetch_assoc($result)){
              
    $members[$row['UserID']] = array_merge($members['UserID'],$row);
           }
       }

    best regards

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,438
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    I thought we gave you the answer in the other thread. Since you chose not to use that answer, which I still feel is the right one, I guess you'll have to figure out some completely different way.

    If the answer in the other thread didn't work, then telling us *HOW* it didn't work--and executing the query in a DB tool instead of in PHP--would be the next logical step.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    Banned
    Join Date
    Oct 2009
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I remember just having this problem just yesterday. totally wow this is exactly the problem I had.
    Last edited by Fou-Lu; 01-05-2010 at 06:42 PM. Reason: Removed image


  •  

    Posting Permissions

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