trying to update records in mysql database (php)

Question

i've been browsing StackOverflow and other sites looking for other ways to do this and nothing I've tried seems to have worked.

The editscript.php seems to stop at echo "making connection<br>"; and the database does not update, and I can't make heads or tails of why.

I'd really apprecite some advice as to where I am going wrong.

Cheers!

<<>>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Edit Entry</title>
</head>

<body>

<?php

echo "<a href=\"index.php\">go home, you are drunk.</a>";

$id=$_GET["id"];

//db vars
$sqlhost='localhost';
$sqluser='sqluser';
$sqlpass='sqlpass';
$sqldb='riggingregister';

// Make a MySQL Connection
$con=mysqli_connect($sqlhost,$sqluser,$sqlpass,$sqldb);

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT * FROM register WHERE id='$id'");

echo "<form action=\"editscript.php\" method=\"post\">";
echo "<table width=\"372\" border=\"0\" align=\"center\">";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr><td>ID</td><td>" . $row['id'] . "</td></tr>";
  echo "<input name=\"id\" type=\"hidden\" value=\"" . $row['id'] ."\" />";
  echo "<tr><td>Register</td><td><input name=\"register\" type=\"text\" value=\"". $row['register'] ."\"/></td></tr>";
  echo "<tr><td>Location</td><td><input name=\"location\" type=\"text\" value=\"". $row['location'] ."\"/></td></tr>";
  echo "<tr><td>Type</td><td><input name=\"type\" type=\"text\" value=\"". $row['type'] ."\"/></td></tr>";
  echo "<tr><td>Capacity</td><td><input name=\"capacity\" type=\"text\" value=\"". $row['capacity'] ."\"/></td></tr>";
  echo "<tr><td>Length</td><td><input name=\"length\" type=\"text\" value=\"". $row['length'] ."\"/></td></tr>";
  echo "<tr><td>Qty</td><td><input name=\"qty\" type=\"text\" value=\"". $row['qty'] ."\"/></td></tr>";
  echo "<tr><td>Serial#</td><td><input name=\"serial\" type=\"text\" value=\"". $row['serial'] ."\"/></td></tr>";
  echo "<tr><td>Certificate#</td><td><input name=\"cert\" type=\"text\" value=\"". $row['cert'] ."\"/></td></tr>";
  echo "<tr><td>Last Inspection Completed On</td><td><input name=\"lastinsp\" type=\"text\" value=\"". $row['lastinsp'] ."\"/></td></tr>";
  echo "<tr><td>Last Inspection Completed By</td><td><input name=\"inspby\" type=\"text\" value=\"". $row['inspby'] ."\"/></td></tr>";
  echo "<tr><td>Date introduced into service</td><td><input name=\"datein\" type=\"text\" value=\"". $row['datein'] ."\"/></td></tr>";
  echo "<tr><td>Date removed from service</td><td><input name=\"dateout\" type=\"text\" value=\"". $row['dateout'] ."\"/></td></tr>";
  echo "<tr><td>Notes</td><td><input name=\"notes\" type=\"text\" value=\"". $row['notes'] ."\"/></td></tr>";
  }
echo "</table>";
echo "<input type=\"submit\" name=\"submit\" id=\"submit\" value=\"Confirm Changes\" />";
echo "</form>";

mysqli_close($con);
?> 

</body>
</html>

<<>>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Edit Entry (Script)</title>
</head>
<body>

<?php
echo "<a href=\"index.php\">Go Home!</a><br /><hr />";

//db vars
$sqlhost='localhost';
$sqluser='riggingregister';
$sqlpass='RIGGINGregister';
$sqldb='riggingregister';

//fetch form input
$id = $_POST['id'];
$register = $_POST['register'];
$location = $_POST['location'];
$type = $_POST['type'];
$capacity = $_POST['capacity'];
$length = $_POST['length'];
$qty = $_POST['qty'];
$serial = $_POST['serial'];
$cert = $_POST['cert'];
$lastinsp = $_POST['lastinsp'];
$inspby = $_POST['inspby'];
$datein = $_POST['datein'];
$dateout = $_POST['dateout'];
$notes = $_POST['notes'];

echo "$id<br /> $register<br /> $location<br /> $type<br /> $capacity<br /> $length<br /> $qty<br /> $serial<br /> $lastinsp<br /> $inspby<br /> $datein<br /> $dateout<br /> $notes<br /><br />";

// Make a MySQL Connection
echo "making connection<br>";
$con = mysqli_connect($sqlhost,$sqluser,$sqlpass,$sqldb) or die(mysql_error());
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

//perform update query
$stmt = mysqli_prepare($con,
                       "UPDATE register SET 
                        register=?, 
                        location=?, 
                        type=?, 
                        capacity=?,
                        length=?,
                        qty=?,
                        serial=?,
                        cert=?,
                        lastinsp=?,
                        inspby=?,
                        datein=?,
                        dateout=?,
                        notes=?
                        WHERE id=?") or die(mysqli_error($con));
mysqli_stmt_bind_param($stmt, 'sssiiisssssssi',
                       $register, $location, $type, $capacity, $length, $qty, $serial, $cert, $lastinsp, $inspby, $datein, $dateout, $notes, $id);
mysqli_stmt_execute($stmt);
//echo "update successful! YAY!<br />";
echo "<a href=\"index.php\">Home</a>";

//close connection to db
mysqli_close();

?> 


</body>
</html>

The argument to `mysql_error()` is the database connection, not a string. — Barmar, Mar 15 '14 at 03:01
Barmar, I changed as per your recomendation, but it just echos `Can't update the records:` after the 'making connections' — ZachFlem, Mar 15 '14 at 03:07
`$id = $_POST[id];` to `$id = $_POST['id'];` and do the same for the others, now that you've changed `mysql_error()` to `mysqli_error()` @ZachFlem — Funk Forty Niner, Mar 15 '14 at 03:13
This line `$con = mysqli_connect($sqlhost,$sqluser,$sqlpass,$sqldb) or die(mysql_error());` needs to be `$con = mysqli_connect($sqlhost,$sqluser,$sqlpass,$sqldb) or die(mysqli_error());` @ZachFlem that came from Barmar where he didn't change it yet as I [`commented on that.`](http://stackoverflow.com/questions/22418988/trying-to-update-records-in-mysql-database-php#comment34090192_22419062) it's still `or die(mysql_error());` where it needs to be `or die(mysqli_error());` — Funk Forty Niner, Mar 15 '14 at 03:26

Barmar · Accepted Answer · 2014-03-15T03:27:42.510

1

You're mixing mysqli and mysql functions, you can't do that. You're also not passing the connection object to the mysqli functions. You should use parametrized queries instead of variable substitution.

$con = mysqli_connect($sqlhost,$sqluser,$sqlpass,$sqldb) or die("Failed to connect to MySQL: " . mysqli_connect_error());

//perform update query
$stmt = mysqli_prepare($con,
                       "UPDATE register SET 
                        register=?, 
                        location=?, 
                        type=?, 
                        capacity=?,
                        length=?,
                        qty=?,
                        serial=?,
                        cert=?,
                        lastinsp=?,
                        inspby=?,
                        datein=?,
                        dateout=?,
                        notes=?
                        WHERE id=?") or die(mysqli_error($con));
mysqli_stmt_bind_param($stmt, 'sssiiisssssssi',
                       $register, $location, $type, $capacity, $length, $qty, $serial, $cert, $lastinsp, $inspby, $datein, $dateout, $notes, $id);
mysqli_stmt_execute($stmt);
//echo "update successful! YAY!<br />";
echo "<a href=\"index.php\">Home</a>";

//close connection to db
mysqli_close($con);

edited Mar 15 '14 at 03:27

answered Mar 15 '14 at 03:10

Barmar

741,623
53
500
612

changed as per suggestions, still same result as before – ZachFlem Mar 15 '14 at 03:14
Sorry, I left `$con` out of `mysqli_prepare()`, try it now. – Barmar Mar 15 '14 at 03:16
Make sure you have error reporting enabled -- that error should have caused a PHP error to be printed. – Barmar Mar 15 '14 at 03:16
@Barmar Your answer still contains `or die(mysql_error());` - 1st line. – Funk Forty Niner Mar 15 '14 at 03:20
this one seems to have fixed things, thanks folks. I have some reading to do to work out exactly what is happening now though! – ZachFlem Mar 15 '14 at 03:31
This answer by the way @ZachFlem is based on using [`prepared statements`](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`here is a good tutorial`](http://www.tutorialized.com/tutorial/PHP-5:-MySQLi-Prepared-Statements/41452) on the subject, should you want to read up on them. – Funk Forty Niner Mar 15 '14 at 03:41
You're welcome. See my edited comment; I added a link to a tutorial on prepared statements. @ZachFlem – Funk Forty Niner Mar 15 '14 at 03:43

trying to update records in mysql database (php)

1 Answers1