MySQL Error

[bravo14]

Hi Guys

 

I am trying to update an entry in a database using the folliwng code

 

<code>

<?php

  include_once('includes/connect.php');

  foreach ($_POST as $key => $val) {

  $key = mysql_real_escape_string($val);

}

  $res = mysql_query("UPDATE `fixtures` SET `yardley_goals` = '".$yardley_goals."',`opposition_goals` ='".$opposition_goals."', `motm` = '".$motm."'`totw`='".$totw."'`totw_reason`='".$totw_reason."' WHERE `match_id` ='".$match."'");

  echo($res);

{

  die('Error: ' . mysql_error());

  }

echo ("1 record added");

mysql_close($con);

      ?>

</code>

 

I am getting the following error

 

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`totw`=''`totw_reason`='' WHERE `match_id` =''' at line 1

[Mchl]

try

 

$res = mysql_query("UPDATE fixtures SET yardley_goals = '$yardley_goals',opposition_goals ='$opposition_goals', motm = '$motm', totw='$totw', totw_reason='$totw_reason' WHERE match_id ='$match'");

 

Also, you probably have register_globals disabled, so you need to do:

$yardley_goals = mysql_real_escape_string($_POST['yardley_goals']);

 

etc... for each of your variables.

 

[bravo14]

I have updated to the following code and the record is not updating even though it is saying the record has been updated

 

  <?php
  include_once('includes/connect.php');
  foreach ($_POST as $key => $val) {
   $key = mysql_real_escape_string($val);
}
  $res = mysql_query("UPDATE fixtures SET yardley_goals = '$yardley_goals',opposition_goals ='$opposition_goals', motm = '$motm', totw= '$totw', totwreason = '$totw_reason' where match_id = '$match'");
    if (!$res) 
{
  die('Error: ' . mysql_error());
  }
  else
echo ("1 record added");

$checkresult=mysql_query("SELECT *FROM fixtures where match_id='$match'");

echo('<table class="profiletext">
 <tr><td>Date</td><td>'.$checkresult[match_date].'</td></tr>
	 <tr><td>Opponent</td><td>'.$checkresult[Opposition].'</td></tr>
 <tr><td>Yardley Goals</td><td>'.$checkresult[yardley_goals].'</td></tr>
 <tr><td>Opposition Goals</td><td>'.$checkresult[opposition_goals].'</td></tr>
 </table>');
mysql_close($con); 
       ?>

 

I have also put in an additional query to find the updated record and that is not displaying the record.

[PFMaBiSmAd]

if (!$res)

The above conditional test only checks if the query was executed without error. It does not test if any row(s) were actually updated. You need to use mysql_affected_rows to find out if the UPDATE query actually changed a row.

 

However, if the row is not being updated, that would imply that the WHERE condition did not match any rows in your table. Have you checked what $match actually contains so that you know the query should find a row in your table?

 

mysql_query(), for your SELECT query, returns a result resource (if the query executes without any errors.) You need to use one of the mysql_fetch_xxxx() functions to actually get a row from the result set. If you were developing and debugging your code on a system with error_reporting set to E_ALL and display_errors set to ON, you would have received error messages when you referenced $checkresult[match_date], $checkresult[Opposition], ... that would have alerted you to the fact that they don't exist as part of the $checkresult variable.

[bravo14]

The values come from a form on a previous page, but now I have turned on Error Reporting it is saying that the variables are undefined.

[Mchl]

Also, you probably have register_globals disabled, so you need to do:

$yardley_goals = mysql_real_escape_string($_POST['yardley_goals']);

 

etc... for each of your variables.

 

[PFMaBiSmAd]

  foreach ($_POST as $key => $val) {
   $key = mysql_real_escape_string($val);
}

 

I'm going to guess that your form is invalid in some way and is not sending the expected data.

[Mchl]

If anything, it should be:

 

  foreach ($_POST as $key => $val) {
   $$key = mysql_real_escape_string($val);
}

 

Although I don't like this solution.