Pdo Error: " Sqlstate[Hy000]: General Error " When Updating Database

PDO error: SQLSTATE[HY000]: General error When updating database

You do not use fetchAll(),as in

$result = $stmt->fetchAll();

with update or insert queries. Removing this statement should rectify the problem.

Need help to find error SQLSTATE[HY000]: General error

My humble apologies to Aynber, as he was right I could not understand answer I have to remove fetchAll from my function. So I have created now two function one for insertion/updating and another for just query. As answer was there but I was seeing other answers.

I just removed following line from my query function

$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);

Thanks now my class is good as it is doing proper error handling.

Updating table error? (SQLSTATE[HY000]: General error)

You are destroying your $query handle by reusing it on the UPDATE query thus the while loop gets broken.

So use another variable for the statement handle used in the UPDATE.

Its also better to prepare a query once and reuse it many times, just replacing the variables. This allows the database to compile and optimise the query only once, but run it many times. It's quicker for your script and places less unnecessary load on the database.

if(isset($_SESSION["discount"])){
    if($_SESSION["discount"] != "na"){
        $sql = "SELECT users FROM discounts WHERE name=:promo";
        $query = $st->prepare($sql);
        $query->execute(array('promo' => $name));

        if($query->rowCount()>0){

            // prepare once use many times
            $sql = "UPDATE discounts 
                    SET users=:updatedips WHERE name=:name";   
            $stmt= $st->prepare($sql);

            while($row = $query->fetch(PDO::FETCH_ASSOC)){
                if(!empty($row["users"])){
                    $usedips = $row["users"];
                    $usedips = $usedips.", ".$_SERVER["REMOTE_ADDR"];
                }else{
                    $usedips = $_SERVER["REMOTE_ADDR"];
                }

                $stmt->execute(array('updatedips' => $usedips,'name' => $name));
            }
        }
    }
}
$_SESSION["discount"] = "na";

PDO Error - PDOException' with message 'SQLSTATE[HY000]: General error'

This is what happens:

• You are trying to fetch an UPDATE query. You can't do that because UPDATE queries does not return values. If you wish to know how many rows were affected by the query, use the rowCount() function instead. Notice that not all DB Drivers provide the affected rows.

• You are using undeclared variables (at least in the code you posted here). This isn't the reason for this particular error, but could generate others.

• You're not using the data you have selected from the database

  Also, it is recommended to make all PDO operations within the try block, otherwise you may get unhandled exceptions.

SQLSTATE[HY000]: General error with PHP and PDO

Based on your latest edit: You can't fetch results with PDO after executing an INSERT query. See here: http://www.php.net/manual/en/pdostatement.fetch.php#105682

Edit: I suppose, since the function's called "login", you want to have something like this as $sql: "SELECT password FROM users WHERE username = :username", and then iterate over the results with the while loop, and then log in the user if the password matches?

Edit2: Based on your edit to provide a SELECT query: DO NOT USE THIS QUERY. What you are doing is NOT SQL injection proof. Never ever use variables from user input (i.e. $_POST, $_GET et al) and put them unfiltered into an SQL query. Please look up the term "prepared statements" here at SO or Google.
As you can see, since you forgot to put single ticks (apostrophes) before and after the double quotes, MySQL thinks that your input refers to another column ("pvtpyro") instead of comparing the value in the column against a string. ALWAYS use the ":username", ":password" syntax (the one with prepended colons) or your queries will be unsafe and enormously dangerous to your application.

PDO error: SQLSTATE[HY000]: General error: 2031

You cannot use ->bind* and ->execute($params). Use either or; if you pass parameters to execute(), those will make PDO forget the parameters already bound via ->bind*.

---

Related Topics