Updating a mysqli table with mysqli_fetch_assoc and html form

[chrisdee]

Hi,

I'm trying to make a php script as dynamic as possible that will updates my mysql table from a html form.
I want the script to get the actual column names from the mysql table and use these along with the values from the html form
to update the table. Below is what I got so far.

<?php

$link   = mysqli_connect('localhost','user','pass','table');
$query  = "SELECT * FROM utlaan WHERE id=".$_GET["id"]."";
$result = mysqli_query($link, $query);
$fetch  = mysqli_fetch_assoc($result);


if(empty($_POST)) {
 print "<table border='0'><form action='" . $_SERVER["PHP_SELF"] . "' method='post'";

 foreach($fetch as $key => $value) {
  print "<tr><td>" . $key . "</td><td><input type='text' name='" . $value . "' value='" . $value . "'></td></tr>";
 }

 print "<tr><td colspan='2'><input type='submit' value='Reserver'></td></tr></form></table>";
}

else {
 mysqli_query("UPDATE `pdb`.`utlaan` SET `$key` = '$value' WHERE `utlaan`.`id` = $_GET[id];");
 print mysqli_affected_rows($link);
}


mysqli_free_result($result);
mysqli_close($link);

?>

The first part (if { }) where the script gets the column names (key) and values from the table and fills it into the form works fine.
The second part (else { }) is where it fails.

I get 3 warnings :

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in /var/www/utlaan/endre.php on line 7

Warning: mysqli_query() expects at least 2 parameters, 1 given in /var/www/utlaan/endre.php on line 21
-1

Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /var/www/utlaan/endre.php on line 26

I wonder how I can get my script to work and update the table. Any help is greatly apreciated.

[Weedpacket]

As the first error message says, you're not passing a result set to [man]mysqli_fetch_assoc[/man], you're passing a boolean; no doubt [font=monospace]FALSE[/font] indicating that the query you tried to use to get the result set in the first place failed. Finding out why that failed should be your next step ([man]mysqli_error[/man] would be useful when doing this).

And please, in the real world, don't stick user-supplied data like [font=monospace]$_GET['id'][/font] straight into a query. You don't know where it's been.

The second error message is even more self-explanatory than the first; [man]mysqli_query[/man] needs two arguments: a database connection and a string. You're only passing it the string.

The third error message has the same cause as the first.

[chrisdee]

I can't get the form variables values (below) inserteted into the table. If I comment out the if statement
with the form and manually insert values in the update statement it works fine. Any ideas why the values from the input form
is not inserted into the table?

 <?php
$link   = mysqli_connect("localhost", "username", "passwd", "database");
$id     = $_GET['id'];
$result = mysqli_query($link, "SELECT * from laptop WHERE id='".$id."'");

if(!isset($_POST['submit'])) {
        print "<form method='post' action='".$_SERVER["PHP_SELF"]."'>";
        while($row = mysqli_fetch_assoc($result)) {
                foreach($row as $key => $val) {
                        print "".$key.": <input type='text' name='".$key."' value='".$val."'><br>";
                }
        }
        print "<br><input type='submit' name='submit' value='Reserver'>";
        print "</form>";
}
else {
        $navn   = $_POST['navn'];
        $fra    = $_POST['fra'];
        $til    = $_POST['til'];
        mysqli_query($link, "UPDATE laptop SET navn=$navn, fra='$fra', til='$til' WHERE id='".$id."'");
        print mysqli_affected_rows($link) . " rows updated!";
}
?> 

mysqli_affected_rows($link)

gives me a

-1 rows updated!

message

[chrisdee]

I got some help on another forum and the script is now working.

The only thing I'm having 'problems' with now is that the fields fra and til are set to date in phpmyadmin (and NULL).
They are always set to 0000-00-00 if no date is set or whatever date I specify.

Is there any way for them to be blank if no date is specified?

Here is the script :

 <?php
$link   = mysqli_connect("localhost", "user", "pass", "db");
$id     = $_GET['id'];
$result = mysqli_query($link, "SELECT * from laptop WHERE id='".$id."'");

