No Error But no MYSQL update

Hi Guys,

I am trying to update a mysql table which has 2 cols

users - this will have a full name i.e Joe Bloggs
fee - this only has a numerical value

I am using the following code (this is an internal site so security isn’t paramount) but if the user has a space in their name i.e for their surname it does not update.


<?php
require('common.php');
error_reporting(E_ALL);
 ini_set('display_errors', 1);
//initilize PHP
if ( isset ( $_POST['newstats'] ) ) //If submit is hit
{

$post = array();
foreach ( $_POST as $key => $value )
{
	
	$sql = "UPDATE `query2` SET `fee` = :value WHERE `user` = :key";
	$statement = $db->prepare($sql); 
	$statement->bindValue(":value", $value);
	$statement->bindValue(":key", $key);
	$statement->execute();
	print('Perfect; Data has been updated!');
	
}
 }

?>


i’ve put

echo('<br>'.$key.' has been updated.<br>'); 

and now i get the user’s name but it has a underscore instead of a space which i think is the issue.

Can we see the html for the form you’re submitting? Also do the quotes around the column names make any difference? I’ve never quoted in that way.

Is there no id field you could use instead of the full name as the means of selecting which row to update? Using the full name is problematic, chiefly when people decide to get married (or the reverse).

Hey dude,

The below is the way it’s submitted.

You may be right i may need to do an ID field and auto increment it


<?php
require('common.php');
$users = $db->query('SELECT * FROM query2');

?>
<!DOCTYPE HTML> 
<html> 
  <head> 
    <meta charset="utf-8"> 
    <title>Stats Admin</title> 
    <meta name="viewport" content="width=device-width, initial-scale=1"> 
    <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script> 
	<style type="text/css"> 
      div {text-align: center}
      #result { margin-top: 15px; }
    </style> 
  </head>

	<!--New Stats Page --> 
	<body>
    <div data-role="page" id="newstats" data-theme="b"> 
      <div id="newstats"> 
        <form action="submit.php" method="post" id="newstatsForm"> 
          <h2><label>Stats Input</label></h2>
		  <?php
		  foreach ($users as $user)
		  {
          echo ('<div>'.$user['user'].'</div>
		  <input name="'.$user['user'].'" type="text" maxlength="10" data-mini="true" value="'.$user['fee'].'" /><br>');
		  } ?>

         <input name="newstats" data-role="button" data-inline="true" type="submit" value="submit" /></form>
		  </div></div>
		  <div id="result"></div></body>
<script>
$("#newstatsForm").on("submit", function(e){
        
        var $inputs = $('#newstatsForm :input'), 
            values = {};
        $inputs.each(function() {
          values[this.name] = this.value;
        });
        
        $.ajax({
          type : "POST",
          url : "submit.php",
          data: values,
          success : function(res) {
            $("#result").html(res);
            $("input[type=text]").val("");
          }
        });
        
        return false;
      });
      
      $(".back").on("click", function(){
        $("#result").html("");
      });
	  </script>
    <!--End Stats Page -->
    
</html>

As the username var comes directly from your initial query, it’s unlikely that the presence of the underline is the problem, as that must be in the database.

If you add


echo $key . " - " . $value;

as the first line of your foreach() loop, what keys and values does it show you? If they seem correct, then the issue must be in the query, and I’d be looking at that. If I type a query into pma and surround the column names with quotes like you have, I get an error when I try to execute it.

It’s doubtful you have a username “newstats” but you will have the POST key in the POST array from the form input name=“newstats”.
You should check for that key in your processing.

&lt;?php
require('common.php');
error_reporting(E_ALL);
ini_set('display_errors', 1);
//initilize PHP
if ( isset ( $_POST['newstats'] ) ) //If submit is hit
{
	
	$post = array();
	foreach ( $_POST as $key =&gt; $value )
	{
		if($key != "newstats"){
			$sql = "UPDATE `query2` SET `fee` = :value WHERE `user` = :key";
			$statement = $db-&gt;prepare($sql);
			$statement-&gt;bindValue(":value", $value);
			$statement-&gt;bindValue(":key", $key);
			$statement-&gt;execute();
			print('Perfect; Data has been updated!');
		}
	}
}
?&gt;