Jump to content


Photo

ORDER BY isn't working - please help


  • Please log in to reply
3 replies to this topic

#1 danielforsyth

danielforsyth
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 03 March 2006 - 12:11 AM

I am trying to display the contents of a mysql table in descending order by "id". The script works fine until I add in the ORDER BY to the query. You can see below that I have the ORDER BY coded only if $category = hand, so I get an error now if category = hand, but not if category = baby or if cat = makeup.

// how many rows to show per page
$rowsPerPage = 6;

// by default we show first page
$pageNum = 1;
$category = $_GET['category'];
// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

if ($category == "hand"){
$query  = "SELECT price, title, id FROM tm_inventory WHERE category='hand' ORDER BY id DESC";
} elseif ($category == "baby"){
$query  = "SELECT price, title, id FROM tm_inventory WHERE category='baby'";
} elseif ($category == "makeup"){
$query  = "SELECT price, title, id FROM tm_inventory WHERE category='makeup'";
}
           
$pagingQuery = "LIMIT $offset, $rowsPerPage";
$result = mysql_query($query . $pagingQuery) or die('Error 1, query failed');

// print the inventory info in table
echo '<table border="0" cellpadding="2" cellspacing="0">';
while($row = mysql_fetch_array($result))
{
   $row2 = mysql_fetch_array($result) or $row2['id'] = "1" and $row2['title'] = "&nbsp;";
   $row3 = mysql_fetch_array($result) or $row3['id'] = "1" and $row3['title'] = "&nbsp;";
   printf("<tr><td align='center'> <img src=\"image3.php?id=%s\" width='150'><br> %s <br> %s </td> \n", $row["id"], $row["title"], $row["price"]);
   printf("<td align='center'> <img src=\"image3.php?id=%s\" width='150'><br> %s <br> %s </td> \n", $row2["id"], $row2["title"], $row2["price"]);
   printf("<td align='center'> <img src=\"image3.php?id=%s\" width='150'><br> %s <br> %s </td></tr>", $row3["id"], $row3["title"], $row3["price"]);
}
echo '</table>';
echo '<br>';

// how many rows we have in database
$result  = mysql_query($query) or die('Error 2, query failed');
$numrows = mysql_num_rows($result);

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];

// 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?category=$category&page=$page\">[Prev]</a> ";

    $first = " <a href=\"$self?category=$category&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?category=$category&page=$page\">[Next]</a> ";

    $last = " <a href=\"$self?category=$category&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
}

// print the page navigation link
//echo $first . $prev . " Page <strong>$pageNum</strong> of <strong>$maxPage</strong> " . $next . $last;
echo $prev . " &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Page <strong>$pageNum</strong> of <strong>$maxPage</strong> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " . $next;

Here are some links so you can see what happens:

[a href=\"http://root.danielforsyth.com/mysql/image_upload/view5.php?category=hand\" target=\"_blank\"]http://root.danielforsyth.com/mysql/image_...p?category=hand[/a]

[a href=\"http://root.danielforsyth.com/mysql/image_upload/view5.php?category=baby\" target=\"_blank\"]http://root.danielforsyth.com/mysql/image_...p?category=baby[/a]

Please tell me how to fix this - thx

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 03 March 2006 - 12:54 AM

PHP error, when you concatenate the string later you end up with:

SELECT price, title, id FROM tm_inventory WHERE category='hand' ORDER BY id DESCLIMIT x,y

DESCLIMIT is bad mmkay?

#3 danielforsyth

danielforsyth
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 03 March 2006 - 05:01 AM

got it, so I just need a space after the DESC?

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 03 March 2006 - 05:41 AM

Yep




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users