Searching for letters and updating data using sql

Question

Next code is looking for characters in an string from "note" column in all rows in a data table. Once it gets row "id", then it should update "did_read" column to value "1" in the same row. But no success. What corrections must I do to make it work?

<?php 

    if (isset($_POST['str'])){ 

       $strid =$_POST['str'];
       $sql="SELECT id FROM notifications WHERE note CONTEINS='$strid'  ";
       $query = mysqli_query($db_conx, $sql);
       $statusnumrows = mysqli_num_rows($query);
       if($statusnumrows> 0){
       while ( $row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
       $statusid = $row["id"];
       $sql = "UPDATE notifications SET did_read='1' WHERE id='$statusid'";
       $query = mysqli_query($db_conx, $sql);
       echo 'did_read_ok';
       exit;
        }
    }

}
?>

Looking now I think that I should be much easier use next. But also is not working.

<?php 

if (isset($_POST['str'])){ 

  $strid= mysqli_real_escape_string($db_conx, $_POST['str']);

   $sql = "UPDATE notifications SET did_read='1' WHERE note LIKE='$strid' ";
   $query= mysqli_query($db_conx, $sql);
   echo 'did_read_ok';
   exit;



}
?>

I have made some conclusions with your help down in comments. The First part of code is working if I do uncomment it. But then I can't understand why other code in the condition loop is not working as I am getting "string(10)'status_218'" and "did_read_ok" ? I am sending 'str' variable with ajax.send().

<?php
     //$strid= 'status_218';
     //  $sql = "UPDATE notifications SET did_read='1' WHERE note LIKE '%$strid%'";// not working:LIKE CONCAT('%', $strid, '%')
     //  $query= mysqli_query($db_conx, $sql);

if (isset($_POST['str'])){ 
   $strid= $_POST['str'];
   var_dump($strid);//I get: string(10)status_218
   $sql = "UPDATE notifications SET did_read='1' WHERE note LIKE='%$strid%'";
   $query= mysqli_query($db_conx, $sql);
   echo 'did_read_ok';
   exit;
}
?>

which SQL dialect are you using? MSSQL has `CONTAINS`, whereas with mysql/mariadb you should use `LIKE`. Also, your query should be sanitized since it takes arguments straight from POST. — onik, Dec 14 '19 at 20:59
@onik SQL Server also has LIKE. And OP is using the mysqli connection interface, which means it's MySQL — Qirel, Dec 14 '19 at 21:41
While using the LIKE operator, you should also include the wildcard `%`, otherwise it's just as using = — Qirel, Dec 14 '19 at 21:42
@Qirel ah, true. So the query should be `SELECT id FROM notifications WHERE note LIKE '%$strid%'` — onik, Dec 14 '19 at 21:43
That's one thing - another is that one should always use a prepared statement. So the condition should be `WHERE note LIKE CONCAT('%', ?, '%')` and then bind that placeholder to `$strid` :-) — Qirel, Dec 14 '19 at 21:54

score 2 · Accepted Answer · answered Dec 15 '19 at 16:39

I should not be answering this question, but I want to help you out. First of all you must enable error reporting if you have not done so already. Read How to get the error message in MySQLi?. Also I would recommend to switch over to PDO instead of mysqli, because it is better and simpler.

Your PHP and SQL have problems.

You don't seem to be connecting to the database anywhere.
Your code is not using prepared statements, which makes it vulnerable to SQL injection.
You can remove exit from your code, it doesn't do anything
The syntax for LIKE is LIKE '%string%', without =

A good example of mysqli code should look like this.

<?php

// report all errors. Check the error log or enable display errors in PHP config
error_reporting(E_ALL);

// Always enable mysqli error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db_conx = new mysqli('localhost', 'username', 'password', 'databaseName');
$db_conx->set_charset('utf8mb4'); // always set the charset

if (isset($_POST['str'])) {
    $strid = '%'. $_POST['str'] .'%';
    // prepare -> bind -> execute
    $stmt = $db_conx->prepare("UPDATE notifications SET did_read=1 WHERE note LIKE ?");
    $stmt->bind_param('s', $strid);
    $stmt->execute();
    echo 'did_read_ok';
}

Searching for letters and updating data using sql

1 Answers1