Update Issue

General PHP Help
#1

Hi,

I was wondering if anyone could possibly help me. I have managed to retrieve individual rows into a form. Some fields are editable so that i can change the quantity etc. However, when i come to pressing the update button i get the following error message


Update Failed: 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 ‘WHERE id = ‘1"’’ at line 2

My code is as follows:

<title>Edit Car Details</title>
<link href="css/adminmain.css" rel="stylesheet" type="text/css" />
Edit Car Details

[php]

<?php // Connecting to db for ODE Test Script update require_once "dbc.php"; mysql_select_db ("prestigesupercars"); //initiating variable: id the system is either getting the information or posting the information held on the database. //i used the get and post function because the user would like to see the latest updates if any fields are changed when they //are on the form. if(isset($_GET['id'])) $id = mysql_real_escape_string($_GET['id']); else $id = mysql_real_escape_string($_POST['id']); $msg = ""; $sql = "SELECT * FROM cars WHERE id = '".$id."'"; $find = mysql_query($sql) or die(mysql_error()); if(mysql_num_rows($find) != 0 ){ $r = mysql_fetch_array($find); } else { die("No record found for car ID number"); } //process updates if(isset($_POST['update'])) { $id = $_POST['id']; $ud = array(); //This is where the system returns all the updated fields into the database and updates the database $sql = "UPDATE cars SET Car_Name = '" . $_POST['ncar'] . "', Price = '" . $_POST['ncarp'] . "', Quantity = '" . $_POST['nq'] . "', WHERE id = '" . $id . "';"; $qry = mysql_query($sql); if($qry) { $msg = "Car Details Updated Click here to go back"; } else { $msg = "Update Failed: ".mysql_error(); } } $sql = "SELECT * FROM cars WHERE id = '".$id."'"; $find = mysql_query($sql) or die(mysql_error()); if(mysql_num_rows($find) != 0 ){ $r = mysql_fetch_array($find); } else { die("No record found for car ID number"); } ?>

[/php]

Car Id: <?=$r['id']?>" />
Car Name: Car Price: Quantity:
<?=$msg?>
#2

hello nitesh,
use below query for select record.

$sql = “SELECT * FROM cars WHERE id =”.$id;

use below query for update record.

$sql = “UPDATE cars SET Car_Name = '”.$_POST[‘ncar’]."’, Price =’".$_POST[‘ncarp’]."’, Quantity = ‘".$_POST[‘nq’]."’, WHERE id=".$id;

Note(tips): Naver use single quote’s arround the any varible if it’s define as integer in database table.
Example:
suppose you have define quantity in database table as integer than your update query is something like this.
$sql = “UPDATE cars SET Car_Name = '”.$_POST[‘ncar’]."’, Price =’".$_POST[‘ncarp’]."’, Quantity =".$_POST[‘nq’].", WHERE id=".$id;

i hope this will helpful for you

SR

#3

Hi,

Thank you for your response, i was wondering how would you define the variable if it was allocated as a float say for instants the price is set to float in my MYSQL table.

I am getting a new error message which i don’t understand…

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 ‘"’ at line 1

[php]

<?php // Connecting to db for updating cars require_once "dbc.php"; mysql_select_db ("prestigesupercars"); //initiating variable: id the system is either getting the information or posting the information held on the database. //i used the get and post function because the user would like to see the latest updates if any fields are changed when they //are on the form. if(isset($_GET['id'])) $id = mysql_real_escape_string($_GET['id']); else $id = mysql_real_escape_string($_POST['id']); $msg = ""; $sql = "SELECT * FROM cars WHERE id =".$id; $find = mysql_query($sql) or die(mysql_error()); if(mysql_num_rows($find) != 0 ){ $r = mysql_fetch_array($find); } else { die("No record found for car ID number"); } //process updates if(isset($_POST['update'])) { $id = $_POST['id']; $ud = array(); //This is where the system returns all the updated fields into the database and updates the database $sql = "UPDATE cars SET Car_Name = '".$_POST['ncar']."', Price ='".$_POST['ncarp']."', Quantity =".$_POST['nq']." WHERE id =.$id;"; $qry = mysql_query($sql); if($qry) { $msg = "Car Details Updated Click here to go back"; } else { $msg = "Update Failed: ".mysql_error(); } } $sql = "SELECT * FROM cars WHERE id = '".$id."'"; $find = mysql_query($sql) or die(mysql_error()); if(mysql_num_rows($find) != 0 ){ $r = mysql_fetch_array($find); } else { die("No record found for car ID number"); } ?>

[/php]

#4

hello nitesh,

