Multiple Table Query, with Page Count (offset PageNum)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Multiple Table Query, with Page Count (offset PageNum)

Post by simonmlewis »

Here's a rather long bit of code. The problem is at the count(id) section - line 71/72.

Code: Select all

<?php 
$search = $_POST['search'];
include "dbconn.php";
echo "<div class='categorytitle'><div class='categorytitleinner'>Scrappage Vehicle Search for $search</div></div>";
$rowsPerPage = 20;
 
// by default we show first page
$pageNum = 1;
 
// if $_GET['pagenum'] defined, use it as page number
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}
 
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
    
echo "<table width='100%' cellpadding='0' cellspacing='0'>";
$result = mysql_query ("SELECT * FROM dxvehicles INNER JOIN dxusers on dxusers.id = dxvehicles.userid 
 
WHERE (dxvehicles.make LIKE '%$search%' OR dxvehicles.title LIKE '%$search%' OR dxvehicles.description LIKE '%$search%' OR dxvehicles.id = '$search' OR dxusers.company LIKE '$search' OR dxusers.town LIKE '$search') AND dxvehicles.stockstatus = 'in stock' AND dxvehicles.scrappage = 'scrappage' LIMIT $offset, $rowsPerPage") or die (mysql_error());
 
while ($row = mysql_fetch_object($result))
      {
 
$userid = $row->userid;
            echo "<tr valign='top'><td width='90'><div class='listimagebox'>";
            if ($row->photoprimary == NULL) { echo "<a href='index.php?page=vehicle&menu=make&make=$row->make&vehicle=$row->id&title=for sale' title='Look at the $row->title'><img src='images/car_blank.jpg' /></a>";}
            elseif ($row->photoprimary != NULL) { echo"
            <a href='index.php?page=vehicle&menu=make&make=$row->make&vehicle=$row->id&title=for sale' title='Look at the $row->title'><img src='images/productphotos/$row->photoprimary' border='0' /></a>";}
            
            echo "</div></td>
            <td>
            <div class='listvehicletitle'><a href='index.php?page=vehicle&menu=make&make=$row->make&vehicle=$row->id&title=for sale' title='Look at the $row->title'>$row->make $row->title</a></div></div>
            <div class='listvehiclesubtitle'>$row->subtitle</div>
            <div class='listvehiclesubdetail'>Reg. Date: $row->regdate, Mileage: $row->mileage, <br/>";
            
            $resultuser = mysql_query ("SELECT id, town, company, salesamount FROM dxusers WHERE id = '$userid'");
while ($rowuser = mysql_fetch_object($resultuser))
{
echo "<b><a href='#' title='$rowuser->company' style='text-decoration: none'>Dealer: $rowuser->company, $rowuser->town.</a>&nbsp;</b>";
 
if($rowuser->salesamount < 100) { echo "Not yet ranked.";}
elseif($rowuser->salesamount >= 100 && $rowuser->salesamount <= 399) { echo "&nbsp;<img src='images/stars1.jpg' />";}
elseif($rowuser->salesamount >= 400 && $rowuser->salesamount <= 699) { echo "&nbsp;<img src='images/stars2.jpg' />";}
elseif($rowuser->salesamount >= 700 && $rowuser->salesamount <= 1099) { echo "&nbsp;<img src='images/stars3.jpg' />";}
elseif($rowuser->salesamount >= 1100 && $rowuser->salesamount <= 1499) { echo "&nbsp;<img src='images/stars4.jpg' />";}
elseif($rowuser->salesamount >= 1500 && $rowuser->salesamount <= 1999) { echo "&nbsp;<img src='images/stars5.jpg' />";}
elseif($rowuser->salesamount > 1999) { echo "&nbsp;<img src='images/stars5plat.jpg' />";}
}
            mysql_free_result($resultuser);
            
            echo "</div>
            </td>
            <td><div class='listvehicleprice'>£$row->price</div>
                        <div class='contactsellerbox'><a href='index.php?page=contacts&menu=makes&title=contact dealer&id=$userid' class='contactsellerlink'>Contact the Dealer</a></div></td>
            </tr>
            <tr>
            <td colspan='3'><hr noshade size='1' color='#cccccc' /></td>
            </tr>
            ";
            }
            
            
    mysql_free_result($result);
    echo "</table>";
            
    echo "<div style='clear:both' />";
 
    $query   = ("SELECT COUNT(dxvehicle.id) AS numrows dxvehicles INNER JOIN dxusers on dxusers.id = dxvehicles.userid
     WHERE (dxvehicles.make LIKE '%$search%' OR dxvehicles.title LIKE '%$search%' OR dxvehicles.description LIKE '%$search%' OR dxvehicles.id = '$search' OR dxusers.company LIKE '$search' OR dxusers.town LIKE '$search') AND dxvehicles.stockstatus = 'in stock' AND dxvehicles.scrappage = 'scrappage' LIMIT $offset, $rowsPerPage") or die (mysql_error());
    
$result  = mysql_query($query) or die(mysql_error());
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
    // how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
 
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';
 
for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"index.php?page=categ&menu=make&make=$mycateg&pagenum=$page\" class='bodylink'>$page</a>";
   }
}
 
// creating previous and next link
// plus the link to go straight to
// the first and last page
 
if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"index.php?page=categ&menu=make&make=$mycateg&pagenum=$page\" class='bodylink'>[Prev]</a> ";
 
   $first = " <a href=\"index.php?page=categ&menu=menu=make&make=$mycateg&pagenum=1\" class='bodylink'>[First Page]</a>";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}
 
