Problemo with LIMITing a QUERY

[badassrocker]

I'm brand spankin' new to PHP & MySQL...

This is what I have so far to display my results in pages limited to 10 results per page...

---Results viewable on [a href=\"http://www.badassrockers.com/data/music-instruments/zzounds/products.php?category=6-String%20Acoustic%20Guitars\" target=\"_blank\"]http://www.badassrockers.com/data/music-in...ustic%20Guitars[/a]

<?php
$s = $_GET['s'];

if (empty($s)){
$s=0;
}
$limit = 10;
$category = @$_GET['category'];
$query = mysql_query("SELECT Name, URL_Image_Small, Price, URL_Product, Product_Type, Category, Description, Popular FROM db WHERE Category LIKE '$category' ORDER BY Popular");

if (!$query) {
exit('<p>Error performing query: ' . mysql_error() . '</p>');
}


?>
<?php
$numresults = $query;
$numrows = @mysql_num_rows($numresults);
//here's where I'm trying to limit... ie: "LIMIT 0, 10"
$query .= " LIMIT $s, $limit";
$result = $query;

while ($row = mysql_fetch_array($result)) {
$name = $row['Name'];
$image_small = $row['URL_Image_Small'];
$price = $row['Price'];
$link = $row['URL_Product'];
$desc = $row['Description'];

//When the code above is used, this echo doesn't display at all...
echo ("<td class='row1h' align='center' width='15%'><a href='$link'><img src='$image_small'></a></td><td class='row1h' align='center' width='75%'><a href='$link'>$name</a><br>$desc</td><td class='row1h' align='center' width='10%'>$$price</td><tr>");
}
?>

So... Problemo is... It doesn't display any results...

I've noticed that when I take out " $query .= "LIMIT $s, $limit"; " from above, it displays results...but, it displays ALL of the results. That's why I'm trying to use LIMIT in there... It's just not working right... Any ideas on what my problem is?

I hope I've listed everything that is relevant...

[earl_dc10]

Unless this isn't what you want, couldn't you just do

[code]
$query = mysql_query("SELECT Name, URL_Image_Small, Price, URL_Product, Product_Type, Category, Description, Popular FROM db WHERE Category LIKE '$category' ORDER BY Popular LIMIT 10");
[/code]

I use this on my blog (slightly altered) and Im assuming that $s is a page id or something, so just multiply $s*10 to get the id's you want to display (($s*10)-1 if your id's start at 0)

[badassrocker]