if(!isset($_POST['submit'])) {
        print "<form method='post' action='".$_SERVER["PHP_SELF"]."'>";
        print "<input type='hidden' name='id' value='$id'>";
        while($row = mysqli_fetch_assoc($result)) {
                foreach($row as $key => $val) {
                        print "$key: <input type='text' name='$key' value='$val'><br>";
                }
        }
        print "<br><input type='submit' name='submit' value='Reserver'>";
        print "</form>";
}
else {
        $id     = $_POST['id'];
        $navn   = $_POST['navn'];
        $fra    = $_POST['fra'];
        $til    = $_POST['til'];
        mysqli_query($link, "UPDATE laptop SET navn='$navn', fra='$fra', til='$til' WHERE id='$id'");
        print mysqli_affected_rows($link) . " rows updated!";
}
?> 

[Derokorian]

You'll have to examine the input data (PS. please sanitize data before inserting into a query like that! very dangerous) and change you're query to either fra='$fra' if a value is given or fra=NULL if no value is given.

[Weedpacket]

And because you're not doing any sanitisation or input validation, and instead just putting user-supplied data straight into the SQL statement, you will need to enter dates in exactly the right format that MySQL expects dates to be written in.

[chrisdee]

Thank you so much for your replies.
So if I understand correctly sanitation my inputs would mean escaping my variables against special characters with mysqli_real_escape_string like I do below?

<?php
$link   = mysqli_connect("localhost", "user", "pass", "db");
$id     = mysqli_real_escape_string($link, $_GET['id']);
$result = mysqli_query($link, "SELECT * from laptop WHERE id='".$id."'");

if(!isset($_POST['submit'])) {
        print "<form method='post' action='".$_SERVER["PHP_SELF"]."'>";
        print "<input type='hidden' name='id' value='$id'>";
        while($row = mysqli_fetch_assoc($result)) {
                foreach($row as $key => $val) {
                        if($key=='utstyr') {
                                print "$key: $val<br>";
                        }
                        elseif($key=='id') {
                        }
                        else {
                                print "$key: <input type='text' name='$key' value='$val'><br>";
                        }
                }
        }
        print "<br><input type='submit' name='submit' value='Reserver'>";
        print "</form>";
}
else {
        $id     = mysqli_real_escape_string($link, $_POST['id']);
        $navn   = mysqli_real_escape_string($link, $_POST['navn']);
        $fra    = mysqli_real_escape_string($link, $_POST['fra']);
        $til    = mysqli_real_escape_string($link, $_POST['til']);
        mysqli_query($link, "UPDATE laptop SET navn='$navn', fra='$fra', til='$til' WHERE id='$id'");
        print mysqli_affected_rows($link) . " rows updated!";
}
?>

[laserlight]

chrisdee wrote:

So if I understand correctly sanitation my inputs would mean escaping my variables against special characters with mysqli_real_escape_string like I do below?

mysqli_real_escape_string is appropriate for strings but for other types you should cast appropriately. Furthermore, you should check that those incoming variables exist before using them, e.g.,

if (isset($_POST['id'], $_POST['navn'], $_POST['fra'], $_POST['til'])) {
    $sql = sprintf(
        "UPDATE laptop SET navn='%s', fra='%s', til='%s' WHERE id=%d",
        mysqli_real_escape_string($link, $_POST['navn']),
        mysqli_real_escape_string($link, $_POST['fra']),
        mysqli_real_escape_string($link, $_POST['til']),
        $_POST['id']
    );
    mysqli_query($link, $sql);
    // ...
}

Actually, better than sprintf + mysqli_real_escape_string would be the use of a prepared statement with parameter bindings:

$stmt = $link->prepare("UPDATE laptop SET navn=?, fra=?, til=? WHERE id=?");
$stmt->bind_param('sssi', $_POST['navn'], $_POST['fra'], $_POST['til'], $_POST['id']);
$stmt->execute();

(I switched to the "object oriented" interface as I feel that the syntactic sugar makes it more pleasant.)

[chrisdee]

Wow, thank you! The prepared statement worked great when I have set from and to to varchar.
These two fields are dates and someone on another forum suggested I should store these (in mysqli) as dates instead of varchar.
But when I set the from and to fields as dates in phpmyadmin the script below gives me a error message :

Fatal error: Call to a member function bind_param() on a non-object in /var/www/utlaan/endre.php on line 26
Any idea what I'm doing wrong?

<?php print "<a href='./'>Tilbake</a></p>";
$link   = mysqli_connect("localhost", "user", "pass", "db");
$id     = mysqli_real_escape_string($link, $_GET['id']);
$result = mysqli_query($link, "SELECT * from laptop WHERE id='".$id."'");

if(!isset($_POST['submit'])) {
        print "<form method='post' action='".$_SERVER["PHP_SELF"]."'>";
        print "<input type='hidden' name='id' value='$id'>";
        while($row = mysqli_fetch_assoc($result)) {
                foreach($row as $key => $val) {
                        if($key=='equipment') {
                                print "$key: $val<br>";
                        }
                        elseif($key=='id') {
                        }
                        else {
                                print "$key: <input type='text' name='$key' value='$val'><br>";
                        }
                }
        }
        print "<br><input type='submit' name='submit' value='Reserver'>";
        print "</form>";
}
else {
        $stmt = $link->prepare("UPDATE laptop SET name=?, from=?, to=? WHERE id=?");
        $stmt->bind_param('sssi', $_POST['name'], $_POST['from'], $_POST['to'], $_POST['id']);
        $stmt->execute();
        print mysqli_affected_rows($link) . " rows updated!";
}
?>

[chrisdee]

Hmm. For some reason this works :

<?php print "<a href=.>Back</a></p>";
$link   = mysqli_connect("localhost", "user", "pass", "db");
$id     = mysqli_real_escape_string($link, $_GET['id']);
$result = mysqli_query($link, "SELECT * from laptop WHERE id='".$id."'");

if(!isset($_POST['submit'])) {
        print "<form method='post' action='".$_SERVER["PHP_SELF"]."'>";
        print "<input type='hidden' name='id' value='$id'>";
        while($row = mysqli_fetch_assoc($result)) {
                foreach($row as $key => $val) {
                        if($key=='equipment') {
                                print "$key: $val<br>";
                        }
                        elseif($key=='id') {
                        }
                        else {
                                print "$key: <input type='text' name='$key' value='$val'><br>";
                        }
                }
        }
        print "<br><input type='submit' name='submit' value='Reserve'><input type='button' onclick='history.go(-1)' value='Cancel'>";
        print "</form>";
}
else {
        $stmt = $link->prepare("UPDATE laptop SET name=? WHERE id=?");
        $stmt->bind_param('si', $_POST['name'], $_POST['id']);
        $stmt->execute();
        print mysqli_affected_rows($link) . " rows updated!";
}
?>

But adding the date fields from and to (weither I use date or varchar) I get the same error message:
Fatal error: Call to a member function bind_param() on a non-object in /var/www/utlaan/endre.php on line 26

<?php print "<a href=.>Back</a></p>";
$link   = mysqli_connect("localhost", "user", "pass", "db");
$id     = mysqli_real_escape_string($link, $_GET['id']);
$result = mysqli_query($link, "SELECT * from laptop WHERE id='".$id."'");

if(!isset($_POST['submit'])) {
        print "<form method='post' action='".$_SERVER["PHP_SELF"]."'>";
        print "<input type='hidden' name='id' value='$id'>";
        while($row = mysqli_fetch_assoc($result)) {
                foreach($row as $key => $val) {
                        if($key=='equipment') {
                                print "$key: $val<br>";
                        }
                        elseif($key=='id') {
                        }
                        else {
                                print "$key: <input type='text' name='$key' value='$val'><br>";
                        }
                }
        }
        print "<br><input type='submit' name='submit' value='Reserve'><input type='button' onclick='history.go(-1)' value='Cancel'>";
        print "</form>";
}
else {
        $stmt = $link->prepare("UPDATE laptop SET name=?, from=?, to=? WHERE id=?");
        $stmt->bind_param('sssi', $_POST['name'], $_POST['from'], $_POST['to'], $_POST['id']);
        $stmt->execute();
        print mysqli_affected_rows($link) . " rows updated!";
}
?>

Don't understand what I'm doing wrong here. Any suggestions are greatly apreciated.

[laserlight]

That means that your $link->prepare call failed for some reason. Check the error messages to find out more.

[chrisdee]

I think there must be some issue with the the from and to fields.
The other fields (name and equipment) gets updated if I remove the from and to fields from the prepare and bind_param statements.

I have googled for several days but can't seem to find the answer I'm looking for. There are some suggestions (from Stackoverflow) that I need to check the
update privileges for the database/table. However I don't think this is my issue since it works without the from and to fields.

