UPDATE for MySQL through PHP not working

Question

I have this chunk of code running PHP on my webpage. I must have one small thing wrong, because when I hit the submit button on the form on this page it doesn't do anything! It has been driving me bonkers for hours.

Here is the form:

<form method="POST">
<strong><br>
</strong><p><input name="creaturein" type="hidden" value="Goblar"><br>
</p><table style="border: 1px;">
                <tbody><tr>
<td></td>
<td>Creature</td>
<td>Stage</td>
<td>Gender</td>
<td>Frozen</td>
</tr>
<tr>
<td rowspan="2"><img src="http://static.eggcave.com/90by90/goblar_2.png"></td>
<td>Goblar</td>
<td><select name="stagein"><option selected="" disabled="">Unspecified</option><option value="Unspecified">Unspecified</option><option value="Stage1">Stage 1(Egg)</option><option value="Stage2">Stage 2</option><option value="Stage3">Stage 3</option><option value="Stage4">Stage 4</option></select></td>
<td><select name="genderin"><option selected="" disabled="">Unspecified</option><option value="Unspecified" selected="">Unspecified</option><option value="Female">Female</option><option value="Male">Male</option></select></td>
<td><select name="frozenin"><option selected="" disabled="">Unspecified</option><option value="Unspecified">Unspecified</option><option value="Yes">Yes</option><option value="No">No</option></select></td>
</tr><tr>
<td colspan="2">Notes: <input name="notesin" type="text" value=""></td>
<td><input name="update" type="submit" id="update" value="Update"></td>
<td><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
</tbody></table>
</form>

And here is the code that should be updating the table:

 // Info to connect to the Wishlist database
        $servername = "****";
        $dbusername = "****";
        $password = "****";
        $dbname1 = "****";
        $dbname2 = "****";

        // To connect to the database please
        $conn = mysqli_connect($servername, $dbusername, $password, $dbname1);

        // If unable to connect to the database display this error
        if ($conn->connect_error) {
            echo "Connection to wishlist failed";
            die("Connection failed: " . $conn->connect_error);
        }

        // Get current user's username
        $current_user = wp_get_current_user();
        $username = $current_user->user_login;

if(isset($_POST['update'])){
    $stage = $_POST['stagein'];
    $gender = $_POST['genderin'];
    $frozen = $_POST['frozenin'];
    $notes = $_POST['notesin'];
    $creature = $_POST['creaturein'];

    $sql2 = 'UPDATE $username SET Stage = "$stage" AND Gender = "$gender" AND Frozen= "$frozen"' .
        ' AND Notes = "$notes" WHERE Creature = "$creature"';
    if ($conn->query($sql2) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }

    // To connect to the database please
    $conn2 = new mysqli($servername, $dbusername, $password, $dbname2);

    // If unable to connect to the database display this error
    if ($conn2->connect_error) {
        echo "Connection to Creatures failed";
        die("Connection failed: " . $conn2->connect_error);
    }

    $sql3 = "SELECT Stage$stage FROM Creatures WHERE Name = '$creature'";
    if ($conn2->query($sql3) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn2->error;
    }

    $sql4 = "UPDATE $username SET Picture='$retval' WHERE Creature = '$creature'";
    if ($conn->query($sql4) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }

    $conn2->close();
}

And the delete button doesn't work either.

if(isset($_POST['delete'])){
    $stage = $_POST['stagein'];
    $gender = $_POST['genderin'];
    $frozen = $_POST['frozenin'];
    $notes = $_POST['notesin'];
    $creature = $_POST['creaturein'];

    $sql5 = "DELETE FROM $username WHERE Creature = '$creature' AND Stage = '$stage' AND " .
        "Gender = '$gender' AND Frozen = '$frozen' AND Notes = '$notes'";
    if ($conn->query($sql5) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }
}

// Close the connection to the database
$conn->close();

I'm not getting any error messages. I'm just so lost. SOS!

---------------------------------------------------------------------------

I have this! This is working for the most part

if(isset($_POST['update'])){
    // prepare and bind
    $stmt = $conn->prepare("UPDATE " . $username. " SET Stage = ?, Gender = ?, Frozen = ?, Notes = ? WHERE Creature = ?");
    $stmt->bind_param('sssss', $stagebind, $genderbind, $frozenbind, $notesbind, $creaturebind);

    // set parameters and execute
    $stagebind = $_POST['stagein'];
    $genderbind = $_POST['genderin'];
    $frozenbind = $_POST['frozenin'];
    $notesbind = $_POST['notesin'];
    $creaturebind = $_POST['creaturein'];
    $stmt->execute();

    $stmt->close();
    exit();
    // To connect to the database please
    $conn2 = mysqli_connect($servername, $dbusername, $password, $dbname2);

    // If unable to connect to the database display this error
    if ($conn2->connect_error) {
        echo "Connection to Creatures failed";
        die("Connection failed: " . $conn2->connect_error);
    }

    // prepare and bind
    $stmt2 = $conn2->prepare("SELECT ? FROM Creatures WHERE Name = ?");
    $stmt2->bind_param('ss', $stagebind, $creaturebind);

    // set parameters and execute
    $creaturebind = $_POST['creaturein'];

    $stmt2->bind_result($picture);
    $stmt2->fetch();

Until about here. It isn't saving the $picture information in my wishlist database.

    // prepare and bind
    $stmt3 = $conn->prepare("UPDATE " . $username . " SET Picture = ? WHERE Creature = ?");
    $stmt3->bind_param('ss', $picture, $creaturebind);

    // set parameters and execute
    $creaturebind = $_POST['creaturein'];

    $stmt3->execute();
    $stmt3->close();
    $stmt2->close();
    $conn2->close();
}

