Adam Kona Adam Kona - 1 month ago 8
MySQL Question

How to store select table values as php to use in sql query

I'm using select values in a form which I want to store and upon submitting the form, a table is displayed with the results of my sql query but using the select values to query the database. My current html is here:



<body>
<div id="top-menu">
<ul>
<li><a class="active" href="">Profile</a></li>
<li><a href="">Favourites</a></li>
<li><a href="">Messages</a></li>

<li id="mainheading" style="align left"><a href="Home.html"><strong>BookSmart</strong></a></li>




</div>

<div id ="search_elements">

<form method="post" action="test.php">
<img src="UniSelect.jpeg">

<select name ="university">
<option selected disabled>Select a university</option>
<option value="ucl">UCL</option>
<option value="kings">Kings College</option>
<option value="imperial">Imperial College</option>
<option value="lse">London School of Economics</option>
</select>

<img height="250px", width="600px" src="PriceSelect.jpeg">

<select name="rent">
<option selected disabled>Select a weekly rent price</option>
<option value="50">0-£50</option>
<option value="100"> £100-£150</option>
<option value="150">£150-200</option>
<option value="200"> £200+</option>
</select>
<img height="250px", width="600px" src="RoomSelect.jpeg">

<select name="roomtype">
<option selected disabled>Room Type</option>
<option value="50">University Halls</option>
<option value="100"> Studio</option>
<option value="150">Flat</option>
<option value="200"> Shared Accomodation</option>

</select>


<img height="250px", width="600px" src="DistanceSelect.jpeg">

<select name="distance">
<option selected disabled> Selecet a distance from the university</option>
<option value="1">0-1 miles</option>
<option value="3"> 1-3 miles</option>
<option value="5">3-5 miles</option>
<option value="6"> 5+ miles</option>

</select>
<input type="submit" name="submit" value="Search">
</form>
</div>



</body>


And the code I currently use to query the database and display the results in a table is:

<p style ="text-align: center" id="php_style">
<?php
error_reporting(E_ERROR | E_PARSE);
session_start();
$counter_name = "counter.txt";
// Check if a text file exists. If not create one and initialize it to zero.
if (!file_exists($counter_name)) {
$f = fopen($counter_name, "w");
fwrite($f,"0");
fclose($f);
}
// Read the current value of our counter file
$f = fopen($counter_name,"r");
$counterVal = fread($f, filesize($counter_name));
fclose($f);
// Has visitor been counted in this session?
// If not, increase counter value by one
if(!isset($_SESSION['hasVisited'])){
$_SESSION['hasVisited']="yes";
$counterVal++;
$f = fopen($counter_name, "w");
fwrite($f, $counterVal);
fclose($f);
}
echo nl2br ("You are visitor number ".$counterVal. " to this site \n Today's date is ". date("d/m/Y"));

$con=mysqli_connect("localhost:8889","root","root","booksmart_properties");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
else
{
echo "we connected";
}

if(isset($_POST['submit']
{
$university = $_POST['university'];
$rent = $_POST['rent'];
$roomtype = $_POST['roomtype'];
$distance = $_POST['distance'];


//Now you can do anything with variables , you can put them inside query like this : //
// $result=mysqli_query($con,"SELECT * FROM Table WHERE Field = '$universit' "); //
// Note : this is dangrous way to do a query in PHP you should prevent something called SQL Injection , search for it //
// Perform queries

$result=mysqli_query($con,"SELECT * FROM ListedProperties
WHERE PropertyType = '.$roomtype' ");
echo "<table border='1'>
<tr>
<th>PropertyType</th>
<th>Description</th>
<th>Rent Price</th>
<th>Location</th>
</tr>";
while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['PropertyType'] . "</td>";
echo "<td>" . $row['Description'] . "</td>";
echo "<td>" . $row['RentPrice'] . "</td>";
echo "<td>" . $row['Location'] . "</td>";
echo "</tr>";
}
echo "</table>";

}))

mysqli_close($con);
?>




I was wondering how the values from the select table can be stored as php variables and inserted into the query for use when the search button is pressed.

Answer

Ok its easy , First you have to give every Select tags a name like this : i will do it for one and you do it for the others :

<select name="university">
      <option selected disabled>Select a university</option>
      <option value="ucl">UCL</option>
      <option value="kings">Kings College</option>
      <option value="imperial">Imperial College</option>
      <option value="lse">London School of Economics</option>
</select>

After you give every SELECT tag a name give your input now a name like this :

    <input type="submit" name="submit" value="Search">

Now its time to receive the values from the form and save them inside PHP variables .

    $con=mysqli_connect("localhost:8889","root","root","booksmart_properties");
    // Check connection
    if (mysqli_connect_errno())
        {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }
    else
    {
        echo "we connected";
    }

    if(isset($_POST['submit')
        {
        $university = $_POST['university'];
        $rent = $_POST['rent']; // i assumed that you name the second Select tag RENT and the Third ROOMTYPE and the last DISTANCE  
        $roomtype = $_POST['roomtype'];
        $distance = $_POST['distance'];

        //Now you can do anything with variables , you can put them inside query like this : //
        // $result=mysqli_query($con,"SELECT * FROM Table WHERE Field = '$universit' "); // 
        // Note : this is dangrous way to do a query in PHP you should prevent something called SQL Injection , search for it  //
        // Perform queries  

        $result=mysqli_query($con,"SELECT * FROM ListedProperties");
        echo "<table border='1'>
            <tr>
                <th>PropertyType</th>
                <th>Description</th>
                <th>Rent Price</th>
                <th>Location</th>
            </tr>";
        while($row = mysqli_fetch_array($result))
            {
                echo "<tr>";
                echo "<td>" . $row['PropertyType'] . "</td>";
                echo "<td>" . $row['Description'] . "</td>";
                echo "<td>" . $row['RentPrice'] . "</td>";
                echo "<td>" . $row['Location'] . "</td>";
                echo "</tr>";
            }
        echo "</table>";

        }

mysqli_close($con);
?>

Important links and subjects that will help you in the future :