trying to join Members table and members pics table?!

[jarv]

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";

[Mchl]

Please do not double post.

 

Use LEFT JOIN instead.

[jarv]

I didn't mean to double post, I have used Left Join several times and it just brings back 2 results, the 2 members that have pictures!

[Mchl]

Mind showing us a table structure for these two tables?

[praveenhotha]

Do a left join on members table i.e, members left join pics .. that will fetch all the members irrespective of their photos..

[jarv]

I have tried this: "SELECT * FROM wmb_members LEFT JOIN wmb_pics ON wmb_members.UserID=wmb_pics.UserID ORDER BY rsDatetaken DESC";

 

as i said before it only brings back 2 members, the 2 that have pictures

[printf]

LEFT OUTER JOIN

[Mchl]

LEFT OUTER JOIN === LEFT JOIN

 

Anyway, if it returns two rows only, it means there are only two rows in the wmb_members table.

Or you did not save/upload your changes.

[PFMaBiSmAd]

I'm going to guess that either the LIMIT term is causing this or your code that is retrieving and displaying the results is causing this.

 

Call me skeptical, but you haven't exactly provided any evidence of what you are getting vs what your data is that should be producing more results.

 

When you echo $query and then execute that query directly in your favorite database management tool, what do you get? What IS your php code that displays the result of the query? For all we know it has a logic error in it that causes it to stop after two entries have been displayed. Have you done a 'view source' of the output in your browser so that you know there are only two entries? Perhaps there is a HTML markup error that is preventing the remainder of the entries from being displayed.