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


<?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)

$query = "SELECT * FROM wmb_members INNER JOIN wmb_pics ON wmb_members.UserID=wmb_pics.UserID ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

When i try a LEFT JOIN, i only get 2 results per page and not 10?!

$query = "SELECT * FROM wmb_members LEFT JOIN wmb_pics ON wmb_pics.UserID=wmb_members.UserID ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

please help!

When i try a LEFT JOIN, i only get 2 results per page and not 10?!

$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

so what do i do now?

so what do i do now?
first I would make the script to to work without pics, only members.

best regards

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

the script works! I am already showing members!


<?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!

please help!

this is a improvisation not a solution. I hope will work until you find a solution:

$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

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.

I remember just having this problem just yesterday. totally wow this is exactly the problem I had.