Search and Update Error

[JCEE]

Hi,

 

I'm having difficulty with some PHP coding I have put together.  I have a HTML search form that allows users to search a MySQL DB for a value and posts the value to a PHP page, this then displays the results.  This part of my code is working.  My problem is the second half of what I want to do.  I have added a submit button for each record that is displayed so that if the user selects that result it increments a value in the row to show how many times it has been selected.  At the minute it always updates the last record rather than the one selected, I can't work out why this is as I have set the update query to update when equal to the id of that row. A second issue is that after clicking submit the page then displays all records from the database rather than those previously displayed after the initial search.  If anyone could point out where I'm going wrong I'd appreciate it.  Thanks, here is my code;

 

<?php

mysql_connect ("localhost", "USERNAME","PASSWORD")  or die (mysql_error());

mysql_select_db ("DBNAME");

 

$term = $_POST['term'];

 

$sql = mysql_query("select * from TABLE where point like '%$term%' or point2 like '%$term%' or point3 like '%$term%' ");

 

echo "here are your results for";

echo "<br>";

  echo "search criteria:  ";

echo $term;  

  echo "<br>";

  echo "  ";

  

while ($row = mysql_fetch_array($sql)){

 

$chosen = $row['choice'];

$id = $row['id'];

 

echo '<form name="form1" id="form1" method="POST" action="">';

     echo "<table>";

     echo "<tr>";

  echo '<td> 1 </td>';

  echo '<td> 2 </td>';

  echo '<td> 3 </td>';

  echo '<td> 4 </td>';

  echo '<td> 5 </td>';

  echo '<td> 6 </td>';

  echo "<tr>";

  echo "<td>" .$row['field1']."</td>";

  echo "<td>" .$row['field2']."</td>";

  echo "<td>" .$row['field3']."</td>";

  echo "<td>" .$row['field4']."</td>";

  echo "<td>" .$row['field5']."</td>";

  echo "<td>" .$row['id']."</td>";

  echo "<tr>";

  echo '<td colspan="6"> <input type = "submit" id="submit" name="submit" value="submit"> </td>';

  echo "</table>";

echo "</form>";

  echo "<br>";

  

}

if (isset($_POST['submit']))

{

$likes = $liked+1;

$insert= mysql_query("UPDATE Players SET choice='$chosen' WHERE id=$id");

}

   

?>

[.josh]

You aren't passing the id in the form submit. You just have the stuff displaying, and a submit button. Then in your $_POST condition you are looking for $id which doesn't actually exist.

 

One way to fix this is to just before the submit button, add a hidden text field:

 

echo "<input type='hidden' name='id' value='$id' />";

and then where you check the form submission:

if (isset($_POST['submit']))
{
$id=(int)$_POST['id']; // cheap way to sanitize the input value, assuming the id is an integer. if not, validate it before using it in your query
$likes = $liked+1;
$insert= mysql_query("UPDATE Players SET choice='$chosen' WHERE id=$id");
}

[.josh]

as for your 2nd issue: http is a stateless protocol, so it doesn't remember what happened previously, unless you make it remember.

 

you set this:

$term = $_POST['term'];

Well that posted variable doesn't exist when you submit the form again. One way to fix this would be to put another hidden field in the form, same as the id i mentioned above. Alternatively you could use a session variable, though that would be a bit more complex and you'd have to consider when/where to unset it (like if the user decides to go to the original form it is entered in)

[.josh]

Also a word of advice: you should not put user-submitted values directly into your database queries. You need to validate that they are expected values in expected formats, or escape them, or use prepared statements. If you do not, a user can inject arbitrary sql syntax into the query string and wreak all kinds of havoc to your database and site, depending on how your database is structured, what's in there, etc..

[JCEE]

Thanks so much for your advice Josh and more to the point swift advice.  It works a treat.  Sometimes it's a mental block and you just can't see how to proceed.  Really appreciate you taking the time!