Jump to content

Problemo with LIMITing a QUERY


badassrocker

Recommended Posts

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...
Link to comment
Share on other sites

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)
Link to comment
Share on other sites

[!--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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

[!--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...
Link to comment
Share on other sites

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 ;)
Link to comment
Share on other sites

[!--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...
Link to comment
Share on other sites

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...
Link to comment
Share on other sites

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
Link to comment
Share on other sites

[!--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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.