mysql update statement

sarmenhb · 05-14-2008, 01:45 AM

hello, for some reason the update statement doesnt work for me. here is what my code looks like
thanks

Code:

<?php


include("config.php");
$viewrec = mysql_query("select * from tbl_clone where id = '$id'");
if(isset($_POST['submit'])) {

#==================
# update record
#==================


$updatesql = "update tbl_clone set 

domain = '{$_POST['txt_domain']} ' ,
client ='{$_POST['txt_client']}' , 
date_prom ='{$_POST['txt_datepromised']}' ,
status = '{$_POST['drop_status']}' ,
devsite = '{$_POST['txt_devsite']}' ,
clone_ready = '{$_POST['readyclone']}' , 
exist_customization = '{$_POST['customexist']}' , 
new_custom = '{$_POST['customaddons']}' ,
sugar = '{$_POST['txt_sugar']}' ,
comments = '{$_POST['txt_comments']}' ,
highlight = '{$_POST['highlights']}' where id='$id' ";

$updatedata = mysql_query($updatesql) or die(mysql_error());

echo "<script>alert('data updated');</script>";


}

?>


<html xmlns="http:www.w3.org1999xhtml">

<head>
<meta http-equiv="Content-Type" content="texthtml; charset=utf-8" >
<title>New Record</title>

<link href="style.css" type="text/css" rel="stylesheet"> 

<style type="text/css">
.style1 {
	color: #FF0000;
}
.style2 {
	text-decoration: none;
}
</style>
</head>

<body>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<div id="header">Clone/Upgrade Statuses</div>
<p>
<br>
Field Highlight:<br>
<select name="highlight">
<option value="notset">Status Not Set</option>
<option value="qc">Sitecheck/QC</option>
<option value="bug">Bug Fixing</option>
<option value="ready">Ready for delivery</option>
</select>
<br><br>
Domain:<br>
<?php

while($row = mysql_fetch_assoc($viewrec)) {



?>

<input type="text" name="txt_domain" value="<?php echo $row['domain']; ?>"> <span class="style1"><strong>(required)</strong></span><br >
<br >


Client Name:<br>
<input type="text" name="txt_client" value="<?php echo $row['client']; ?>"><br >
<br >
&nbsp;Delivery Date:<br>
<input type="text" name="txt_datepromised" value="<?php echo $row['date_prom']; ?>"> <br >
<br>


Status:<br>
<input type="text" name="drop_status" value="<?php echo $row['status']; ?>" ><br >
<br >


Dev Site<br>
<input type="text" name="txt_devsite" value="<?php echo $row['devsite']; ?>"><br >
<br >

Ready to CloneUpgrade?<br>
<input type="text" name="readyclone" value="<?php echo $row['clone_ready']; ?>">
 <br >
<br >

Customizations Existing<br>
<textarea style="width: 229px; height: 46px" name="customexist"><?php echo $row['exist_customization']; ?></textarea> <br >
<br >

Customizations (add-ons)<br>
<textarea style="width: 229px; height: 50px" name="customaddons"><?php echo $row['new_custom']; ?></textarea><br >
<br >
User:<br>
<input type="text" name="txt_sugar" value="<?php echo $row['sugar']; ?>"> <br >
<br >

Comments<br>
<textarea style="width: 232px; height: 52px" name="txt_comments"><?php echo $row['comments']; ?></textarea> <br >
<br >
<?php } ?>
<input type="submit" name="submit" value="Save Record">

</p>
</form>


<br>
<a href="index.php" class="style2">home</a>


</body>

</html>

Jim · 05-14-2008, 07:11 AM

What is the error?

You should immidiatly add the error and all kinds of information what you can give us :)

zxt3st · 05-15-2008, 06:30 AM

Quote:

Originally Posted by sarmenhb

hello, for some reason the update statement doesnt work for me. here is what my code looks like
thanks

Code:

<?php


include("config.php");
$viewrec = mysql_query("select * from tbl_clone where id = '$id'");
if(isset($_POST['submit'])) {

#==================
# update record
#==================


$updatesql = "update tbl_clone set 

domain = '{$_POST['txt_domain']} ' ,
client ='{$_POST['txt_client']}' , 
date_prom ='{$_POST['txt_datepromised']}' ,
status = '{$_POST['drop_status']}' ,
devsite = '{$_POST['txt_devsite']}' ,
clone_ready = '{$_POST['readyclone']}' , 
exist_customization = '{$_POST['customexist']}' , 
new_custom = '{$_POST['customaddons']}' ,
sugar = '{$_POST['txt_sugar']}' ,
comments = '{$_POST['txt_comments']}' ,
highlight = '{$_POST['highlights']}' where id='$id' ";

$updatedata = mysql_query($updatesql) or die(mysql_error());

echo "<script>alert('data updated');</script>";


}

?>


<html xmlns="http:www.w3.org1999xhtml">

<head>
<meta http-equiv="Content-Type" content="texthtml; charset=utf-8" >
<title>New Record</title>

<link href="style.css" type="text/css" rel="stylesheet"> 

<style type="text/css">
.style1 {
	color: #FF0000;
}
.style2 {
	text-decoration: none;
}
</style>
</head>

<body>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<div id="header">Clone/Upgrade Statuses</div>
<p>
<br>
Field Highlight:<br>
<select name="highlight">
<option value="notset">Status Not Set</option>
<option value="qc">Sitecheck/QC</option>
<option value="bug">Bug Fixing</option>
<option value="ready">Ready for delivery</option>
</select>
<br><br>
Domain:<br>
<?php

while($row = mysql_fetch_assoc($viewrec)) {



?>

<input type="text" name="txt_domain" value="<?php echo $row['domain']; ?>"> <span class="style1"><strong>(required)</strong></span><br >
<br >


Client Name:<br>
<input type="text" name="txt_client" value="<?php echo $row['client']; ?>"><br >
<br >
&nbsp;Delivery Date:<br>
<input type="text" name="txt_datepromised" value="<?php echo $row['date_prom']; ?>"> <br >
<br>


Status:<br>
<input type="text" name="drop_status" value="<?php echo $row['status']; ?>" ><br >
<br >


Dev Site<br>
<input type="text" name="txt_devsite" value="<?php echo $row['devsite']; ?>"><br >
<br >

Ready to CloneUpgrade?<br>
<input type="text" name="readyclone" value="<?php echo $row['clone_ready']; ?>">
 <br >
<br >

Customizations Existing<br>
<textarea style="width: 229px; height: 46px" name="customexist"><?php echo $row['exist_customization']; ?></textarea> <br >
<br >

Customizations (add-ons)<br>
<textarea style="width: 229px; height: 50px" name="customaddons"><?php echo $row['new_custom']; ?></textarea><br >
<br >
User:<br>
<input type="text" name="txt_sugar" value="<?php echo $row['sugar']; ?>"> <br >
<br >

Comments<br>
<textarea style="width: 232px; height: 52px" name="txt_comments"><?php echo $row['comments']; ?></textarea> <br >
<br >
<?php } ?>
<input type="submit" name="submit" value="Save Record">

</p>
</form>


<br>
<a href="index.php" class="style2">home</a>


</body>

</html>

yeah agree on the above post, kindly post the error as well as the details of php/mysql {version} you are using.

Village Idiot · 05-15-2008, 01:30 PM

For starters, don't put {'s around the values in the query, that probably is creating an error.

xenon · 05-15-2008, 08:15 PM

Quote:

Originally Posted by Village Idiot

For starters, don't put {'s around the values in the query, that probably is creating an error.

That's not an issue inside double quotes. { and } are actually recommended when trying to use variables inside a double quoted statement, because they clearly delimit the beginning and the end of a variable name. That way, the PHP parser will not get confused when you try to use something like:

Code:

echo "abcd some $var->some_property['abcd']->some_other_property and some more text";

sarmenhb · 05-15-2008, 09:02 PM

i've updated the code and found out that it was something to do with the sql i was using at the where clause.
here is the new code

Code:

<?php


require("config.php");
$viewrec = mysql_query("select * from tbl_clone where id = '$id'") or die("query failed");

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

#==================
# update record
#==================


if(!$_POST['txt_domain']) { die('the domain name is a required field <br><a href="index.php">go back</a>'); }

$domain = $_POST['txt_domain'];
$client = $_POST['txt_client'];
$dateprom = $_POST['txt_datepromised'];
$status = $_POST['drop_status'];
$devsite = $_POST['txt_devsite'];
$readyclone = $_POST['readyclone'];
$customexist = $_POST['customexist'];
$customaddon = $_POST['customaddons'];
$reporter = $_POST['reporter'];
$comments = $_POST['txt_comments'];
$highlight = $_POST['highlight'];
die

$updatedata = mysql_query("update tbl_clone set 

domain =                   '$domain',
client =                   '$client', 
date_prom =                '$dateprom',
status =                   '$status',
devsite =                  '$devsite',
clone_ready =              '$readyclone', 
exist_customization =      '$customexist', 
new_custom =               '$customaddon',
sugar =                    '$reporter',
comments =                 '$comments',
highlight =                '$highlight',


where id='$record'") or die(mysql_error());




}

?>




<html xmlns="http:www.w3.org1999xhtml">

<head>
<meta http-equiv="Content-Type" content="texthtml; charset=utf-8" >
<title>New Record</title>

<link href="style.css" type="text/css" rel="stylesheet"> 

<style type="text/css">
.style1 {
	color: #FF0000;
}
.style2 {
	text-decoration: none;
}
</style>
</head>

<body>

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<div id="header">Clone/Upgrade Statuses</div>
<p>
<br>
Field Highlight:<br>
<select name="highlight">
<option value="notset">Status Not Set</option>
<option value="qc">Sitecheck/QC</option>
<option value="bug">Bug Fixing</option>
<option value="ready">Ready for delivery</option>
</select>
<br><br>
Domain:<br>
<?php

while($row = mysql_fetch_assoc($viewrec)) {



?>

<input type="text" name="txt_domain" value="<?php echo $row['domain']; ?>"> <span class="style1"><strong>(required)</strong></span><br >
<br >


Client Name:<br>
<input type="text" name="txt_client" value="<?php echo $row['client']; ?>"><br >
<br >
&nbsp;Delivery Date:<br>
<input type="text" name="txt_datepromised" value="<?php echo $row['date_prom']; ?>"> <br >
<br>


Status:<br>
<input type="text" name="drop_status" value="<?php echo $row['status']; ?>" ><br >
<br >


Dev Site<br>
<input type="text" name="txt_devsite" value="<?php echo $row['devsite']; ?>"><br >
<br >

Ready to CloneUpgrade?<br>
<input type="text" name="readyclone" value="<?php echo $row['clone_ready']; ?>">
 <br >
<br >


Customizations Existing<br>
<textarea style="width: 229px; height: 46px" name="customexist"><?php echo $row['exist_customization']; ?></textarea> <br >
<br >


Customizations (add-ons)<br>
<textarea style="width: 229px; height: 50px" name="customaddons"><?php echo $row['new_custom']; ?></textarea><br >
<br >
User:<br>
<input type="text" name="reporter" value="<?php echo $row['sugar']; ?>"> <br >
<br >


Comments<br>
<textarea style="width: 232px; height: 52px" name="txt_comments"><?php echo $row['comments']; ?></textarea> <br >
<br >
<?php } ?>
<input type="submit" name="submit" value="Save Record">











</p>
</form>





<br>
<a href="index.php" class="style2">home</a>





</body>

</html>

here is the error

Code:

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=''' at line 16

i'm passing the $id variable like this: editrecord.php?id=25

Jim · 05-15-2008, 09:06 PM

Remove the last comma, the last field doesn't need one :)

And you dont get the ID because the form reloads the page (sort of) and doesn't send the ID with it.

sarmenhb · 05-15-2008, 09:36 PM

Quote:

Originally Posted by Jim

Remove the last comma, the last field doesn't need one :)

And you dont get the ID because the form reloads the page (sort of) and doesn't send the ID with it.

whats another way to do it?

Jim · 05-16-2008, 05:27 AM

With the form you've made, include the ID in the URL. So the action tag will be action="<?php echo $_SERVER['PHP_SELF'] . '&id= '. $id; ?>".

But first you want to check the ID is save for HTML input via the GET variable:

if(!is_numeric($_GET['id']) OR empty($_GET['id'])) {
die('no valid id');
}

That is a very simple (but ugly) way to do it :)

EyeDentify · 05-16-2008, 07:55 AM

well i would have included the ID as a walue of a hidden input field in the form.
And used the POST method to send it.

xenon · 05-16-2008, 07:45 PM

Quote:

Originally Posted by EyeDentify

well i would have included the ID as a walue of a hidden input field in the form.
And used the POST method to send it.

I support that, simply because I do these kind of operations the same way.

sarmenhb · 05-17-2008, 12:42 AM

what if i have a table on the first page that displays rows of data from the database and each row has a unique id. if if were to use hidden textbox fields how do i insert the hidden fields value to be the value that was selected as a link to have that pass over to the next page?

example:

<a href="page.php?id=1">dynamic link1</a>
<a href="page.php?id=2">dynamic link1</a>
<a href="page.php?id=3">dynamic link1</a>
etc.

so if these links were to dynamically be created based on the ammouint of rows in the database,

how do i set the hidden textboxes value to the item selected?

thanks

sarmenhb · 05-20-2008, 01:46 AM

i got it lol, what i did was once i got on the edit page i threw a hidden textbox into the html that grabs the urls id variable and displays its output then i got php to grab the numerical id value from the hidden textbox and process the page. thanks to eyedenify and xenon.