Cant perform an SQL update when using php varibles

Question

I just noticed that i can not perform SQL updates when i am using PHP varibles from the link My code (I don't noticed any errors, and no error output)

<?php

if ($_POST && isset($_POST['hdduid'], $_POST['status'])) {
    $dbhost = 'localhost';
    $dbuser = 'root';
    $dbpass = 'L24wmc1nJBVP90q9yY';
    $dbname = 'watt';

    try {
        // Try to connect
        $dbh = new PDO(
            'mysql:host='.$dbhost.';dbname='.$dbname,
            $dbuser,
            $dbpass
        );

        // Data
        $hdduid = $_POST['hdduid'];
        $status = $_POST['status'];

        // query
        $sql = "UPDATE users SET paid=':status' WHERE hdduid=':hdduid'";
        $q = $dbh->prepare($sql);
        $q->execute(array(
            ':message' => $message,
            ':email' => $email
        ));

        // Null connection
        $dbh = null;
    } catch (PDOException $e) { // if exception
        print "Error!: " . $e->getMessage() . "<br/>";
        die();
    }

?>

I edited the code, it still wont working

Inside the condition where you check `if ($conn->query($sql) === TRUE) {`, what is `echo $conn->affected_rows;`? Also, you should be using a prepared statement. — Qirel, Apr 14 '19 at 15:30
You're also mixing APIs here. `mysql_real_escape_string()` doesn't work with `mysqli`. — Qirel, Apr 14 '19 at 15:30
Possible duplicate of [Can I mix MySQL APIs in PHP?](https://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php) — Qirel, Apr 14 '19 at 15:30
@Johannes It checks for errors if the query fails, `echo "Error updating record: " . $conn->error;` (although you shouldn't display those out to the end user...). — Qirel, Apr 14 '19 at 15:31
Shouldn't be - $hhduid = $_GET["hdduid"] and $status = $_GET["status"] — Khilesh Chauhan, Apr 14 '19 at 15:32
Do you have PHP error reporting on, and you are checking error logs? `mysql_real_escape_string` with PHP 7+ will cause fatal error because the function is undefined. — user3783243, Apr 14 '19 at 15:32
You do realize that your `if` statement is not closed. You are missing last `}` — Dharman, Apr 14 '19 at 16:37
What are the contents of `$message` and `$email`? Where do you declare these variables? — Dharman, Apr 14 '19 at 16:41
Placeholders should not be quoted. If they are they become a string, not a placeholder, and won't be bound. — user3783243, Apr 15 '19 at 02:27

score 0 · Answer 1 · answered Apr 14 '19 at 15:45

0

You need to use mysqli_real_escape_string Not mysql_real_escape_string You can not mix mysql with MySQLi

answered Apr 14 '19 at 15:45

Adam Hull

214
1
8

You also can't mix `pdo` with `mysqli`. – user3783243 Apr 15 '19 at 02:26
Original code posted by op was MySQLi and has edited the code to use pdo – Adam Hull Apr 15 '19 at 14:10

yuko · Answer 2 · 2019-04-14T16:56:46.767

Here is a solution. It uses mysqli_real_escape_string instead of mysql_real_escape_string. I also changed the name of $status to $paid for better readability. Good luck!

$servername = "localhost";
$username = "root";
$password = ""; //$password = "L24wmc1nJBVP90q9yY";
$dbname = "test";   //$dbname = "ft";

// Create connection
$connection = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}

$hdduid = $_GET["hdduid"];
$paid = $_GET["status"];

$sql = "UPDATE users SET paid='$paid' WHERE hdduid='$hdduid'";

if ($connection->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $connection->error;
}

$connection->close();

yuko · Answer 3 · 2019-04-14T16:56:14.460

-1

Here is another solution using prepared statements.

$servername = "localhost";
$username = "root";
$password = "L24wmc1nJBVP90q9yY";
$dbname = "ft";

// Create connection
$connection = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}

$paid = $_GET["status"];
$hdduid = $_GET["hdduid"];

//Prepared statements
$statement = $connection->prepare("UPDATE users SET paid = ? WHERE hdduid = ?");
$statement->bind_param("ss", $paid, $hdduid);

if(!$statement->execute()) {
    echo "Error updating record: " . $statement->error;
} else {
    echo "Record updated successfully";
}

$statement->close();
$connection->close();

edited Apr 14 '19 at 16:56

answered Apr 14 '19 at 16:36

yuko

325
1
8

What exactly is this helping with? What have you changed other than made it worse by switching to MySQLi from PDO? – Dharman Apr 14 '19 at 16:39
Well it worked on my computer, thats why I posted it. And I posted another answer because I thought prepared statements might have been what he was looking for. I chose MySQLi because I'm not familiar with PDO. – yuko Apr 14 '19 at 16:44
I am glad you are trying to help, YuKo, but you can't give an answer which is not explaining the problem or how your solution would help. If you are not familiar with PDO I would strongly recommend to learn it, because it is easier for beginners. – Dharman Apr 14 '19 at 16:46
If you use prepared statements then why would you still need `mysqli_real_escape_string`? – Dharman Apr 14 '19 at 16:48
I thought I'd do that to get rid of special characters. EDIT: Well I was wrong in my understanding about special characters. You are right, it's unnecessary. – yuko Apr 14 '19 at 16:53
....What special characters? Why do you want to get rid of some characters and leave the others? Who gave you the idea that this function removes any characters? – Dharman Apr 14 '19 at 16:55
Thanks for the feedback. What do you think the error might be? – yuko Apr 14 '19 at 16:57

Cant perform an SQL update when using php varibles

3 Answers3