Update MySQL using HTML Form and PHP

Question

I've been looking through dozens of threads on here about this but have yet to find a solution.

I've created a form that is supposed to show database table contents in input boxes, and when the content of the input boxes are changed and submitted the database is supposed to update.

<html>
<head>
</head>
<body>

<?php

$dbhost = '****';
$dbuser = '****';
$dbpass = '****';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
mysql_select_db('****');

$query = "SELECT * FROM anstalld";
$result = mysql_query($query) or die(mysql_error());

?>


<form method="post" action="<?php $_PHP_SELF ?>">
<table width="400" border="0" cellspacing="1" cellpadding="2">
<tr>

<?php 

while($row = mysql_fetch_array($result))
            {

$namn = $row['namn'];
$mandag = $row['mandag'];
$tisdag = $row['tisdag'];
$onsdag = $row['onsdag'];
$torsdag = $row['torsdag'];
$fredag = $row['fredag'];
?>


<td width="100"></td>
<td><?=$namn?></td>
</tr>
<tr>
<td width="100">Mandag</td>
<td><input name="mandagid" type="text" value="<?=$mandag?>"></td>
</tr>
<tr>
<td width="100">Tisdag</td>
<td><input name="tisdagid" type="text" value="<?=$tisdag?>"></td>
</tr>
<tr>
<td width="100">Onsdag</td>
<td><input name="onsdagid" type="text" value="<?=$onsdag?>"></td>
</tr>
<tr>
<td width="100">Torsdag</td>
<td><input name="torsdagid" type="text" value="<?=$torsdag?>"></td>
</tr>
<tr>
<td width="100">Fredag</td>
<td><input name="fredagid" type="text" value="<?=$fredag?>"></td>
</tr>
<?php } ?>
<tr>
<td width="100"> </td>
<td> </td>
</tr>
<tr>
<td width="100"> </td>
<td>
<input name="update" type="submit" id="update" value="Update">
</td>
</tr>
</table>
</form>



<?php

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

$namn = $_POST['namnid'];
$mandag = $_POST['mandagid'];
$tisdag = $_POST['tisdagid'];
$onsdag = $_POST['onsdagid'];
$torsdag = $_POST['torsdagid'];
$fredag = $_POST['fredagid'];

$sql = mysql_query("UPDATE anstalld SET mandag = '$mandag', tisdag = '$tisdag', onsdag = '$onsdag', torsdag = '$torsdag', fredag = '$fredag' WHERE namn = '$namn'");

$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";

}


?>
</body>
</html>

The forms show the content of the database fine, but upon updating I get this message:

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 '1' at line 1

I appreciate any help I can get on this.

The mysql_* functions are no longer maintained and shouldn't be used in any new codebase. It is being phased out in favor of newer APIs. Instead you should use prepared statements with either PDO or MySQLi. — tereško
– tereško, Commented Sep 25, 2013 at 2:02
This can't work this way because you have multi forms with always the same names for input fields. Also you are missing to put namn in an at least hidden field. And you missed a lot of closing } — djot
– djot, Commented Sep 25, 2013 at 2:07
@djot Thanks for pointing the multi forms thing out! How would I go about pointing the UPDATE query to the input boxes without using static names? — user2052849
– user2052849, Commented Sep 25, 2013 at 2:48
@user2052849 You could use an array instead: e.g. name="fredagid[]". You then could loop through $_POST and will have several "fridays" (as many as rows hopefully). var_dump($_POST) then to see what I mean. Hope, your SQL will be as secure as the "anstalld" - currently it is vulnerable to SQL injection. — djot
– djot, Commented Sep 25, 2013 at 7:43

Shafeeque · Accepted Answer · 2013-09-25 03:54:52Z

2

Update query may have some issues

$query = "UPDATE anstalld SET mandag = '$mandag', tisdag = '$tisdag', onsdag = '$onsdag', torsdag = '$torsdag', fredag = '$fredag' WHERE namn = '$namn' ";
echo $query;

