SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    May 2003
    Location
    Ontario, Canada
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL result set differs between php and ssh

    Hello All,

    I just joined the board, so my apologies if this question has been asked before. (Any pointers to relevant previous threads are welcome as well.)

    I'm developing a simple php/mysql used vehicle search for a car retailer's website, and am experiencing a strange problem in my search results. I find that if, for example, I specify a search for vehicles between the years of 1989 and 1993, no search results are returned, although I know there is a 1993 model in the db. However, the same query through my ssh client finds the car. Not until I increase the upper year (on the form) to 1995 does the 1993 vehicle turn up in the php/mysql results. The 1995 vehicle in the db does not turn up when the upper year is 1995, but if I increase the upper year to 1996, the 1993, 1995, and 1996 vehicles all turn up. This year problem does not appear to happen again as I increase the years.

    There is also some weirdness happening if I manipulate the other search criteria as well. Cars that should show up in the php/mysql search results do not, while the same query through the ssh client will find them. Most frustrating, especially with a looming deadline.

    I've pasted in the relevant bits of code below. If anyone notices anything strange pertaining to my form variables or my select statement, please let me know! I've looked over this so many times I don't really see anything anymore. I've echoed the form variables, and the correct values are being passed.

    Thanks very much for your time! If any supplementary info is required, please let me know.

    Simm


    The form:
    HTML Code:
    <form name="used" method="post" action="index.php">
    <input type="hidden" name="action1" value="1">
    <table cellpadding="1" cellspacing="0" border="0" width="60%" bgcolor="#e7e7e7">
    <tr>
    <td>
    
    <table cellpadding="4" cellspacing="0" border="0" width="100%" bgcolor="#f8f8f8">
    <tr bgcolor="#9c0000">
    <td><span class="white"><b>Search our used car inventory</b></span></td>
    </tr>
    <tr>
    <td>
    <b>Model year:</b><br />
    From:*<select name="year_from" tabindex="1" class="year">
    <option value="1989">1989</option>
    <option value="1990">1990</option>
    <option value="1991">1991</option>
    <option value="1992">1992</option>
    <option value="1993">1993</option>
    <option value="1994">1994</option>
    <option value="1995">1995</option>
    <option value="1996">1996</option>
    <option value="1997">1997</option>
    <option value="1998">1998</option>
    <option value="1999">1999</option>
    <option value="2000">2000</option>
    <option value="2001">2001</option>
    <option value="2002">2002</option>
    <option value="2003">2003</option>
    </select>
    **
    To:*
    <select name="year_to" tabindex="2" class="year">
    <option value="1989">1989</option>
    <option value="1990">1990</option>
    <option value="1991">1991</option>
    <option value="1992">1992</option>
    <option value="1993">1993</option>
    <option value="1994">1994</option>
    <option value="1995">1995</option>
    <option value="1996">1996</option>
    <option value="1997">1997</option>
    <option value="1998">1998</option>
    <option value="1999">1999</option>
    <option value="2000">2000</option>
    <option value="2001">2001</option>
    <option value="2002">2002</option>
    <option value="2003" selected>2003</option>
    </select>
    </td>
    </tr>
    <tr>
    <td>
    <b>Price:</b><br />
    <select name="price" tabindex="3" class="price">
    <option value="200000">Unlimited</option>
    <option value="10000">Up to $10,000</option>
    <option value="12000">Up to $12,000</option>
    <option value="14000">Up to $14,000</option>
    <option value="16000">Up to $16,000</option>
    <option value="18000">Up to $18,000</option>
    <option value="20000">Up to $20,000</option>
    <option value="25000">Up to $25,000</option>
    <option value="30000">Up to $30,000</option>
    <option value="35000">Up to $35,000</option>
    <option value="40000">Up to $40,000</option>
    <option value="50000">Up to $50,000</option>
    <option value="60000">Up to $60,000</option>
    <option value="70000">Up to $70,000</option>
    <option value="200000">Above $70,000</option>
    </select>
    </td>
    </tr>
    <tr>
    <td>
    <b>Transmission:</b><br />
    <input type="radio" name="transmission" value="5" tabindex="6">*Automatic**<input type="radio" name="transmission" value="2" tabindex="7">*Manual**<input type="radio" name="transmission" value="either" tabindex="8" checked>*Either
    </td>
    </tr>
    <tr>
    <td>
    <b>Kilometres:</b><br />
    <select name="kilometres" tabindex="9" class="km">
    <option value="500000">All kilometre ranges</option>
    <option value="20000">Less than 20,000</option>
    <option value="40000">Less than 40,000</option>
    <option value="60000">Less than 60,000</option>
    <option value="80000">Less than 80,000</option>
    <option value="100000">Less than 100,000</option>
    <option value="120000">Less than 120,000</option>
    <option value="500000">Over 120,000</option>
    </select>
    </td>
    </tr>
    <tr>
    <td>
    
    </td>
    </tr>
    <tr>
    <td><br /><input type="submit" value="Submit">**<input type="reset" value="Clear Form"></td>
    </tr>
    </table>
    
    </td>
    </tr>
    </table>
    </form>
    The php script:

    PHP Code:
    $result = @mysql_query("SELECT make, model, year, mileage, transmission_id, colour, vehicle_type_id, stock_no, gt_price FROM vehicle WHERE year >= $year_from AND year <= $year_to AND gt_price <= $price AND mileage < $kilometres ORDER BY gt_price desc");

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

    $number mysql_numrows($result);
    mysql_close($dbcnx);

    echo (
    "<table cellspacing=\"0\" cellpadding=\"0\" border=\"0\" width=\"772\">");
    echo (
    "<tr><td width=\"18\"><img src=\"../trans.gif\" width=\"18\" height=\"1\" border=\"0\" alt=\"\" /></td>");
    echo (
    "<p><span class=\"secondary\">Used vehicle search results</span></p>");
    echo (
    "<table cellspacing=\"1\" cellpadding=\"4\" border=\"0\" width=\"772\">");
    echo (
    "<tr><td  colspan=\"9\" align=\"right\"><b><a href=\"index.php\">New search</a></b></td></tr>");
    echo (
    "<tr bgcolor=\"#9c0000\"><td><span class=\"white\"><b>Make</b></span></td><td><span class=\"white\"><b>Model</b></span></td><td><span class=\"white\"><b>Year</b></span></td><td><span class=\"white\"><b>Mileage</b></span></td><td><span class=\"white\"><b>Transmission</b></span></td><td><span class=\"white\"><b>Colour</b></span></td><td><span class=\"white\"><b>Vehicle Type</b></span></td><td><span class=\"white\"><b>Stock No.</b></span></td><td><span class=\"white\"><b>Price</b></span></td></tr>");


    if (
    $number 1
    {
        echo (
    "<tr bgcolor=\"#e7e7e7\"><td colspan=\"9\">There were no used vehicles matching your search criteria. Please try again.</td></tr>");
    }
    else
    {
        while (
    $row mysql_fetch_array($result) ) 
        {
            
    //alternate row bgcolor
            
    function row_color($i)
            { 
                
    $bg1 "#f8f8f8"// color one     
                
    $bg2 "#e7e7e7"// color two 

                
    if ( $i%
                { 
                    return 
    $bg1
                } 
                else 
                { 
                    return 
    $bg2
                } 
            } 
    //end function row_color()
        
            
    for( $i 0$i $row mysql_fetch_array($result); $i++)
            {     
                    
    $row["colour"] = ucfirst($row["colour"]);
                echo (
    "<tr bgcolor=\"".row_color($i)."\"><td>".$row["make"]."</td><td>".$row["model"]."</td><td>".$row["year"]."</td><td align=\"right\">".$row["mileage"]." km</td><td>".$row["transmission_id"]."</td><td>".$row["colour"]."</td><td>".$row["vehicle_type_id"]."</td><td align=\"center\">".$row["stock_no"]."</td><td align=\"right\">$".$row["gt_price"]."</td></tr>");
            }
    //end for
        
    }//end while
    }//end if

    echo ("</table>");
    echo (
    "</td></tr></table><br /><br />"); 

  2. #2
    SitePoint Zealot
    Join Date
    Jul 2002
    Location
    Fort Lauderdale
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The results will not differ if the query is the same so I would try echoing the query.
    Code:
    $query = "select...";
    echo $query;
    $result = mysql_query($query);
    I also noticed a typo on the line:
    $number = mysql_numrows($result);
    should be
    $number = mysql_num_rows($result);

    www.unpossiblemedia.com - A Fort Lauderdale based multimedia company.

  3. #3
    SitePoint Member
    Join Date
    May 2003
    Location
    Ontario, Canada
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much for the reply - and thanks for catching the typo. I'll give your suggestions a try.

    Simm

  4. #4
    SitePoint Member
    Join Date
    May 2003
    Location
    Ontario, Canada
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I figured it out. After the vehicle ids were echoed in ascending order, it seems that the first record was not being written to the page. A little test for the fist record fixed it.


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
  •