SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Search query returning all records in database when they are no records

    Hey very new to PHP

    SEARCH PAGE:
    <form id="searcMennuu" name="searcMennuu" method="post" action="VehicleListing.php">
    <select name="vehicles" id="vehicles">
    <option value="allCars" selected="selected">ALL</option>
    <option value="Sedan">Sedan</option>
    <option value="Jeep">Jeep</option>
    <option value="Motorbike">Motorbike</option>

    </select>

    <input name="radioVehicle" type="radio" id="searchSale" value="searchSale" />
    Vehicles for Sale</label>
    <label>
    <input type="radio" name="radioVehicle" id="searchRent" value="searchRent" />
    Vehicles for Rent</label>

    <input type="submit" name="searchButton" id="searchButton" value="Search" />
    </form>

    RESULTS PAGE:
    mysql_select_db($database_ddd, $ddd);

    $query_rsTest = "SELECT `year`, `model` FROM vehicles";
    if (isset ($_POST ['vehicles'])) {
    $parSearch = mysql_real_escape_string ($_POST['vehicles']);

    if($_POST['vehicles'] == "Sedan"){
    $query_rsTest.= " WHERE vehicle_type LIKE '%$parSearch%'";
    }
    }

    $rsTest = mysql_query ($query_rsTest, $ddd) or die(mysql_error());
    $row_rsTest = mysql_fetch_assoc ($rsTest);
    $totalRows_rsTest = mysql_num_rows ($rsTest);

    <div class="showPage"><?php echo $totalRows_rsTest ?></div>
    <?php do { ?>

    <?php if ($totalRows_rsTest > 0) { // Show if recordset not empty ?>

    <div class="output" id="Results">
    <p><?php echo $row_rsTest['year']; ?></p>
    <p><?php echo $row_rsTest['model']; ?></p>
    </div>
    <?php } // Show if recordset empty ?>
    <?php } while ($row_rsTest = mysql_fetch_assoc($rsTest)); ?>




    1. I think i found out why the hide region is not working, whenever i search for something that is not in my database or does not satisfy my search criteria the results page is displaying all records in the database. However i don't know how to fix that. Can someone please help me?

    2. Also i'm trying to find out how i would display ALL vehicles in my database either for rent or sale, i dont have a field in my database for "ALL" so i'm not sure where to start. Thanks in advance

  2. #2
    Non-Member algozone's Avatar
    Join Date
    Jul 2006
    Location
    Hackensack, NJ
    Posts
    119
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Based on your code search only works for vehicles == "Sedan".
    Make sure that is passed to the post.

    if($_POST['vehicles'] == "Sedan"){
    $query_rsTest.= " WHERE vehicle_type LIKE '%$parSearch%'";
    }
    }

  3. #3
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HTML Code:
    <form id="searcMennuu" name="searcMennuu" method="post" action="VehicleListing.php">
    <select name="vehicles" id="vehicles">
    <option value="allCars" selected="selected">ALL</option>
    <option value="Sedan">Sedan</option>
    <option value="Jeep">Jeep</option>
    <option value="Motorbike">Motorbike</option>
    
    </select>
    
    <input name="radioVehicle" type="radio" id="searchSale" value="searchSale" />
    Vehicles for Sale</label>
    <label>
    <input type="radio" name="radioVehicle" id="searchRent" value="searchRent" />
    Vehicles for Rent</label>
    
    <input type="submit" name="searchButton" id="searchButton" value="Search" />
    </form>
    RESULTS PAGE:
    PHP Code:
    <?php
    $maxRows_rsTest 
    10;
    $pageNum_rsTest 0;
    if (isset(
    $_GET['pageNum_rsTest'])) {
      
    $pageNum_rsTest $_GET['pageNum_rsTest'];
    }
    $startRow_rsTest $pageNum_rsTest $maxRows_rsTest;
    mysql_select_db($database_ddd$ddd);

    $query_rsTest "SELECT `year`, `model` FROM vehicles"
    if (isset (
    $_POST ['vehicles']) && ($_POST ['radioVehicles'])) { 

    $parSearch mysql_real_escape_string ($_POST['vehicles']); 
    $parSearch1 mysql_real_escape_string ($_POST['radioVehicles']); 

    if(
    $_POST['vehicles'] == "Sedan"){
    $query_rsTest.= " WHERE vehicle_type LIKE '%$parSearch%'";
    }else if 
    f($_POST['vehicles'] == "Jeep"){
    $query_rsTest.= " WHERE vehicle_type LIKE '%$parSearch%'";
    }
    }

    if (isset(
    $_GET['totalRows_rsTest'])) {
      
    $totalRows_rsTest $_GET['totalRows_rsTest'];
    } else {
      
    $all_rsTest mysql_query($query_rsTest);
      
    $totalRows_rsTest mysql_num_rows($all_rsTest);
    }

    $query_limit_rsTest sprintf("%s LIMIT %d, %d"$query_rsTest$startRow_rsTest$maxRows_rsTest);
    $rsTest mysql_query($query_limit_rsTest$ddd) or die(mysql_error());
    $row_rsTest mysql_fetch_assoc ($rsTest);
    $totalRows_rsTest mysql_num_rows ($rsTest);

    $totalPages_rsProperties ceil($totalRows_rsProperties/$maxRows_rsProperties)-1;

    ?>

    <div class="showPage"><?php echo $totalRows_rsTest ?></div>
    <?php do { ?>

    <?php if ($totalRows_rsTest 0) { // Show if recordset not empty ?>

    <div class="output" id="Results">
    <p><?php echo $row_rsTest['year']; ?></p>
    <p><?php echo $row_rsTest['model']; ?></p>
    </div>
    <?php // Show if recordset empty ?>
    <?php 
    } while ($row_rsTest mysql_fetch_assoc($rsTest)); ?>
    My problem is when i try to search for sedan and there are no sedans in my database i display all records, how do i get the query to display nothing if there is nothing in my database.
    Last edited by SpacePhoenix; Jan 6, 2012 at 01:23. Reason: placed appropriate tags around code

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2011
    Location
    Portland
    Posts
    148
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you test this as you where building it? "else if f($_POST['vehicles'] == "Jeep"){" But besides that you need to add a condition to your script to check if you results set is empty or not. Then you can display accordingly.

  5. #5
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jgetner View Post
    Did you test this as you where building it? "else if f($_POST['vehicles'] == "Jeep"){" But besides that you need to add a condition to your script to check if you results set is empty or not. Then you can display accordingly.
    That "f" is a typo from putting the code on this site. Everything works once i have info in the database that matches my search criteria, but when there is no matching information everything is displayed. You mentioned a condition to check if the results is empty, can you help me the relevant code?

    Thanks in advanced

  6. #6
    SitePoint Zealot
    Join Date
    Jan 2011
    Location
    Portland
    Posts
    148
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <div class="showPage"><?php echo $totalRows_rsTest ?></div>


    <?php if($totalRows_rsTest 0): // Show if recordset not empty ?>
        <div class="output" id="Results">
            <p><?php echo $row_rsTest['year']; ?></p>
            <p><?php echo $row_rsTest['model']; ?></p>
        </div>
    <?php endif; // Show if recordset empty ?>
    <?php 
    else{
        
    $row_rsTest mysql_fetch_assoc($rsTest
    ?>

  7. #7
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jgetner View Post
    PHP Code:
    <div class="showPage"><?php echo $totalRows_rsTest ?></div>


    <?php if($totalRows_rsTest 0): // Show if recordset not empty ?>
        <div class="output" id="Results">
            <p><?php echo $row_rsTest['year']; ?></p>
            <p><?php echo $row_rsTest['model']; ?></p>
        </div>
    <?php endif; // Show if recordset empty ?>
    <?php 
    else{
        
    $row_rsTest mysql_fetch_assoc($rsTest
    ?>

    I am getting an error code in syntax where the endif statement is

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2011
    Location
    Portland
    Posts
    148
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $row_rsTest = mysql_fetch_assoc($rsTest) add the closing tag ";"

  9. #9
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jgetner View Post
    $row_rsTest = mysql_fetch_assoc($rsTest) add the closing tag ";"
    I have that, still not working

  10. #10
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does nobody know how to help me, I believe the problem is in the way my database query is set up. Help please

  11. #11
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Leave PHP out of the equation for a moment.

    Use whatever you use to manage your database to create a valid sql statement which returns the number of rows you expect it to.

    Paste that here and lets have a look at it - tell us how many rows it brings back, post just one of those rows here too.

    Also, then issue the following instruction to Mysql DESCRIBE vehicles and post that here too, then we can see if you have set up your table correctly.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    cups, i prefer SHOW CREATE TABLE rather than DESCRIBE because the former includes indexes, which are sometimes important in debugging a query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I'll remember that, thanks.

  14. #14
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the structure

    SELECT * FROM `vehicles` WHERE 1

    This how it looks in the database

    # Column Type Collation Attributes Null Default Extra Action
    1 vehicle_id int(11) No None AUTO_INCREMENT Change Drop More
    2 vehicle_type varchar(255) latin1_swedish_ci No None Change Drop More
    3 model varchar(255) latin1_swedish_ci No None Change Drop More
    4 year varchar(255) latin1_swedish_ci No None Change Drop More
    5 sale varchar(255) latin1_swedish_ci No None Change Drop More
    6 rent varchar(255) latin1_swedish_ci No None Change Drop More

  15. #15
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    mysql_select_db($database_ddd$ddd);

    $query_rsTest "SELECT `year`, `model` FROM vehicles"
    if (isset (
    $_POST ['vehicles'])) { 
    $parSearch mysql_real_escape_string ($_POST['vehicles']); 

    if(
    $_POST['vehicles'] == "Sedan"){
    $query_rsTest.= " WHERE vehicle_type LIKE '%$parSearch%'";
    }

    So your Q is about queries like the above, I want to know if you have any matching data in the database. The use of LIKE on a column such as vehicle_type looks way off.

    Surely vehicle type is limited to a small set of possibilities:

    van
    sedan
    pickup

    and so on?

    If so, you should have the equivalent of "select `year`, `model`, from `vehicles` where vehicle_type = 'sedan'"

    What you need to prove is that

    a) you have some matching data in your database.
    b) you successfully connected to mysql
    c) PHP ACTUALLY does construct that desired sql string correctly

    BTW, varchar(255) is not the best way to hold a YEAR value, use a datestamp instead.

  16. #16
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the response, and thanks for the correction with datestamp.

    My problem is, when i have no jeeps or sedans for sale and someone selects one of them i am getting all the vehicles for sale rather than them message no vehicles for sale. I have added the vehicle count from Dreamweaver server behaviour and tht is counting correctly everytime. So I believe my problem is the records i am returning.

    eg. If i select Sedan since i have 2 Sedans in the database, the count for Sedans = 2 and the two Sedans are displayed.
    However i have no Jeeps in the database and when i select Jeeps all the vehicles in the database are displayed. How do i go about fixing that problem?

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Shoapin View Post
    However i have no Jeeps in the database and when i select Jeeps all the vehicles in the database are displayed. How do i go about fixing that problem?
    i don't do php, but my guess is, the problem is here --
    PHP Code:
    if($_POST['vehicles'] == "Sedan"){
    $query_rsTest.= " WHERE vehicle_type LIKE '%$parSearch%'"
    where is $parSearch being initialized? why aren't you using $_POST['vehicles'] in the LIKE string?

    if $parSearch is left empty, the query becomes WHERE vehicle_type LIKE '%%' which of course would explain why it's returning all rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i don't do php, but my guess is, the problem is here --
    PHP Code:
    if($_POST['vehicles'] == "Sedan"){
    $query_rsTest.= " WHERE vehicle_type LIKE '%$parSearch%'"
    where is $parSearch being initialized? why aren't you using $_POST['vehicles'] in the LIKE string?

    if $parSearch is left empty, the query becomes WHERE vehicle_type LIKE '%%' which of course would explain why it's returning all rows
    Ok so i should just swap the $_POST['vehicles'] in place of $parSearch and use AND if i need to add the other string for the radio button?

  19. #19
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    To look at c) in my previous post you would simply echo the assembled sql query onto the page:

    PHP Code:
    echo $query_rsTest
    ... and take a close look at it, paste it into your database admin screen and see what it returns.

    My guess is that you are not adding the second part, the WHERE clause because you are searching on "jeep" instead of "Jeep" or perhaps you have a rogue space eg " Jeep"

  20. #20
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    To look at c) in my previous post you would simply echo the assembled sql query onto the page:

    PHP Code:
    echo $query_rsTest
    ... and take a close look at it, paste it into your database admin screen and see what it returns.

    My guess is that you are not adding the second part, the WHERE clause because you are searching on "jeep" instead of "Jeep" or perhaps you have a rogue space eg " Jeep"

    It returns everything in the database. Do you know how to code it to work?

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Shoapin View Post
    It returns everything in the database. Do you know how to code it to work?
    could we see it please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could we see it please?
    When i echo $query_rsTest i get this

    "SELECT `year`, `model` FROM vehicles"

    when i echo $totalRows_rsTest

    I get 20, which are the total number of records i have in the database

  23. #23
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Nearly there I thiink, now show us the result of

    PHP Code:
    var_dump($_POST); 
    which should be added to your postback form handler script, temporarily.

  24. #24
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    Nearly there I thiink, now show us the result of

    PHP Code:
    var_dump($_POST); 
    which should be added to your postback form handler script, temporarily.

    I put it after $query_rsTest = "SELECT `year`, `model` FROM vehicles"; hope that was the right place.

    It returned "Array (0) {}"

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Shoapin View Post
    "SELECT `year`, `model` FROM vehicles"
    this explains why you are returning all rows in the table, correct?

    you've forgotten the WHERE clause
    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
  •