Utilize user input in SQL query

Question

I'm trying to update a table given user input. Once the user hits submit on the form, I want the WHERE portion of my query to reflect the zip code entered by the user. Here is what I have so far, but it doesn't work. Any help would be greatly appreciated!

<form id="user-location" method="post" action="#">
      <input id="addressInput" name="addressInput" type="text">
      <input id="submit" onclick="searchLocations()" value="GO" type="button">
</form>

<?php
$con=mysqli_connect("localhost","######","######","######");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM Prospects WHERE zip = 'echo $_POST['addressInput']'");

echo "<table width='540' cellpadding='0' border='0' cellspacing='0'>
<tr>
<th>Under 4</th>
<th>5 - 9</th>
<th>10 - 14</th>
<th>15 - 17</th>
<th>18 - 20</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['cy_pop_04'] . "</td>";
  echo "<td>" . $row['cy_pop_59'] . "</td>";
  echo "<td>" . $row['cy_pop_1014'] . "</td>";
  echo "<td>" . $row['cy_pop_1517'] . "</td>";
  echo "<td>" . $row['cy_pop_1820'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>

WHERE zip = '$_POST[addressInput]' and not WHERE zip = 'echo $_POST['addressInput']' plus, you shouldn't be using this method; you're open to SQL injection; use prepared statements. Make sure you also have a valid JS function for your searchLocations() — Funk Forty Niner
– Funk Forty Niner, Commented Mar 14, 2014 at 13:33
use $zip = mysqli_real_escape_string($con,$_POST['addressInput']) and use WHERE zip = '$zip' at-least if not using prepare statements. — Abhik Chakraborty
– Abhik Chakraborty, Commented Mar 14, 2014 at 13:36
Thanks for the help! I made those changes but it doesn't seem to be updating my table. Is my submit button correct? (I removed the searchLocations() as well) — user2155400
– user2155400, Commented Mar 14, 2014 at 13:42
You're welcome. Are you using both your form and PHP inside the same page? @user2155400 If so, change <input id="submit" onclick="searchLocations()" value="GO" type="button"> to <input id="submit" value="GO" type="submit"> then use a conditional statement. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 14, 2014 at 13:43

Community · Accepted Answer · 2017-05-23 10:33:27Z

Change <input id="submit" onclick="searchLocations()" value="GO" type="button"> to <input id="submit" value="GO" type="submit" name="submit"> then use a conditional statement.

I.e.: if(isset($_POST['submit']))

Here is a prepared statement method.

The way you're doing it now (or intended to use), will leave you open to SQL injection.

<?php
$con=mysqli_connect("localhost","######","######","######");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

if(isset($_POST['submit'])){
$zip = $_POST['addressInput'];

if($query = $con->prepare("SELECT * FROM Prospects WHERE zip=?")){
    $query->bind_param("s", $zip);
    $query->execute();
}

echo "<table width='540' cellpadding='0' border='0' cellspacing='0'>
<tr>
<th>Under 4</th>
<th>5 - 9</th>
<th>10 - 14</th>
<th>15 - 17</th>
<th>18 - 20</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['cy_pop_04'] . "</td>";
  echo "<td>" . $row['cy_pop_59'] . "</td>";
  echo "<td>" . $row['cy_pop_1014'] . "</td>";
  echo "<td>" . $row['cy_pop_1517'] . "</td>";
  echo "<td>" . $row['cy_pop_1820'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

} // closing brace for if(isset($_POST['submit']))

mysqli_close($con);
?>

Footnotes:

Do not do or use this:

WHERE zip = 'echo $_POST['addressInput']'
             ^^^^        ^            ^

It's always better using prepared statements when using mysqli_* functions.

Here is a tutorial on using prepared statements.

Collectives™ on Stack Overflow

Utilize user input in SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest