SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member phil101's Avatar
    Join Date
    Sep 2003
    Location
    WG
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Pagbation Random Results help !

    I have a DB of models.
    I want when you do a search for say a male in the state of florida the results come back in random order each time. The problem I am having is in my pagnation when you go to page 2 you get some of page 1 results. Is there anyway to alter my script below to give back random results in a set and hold them so the pagnation will work correctly, So basically it will not scramble the results until the person does another search. Can anyone help me get through this from start to finish. Below is my script if you could alter it to do this please post.
    _____________________________________________________________
    <?php

    $conn=mysql_connect("localhost","","");
    $rs=mysql_select_db("mymodelfinder",$conn);

    /* The str_replace function replaces the string accordingly, for example 10,11,12,13 to 10','11','12','13. Later in query its formated as '10','11','12','13' Which is the standard format to use in WHERE IN Query. */

    $mysex = str_replace(",", "','", $_POST['sex']);
    $myage = str_replace(",", "','", $_POST['age']);
    $myweight = str_replace(",", "','", $_POST['weight']);
    $myheight = str_replace(",", "','", $_POST['height']);
    $myhaircolor = str_replace(",", "','", $_POST['haircolor']);
    $myeyecolor = str_replace(",", "','", $_POST['eyecolor']);
    $mystate = str_replace(",", "','", $_POST['state']);
    // get the pager input values
    $mypage = "";
    $mypage = $_POST['page'];
    $limit = 5; // 5 records per page...you can change it according to your wish.

    $result = mysql_query("SELECT count(*) FROM models WHERE sex IN ('$mysex') AND age IN ('$myage') AND weight IN ('$myweight') AND height IN ('$myheight') AND haircolor IN ('$myhaircolor') AND eyecolor IN ('$myeyecolor') AND state IN ('$mystate')", $conn);

    $total = mysql_result($result, 0, 0);

    // work out the pager values
    $pager = Pager::getPagerData($total, $limit, $mypage);
    $offset = $pager->offset;
    $limit = $pager->limit;
    $mypage = $pager->page;

    $answer = "SELECT * FROM models WHERE sex IN ('$mysex') AND age IN ('$myage') AND weight IN ('$myweight') AND height IN ('$myheight') AND haircolor IN ('$myhaircolor') AND eyecolor IN ('$myeyecolor') AND state IN ('$mystate') ORDER BY RAND() LIMIT $offset, $limit ";

    $rs=mysql_query($answer,$conn) or die("Error occured in SQL -> ".mysql_error());

    if (mysql_num_rows($rs) == 0)
    {

    echo("No models with these Parameters where found!...please try again! ");
    }
    echo("MYMODELFINDER.com SEARCH RESULTS ");

    while ($row =mysql_fetch_array($rs) )
    { print"<table width = 551 border=0>";

    echo ("<tr><td><b> ID/b> " .$row ["id"]."&nbsp; &nbsp;");
    echo ("<b> Name/b> " .$row ["name"]."&nbsp; &nbsp;");
    echo ("<b> Age/b> ".$row["age"]."&nbsp; &nbsp;");
    echo ("<b> Weight/b> ".$row["weight"]."&nbsp; lbs &nbsp;");
    echo ("<b> Height/b> ".$row["height"]."<br>");
    echo ("<b> Hair Color/b> ".$row["haircolor"]."&nbsp; &nbsp;");
    echo ("<b> Eye Color/b> ".$row["eyecolor"]."&nbsp; &nbsp;");
    $mylink= $row["link"];
    $go ="More from this model";
    echo "<a href=\"$mylink\" target='_blank'>$go</a></tr></td><br>";
    $filepath = $row["filepath"];
    echo "<td><img src='$filepath'></td><hr></tr>";

    print"</table>";
    }

    ?>
    <?php

    class Pager
    {
    function getPagerData($numHits, $limit, $page)
    {
    $numHits = (int) $numHits;
    $limit = max((int) $limit, 1);
    $page = (int) $page;
    $numPages = ceil($numHits / $limit);

    $page = max($page, 1);
    $page = min($page, $numPages);

    $offset = ($page - 1) * $limit;

    $ret = new stdClass;

    $ret->offset = $offset;
    $ret->limit = $limit;
    $ret->numPages = $numPages;
    $ret->page = $page;

    return $ret;
    }
    }

    ?>
    <form action="mmf_result.php" method="POST" target="_self">
    <input name="state" type="hidden" id="state" value="<?php echo $_POST['state']; ?>">
    <input type="hidden" name="sex" value="<?php echo $_POST['sex']; ?>">
    <input type="hidden" name="age" value="<?php echo $_POST['age']; ?>">
    <input type="hidden" name="weight" value="<?php echo $_POST['weight']; ?>">
    <input type="hidden" name="height" value="<?php echo $_POST['height']; ?>">
    <input type="hidden" name="haircolor" value="<?php echo $_POST['haircolor']; ?>">
    <input type="hidden" name="eyecolor" value="<?php echo $_POST['eyecolor']; ?>">
    <select name="page">
    <?php

    // output paging system
    for ($i = 1; $i <= $pager->numPages; $i++)
    {
    if ($i == $mypage)
    echo "<option value='$i' SELECTED> $i </option>";
    else
    echo "<option value='$i'> $i </option>";
    }

    ?>
    </select>
    <input type="submit" name="submit" value="GO">
    </form>

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    your queries look good, except for one small thing

    you are handling the list of numbers needlessly
    /* The str_replace function replaces the string accordingly, for example 10,11,12,13 to 10','11','12','13. Later in query its formated as '10','11','12','13' Which is the standard format to use in WHERE IN Query. */
    actually, the standard simply says that when single quotes are used, whatever is inside them is a string

    and of course if your query compares values in a numeric column (which presumably eyecolor, haircolor, etc., are) with strings, then this requires a datatype conversion, which the standard says should not be allowed

    some database systems will warn you, others will just go ahead and do the conversion, occasionally with unpredictable results

    my advice is not to enclose numbers in quotes, so that (a) your script will run faster, and (b) so will the database

    as far as your actual question is concerned, it may have better been asked in the php forum, and hopefully one of the moderators of this forum will move it for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •