Update Query PHP MySQL

Update query PHP MySQL

You have to have single quotes around any VARCHAR content in your queries. So your update query should be:

mysql_query("UPDATE blogEntry SET content = '$udcontent', title = '$udtitle' WHERE id = $id");

Also, it is bad form to update your database directly with the content from a POST. You should sanitize your incoming data with the mysql_real_escape_string function.

PHP/MYSQL: UPDATE Statement containing carriage returns or new lines

The statement is parsed 2 times: First by PHP and then my MySQL.
PHP replaces \n with the line feed character, but MySQL seems to ignore those whitespace characters. By double escaping \n to \\n you achieve, that the \n character sequences are parsed by MySQL and inserted as line feeds.

PHP MYSQL Update Query with @variable not working

You can't put multiple queries in a single call to mysql_query(). Split it into two calls:

$query = 'SET @order := 0'; 
mysql_query($query) OR die(mysql_error());
$query = 'UPDATE `task` SET `order` = @order := @order + 10 WHERE project = "'.$project.'";';
mysql_query($query) OR die(mysql_error());

Variables like @order persist between calls, since they're associated with the connection, not the call.

SQL Select/ Insert / Update query PHP MySQL

I did it from my mobile not tested but you will get the idea of what is going on

if(isset($check))
{

$sql="SELECT * FROM dtr WHERE userID = $userID";
$result = mysqli_query($con,$sql);
$check = mysqli_fetch_array($result);

if(isset($check))
{
    echo "Already in";
    if(isset($check['timeIN']) && !isset($check['timeOUT']))
    {
        $sql = "UPDATE dtr SET timeOUT= now() WHERE userID=$userID";
        mysqli_query($con, $sql);
        mysqli_close($con);
    }
}
else
{
    $sql = "INSERT INTO dtr (userID,timeIN) VALUES ('$userID', now())";
    mysqli_query($con, $sql);
    mysqli_close($con);
    echo 'Time IN Successful!';
}
}
else
{
echo 'Invalid USER ID. Please try again!';
mysqli_close($con);
}           

MYSQL update or insert with PHP

You can solve this using INSERT ... ON DUPLICATE KEY

Using this method will be transactionally safe, as MySQL is handling it in one implicit transaction. Your current method allows a split moment where two concurrent requests could interfere with one another.

Assuming you're on MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Your query would look like this:

INSERT INTO user_meta SET user_id = 125, active = 0
  ON DUPLICATE KEY UPDATE active = 0

---

Update:
Just to elaborate on why your current method is transactionally unsafe, imagine two concurrent requests executing in parallel.

1. Request 1 attempts an update, sees 0 affected rows.
2. Request 2 attempts an update, sees 0 affected rows.
3. Request 1 inserts a new record.
4. Request 2 inserts a new record.

Whether you explicitly start and complete a transaction, or whether you implicitly do (e.g. through INSERT ... ON DUPLICATE KEY) MySQL will take the responsibility for ensuring that the aforementioned faulty scenario doesn't occur by blocking a second request until the first finishes.

Use a Function in MySql update statement

A SQL query runs in the database server not in PHP. You use PHP to generate a text representing the query then ask the database to run it. In your case you have two options:

1. Two separate queries, first a SELECT query to get the existing data, then a new UPDATE query for each row with the formatted value.
2. Write a stored procedure (a function in the database server) that does what you need.

---

Related Topics