[!--quoteo(post=362692:date=Apr 7 2006, 07:22 PM:name=earl_dc10)--][div class=\'quotetop\']QUOTE(earl_dc10 @ Apr 7 2006, 07:22 PM) [snapback]362692[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Unless this isn't what you want, couldn't you just do

[code]
$query = mysql_query("SELECT Name, URL_Image_Small, Price, URL_Product, Product_Type, Category, Description, Popular FROM db WHERE Category LIKE '$category' ORDER BY Popular LIMIT 10");
[/code]
[/quote]

No, it's not really what I want...

When I do that, it obviously limits my results to 10...but...

What I want to do...(If I can explain it and make sense)...Is get all of the results from the initial query, [i]then[/i] limit to 10 per page... Otherwise, there are no pages... Just 10 results on one page...

Make sense? Am I being too difficult? It sure is hard to explain something when you don't know a whole lot about it...

[!--quoteo(post=362693:date=Apr 7 2006, 07:29 PM:name=badassrocker)--][div class=\'quotetop\']QUOTE(badassrocker @ Apr 7 2006, 07:29 PM) [snapback]362693[/snapback][/div][div class=\'quotemain\'][!--quotec--]
No, it's not really what I want...

When I do that, it obviously limits my results to 10...but...

What I want to do...(If I can explain it and make sense)...Is get all of the results from the initial query, [i]then[/i] limit to 10 per page... Otherwise, there are no pages... Just 10 results on one page...

Make sense? Am I being too difficult? It sure is hard to explain something when you don't know a whole lot about it...
[/quote]

Well... Here's the script I'm using to move between pages... Maybe this will make more sense...

[code]<?php
        
        $categor = eregi_replace(' ','%20',$category);
        
        echo ("<br />");
        
        if ($s >= 1){
          $prev=($s - $limit);
          echo (" <a href=\"$PHP_SELF?category=$categor&s=$prev\"><< Prev 10</a>  ");
        }
        $pages = intval($numrows/$limit);
        
        if ($numrows%$limit){
          $pages++;
        }
        if (!((($s+$limit)/$limit) == $pages) && $pages!=1){
          $next = $s+$limit;
          echo (" <a href=\"$PHP_SELF?category=$categor&s=$next\">Next 10 >></a>");
        }
        $a = $s + $limit;
        if ($a > $numrows){
          $a = $numrows;
        }
        $b = $s + 1;
        echo ("<p>Showing products $b to $a of $numrows</p>");
        ?>[/code]

[anatak]

Here are two tutorials about what you want to do

[a href=\"http://www.phpfreaks.com/tutorial_cat/25/Page-Number--Pagination.php\" target=\"_blank\"]http://www.phpfreaks.com/tutorial_cat/25/P...-Pagination.php[/a]

what you do wrong is
can you echo your $query ?
after you do
$query .= " LIMIT $s, $limit";

maybe you have something in your query that makes you come up with 0 results ?

anatak

[badassrocker]

[!--quoteo(post=362694:date=Apr 7 2006, 07:34 PM:name=anatak)--][div class=\'quotetop\']QUOTE(anatak @ Apr 7 2006, 07:34 PM) [snapback]362694[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Here are two tutorials about what you want to do

[a href=\"http://www.phpfreaks.com/tutorial_cat/25/Page-Number--Pagination.php\" target=\"_blank\"]http://www.phpfreaks.com/tutorial_cat/25/P...-Pagination.php[/a]

what you do wrong is
can you echo your $query ?
after you do
$query .= " LIMIT $s, $limit";

maybe you have something in your query that makes you come up with 0 results ?

anatak
[/quote]

I'll check out those tutorials...

If you look at the bottom of [a href=\"http://www.badassrockers.com/data/music-instruments/zzounds/products.php?category=6-String%20Acoustic%20Guitars\" target=\"_blank\"]My Webpage[/a], you'll see that is returns the number of rows in the set, so I know there are results... And if I take out $query .= " LIMIT $s, $limit"; it displays everything... But that's the problem... I only want it to display 10 per page...

[earl_dc10]

ahh, I see what you mean now, pagination I think they call it (not really sure) yeah, ok in essence what you want to do is this :
[code]
$select_all = "SELECT * FROM $table"; // can replace * with a column that has all rows filled
$num_all = mysql_num_rows($select_all);

// now run that select script with a limit in it

//and for the pages (really simplified)
for($x = 0; $x <= $num_all; $x++)
   {if($x % 10 == 0)
    echo "page stuff";
   }
[/code]

use the select limit query in conjunction with pagination and you should get what you want,

EDIT looks like I take too long to type ;)

[badassrocker]

[!--quoteo(post=362697:date=Apr 7 2006, 07:40 PM:name=earl_dc10)--][div class=\'quotetop\']QUOTE(earl_dc10 @ Apr 7 2006, 07:40 PM) [snapback]362697[/snapback][/div][div class=\'quotemain\'][!--quotec--]
ahh, I see what you mean now, pagination I think they call it (not really sure) yeah, ok in essence what you want to do is this :
[code]
$select_all = "SELECT * FROM $table"; // can replace * with a column that has all rows filled
$num_all = mysql_num_rows($select_all);

// now run that select script with a limit in it

//and for the pages (really simplified)
for($x = 0; $x <= $num_all; $x++)
   {if($x % 10 == 0)
    echo "page stuff";
   }
[/code]

use the select limit query in conjunction with pagination and you should get what you want,

EDIT looks like I take too long to type ;)
[/quote]


Bitchin'... I'll check out the tutorials a little more, and give what you told me a shot... I definitely appreciate your time...

[badassrocker]

OK...

I guess I'm just a dunce... I'm becoming extremely irritated now... I've gone through the tutorials...it seems really simple...and it makes perfect sense to me...however...I can't make it work...

Would someone be willing to take my .php file and figure out what I am doing wrong? I am at a total loss now...

[anatak]

I took a look at your page.

you have the pagination working I guess as I see the
prev 10 and next 10
in your url I see the s=0 / 10 /20

<?php
$numresults = $query;
$numrows = @mysql_num_rows($numresults);
//here's where I'm trying to limit... ie: "LIMIT 0, 10"
$query .= " LIMIT $s, $limit";
$result = $query;

I don't want to sound stupid but I think you never execute the query
$query .= " LIMIT $s, $limit";
you execute your query to get the number of results but never the query to get the actual data

don't you want to do something like
$result = mysql_query($Query) or die(mysql_error());

anatak

[badassrocker]

[!--quoteo(post=362724:date=Apr 7 2006, 10:45 PM:name=anatak)--][div class=\'quotetop\']QUOTE(anatak @ Apr 7 2006, 10:45 PM) [snapback]362724[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I took a look at your page.

you have the pagination working I guess as I see the
prev 10 and next 10
in your url I see the s=0 / 10 /20

<?php
$numresults = $query;
$numrows = @mysql_num_rows($numresults);
//here's where I'm trying to limit... ie: "LIMIT 0, 10"
$query .= " LIMIT $s, $limit";
$result = $query;

I don't want to sound stupid but I think you never execute the query
$query .= " LIMIT $s, $limit";
you execute your query to get the number of results but never the query to get the actual data

don't you want to do something like
$result = mysql_query($Query) or die(mysql_error());

anatak
[/quote]

Here's what I did...


[code]
$query .= " LIMIT $s, $limit";
$result = @mysql_query($query);
[/code]

Here's what it outputs:

Result is err - Error performing query: 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 'Resource id #3 LIMIT 0, 10' at line 1

[anatak]

can you do an
echo $query;
before you execute it ?
that way I can have a look at it without the code getting in the way.

post the echo in here please

anatak