TalkPHP - mysql update statement

TalkPHP (http://www.talkphp.com/forums.php)

- Absolute Beginners (http://www.talkphp.com/absolute-beginners/)

- - mysql update statement (http://www.talkphp.com/absolute-beginners/2802-mysql-update-statement.html)

mysql update statement

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>

What is the error?

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

Quote:

Originally Posted by sarmenhb (Post 14601)

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.

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

Quote:

Originally Posted by Village Idiot (Post 14648)

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";

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

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.

Quote:

Originally Posted by Jim (Post 14665)

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?

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 :)

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.

Quote:

Originally Posted by EyeDentify (Post 14671)

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.

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

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.