Create a Dynamic MySQL Query Using PHP Variables

Create a dynamic mysql query using php variables

Just check if the variables contain a value and if they do, build the query like so:

$sql = [];
$parameters = [];
    
if ($stationFilter) {
    $sql[] = " STATION_NETWORK = ?";
    $parameters[] = $stationFilter;
}
if ($verticalFilter) {
    $sql[] = " VERTICAL = ?";
    $parameters[] = $verticalFilter;
}

$query = "SELECT * FROM tableName";

if ($sql) {
    $query .= ' WHERE ' . implode(' AND ', $sql);
}
$stmt = $mysqli->prepare($query);

if ($parameters) {
    $stmt->bind_param(str_repeat('s', count($array), ...$parameters);
}
$stmt->execute();
$result = $stmt->get_result();
$data = $result->fetch_all(MYSQLI_ASSOC);

Create a Dynamic MySQL Query using an Array - PHP

So build your query dynamically too:

$foo = '... list of pieces ...';
$parts = explode(' ', $foo);

$likes = array();
foreach($parts as $part) {
   $likes[] = "piece LIKE '%$part%'";
}

$sql = "SELECT ... WHERE " . implode(' or ', $likes);

But note that this is vulnerable to sql injection attacks.

MySQL How to use dynamic PHP variables inside INSERT INTO SELECT query?

You can split the task for 2 parts:

• get data from MySQl

• nsert new data updated by PHP

  $sql = "SELECT origin, name, color, position, controller 
          FROM robots
          WHERE controller = 'remote'";
  
  $stmt= $pdo->prepare("insert into robots (origin, name, color, position, controller) values (?, ?, ?, ?, ?);");
  
  foreach ($pdo->query($sql) as $row) {
      $stmt->execute([
          $row['origin'],
          generateRobotName(),
          $row['color'],
          $row['position'],
          $row['controller']
      ]);
  }
  
  

Here the live code PHPize.online

Another example with named parameters:

$sql = "SELECT origin, name, color, position, controller
        FROM robots WHERE controller = 'remote'";

$stmt= $pdo->prepare(
    "insert into robots (origin, name, color, position, controller) 
        values (:origin, :name, :color, :position, :controller);"
);

foreach ($pdo->query($sql, PDO::FETCH_ASSOC) as $row) {
    $row['name'] = generateRobotName();
    $stmt->execute($row);
}

PHPize it here

How to use dynamic variable in function with query in PHP and MySQL

How about using switch structure?

<?php

function GeneralQuery($connMysqli, string $type = null)
{
switch($type) {
    case "school": 
        $sqlQuery = 'GROUP BY school
        ORDER BY school';

        break;

    case "class": 
        $sqlQuery = 'GROUP BY school, class
        ORDER BY class';

        break;

    case "student": 
        $sqlQuery = 'GROUP BY school, class, student
        ORDER BY class, avg DESC';

        break;

    default: 

        //your default case when
        //none of above mathes
        //for exampe no $type was passed
        //to the function
        $sqlQuery = 'GROUP BY class, u.id
        ORDER BY class, avg DESC';

        break;

}

 $sql = "SELECT avg(finalgrade) as avg, u.lastname as school, u.department as class, u.id as student from fhrw_grade_grades gg
    inner join fhrw_user u on u.id=gg.userid
    where gg.rawgrade is not null " . $sqlQuery . "";

    $res = mysqli_query($connMysqli,$sql)or die(mysqli_error($connMysqli));
    $list = array();
    while($item = mysqli_fetch_assoc($res)){
        $list[] = $item;
    }

    return $list;
}

$type = 'school';
$data = GeneralQuery($connMysqli, $type);
?><h4>AVG by School</h4><?
foreach($data as $item){
 echo round($item['avg'],2) . ' - ' . $item['school'] . '<br>';
}

$type = 'class';
$data = GeneralQuery($connMysqli, $type);
?><h4>AVG by Class</h4><?
foreach($data as $item){
 echo round($item['avg'],2) . ' - ' . $item['class'] . '<br>';
}

$type = 'student';
$data = GeneralQuery($connMysqli, $type);
?><h4>AVG by Student</h4><?
foreach($data as $item){
 echo round($item['avg'],2) . ' - ' . $item['student'] . '<br>';
}

// close connection
mysqli_close($connMysqli);

Note a must read about SQL Injection

Note there is no FROM in the SELECT query, the syntax for SELECT statemet or here

Query with dynamic variables in clause WHERE

You can change your query to:

SELECT name FROM users WHERE name = ? AND surname like ?

and when you want to search by name then the arguments of test($name,$surname) will be --> test('yourname','%');

here note that % is wild card and surname in query is using "like" so at the end the query will be:

SELECT name FROM users WHERE name = 'yourname' AND surname like '%'.
What it will search is data will 'yourname' as name and any value for surname.

Now, if you want to search by name and surname then you can call the function as
--> test('yourname','yoursurname')
so the query will become:

SELECT name FROM users WHERE name = 'yourname' AND surname like 'yoursurname'.
What it will search is data will 'yourname' as name and 'yoursurname' as surname. Thus just using a single query you can solve both purpose

Dynamic value in sql query using php

It looks to me that you want to generate a where clause that looks at any available nvarchar column of your table for a possible match. Maybe something like the following is helpful to you?

I wrote the following with SQL-Server in mind since at the beginning the question wasn't clearly tagged as MySql. However, it turns out that with a few minor changes the query work for MySql too (nvarchar needs to become varchar):

$search='%';$tbl='feedback';
if (isset($_POST['search'])) $search = $_POST['search'];
$columns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = '$tbl' AND DATA_TYPE ='nvarchar'";
$columns_result = $conn->query($columns);
$columns_array = array();
if(!$columns_result) print_r($conn->errorInfo());
else while ($row = $columns_result->fetch(PDO::FETCH_ASSOC)) 
   array_push($columns_array, "$row[COLUMN_NAME] LIKE ?");

$where = join("\n OR ",$columns_array);
$sth = $conn->prepare("SELECT * FROM $tbl WHERE $where");
for ($i=count($columns_array); $i;$i--) $sth->bindParam($i, $search);
$sth->execute();

$result = $sth->fetchAll(PDO::FETCH_ASSOC);

print_r($result);

The above is a revised version using prepared statements. I have now tested this latest version using PHP 7.2.12 and SQL-Server. It turned out that I had to rewrite my parameter binding part. Matching so many columns is not a very elegant way of doing queries anyway. But it has been a nice exercise.

Dynamically create PHP variable name from Mysql

use curly braces '{}'.., see explanation at curly braces
ex:

$result = $mysqli->query("SELECT * FROM items") or die(mysql_error());
while($row = $result->fetch_array())  {
   ${'price'.$row['varname']} = $_row['price'];
}

if i assume you field varname values is item01,item02,item03 ...
you can print your variables with:

echo $priceitem01;

Dynamic SQL SELECT Statement with PHP based on user options

try this:

<?php
$location= $_GET['location'];
$language= $_GET['language'];
$name= $_GET['name'];

if($location!=""){
    $where[] = " `location` = '".mysql_real_escape_string($location)."'";
}
if($language!=""){
    $where[] = " `language` = '".mysql_real_escape_string($language)."'";
}
if($name!=""){
    $where[] = " `name` = '".mysql_real_escape_string($name)."'";
}
$where_clause = implode(' OR ', $where);
//same code for the other 2 options

$sql_json = "SELECT * FROM myTable WHERE $where_clause ORDER BY id DESC";
?>

---

Related Topics