if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"index.php?page=categ&menu=make&make=$mycateg&pagenum=$page\" class='bodylink'>[Next]</a>";
 
   $last = " <a href=\"index.php?page=categ&menu=make&make=$mycateg&pagenum=$maxPage\" class='bodylink'>[Last Page]</a>";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}
 
// print the navigation link
echo "<div class='navpages'>" . $first . $prev . $nav . $next . $last . "</div>";
    mysql_close($sqlconn);
?>
Some of the web addresses in the page numbers are wrong, but that's irrelevant to make it work.

The error I get is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dxvehicles INNER JOIN dxusers on dxusers.id = dxvehicles.userid WHERE (dxvehi' at line 1
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Multiple Table Query, with Page Count (offset PageNum)

Post by andyhoneycutt »

Code: Select all

$query   = ("SELECT COUNT(dxvehicle.id) AS numrows dxvehicles INNER JOIN dxusers on dxusers.id = dxvehicles.userid
     WHERE (dxvehicles.make LIKE '%$search%' OR dxvehicles.title LIKE '%$search%' OR dxvehicles.description LIKE '%$search%' OR dxvehicles.id = '$search' OR dxusers.company LIKE '$search' OR dxusers.town LIKE '$search') AND dxvehicles.stockstatus = 'in stock' AND dxvehicles.scrappage = 'scrappage' LIMIT $offset, $rowsPerPage") or die (mysql_error());
// ... 
You're missing a comma:

Code: Select all

$query   = ("SELECT COUNT(dxvehicle.id) AS numrows[b][color=#000000],[/color][/b] dxvehicles INNER JOIN dxusers on dxusers.id = dxvehicles.userid
     WHERE (dxvehicles.make LIKE '%$search%' OR dxvehicles.title LIKE '%$search%' OR dxvehicles.description LIKE '%$search%' OR dxvehicles.id = '$search' OR dxusers.company LIKE '$search' OR dxusers.town LIKE '$search') AND dxvehicles.stockstatus = 'in stock' AND dxvehicles.scrappage = 'scrappage' LIMIT $offset, $rowsPerPage") or die (mysql_error());
// ...
-Andy
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Multiple Table Query, with Page Count (offset PageNum)

Post by simonmlewis »

Thanks.
With that updated, I now get this error in the Page Number sector of the page:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN dxusers on dxusers.id = dxvehicles.userid WHERE (dxvehicles.make L' at line 1
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Multiple Table Query, with Page Count (offset PageNum)

Post by andyhoneycutt »

Oh wow, i should have caught this right off. My apologies! You're missing a FROM clause. You have to specify a FROM table, bare minimum.

Code: Select all

$query   = ("SELECT COUNT(dxvehicle.id) AS numrows FROM dxvehicles INNER JOIN dxusers on dxusers.id = dxvehicles.userid
    WHERE (dxvehicles.make LIKE '%$search%' OR dxvehicles.title LIKE '%$search%' OR dxvehicles.description LIKE '%$search%' OR dxvehicles.id = '$search' OR dxusers.company LIKE '$search' OR dxusers.town LIKE '$search') AND dxvehicles.stockstatus = 'in stock' AND dxvehicles.scrappage = 'scrappage' LIMIT $offset, $rowsPerPage") or die (mysql_error());
// ...
I believe instead of having a comma where I told you to earlier, you actually need to put your FROM predicate.

-Andy
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

[RESOLVED] Re: Multiple Table Query, with Page Count

Post by simonmlewis »

Bingo!!

I altered

Code: Select all

("SELECT COUNT(dxvehicle.id)
to

Code: Select all

("SELECT COUNT(dxvehicles.id)
(my bad...)
As there is no table called dxvehicle. But hey, perfect. Thanks, as that is one heck of a lot of code in the section.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Multiple Table Query, with Page Count (offset PageNum)

Post by andyhoneycutt »

Awesome deal!
Post Reply