that could become very unmanageable and might run into issues if more than one user has the same username( probably not allowed ) — Professor Abronsius
– Professor Abronsius, Commented Jul 24, 2016 at 5:52
no user will have the same username. It's a wishlist feature and idk how else to change it. If you have ideas let me know. Always open to good ideas. It's on a wordpress site... ugg. The site owner started it up there and doesn't want to move. So if you're a plugin writing sort of person feel free haha — LucyTurtle
– LucyTurtle, Commented Jul 24, 2016 at 5:55

msantos · Accepted Answer · 2016-07-25 02:16:23Z

2

You forgot to add <form method="POST">.By default, when the method is not added, the html consider form method='GET'.

UPDATE: You forgot to add the first parameter which is to used to inform the types of the fields.See below:

$stmt->bind_param('sssss',$stagebind, $genderbind, $frozenbind, $notesbind, $creaturebind);

UPDATE 2: Remove the exit(). Another adjustment, the question mark to retrieve colums wont work, use only the question mark in the where condition. See below:

// prepare and bind
    $stmt2 = $conn2->prepare("SELECT $stagebind FROM Creatures WHERE Name = ?");
    $stmt2->bind_param('s',$creaturebind);

edited Jul 25, 2016 at 2:16

answered Jul 24, 2016 at 4:33

msantos

8016 silver badges7 bronze badges

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

8 Comments

LucyTurtle Over a year ago

I KNEW it was going to be something simple and stupid. Thank you a ton!

msantos Over a year ago

Don't worry. I'm here to help.

LucyTurtle Over a year ago

That seemed to be making things BETTER for sure. But Now it tells me that it has been successfully updated, but hasn't actually updated the database

msantos Over a year ago

Can you echo the sql update to check what is happening?

LucyTurtle Over a year ago

UPDATE $username SET Stage = "$stage" AND Gender = "$gender" AND Frozen= "$frozen" AND Notes = "$notes" WHERE Creature = "$creature" Why isn't it changing the variables to their values?

|

Indrasis Datta · Accepted Answer · 2016-07-24 04:35:20Z

2

Please see that your HTML form code doesn't state the form action or the method.

Try this:

<form action ="" method="POST">

If you don't mention this method, it's assumed that the method is GET, and therefore, your isset($_POST[...]) code isn't executed.

answered Jul 24, 2016 at 4:35

Indrasis Datta

8,6182 gold badges18 silver badges32 bronze badges

5 Comments

LucyTurtle Over a year ago

I KNEW it was going to be something simple and stupid. Thank you a ton!

Indrasis Datta Over a year ago

You're welcome, mate. Please accept my answer if it solves your purpose. Thanks! :)

LucyTurtle Over a year ago

That seemed to be making things BETTER for sure. But Now it tells me that it has been successfully updated, but hasn't actually updated the database.

Afshan Shujat Over a year ago

You need to check updated rows count, if it is returning 0 it means update query is executing successfully but where condition is not matching with any row. You can print your query and can execute directly on table (mysql) for getting the clear view.

LucyTurtle Over a year ago

When I echo the UPDATE statement it says this: UPDATE $username SET Stage = "$stage" AND Gender = "$gender" AND Frozen= "$frozen" AND Notes = "$notes" WHERE Creature = "$creature" Why isn't it changing the variables to their values?

Fabiano · Accepted Answer · 2016-07-24 17:35:07Z

1

How isn't anyone flipping about those variables being used directly into a query? Seriously, please use prepared statements otherwise SQL injection will most likely to occur.

answered Jul 24, 2016 at 17:35

Fabiano

9326 silver badges19 bronze badges

4 Comments

LucyTurtle Over a year ago

I don't 100% understand how to do this/what this means. Im new to SQL

LucyTurtle Over a year ago

Warning: mysqli_stmt::bind_param(): Invalid type or no types specified in /home/karenna19/public_html/wp-content/plugins/insert-php/insert_php.php(48) : eval()'d code on line 73 Fatal error: Call to a member function bind_param() on a non-object in /home/karenna19/public_html/wp-content/plugins/insert-php/insert_php.php(48) : eval()'d code on line 96

Fabiano Over a year ago

There are tons of tutorials out there teaching how to write secure queries and binding parameters. The PHP page itself shows a decent amount of basic examples that will most likely fulfill your needs.

LucyTurtle Over a year ago

I've updated my code with what I have written. I still seem to be getting some errors (in comments for my post)

Collectives™ on Stack Overflow

UPDATE for MySQL through PHP not working

---------------------------------------------------------------------------

---------------------------------------------------------------------------

3 Answers 3

8 Comments

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

---------------------------------------------------------------------------

---------------------------------------------------------------------------

3 Answers 3

8 Comments

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related