0

I have a tests.php web page that successfully creates and populates a html table using data from a mysql database on an online server. Next I want to make the web page interactive by adding html select to limit the number of rows the sql query returns. I've added the html select code and amened my sql query to use a variable for LIMIT. The issue I have is how to make the query run when the select dropdown is changed. I'm assuming some kind of page refresh needs to be called but unsure how to go about this. For example would I need to enclose the html as a from and have a submit button to make this work? This IS NOT what I want, ideally the user presses html select and then the page refreshes or re runs the sql database query.

Please be kind this is the first php code I've written.

My select code

<select name="limit" id="limit">
    <option value="10">10</option>
    <option value="25">25</option>
    <option value="50">50</option>
    <option value="100">100</option>
</select>

The php code

</body>
</html>
<?php
$limit = 10;
if(isset($_POST["limit"])) $limit = $_POST["limit"];


$con=mysqli_connect("localhost","user","password","database");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM TableName WHERE Region='Home' ORDER BY TeamName ASC LIMIT $limit");

echo "<table class='sample' style='width:50%'>
<tr>
<th>Team Name</th>
<th># Players</th>
<th>Venue</th>
<th>Date</th>
<th>Score</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['TeamName'] . "</td>";
echo "<td>" . $row['NumPlayers'] . "</td>";
echo "<td>" . $row['Venue'] . "</td>";
echo "<td>" . $row['Date'] . "</td>";
echo "<td>" . $row['Score'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

UPDATE

I think I may have a simpler solution using javascipt although it's not working correctly.

Using javascript function:

 window.location.reload(true);

and change my html select

  <select onchange="refresh.call()">

However when the page re loads it's using the typed $limit value

  $limit =10;

instead of the select value.

The page refresh is working but is there a way to inject the html select value somehow?

user3298021
  • 33
  • 5
  • 1
    Yes you need a `
    ` and a submit button. Unless you ant to do it with AJAX! But I woudl go for the `
    ` mechanism first
     – RiggsFolly Sep 10 '18 at 10:48
  • What is the error message ? – Pardeep Sep 10 '18 at 10:48
  • "The issue I have is how to make the query run when the select dropdown is changed"...find a tutorial about forms in PHP/HTML. You need to enclose the select inside a form tag, and have a button which causes the browser to submit the values from the form to the server. N.B. It would be very wise to write some PHP code to check that `$limit` is definitely a number before using it in your query. Otherwise you could leave yourself open to SQL Injection attacks. – ADyson Sep 10 '18 at 10:48
  • 1
    Here is the [PHP manual pages on Handling forms](http://www.php.net/manual/en/tutorial.forms.php) start by reading that – RiggsFolly Sep 10 '18 at 10:49
  • 1
    onchange of your select box, use Ajax and call other php file which will run your query and return the data result – manny Sep 10 '18 at 10:49
  • 1
    @Pardeep where does it mention an error message? The question is clearly because the OP doesn't know what to do, not because they have a specific bug. – ADyson Sep 10 '18 at 10:50
  • 1
    @manny For someone that has to ask "Do I need a Form" Jumping straight into AJAX might be a step or 2 too far for a first try – RiggsFolly Sep 10 '18 at 10:50
  • 1
    Actually when I look closer at your PHP code, all the php script is AFTER the ` – RiggsFolly Sep 10 '18 at 10:55
  • Have a look my answer. You need to decide whether you will reload the whole page when the combobox changes ( using `onChange`), or weather you will use an Ajax Call to get back the results, and then manipulate the current HTML – Menelaos Sep 10 '18 at 10:58
  • @MenelaosBakopoulos While your answer is probably very good. Remember the OP has not even got as far as creating their first `
    ` swamping them with all this GREAT info is probably going to go right over their head!
     – RiggsFolly Sep 10 '18 at 11:00
  • @RiggsFolly :) ... well, what they did ask does involve 2-3 steps. But you are right on that point about info :D – Menelaos Sep 10 '18 at 11:02

1 Answers1

0

Sanitizing Input

First off, you need to sanitize your limit variable as it's insecure to allow input directly into the SQL.

see:

Single Page App Approach

Secondly, you need to decide if you will re-write the html notes using javascript ( in which case you will make an ajax/jquery request). Then you will rewrite the html using some framework or javascript ( innerHTML = '') or editing the DOM tree.

See: Ajax tutorial for post and get

AND

https://stackoverflow.com/a/19527642/1688441

Reloading the page

If you decide you don't want to use javascript to rewrite the Dom tree, then the only solution is to refresh the entire page using either a GET or POST with the limit parameter.

So you will use an onChange listener, and call the page again with a limit parameter.

See: Reload page after user clicks on combobox (with some rules)

Menelaos
  • 23,508
  • 18
  • 90
  • 155