There is an array that I get from a site.com/?cpu=1,2&brand=2,3&ram=2,3 type link with the $ _GET method

array(3) { ["cpu"]=> string(3) "1,2" ["brand"]=> string(3) "2,3" ["ram"]=> string(3) "2,3" }

How can I glue the following query from this array? PS Probably I have a wrong vision of making a request, if somewhere the logic of the request was wrong, please correct it.

 SELECT * FROM table WHERE slug = cpu AND name = 1 OR slug = cpu AND name = 2 WHERE slug = brand AND name = 2 OR slug = brand AND name = 3 WHERE slug = ram AND name = 2 OR slug = ram AND name = 3

I tried to do something like this, but apparently I have the wrong idea about the implementation.

  foreach($request->all() as $name => $value) { $valuesArray = explode(',', $value); foreach($valuesArray as $row) { $sql[] = 'WHERE ' . $name . '=' . $row; } }

PS I make a dynamic filter that returns the position of the query which is generated through $ _GET

  • table structure show - rjhdby
  • The fact is that of course there is still no understanding of how the tables and their connections should look right, I am trying to realize so far: - asd
  • clip2net.com/s/3D8WvtT - asd
  • For example, in Y.Market, data is taken from $ _GET and they are already being substituted and a request is being formed, here I want to implement something similar. - asd
  • Google on demand dynamic filter gives info on EAV, maybe this is what I want to get in the end. - asd

2 answers 2

If for laboratory, then so:

 $parts = []; foreach ($request->all() as $name => $value) { $parts[] = "(slug = '$name' AND name in ($value))"; } $sql = "SELECT * FROM table WHERE " . implode(" OR ", $parts);

But in the industrial code, of course, this can not be used for anything. It is necessary to add a very thorough check of incoming data: checking the existence of fields, checking the format of fields, escaping input data for SQL, take into account the case when there is no filter, add pagination if there is a lot of data.

    Try to avoid creating a SQL query by gluing conditions from the incoming data. This is not even a shot in the foot, but throwing a grenade into it. In the best case, get just inconsistent SQL, and at worst - cool Hacker, who read what SQL injection is.

    I will try to tell you that in the case of ram you have the numbers of the minimum and maximum values, the base mysql (for other databases there will be just other operators)

     function fields($field, $count){ $out = array(); for($i = 0;$i < $count; $++) $out[] = $field.'=?'; return implode(' OR ', $out); } function binds($type, $count){ $out = ''; for($i = 0;$i < $count; $++) $out .= $type; return $out; } $db = new mysqli(данные для коннекта); $ramMin = isset($_GET['ram'])?$_GET['ram']:PHP_INT_MIN; $ramMax = isset($_GET['ram'])?$_GET['ram']:PHP_INT_MAX; $SQL = "SELECT * FROM table WHERE ram>=? AND ram<=?"; $params = array($ramMin, $ramMax); $bind = "ii"; if(isset($_GET['brand']){ $params = array_merge($params, explode(',',$_GET['brand'])); $SQL .= ' AND ('.fields('brand', count($brandArray)).')'; $bind .= binds('s', count($brandArray)); } //аналогично для cpu $finalParams = array(); foreach($params as $param) $finalParams = &$param; $stmt = $db->prepare($SQL); call_user_func_array( array($stmt, 'bind_param'), array_merge(array($bind), $finalParams) ); $stmt->execute();