replace below line(remove this line)
$sql = “UPDATE cars SET Car_Name = '”.$_POST[‘ncar’]."’, Price =’".$_POST[‘ncarp’]."’, Quantity =".$_POST[‘nq’]." WHERE id =.$id;";

with below code (use this line)
$sql = “UPDATE cars SET Car_Name = '”.$_POST[‘ncar’]."’, Price =’".$_POST[‘ncarp’]."’, Quantity =".$_POST[‘nq’]." WHERE id = ".$id;

SR

#5

Hi Sarthak,

Unfortunately i’m still getting the same error message :frowning:

#6

hello nitesh,
post your latest code which is you are using now.
SR

#7

Hi Sarthak,

This is currently my current up to date code:

Thanks :slight_smile:

Prestige Supercars 
<title>Edit Car Details</title>
Edit Car Details

[php]

// Connecting to db for updating cars
require_once “dbc.php”;
mysql_select_db (“prestigesupercars”);
//initiating variable: id the system is either getting the information or posting the information held on the database.
//i used the get and post function because the user would like to see the latest updates if any fields are changed when they
//are on the form.
if(isset($_GET[‘id’]))
$id = mysql_real_escape_string($_GET[‘id’]);
else
$id = mysql_real_escape_string($_POST[‘id’]);
$msg = “”;
$sql = “SELECT * FROM cars WHERE id =”.$id;
$find = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($find) != 0 ){
$r = mysql_fetch_array($find);
} else {
die(“No record found for car ID number”);
}
//process updates
if(isset($_POST[‘update’])) {

$id = $_POST[‘id’];
$ud = array();

//This is where the system returns all the updated fields into the database and updates the database
$sql = "UPDATE cars SET Car_Name = '".$_POST['ncar']."', Price ='".$_POST['ncarp']."', Quantity =".$_POST['nq']." WHERE id = ".$id; 

$qry = mysql_query($sql);
if($qry) {
     $msg = "Car Details Updated <a href='editcar.php'>Click here to go back</a>";
  } else {
     $msg = "Update Failed: ".mysql_error();
  }

}

$sql = “SELECT * FROM cars WHERE id =”.$id;
$find = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($find) != 0 ){
$r = mysql_fetch_array($find);
} else {
die(“No record found for car ID number”);
}

?>

[/php]

Car Id: <?=$r['id']?>" />
Car Name: Car Price: Quantity:
<?=$msg?> 
</div>
<div id="sidebar">
	<div id="sidebar2">

	<div id="sidebar-title">Home Page</div>
	<div id="sidebar-content"><strong><a href="systemadmin.html" class="button"><br>Home</strong></li></a>
</div>
#8
hello , use below php code in your code [php] <? // Connecting to db for updating cars require_once "dbc.php"; mysql_select_db ("prestigesupercars"); //initiating variable: id the system is either getting the information or posting the information held on the database. //i used the get and post function because the user would like to see the latest updates if any fields are changed when they //are on the form. $msg = ""; //process updates if(isset($_POST['update'])) { $id = $_POST['id']; $ud = array(); //This is where the system returns all the updated fields into the database and updates the database $updatesql = "UPDATE cars SET Car_Name = '".$_POST['ncar']."', Price ='".$_POST['ncarp']."', Quantity =".$_POST['nq']." WHERE id = ".$id; $qry = mysql_query($updatesql); if($qry){ $msg = "Car Details Updated Click here to go back"; } else { $msg = "Update Failed: ".mysql_error(); } } else { if(isset($_GET['id'])) $id = mysql_real_escape_string($_GET['id']); } 
$sql = "SELECT * FROM cars WHERE id =".$id;
$find = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($find) != 0 ){
   $r = mysql_fetch_array($find);
} 
else {
   die("No record found for car ID number");
}

?>

[/php]
Do one more thing echo $updatesql; and execute directly in you database.

SR

#9

Hi Sarthak.

Unfortunately another syntax error occurred when i tried the code again… :frowning:

UPDATE cars SET Car_Name = ‘Porsche Panamera GTS’, Price =‘704901’, Quantity =4 WHERE id = 1"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 ‘"’ at line 1

However, when i was in my MYSQL table when i inserted the following sql statement:

UPDATE cars SET Car_Name = ‘Porsche Panamera GTS’, Price =‘704901’, Quantity =4 WHERE id = ‘1"’;

It worked. so i think its something to do with the id ??? could you please help me thanks :slight_smile:

#10

Well, the query you posted ended with this: =4 WHERE id = ‘1"’;

You have a double-quote inside the single quotes. That is backwards! Try: =4 WHERE id = ‘1’";

Sponsor our Newsletter | Privacy Policy | Terms of Service