Trying to update a record to 2 tables using a form

PHP
1

Hi all I hope you can help. I have an edit button on a form that updates a row from a mysql DB. When I was pulling from one table it worked fine using $query = "UPDATE backplane_header_information SET "; but now I’ve joined 2 tables with the common field between them being HEADID and when I try to update a record I get a failed error. Here’s is the code. Thank you in advance for the assistance.

//Edit Record
if ($id == ‘’){
$result = ‘error’;
$message = ‘id missing’;
} else {
$query = "UPDATE table1
INNER JOIN table2
ON table1.HEADID = table2.HEADID
SET ";

  if (isset($_GET['var1']))         		{ $query .= "var1 = '" 				. mysqli_real_escape_string($db_connection, $_GET['var1']) 				. "', "; }
  if (isset($_GET['var2'])) 				{ $query .= "var2 = '" 				. mysqli_real_escape_string($db_connection, $_GET['var2']) 				. "', "; }
  if (isset($_GET['var3']))   				{ $query .= "var3 = '" 				. mysqli_real_escape_string($db_connection, $_GET['var3']) 				. "', "; }
  if (isset($_GET['var4']))  				{ $query .= "var4 = '" 				. mysqli_real_escape_string($db_connection, $_GET['var4']) 				. "', "; }
  if (isset($_GET['var5']))  				{ $query .= "var5 = '" 				. mysqli_real_escape_string($db_connection, $_GET['var5']) 				. "', "; }
  if (isset($_GET['var6']))    				{ $query .= "var6 = '" 				. mysqli_real_escape_string($db_connection, $_GET['var6']) 				. "', "; }
  if (isset($_GET['var7']))   				{ $query .= "var7 = '" 				. mysqli_real_escape_string($db_connection, $_GET['var7']) 				. "', "; }
  if (isset($_GET['var7'])) 				{ $query .= "var7 = '" 				. mysqli_real_escape_string($db_connection, $_GET['var7']) 				. "', "; }
  if (isset($_GET['var7'])) 				{ $query .= "var7 = '" 				. mysqli_real_escape_string($db_connection, $_GET['var7']) 				. "', "; }		
  if (isset($_GET['var8'])) 				{ $query .= "var8 = '" 				. mysqli_real_escape_string($db_connection, $_GET['var8'])				. "', "; }
  if (isset($_GET['var_from_table2'])) 		{ $query .= "var_from_table2 = '" 	. mysqli_real_escape_string($db_connection, $_GET['var_from_table2'])	. "'";   }	  	

  $query .= "WHERE table1.HEADID = '" . mysqli_real_escape_string($db_connection, $id) . "'";
  $query  = mysqli_query($db_connection, $query);
  if (!$query){
    $result  = 'error';
    $message = 'query error';
  } else {
    $result  = 'success';
    $message = 'query success';
  }
}
2

echo the generated $query. Should tell you where it’s off, though I’d guess it’s this portion mysqli_real_escape_string($db_connection, $id)

3

Aren’t you going to end up with a spare comma in the query string, if the user set any of var1 to var8, but not var_from_table2? I can never remember if that matters, and don’t have sql running to test.

1 Like
4

My guess is the field needs to be qualified with the table name.

5

After echoing the query I got a parsererror error so it appears to be json related. I feel that the syntax is correct but I’m missing something somewhere?

6

The error is on the echo of the query, or on the run of the query? Can you put the completed query here?

7

Sure thing, thanks. The way the page works is it retrieves all the records from the 2 tables then the user can pick which record they want to edit. Getting the data from both tables works fine as does choosing a desired record. It’s just when they try to update that record is when things go south. Thanks again.

Also, I noticed I posted 3 var7’s "if (isset($_GET[‘var7’])) where it should be var7, var8, var9, and var10. Sorry about that.

**// Get records**
$query = "SELECT table1.HEADID, 
				 table1.var1, 
				 table1.var2, 
				 table1.var3, 
				 table1.var4, 
				 table1.var5, 
				 table1.var6, 
				 table1.var7, 
				 table1.var8, 
				 table1.var9, 
				 table1.var10, 
				 table2.var_from_table2
		  FROM table1
		  INNER JOIN table2
		  ON table1.HEADID = table2.HEADID
		  ORDER BY var1";
$query = mysqli_query($db_connection, $query);
if (!$query){
  $result  = 'error';
  $message = 'query error';
} else {
  $result  = 'success';
  $message = 'query success';
  while ($record = mysqli_fetch_array($query)){
    $functions  = '<div class="function_buttons"><ul>';
    $functions .= '<li class="function_edit"><a data-id="'   		. $record['HEADID'] . '" data-name="' . $record['var2'] . '"><span>Edit</span></a></li>';
    $functions .= '<li class="function_clone"><a data-id="'   		. $record['HEADID'] . '" data-name="' . $record['var2'] . '"><span>Clone</span></a></li>';
    $functions .= '<li class="function_report"><a data-id="' 		. $record['HEADID'] . '" data-name="' . $record['var2'] . '"><span>PDF Report</span></a></li>';
    $functions .= '<li class="function_tab"><a <a href="http://apcweb03/eqr/" target=_blank></a></li>';				
    $functions .= '<li class="function_delete"><a data-id="' 		. $record['HEADID'] . '" data-name="' . $record['var2'] . '"><span>Delete</span></a></li>';
    $functions .= '</ul></div>';
    $mysql_data[] = array(
      "var1"          	=> $record['var1'],
      "var2"  			=> $record['var2'],
      "var3"    		=> $record['var3'],
      "var4" 			=> $record['var4'],
      "var5"   			=> $record['var5'],
      "var6"     		=> $record['var6'],
      "var7"    		=> $record['var7'],
      "var8"  			=> $record['var8'],
      "var9"  			=> $record['var9'],
      "var10"  			=> $record['var10'],
      "var_from_table2" => $record['var_from_table2'],		  		  		  
      "functions"     	=> $functions
    );
  }
}

} elseif ($job == ‘get_record’){

**// Get record**

if ($id == ''){
  $result  = 'error';
  $message = 'id missing';
} else {
  $query = "SELECT table1.HEADID, 
  				   table1.var1, 
				   table1.var2, 
				   table1.var3, 
				   table1.var4, 
				   table1.var5, 
				   table1.var6, 
				   table1.var7, 
				   table1.var8, 
				   table1.var9, 
				   table1.var10, 
				   table2.var_from_table2
		  FROM table1
		  INNER JOIN table2
		  ON table1.HEADID=table2.HEADID
		  WHERE table1. HEADID = '" . mysqli_real_escape_string($db_connection, $id) . "'";
  $query = mysqli_query($db_connection, $query); 
  if (!$query){
    $result  = 'error';
    $message = 'query error';
  } else {
    $result  = 'success';
    $message = 'query success';
    while ($record = mysqli_fetch_array($query)){
      $mysql_data[] = array(
        "var1"            => $record['var1'],
        "var2"  		  => $record['var2'],
        "var3"    		  => $record['var3'],
        "var4"   		  => $record['var4'],
        "var5"   		  => $record['var5'],
        "var6"     		  => $record['var6'],
        "var7"    		  => $record['var7'],
        "var8"  		  => $record['var8'],
      	"var9"  		  => $record['var9'],
        "var10"  		  => $record['var10'],		
        "var_from_table2" => $record['var_from_table2']
      );
    }
  }
}
8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.