Please make sure that, your variable not having values with qoutes ( ' ), May be the query is breaking somewhere.

echo the query and try to execute in phpmyadmin itself. Then you can find the issues.

answered Sep 25, 2013 at 3:54

Shafeeque

2,0692 gold badges13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Konsole · Accepted Answer · 2013-09-25 03:56:37Z

1

You have already executed your query here

$sql = mysql_query("UPDATE anstalld SET mandag = '$mandag', tisdag = '$tisdag', onsdag = '$onsdag', torsdag = '$torsdag', fredag = '$fredag' WHERE namn = '$namn'");

So this line has the problem

$retval = mysql_query( $sql, $conn ); //$sql is not a query its a result set here

Try something like this:

$sql = "UPDATE anstalld SET mandag = '$mandag', tisdag = '$tisdag', onsdag = '$onsdag', torsdag = '$torsdag', fredag = '$fredag' WHERE namn = '$namn'";    
$retval = mysql_query( $sql, $conn ); //execute your query

As a sidenote: MySQL_* extension is deprecated use MySQLi_* or PDO instead.

answered Sep 25, 2013 at 3:56

Konsole

3,4753 gold badges31 silver badges41 bronze badges

2 Comments

user2052849 Over a year ago

Thank you! This fixed the "You have an error in your SQL syntax" msg, however, my DB is still not being updated with the newly added content.

Konsole Over a year ago

Your db is not being updated because you are generating your input box in a loop which will create many input elements with same name. You can try by changing the name of the input fields to an array like this <input name="mandagid[]" type="text" value="<?=$mandag?>">. You need to do this for every input element and process accordingly.

user3510431 · Accepted Answer · 2016-09-29 09:22:40Z

1

Your sql is incorrect.

$sql = mysql_query("UPDATE anstalld....

only

$sql = "UPDATE anstalld...

answered Sep 29, 2016 at 9:22

user3510431

558 bronze badges

Comments

hassan gul · Accepted Answer · 2014-09-20 08:34:48Z

0

you have error in your sql syntax.

please use this query and checkout.

$query = mysql_query("UPDATE `anstalld` SET mandag = '$mandag', tisdag = '$tisdag', onsdag = '$onsdag', torsdag = '$torsdag', fredag = '$fredag' WHERE namn = '$namn' ");

answered Sep 20, 2014 at 8:34

hassan gul

211 silver badge7 bronze badges

Comments

Nirav Patel · Accepted Answer · 2016-08-10 13:21:59Z

0

Try it this way. Put the table name in quotes (``). beside put variable '".$xyz."'.

So your sql query will be like:

$sql = mysql_query("UPDATE `anstalld` SET mandag = "'.$mandag.'" WHERE namn =".$name)or die(mysql_error());

edited Aug 10, 2016 at 13:21

answered Aug 10, 2016 at 13:10

Nirav Patel

135 bronze badges

3 Comments

Nirav Patel Over a year ago

it is. I'm working with this code only. Still how? can you point out the mistake so. I can improve

Naktibalda Over a year ago

You have a problem with quotes, the first string is "UPDATE anstalld SET mandag = " the next one is '.$mandag.', you would get a parse error because there is no . between them. If you fix all your mistakes, your code will become the same as one of 0 point answers below

Nirav Patel Over a year ago

can you write the entire query for me? Actually, I didn't get what you are saying. @Naktibalda

Neo · Accepted Answer · 2018-10-17 15:59:29Z

First of all use mysqli_connect($dbhost,$dbuser,$dbpass,$dbname)

Second - put mysqli_ everywhere instead of mysql_

Third - use this $sql = "UPDATE anstalld SET mandag = '.$mandag.', tisdag = '.$tisdag.', onsdag = '.$onsdag.', torsdag = '.$torsdag.', fredag = '.$fredag.' WHERE namn = '.$namn.'"; $retval = mysqli_query( $conn, $sql ); //execute your query

if Your data is being updated in your database but not in your table its because when you will click on update button, the request is made to the same file. It first selects the data from the database when it is not updated prints it in the table and then update it according to the flow. If you have to update it as you click on update button then put this section

<?php
if(isset($_POST['update']))
{

$namn = $_POST['namnid'];
$mandag = $_POST['mandagid'];
$tisdag = $_POST['tisdagid'];
$onsdag = $_POST['onsdagid'];
$torsdag = $_POST['torsdagid'];
$fredag = $_POST['fredagid'];

$sql = mysql_query("UPDATE anstalld SET mandag = '$mandag', tisdag = '$tisdag', onsdag = '$onsdag', torsdag = '$torsdag', fredag = '$fredag' WHERE namn = '$namn'");

$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";

}


?>`

after connecting with database.

Albay Commandante · Accepted Answer · 2022-03-14 21:42:29Z

-1

First example does not require the accumulative kind of ordering.

answered Mar 14, 2022 at 21:42

Albay Commandante

11 bronze badge

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Update MySQL using HTML Form and PHP

7 Answers 7

Comments

2 Comments

Comments

Comments

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

2 Comments

Comments

Comments

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related