Trying to write a class for sql query. Here, I tried to write this:

class SQL { public $connection; public $query; public function __construct() { $this->connection = mysqli_connect("localhost", "blabla", "arararar"); mysqli_select_db($this->connection, "blabla"); } public function select($rowName, $tableName, $rowNameCondition, $whereCondition) { if (strlen($rowNameCondition) == 0 && strlen($whereCondition) == 0) { $this->query = $this->connection->prepare("select ? from ?"); $this->query->bind_param('ss', $rowName, $tableName); $this->query->execute(); return $this->query->fetchAll(); } else { $this->query = $this->connection->prepare("select ? from ? where ? = ?"); $this->query->bind_param('sssi', $rowName, $tableName, $rowNameCondition, $whereCondition); $this->query->execute(); return $this->query->fetchAll(); } //return mysqli_query($this->connection, $this->query); }

}

I call and use in this way:

 $result = $SQLobject->select("city", "telegramUsers", "chatID", $chatID); while ($row = mysqli_fetch_assoc($result)) { if ($row["city"] !== "none") { $cityID = $row["city"]; } elseif ($row["city"] == "none") { $telegramAPIobject->sendMessage("Будь ласка, виберіть місто", $chatID); } }

Please, please, tell me what I'm doing wrong.

  • Variables can only be substituted, the name of the fields and tables will have to be put directly into the query. To avoid problems, they should be checked against the white lists of allowed names! - Visman
  • in addition, you already do fetchAll in select and probably return an array of results already. So why you calling the code you have a loop with mysqli_fetch_assoc ? you have in $result there ready data lie - teran
  • + data transfer format in select curve. you have the last two parameters, this is the name and the value of the field to generate where , since you are substituting them as ? = ? ? = ? This means that you can write a condition to only one field. And if you need two fields, then what to do? Transfer an array, where the keys are the field names, and the values ​​are the parameter values. keys can also be used for named placeholders. - teran

1 answer 1

You want to write a SQL query constructor. It is convenient to separate the construction and use of the received request. To begin with, let's collect a query with placeholders (without values, but with pointers where you need to substitute values), which can be passed to mysqli_prepare.

To substitute the names of tables and columns in the query string, you need the screening function. In MySQL, these names can contain a variety of characters and are limited by the backslash character. Names can also contain a reverse apostrophe, then it needs to be escaped with another apostrophe (you can read more about this in the MySQL documentation ), I hope the code will understand what it means:

 class SQL { private static function quote($str) { return '`'.strtr($str, array('`' => '``')).'`'; } }

Now we add a simple method to create a select query:

 public static function select($rowName, $tableName, $rowNameCondition = null) { $sql = 'select ' . self::quote($rowName); $sql .= ' from ' . self::quote($tableName); if ($rowNameCondition) { $sql .= ' where ' . self::quote($rowNameCondition) . ' = ?'; } return $sql; }

Now calling SQL::select('city', 'telegramUsers', 'chatID'); , we get the string:

 select `city` from `telegramUsers` where `chatID` = ?

It remains to write the code that will transfer the request to the database:

 class DB { private $connection; public function __construct($host, $username, $password, $defaultDb) { $this->connection = new mysqli($host, $username, $passwordm $defaultDb); } public function select($rowName, $tableName, $rowNameCondition = null, $whereCondition = null) { $sql = SQL::select($rowName, $tableName, $rowNameCondition); $query = $this->connection->prepare($sql); if ($rowNameCondition) { $query->bind_param('s', $whereCondition); } $query->execute(); $result = $query->get_result(); return $result->fetch_all(); // доступно только с расширением mysqlnd } }

Use!

 $db = new DB("localhost", "blabla", "arararar", "blabla"); $row = $db->select("city", "telegramUsers", "chatID", $chatID); foreach ($rows as $row) { var_dump($row); }

Developing this code, pay attention to the following:

  1. I used the parameters of the DB constructor in order not to stitch them inside. This will allow to use the class for different databases.
  2. I did not use $this->query , because the variable is not needed outside of the method.
  3. I used the default values, which is convenient.
  4. You missed that the fetch_all method is a fetch_all class mysqli_result .
  5. Please note that there are ready-made solutions for your task, maybe you should use them. If you are not familiar with the dependency manager composer , be sure to meet.