Another is to check that I have a updated php version installed. But I don't think it's got anything to do with that either. Because again it works without the from and to fields.

I'm kind of lost here.

[laserlight]

As a quick debugging tool you could write:

$stmt = $link->prepare("UPDATE laptop SET name=?, from=?, to=? WHERE id=?") or die($link->error);

[chrisdee]

Thank you. I will try that.

[chrisdee]

For some odd reason it now works even thow I get a -1 rows updated! message from mysqli_affected_rows($link) :
Any idea why?

 <?php print "<a href=.>Back</a></p>";
$link   = mysqli_connect("localhost", "user", "pass", "db");
$id     = mysqli_real_escape_string($link, $_GET['id']);
$result = mysqli_query($link, "SELECT * from laptop WHERE id='".$id."'");

if(!isset($_POST['submit'])) {
        print "<form method='post' action='".$_SERVER["PHP_SELF"]."'>";
        print "<input type='hidden' name='id' value='$id'>";
        while($row = mysqli_fetch_assoc($result)) {
                foreach($row as $key => $val) {
                        if($key=='equipment') {
                                print "$key: $val<br>";
                        }
                        elseif($key=='id') {
                        }
                        elseif($key=='from' or $key=='to') {
                                print "$key: <input type='date' name='$key' value='$val'><br>";
                        }
                        else {
                                print "$key: <input type='text' name='$key' value='$val'><br>";
                        }
                }
        }
        print "<br><input type='submit' name='submit' value='Reserve'><input type='button' onclick='history.go(-1)' value='Cancel'>";
        print "</form>";
}
else {
        $stmt = $link->prepare("UPDATE laptop SET `name`=?, `from`=?, `to`=? WHERE `id`=?") or die($link->error);
        $stmt->bind_param('sssi', $_POST['name'], $_POST['from'], $_POST['to'], $_POST['id']);
        $stmt->execute();
        $stmt->close();
        print mysqli_affected_rows($link) . " rows updated!";
}
?> 

[chrisdee]

I have another question but it's related. In my mysql table I have defined the fields from and to as date.
Is there a quick and easy way to get up a calendar for these fields in php?

I see the date fields automatically gets a selectable dropdown calendar menu in phpmysql.
It would be great if there was some easy way also get this in php?

[Weedpacket]

This is another question; it should be in another thread (in the Clientside Technologies forum, since that is what the question is about).

[chrisdee]

Ok, thank you. How do I move it?

What about the previous question? I wonder why the table gets updated when I get a a -1 rows updated! message from mysqli_affected_rows($link)?
Any ideas?

[chrisdee]

Nevermind, I found the solution.

Had to move $stmt->close(); to the end after print mysqli_affected_rows($link) . " rows updated!";

Now it works as intended :

 <?php print "<a href=.>Tilbake</a></p>";
$link   = mysqli_connect("localhost", "user", "pass", "db");
$id     = mysqli_real_escape_string($link, $_GET['id']);
$result = mysqli_query($link, "SELECT * from laptop WHERE id='".$id."'");

if(!isset($_POST['submit'])) {
        print "<form method='post' action='".$_SERVER["PHP_SELF"]."'>";
        print "<input type='hidden' name='id' value='$id'>";
        while($row = mysqli_fetch_assoc($result)) {
                foreach($row as $key => $val) {
                        if($key=='equipment') {
                                print "$key: $val<br>";
                        }
                        elseif($key=='id') {
                        }
                        elseif($key=='from' or $key=='to') {
                                print "$key: <input type='date' name='$key' value='$val'><br>";
                        }
                        else {
                                print "$key: <input type='text' name='$key' value='$val'><br>";
                        }
                }
        }
        print "<br><input type='submit' name='submit' value='Reserver'><input type='button' onclick='history.go(-1)' value='Avbryt'>";
        print "</form>";
}
else {
        $stmt = $link->prepare("UPDATE laptop SET `name`=?, `from`=?, `to`=? WHERE `id`=?") or die($link->error);
        $stmt->bind_param('sssi', $_POST['name'], $_POST['from'], $_POST['to'], $_POST['id']);
        $stmt->execute();
        print mysqli_affected_rows($link) . " rows updated!";
        $stmt->close();
}
?>

[chrisdee]

Thanks for all the help!