在数据库中找不到记录

Question

I have the following problem: I am receiving a GET variable in a url. If the variable GET arrives, I send the contents of the variable to my controller.

My controller first brings the whole "sales" table, then I look for the record that has the same content of the GET variable in a column. Finally, I update the status of that record I found.

But nothing happens, and I do not know what I'm doing wrong.

I leave the code:

PHP file where the variable GET is received:

if(isset( $_GET['number'])){

$number = $_GET['number'];

$response = CartController::ctrShowSales($number);

echo $response;

}

PHP Controller:

static public function ctrShowSales($number){

    $table = "sales";

    $respuesta = CartModel::mdlShowSales($table);

    $find = 0;

    foreach ($response as $key => $value) {

        if ($value["number"] == $number) {

            $find = 1;
            $id = $value["id"];

            break;

        } 

    }

    if ($find == 1){

        $response2 = CartModel ::mdlUpdateRecord($table, $id);
        return $response2;

    } else { return "Did not find";}

}

PHP Model:

static public function mdlShowSales($table){

    $stmt = Conection::conect()->prepare("SELECT * FROM $table");

    $stmt -> execute();

    return $stmt -> fetch();

    $stmt -> close();

    $tmt =null;

}

static public function mdlUpdateRecord($table, $id) {

    $stmt = Conection::conect()->prepare("UPDATE $table SET status = :status WHERE $id = :$id");

            $stmt->bindParam(":id", $id, PDO::PARAM_INT);
    $stmt->bindParam(":status", "Verified", PDO::PARAM_STR);

    if($stmt -> execute()){

        return "ok";

    }else{

        return "error"; 

    }

    $stmt -> close();

    $stmt = null;

}

Answer 1

In addition to the other answers I would add this simple method to your models,

protected static $tables  = ['sales'];

final static public function ckTable($table){
     if(false !== ($index = array_search($table, static::$tables, true))){
         return $tables[$index]; //return your table value
     }
     throw new Exception('Unknown Table');
}

static public function mdlShowSales($table){
       //here you can clearly see the table is being handled
       $safeTable = self::ckTable($table); //use a different var here


       $stmt = Conection::conect()->prepare("SELECT * FROM $safeTable");
       ....

      //or $stmt = Conection::conect()->prepare("SELECT * FROM ".self::ckTable($table));
}

Right now you have only the fact that you hard coded this, in your controller:

$table = "sales";

All it would take is to one day make this mistake in a controller

//here you cannot tell if this is safe to do or not as you cannot see how the query is done.
static public function somepage($table){
      $respuesta = CartModel::mdlShowSales($table);
}

And you would be open to SQL Injection even if you prepare the query.

Right now it's just Improbable that, that will happen, we should make this impossible.

Also, this is basically what you are doing:

//everything under PHP Controller can be done with this sql:
SELECT id FROM sales WHERE number = :number LIMIT 1
/*
  SELECT * FROM sales
foreach ($response as $key => $value) {   
        if ($value["number"] == $number) { //-- WHERE number = :number
            $find = 1;
            $id = $value["id"]; //-- SELECT id
            break; //-- LIMIT 1
        } 
    }
 */

//mdlUpdateRecord
UPDATE sales SET status = :status WHERE id = :id

So why not just do this

UPDATE sales SET status = :status WHERE number = :number LIMIT 1

Basically I am just rewording your code into just SQL, you can do it however you want. I think maybe ordering will be an issue here with Limit 1 if your order is different and you have multiple number rows for the same value. But I don't know what your DB looks like to say for sure, this is true with your original code as well.