Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 6 of 6
  1. #1
    Junior Member
    Join Date
    Jan 2003
    Posts
    11
    Member #
    524
    Hi I am using a MYSQL database and Apache webserver. I have a PHP page that queries my database using the standard PHP/MYSQL functions. I use while ( $row = mysql_fetch_array($result) ) {

    echo......
    To print out the result of the queries.
    The problem is it prints all of the results on one page( depending on what is searched for there can be hundreds of results).
    I would like to break this down into pages that display 50 results per page.( Just like on the forum page here where it has three pages of posts) Please help. THnaks

    I have attached the code page in case anyone needs it.

  2.  

  3. #2
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    Change the query to use the LIMIT keyword.

    http://www.mysql.com/doc/en/SELECT.html :
    The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments. The arguments must be integer constants. If two arguments are given, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1): To be compatible with PostgreSQL MySQL also supports the syntax: LIMIT # OFFSET #.
    mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15
    To retrieve all rows from a certain offset upto the end of the result set, you can use -1 for the second parameter:
    mysql> SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.
    If one argument is given, it indicates the maximum number of rows to return:
    mysql> SELECT * FROM table LIMIT 5; # Retrieve first 5 rows
    In other words, LIMIT n is equivalent to LIMIT 0,n.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  4. #3
    Junior Member
    Join Date
    Jan 2003
    Posts
    11
    Member #
    524
    The ways of using LIMIT that i have tried have not worked at least the standard way of using it because i need the full set of results from the search but i need to seperate it into pages for display.(i am not familiar with using -1 can you explain more?) I thought I would need to do some sort of loop where the offset is set to i and then increases each time and prints an href for each page i am just not sure exactly how to do this? I was hoping someone new of an easier way where the code would be applied to the array after the query is returned. I have several different queries from which to choose so i was thinking that there was some way to split the mysql_fetch_array into chunks of fifty for display. Am i going about this completely wrong?

  5. #4
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    If by per page you mean, for example, myfile.php?page=1 then LIMIT is definitely the way to go. Never query in a loop.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  6. #5
    Junior Member
    Join Date
    Jan 2003
    Posts
    11
    Member #
    524
    I kind of figured something out that works (sort of). I am not able to display the total amount of results and pages though(ex. Here are results 1-50 of 300...)There is probably a better way. I have attached the code if anyone has any ideas. Thanks

    <?php
    trim($searchvar);
    if (! $start){
    $start=0;
    }


    $dbcnx = mysql_connect("localhost", "webuser", "puff2332");
    if (! $dbcnx) {
    echo( "Unable to connect.");
    exit();
    }
    if (! mysql_select_db("bars", $dbcnx) ) {
    echo( "<p>Unable to get bars.</p>" );
    exit();
    }

    /* To search for all bars by location */
    if ($searchtype=="" AND $searchloc AND $searchvar=="") {
    $sql= "SELECT * FROM bar_info WHERE bar_loc=".$searchloc." ORDER BY bar_name LIMIT ".$start.",50";
    }
    /* To search all locations if one is not picked */
    elseif ($searchtype AND $searchloc=="1 OR 2 OR 3 OR 4 OR 5" AND $searchvar) {
    $sql= "SELECT * FROM bar_info WHERE " .$searchtype. " LIKE '%".$searchvar."%' ORDER BY bar_name LIMIT ".$start.",50";
    }
    /* To search all columns if a searchtype is not picked */
    elseif ($searchtype=="" AND $searchloc AND $searchvar) {
    $sql= "select * from bar_info where ((bar_name like '%".$searchvar."%' OR bar_address like '%".$searchvar."%' OR bar_phone like '%".$searchvar."%' OR bar_desc like '%".$searchvar."%' OR bar_spec like '%".$searchvar."%' OR bar_event like '%".$searchvar."%') AND (bar_loc=" . $searchloc. ")) ORDER BY bar_name LIMIT ".$start.",50" ;
    }
    /*To search locations by searchtype
    elseif ($searchtype AND $searchloc AND $searchvar=="") {
    $sql= "SELECT * FROM bar_info WHERE " .$searchtype. " LIKE '%".$searchvar."%' ORDER BY bar_name LIMIT ".$start.",50";
    }*/
    else {
    $sql= "SELECT * FROM bar_info WHERE((" .$searchtype." LIKE '%".$searchvar."%') AND (bar_loc=".$searchloc.")) ORDER BY bar_name LIMIT ".$start.",50";
    }

    echo ("<strong><font size=4>'" .$searchvar."'</strong></font> is what you searched for at ");
    echo date("H:i, jS F");
    echo "<br>";

    $result = mysql_query($sql, $dbcnx);

    if (!$result) {
    echo( "<p>There was an error performing the search. Try again.</p>");
    echo("<p><a href='index.html'><img src='Images/goback.jpg' width='136' height='41' border='0'></a>");
    exit();
    }
    $num_results=mysql_num_rows($result);
    echo("Here are the first ". $num_results. " bars match your search criteria.");
    if($num_results==0) {
    echo("<p> Try searching again using only the first few letters of the word.</p>");
    echo("<p><a href='index.html'><img src='Images/goback.jpg' width='136' height='41' border='0'></a>");
    }

    echo("<p><strong><table width='100%' border='2' bordercolor='#999999'>");

    while ( $row = mysql_fetch_array($result) ) {

    echo( "<tr><td><font color='#FFFFFF'><a href=/bar_description.php?bar_id=" .$row["bar_id"].">" .$row["bar_name"] . "</a>");
    echo("</strong><br><font size=1>" .$row["bar_address"]);
    if ($row["bar_phone"]) {
    echo("<br>" .$row["bar_phone"]);
    }
    if ($row["bar_website"]) {
    echo '<br><a href=http://'. $row['bar_website'] . '>' . $row[ 'bar_website'] . '</a>';}
    if($row["bar_dance"]=="yes") {
    echo("</td><td><font color='#0000FF'>Dance</font></td>"); }
    if($row["bar_livemusic"]=="yes") {
    echo("</td><td><font color='#0000FF'>Live Music</font></td>"); }
    if($row["bar_cocktail"]=="yes") {
    echo("</td><td><font color='#0000FF'>Cocktails</font></td>"); }
    if($row["bar_dining"]=="yes") {
    echo("</td><td><font color='#0000FF'>Dining</font></td>"); }
    if($row["bar_sports"]=="yes") {
    echo("</td><td><font color='#0000FF'>Sports</font></td>"); }
    if($row["bar_dressy"]=="yes") {
    echo("</td><td><font color='#0000FF'>Dressy</font></td>"); }
    if($row["bar_late"]=="yes") {
    echo("</td><td><font color='#0000FF'>Open Late</font></td>"); }
    if($row["bar_gamble"]=="yes") {
    echo("</td><td><font color='#0000FF'>Gambling</font></td>"); }
    echo("</font></td></tr>"); }
    echo("</table>");



    ?></p>
    <?
    if ($num_results==50){
    $start=$start+50;
    echo ("<a href='dbcall.php?start=$start&searchtype=$searchty pe&searchloc=$searchloc&searchvar=$searchvar'> NEXTPAGE </a>");
    }
    ?>

  7. #6
    Senior Member
    Join Date
    Mar 2002
    Location
    Alabama sans the damn flag
    Posts
    263
    Member #
    38
    ummmm...yeah. Apparently, you have 3 search variables any or all of which might have stuff in them.

    In ASP (and i presume, PHP) the select construct was designed to allow if...elseif...elseif... In this case you have a large number of possible constructions. Try setting up a variable that can keep track of which of the combinations you have. WIth this few, Goedle numbers will work.

    Code:
       ThisNum = 1
       if SearchLoc>"" then ThisNum = ThisNum * 3
       if Searchvar>"" then ThisNum = ThisNum * 5
       if SearchType>"" then ThisNum = ThisNum * 7
    
       Select case ThisNum
          case 1
                   logic for all blank
          case 3
                   SearchLoc only 
          case 5
                   Searchvar Only
          case 7
                  SearchType Only
          case 15
                  SearchLoc and SearchVar
          case 31
                  SearchLoc and SearchTYpe
          case 35
                  SearchVar and SearchType
          case 105
                  All have value
       End Select
    That part I KNOW will work. Not knowing PHP at all, I don't know about the next part. There is a method to ADO called GetRows that appears to be quite similar to the mysql_fetch_array construct. Perhaps there is such an animal as GetString? The arguments to GetString are the recordset, the column delimiter and the rowdelimiter. The delimiters are not limited to one character. Thus it is perfectly legitimate to do the following:

    Code:
        Assume RST to be the recordset
        strTemp0 = "<table .......>"
        strTemp0 = strTemp0 & "<tr> <td>" 
        strTemp0 = strTemp0 & Rst.getstring(, , "</td> <td>", "</td> </tr> <TR> <TD>", "no data")
        strTemp0 = strTemp0 & "</td> </tr> </table>"

    which just happens to be the table definition you need.

    Hope this helps.
    DerFarm
    I talk to squirrels.
    Squirrels Answer.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:04 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com