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 10 of 10
  1. #1
    Junior Member
    Join Date
    Jun 2012
    Posts
    16
    Member #
    31986
    Hello!

    I am new to PHP and MySQL and I am working on a project for my company to build a dynamic search for one of our product offerings (conveyors). I have built a local testing mysql database that contains the different conveyors that we carry. The database includes items like max width, max weight, etc. I built a search form where a visitor can fill out a small form to filter through our products to find all the conveyors that meet their needs.

    I am having trouble with my POST script. I cannot get the script to show the results from the database. I have been hacking at this for the past two weeks trying to figure it out. Please help! Thanks in advance!

    FORM PAGE:::

    <body>

    <form action="action-5.php" method="post">
    <label for="manufacturer">Manufacturer</label>
    <select name="manufacturer" size="1" id="manufacturer">
    <option> </option>
    <option>Dorner</option>
    <option>FlexLink</option>
    </select><br />

    <form action="action-5.php" method="post">
    <label for="type">Type of Conveyor</label>
    <select name="type" size="1" id="type">
    <option> </option>
    <option>Plastic</option>
    <option>Roller</option>
    <option>Belt</option>
    </select><br />

    <form action="action-5.php" method="post">
    <label for="orientatino">Orientation of Conveyor</label>
    <select name="orientation" size="1" id="orientation">
    <option> </option>
    <option>Straight</option>
    <option>Z</option>
    <option>Curved</option>
    </select> <br />

    <form action="action-5.php" method="post">
    <label for="width">Max product width (in)</label>
    <input name="width" type="text" maxlength="6" /><br />

    <form action="action-5.php" method="post">
    <label for="weight">Max product weight (lbs)</label>
    <input name="weight" type="text" maxlength="6" /><br />


    <input name="submit" type="submit" />

    </form>


    </body>
    </html>

    ACTION PAGE::::

    <?php
    //Grabs input from search.php
    echo $_POST["manufacturer"]; ?><br />
    <?php echo $_POST["type"]; ?><br />
    <?php echo $_POST["orientation"]; ?><br />
    <?php echo $_POST["width"]; ?><br />
    <?php echo $_POST["weight"]; ?><br />


    <?php
    //see if posted search field is set and has value
    if(isset($_POST['width']) && $_POST['width'] != ""){
    //filter the search query user input
    $width = preg_replace('#[0-9.]#i', '', $_POST['width']);
    }

    // connect to MySQL database here
    $hostname_dbconn = "localhost";
    $database_dbconn = "conveyor_test";
    $username_dbconn = "john";
    $password_dbconn = "password";

    $mysqli = new MySQLi("$hostname_dbconn, $username_dbconn, $password_dbconn, $database_dbconn");

    // Runs collected imput data from search.php against Sheet1 in conveyor_test
    $result = $mysqli->query("SELECT * FROM Sheet1 WHERE MANUFACTURER = ('$manufacturer') AND TYPE = ('$type') AND ORIENTATION = ('$orientation') AND MAX_WIDTH >= ('$width') AND MAX_WEIGHT >= ('$weight') ORDER BY MANUFACTURER, SERIES, NAME"); {


    $row = $result->fetch_assoc();{
    echo ($row['ID']);
    echo ($row['MANUFACTURER']);
    echo ($row['SERIES']);
    echo ($row['NAME']);
    }
    }
    ?>

  2.  

  3. #2
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,716
    Member #
    5580
    Liked
    718 times
    Try changing this:

    $row = $result->fetch_assoc();{
    echo ($row['ID']);
    echo ($row['MANUFACTURER']);
    echo ($row['SERIES']);
    echo ($row['NAME']);
    }
    to:

    $row = $result->fetch_assoc();
    foreach($row as $entry){
    echo $entry['ID']."<br />";
    echo $entry['MANUFACTURER']."<br />";
    echo $entry['SERIES']."<br />";
    echo $entry['NAME']."<br />";
    echo "<br />";
    }

    I'm also not sure what the parenthesis are for in your query ...

    My version .... not tested
    PHP Code:

    <?php
    //see if posted search field is set and has value
    if(isset($_POST['width']) && $_POST['width'] != ""){
    //filter the search query user input
    $width preg_replace('#[0-9.]#i'''$_POST['width']);
    }

    // connect to MySQL database here
    $hostname_dbconn "localhost";
    $database_dbconn "conveyor_test";
    $username_dbconn "john";
    $password_dbconn "password";

    $mysqli = new MySQLi("$hostname_dbconn$username_dbconn$password_dbconn$database_dbconn");

    // Runs collected imput data from search.php against Sheet1 in conveyor_test
    $result $mysqli->query("SELECT * FROM Sheet1 WHERE MANUFACTURER = '$manufacturer' AND TYPE = '$type' AND ORIENTATION = '$orientation' AND MAX_WIDTH >= '$width' AND MAX_WEIGHT >= '$weight' ORDER BY MANUFACTURER, SERIES, NAME"); 

    $row $result->fetch_assoc();
    foreach(
    $row as $entry){
    echo 
    $entry['ID']."<br />"
    echo 
    $entry['MANUFACTURER']."<br />";
    echo 
    $entry['SERIES']."<br />";
    echo 
    $entry['NAME']."<br />";
    echo 
    "<br />";
    }

    ?>


  4. #3
    Junior Member
    Join Date
    Jun 2012
    Posts
    16
    Member #
    31986
    Thanks for the reply. I made your changes and took the parenthesis out and still have no results showing.

    Any other thoughts/suggestions?

  5. #4
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,716
    Member #
    5580
    Liked
    718 times
    I overlooked something ... missing the POST ... so I added it below in the script.

    Do you need to use MySQLi, or can you use MySQL?

    Also, for testing, do a simpler query just in case none of your AND statements match.
    Maybe search for one thing first, and then see if you can at least get one result.

    PHP Code:

    <?php
    //see if posted search field is set and has value
    if(isset($_POST['width']) && $_POST['width'] != ""){
    //filter the search query user input
    $width preg_replace('#[0-9.]#i'''$_POST['width']);
    }

    $manufacturer=$_POST['manufacturer'];
    $type=$_POST['type'];
    $orientation=$_POST['orientation'];
    $width=$_POST['width'];
    $weight=$_POST['weight'];

    // connect to MySQL database here
    $hostname_dbconn "localhost";
    $database_dbconn "conveyor_test";
    $username_dbconn "john";
    $password_dbconn "password";

    $mysqli = new MySQLi("$hostname_dbconn$username_dbconn$password_dbconn$database_dbconn");

    // Runs collected imput data from search.php against Sheet1 in conveyor_test
    $result $mysqli->query("SELECT * FROM Sheet1 WHERE MANUFACTURER = '$manufacturer' AND TYPE = '$type' AND ORIENTATION = '$orientation' AND MAX_WIDTH >= '$width' AND MAX_WEIGHT >= '$weight' ORDER BY MANUFACTURER, SERIES, NAME");

    $row $result->fetch_assoc();
    foreach(
    $row as $entry){
    echo 
    $entry['ID']."<br />";
    echo 
    $entry['MANUFACTURER']."<br />";
    echo 
    $entry['SERIES']."<br />";
    echo 
    $entry['NAME']."<br />";
    echo 
    "<br />";
    }

    ?>


  6. #5
    Junior Member
    Join Date
    Jun 2012
    Posts
    16
    Member #
    31986
    I tried the change and am still not getting the query results. You're right I should've done a simpler query at first. I made the following adjustments to simplify the query to just search for manufacturer and still am getting nothing.

    I don't need to use MySQLi, I just thought that was the best option after researching them on the MySQL site. They basically suggest not to develop anything new in MySQL as it lacks the new features and support that MySQLi has(or at least that's how I interpreted it).

    Do you think MySQL would be a better option?

    PHP Code:
    <?php
    //Grabs input from search.php
    echo $_POST["manufacturer"]; ?><br />
    <?php echo $_POST["type"]; ?><br />
    <?php echo $_POST["orientation"]; ?><br />
    <?php echo $_POST["width"]; ?><br />
    <?php echo $_POST["weight"]; ?><br />
     
    <?php
    //see if posted search field is set and has value
    if(isset($_POST['width']) && $_POST['width'] != ""){
    //filter the search query user input
    $width preg_replace('#[0-9.]#i'''$_POST['width']);
    }

    $manufacturer=$_POST['manufacturer'];
    $type=$_POST['type'];
    $orientation=$_POST['orientation'];
    $width=$_POST['width'];
    $weight=$_POST['weight'];

    // connect to MySQL database here
    $hostname_dbconn "localhost";
    $database_dbconn "conveyor_test";
    $username_dbconn "john";
    $password_dbconn "password";

    $mysqli = new MySQLi("$hostname_dbconn$username_dbconn$password_dbconn$database_dbconn");

    // Runs collected imput data from search.php against Sheet1 in conveyor_test
    $result $mysqli->query("SELECT * FROM Sheet1 WHERE MANUFACTURER = '$manufacturer' ORDER BY MANUFACTURER, SERIES, NAME"); {
     
    $row $result->fetch_assoc();
    foreach(
    $row as $entry){
    echo 
    $entry['ID']."<br />";
    echo 
    $entry['MANUFACTURER']."<br />";
    echo 
    $entry['SERIES']."<br />";
    echo 
    $entry['NAME']."<br />";
    echo 
    "<br />";
    }
    }
    ?>

  7. #6
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,716
    Member #
    5580
    Liked
    718 times
    First of all, are you sure that you actually have things stored in your MySQL database?

    When you look at your table called "Sheet1" (and that is really what the table is called),
    you have something entered in the column called "MANUFACTURER"?

    You are certain of your column names - upper and lower case letters (because it's case sensitive)?

    I have a hunch there are more things wrong than just the query.
    It's suspicious that you can't query things from your database ... that leads me to
    believe that you never put anything in there. Who or how did information get into
    your table called "Sheet1"? Did you enter data using your phpMyAdmin control panel?

    Follow this tutorial...
    Make sure you are connecting, and then do a simple query.
    Use your own table name(s) and column name(s) for the examples they show.
    http://www.tizag.com/mysqlTutorial/mysqlconnection.php


  8. #7
    Junior Member
    Join Date
    Jun 2012
    Posts
    16
    Member #
    31986
    I ran the test with the link that you posted to double check the table and was able to output the 1st entry. So I went and created a new PHP script based off of that tutorial successfully. I'm still not sure what was wrong with the original code we were working on. Thanks for pointing me in the right direction, the new working script is below.

    I have one last question: Is there a way to nullify an entry in the query search? For example if someone leaves one of the fields in my search form blank (which is currently an option) can I stop the script from searching that column in the database? Right now if a field in the search is left blank then the script returns no results because all of the columns have data.

    I'm guessing it would have to be some type of IF statement?

    <?php
    // Make a MySQL Connection

    mysql_connect("localhost", "john", "password") or die(mysql_error());
    mysql_select_db("conveyor_test") or die(mysql_error());

    echo $_POST["manufacturer"];
    echo $_POST["type"];
    echo $_POST["orientation"];
    echo $_POST["width"];
    echo $_POST["weight"];

    $manufacturer=$_POST['manufacturer'];
    $type=$_POST['type'];
    $orientation=$_POST['orientation'];
    $width=$_POST['width'];
    $weight=$_POST['weight'];

    // Retrieve all the data from the "example" table


    $query = "SELECT * FROM Sheet1 WHERE MANUFACTURER = '$manufacturer' AND TYPE = '$type' AND ORIENTATION = '$orientation' AND MAX_WIDTH >= '$width' AND MAX_WEIGHT >= '$weight' ORDER BY MANUFACTURER, SERIES, NAME";

    $result = mysql_query($query) or die(mysql_error());


    while($row = mysql_fetch_array($result)){
    echo $row['ID']. " - ". $row['MANUFACTURER'];
    echo "<br />";
    }



    // store the record of the "example" table into $row
    $row = mysql_fetch_array( $result );
    // Print out the contents of the entry

    echo "ID: ".$row['ID'];
    echo " MANUFACTURER: ".$row['MANUFACTURER'];
    echo " SERIES: ".$row['SERIES'];
    echo " NAME: ".$row['NAME'];

    ?>

  9. #8
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,716
    Member #
    5580
    Liked
    718 times
    I have an answer, but not enough time to type it now.
    Check back later or tomorrow.


  10. #9
    WDF Staff mlseim's Avatar
    Join Date
    Apr 2004
    Location
    Cottage Grove, Minnesota
    Posts
    7,716
    Member #
    5580
    Liked
    718 times
    The script below is untested.
    You'll see that you "build" the query based on which posted variables are not empty.

    PHP Code:
    <?php
     
    // Make a MySQL Connection
     
    mysql_connect("localhost""john""password") or die(mysql_error());
     
    mysql_select_db("conveyor_test") or die(mysql_error());
     
    echo 
    $_POST["manufacturer"];
     echo 
    $_POST["type"];
     echo 
    $_POST["orientation"];
     echo 
    $_POST["width"];
     echo 
    $_POST["weight"];
     
    // Start the query string ... 
    $query "SELECT * FROM Sheet1 WHERE ";

    // Continue adding onto the query string as items are identified ...
    // It will only be added if the posted value is not empty.
    // Also sanitize all variables that are used in a query. 
    if($_POST['manufacturer']){
    $query .= "MANUFACTURER = '".mysql_real_escape_string($_POST['manufacturer'])."' AND ";
    }
    if(
    $_POST['type']){
    $query .= "TYPE = '".mysql_real_escape_string($_POST['type'])."' AND ";
    }
    if(
    $_POST['orientation']){
    $query .= "ORIENTATION = '".mysql_real_escape_string($_POST['orientation'])."' AND ";
    }
    if(
    $_POST['width']){
    $query .= "MAX_WIDTH >= '".mysql_real_escape_string($_POST['width'])."' AND ";
    }
    if(
    $_POST['weight']){
    $query .= "MAX_WEIGHT >='".mysql_real_escape_string($_POST['weight'])."' AND ";
    }

    // Lop-off the rightmost 4 characters because you don't know
    // which one was the last item identified.  You don't want to
    // have a trailing "AND" on the end of the string. 
    $query substr($query0, -4);

    // Finish the query string ...
    $query .= "ORDER BY MANUFACTURER, SERIES, NAME";
     
    // Perform the query. Retrieve all the data from the "example" table
    $result mysql_query($query) or die(mysql_error());
     

    while(
    $row mysql_fetch_array($result)){
     echo 
    $row['ID']. " - "$row['MANUFACTURER'];
     echo 
    "<br />";
     }

    // store the record of the "example" table into $row
     
    $row mysql_fetch_array$result );
     
    // Print out the contents of the entry 

    echo "ID: ".$row['ID'];
     echo 
    " MANUFACTURER: ".$row['MANUFACTURER'];
     echo 
    " SERIES: ".$row['SERIES'];
     echo 
    " NAME: ".$row['NAME'];
     
    ?>


  11. #10
    Junior Member
    Join Date
    Jun 2012
    Posts
    16
    Member #
    31986
    Works great! Thank you very much for the help!


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:03 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com