CODEWITHSUNDEEP
phphtmlmysqlformssql-update
user2052849
user2052849

Reputation: 99

Update MySQL using HTML Form and PHP

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.

Upvotes: 6

Views: 104838

Answers (8)

Albay Commandante
Albay Commandante

Reputation: 1

First example does not require the accumulative kind of ordering.

Upvotes: -1

Neo
Neo

Reputation: 13

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.

Upvotes: 0

user3510431
user3510431

Reputation: 55

Your sql is incorrect.

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

only

$sql = "UPDATE anstalld...

Upvotes: 1

Nirav Patel
Nirav Patel

Reputation: 13

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());

Upvotes: 0

hassan gul
hassan gul

Reputation: 21

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

Upvotes: 0

Konsole
Konsole

Reputation: 3475

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.

Upvotes: 1

Shafeeque
Shafeeque

Reputation: 2069

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.

Upvotes: 2

Undefined Behavior
Undefined Behavior

Reputation: 2208

Use mysqli instead of mysql, and you need to pass the database name or schema:

before:

$conn = mysql_connect($dbhost, $dbuser, $dbpass);

after:

$conn = mysql_connect($dbhost, $dbuser, $dbpass, $myDBname);

Upvotes: 5

Related Questions