How to generate a dynamic mysqli query

PHP
1

Hey there! By dynamic query string, I do not mean prepared statements. I have a bunch of input forms and don’t know in which one information might be entered. Let’s say I have

    $val1 = $_GET['val1'];
    $val2 = $_GET['val2'];
    $val3 = $_GET['val3'];

And I don’t know what variables might be set (in order to have “INSERT INTO table (/*random vars */) VALUES (/*random vars */)”).

So I tried

     list($var1, $var2, $var3) = $new_arr;
     $str = "INSERT INTO table (/*random vars */) VALUES (";
     for ($i = 0;i < $count($new_arr); $i++) {
     if (!empty($new_arr[$i]) {
     $str .= "'" . $new_arr[$i] . "',";
     }
     }

     $str .= "')";

However, although this might work, I can’t figure out how to bind the parameters to the initial part of the query. I would consider using sprintf but I still do not know how many strings to expect so it’s pretty much useless here. Does anyone have an idea?

2

Try splitting the query into thee parts:

  1. Insert into table(
  2. Create input vars loop and build string
  3. Create values loop and build string
  4. Add strings from items 1, 2 and 3
  5. Test
  6. Validate input
  7. Optimise
3

Yet you should.

A php >=5.6 version. For the older versions see here

$pholders  = str_repeat('?,', count($new_arr) - 1) . '?';
$types = str_repeat('s', count($new_arr));
$sql = "INSERT INTO table (/*random vars */) VALUES ($pholders)";
$stmt = $db->prepare($sql);
$statement->bind_param($types, ...$new_arr);
$statement->execute();

Another approach is to use a library I wrote especially to simplify dynamic queries. In this case the code would be

$allowed = array('col1','col2','col3');
$new_arr = $db->filterArray($_GET, $allowed);
$db->query("INSERT INTO table SET ?u", $new_arr);
4

You don’t understand: I don’t know if those fields actually hold any variable as they are not compulsory in that segment of the application. So first, I need a loop to check which ones are set and add them to the end of the query like I did in the OP, then somehow also add their corresponding column names in the center of the query.

5

Can you supply at least three examples of the $_GET parameters and their required SQL statements?

6

I quite understand actually. As I am specifically interested in creating dynamic queries, and can do it for any PHP database extension blindfolded.

What you need actually is to create an array with data, where keys are the same as column names in the table and values are values. The rest will be done automatically.

And yes - in any case your query should be using prepared statements.

7 
`       $announcements = $_GET['announcements'];
	$testimony = $_GET['testimony'];
	$testimony_author = $_GET['author'];
	$book = $_GET['book_of_the_month'];`
	
	$first_part = "INSERT INTO sidebar ('";
	$second_part = "";
	$third_part = "";
	$sidebar = $first_part . $second_part . $third_part;
	
	$new_arr = list($announcements, $testimony, $testimony_author, $book);
	
	for ($i = 0;$i < $count($new_arr); $i++) {
		if (!empty($new_arr[$i]) {
			$third_part .= "'" . $new_arr[$i] . "'";
		}
		$third_part .= ",";
	}

`
That’s what I have now (and what I had in my mind while opening the thread). At this point, the only means of progress is to manually use if statements to check which ones are set and append them to the second part. However, if I do that, then there is no point using the loop I used earlier on and that would create a lot of markup.

8

My query string should look like
INSERT INTO table (announcements, testimony, book) VALUES ($announcements, $testimony, $book);

Or maybe, should I just use entirely different tables for each of those variables?

9

First of all, it is not an SQL query, but a PHP code to generate one.
Second, a PHP code should never look like this, because it will generate an incorrect SQL.

A correct one would be

INSERT INTO table (announcements, testimony, book) VALUES (?, ?, ?);
10

I haven’t actually tested this version in the live app but running it on online sandboxes returns no errors so I think this is my solution

                $announcements = $_GET['announcements'];
		$testimony = $_GET['testimony'];
		$testimony_author = $_GET['author'];
		$book = $_GET['book_of_the_month'];
		
		$one = "INSERT INTO announcements (announcements) VALUES ('$announcements')\;";
		$two = "INSERT INTO testimonies (testimony, author) VALUES ('$testimony', $testimony_author)\;";
		$three = "INSERT INTO books (book_name, book_image) VALUES ('$book', '$book_image')";
		
		$multi = "";

		$conn = new mysqli("xxxx", "root",  "xxx", "db");
		$key_arr = list($announcements, $testimony, $book);
		$val_arr = list($one, $two, $three);
		
		for ($i = 0;$i < count($key_arr); $i++) {
			for ($j = 0;$j < count($val_arr); $j++) {
				if (!empty($key_arr[$i])) {
					$multi .= $val_arr[$j];
			}
		}	
		}
		$conn->multi_query($multi);
11

Try this:

function fred($val) { echo '<pre>'.print_r($val,true) .'</pre>';}

    fred($_GET);

    $gets  = ['announcements', 'testimony', 'author', 'book-of-the-month'];
    $value = [];
    $sql   = 'INSERT INTO sidebar ("';
        foreach($gets as $get):
            if( isset($_GET[$get]) ):
                $sql    .= $get. ', ';
                $value[] = $_GET[$get];
            endif;    
        endforeach;
        $sql .= '") VALUES ("';    
        $sql .= implode(', ', $value); 
        $sql .= '");';    
    echo '<br>$sql == ' .$sql;

Output:
$URL = http://localhost/index.php?XXXannouncements=announce&testimony=test&author=aut&book-of-the-month=botm

Edit:
I am not sure if the quotation marks are required or the trailing commas.

There’s always another way and waiting for the PDO version:

1 Like
12

Why you all are so persistent in creating a syntactically incorrect and vulnerable queries?

13

If the remark was directed at me then I do not have a database setup to try the proposed $sql statement.

I think I maybe wrong and quite often am wrong :frowning:

It is not far from what @nmeri17 requirements and very little tweaking to get it to work.

As previously mentioned there is always a another way and I await your PDO version.

14

Lmaaaooo :smile: :smile: No offence but are you a comedian?

1 Like
15

Yours also seems correct although each variable in a valid SQL should be wrapped in quotes. Also I think empty is safer. All the same, if mine encounters any bugs(which I doubt it would) I’ll do it your way. :wink:

1 Like
16

You just need to maintain an array of bind parameters. Pass that to execute.

17

You can do it in a simplified way by using a DBAL in a quite neat way:

First define your variables based on user input.

$name = 'Bill';
$year = NULL;
$room = ['g23','f43','g43'];
$type = NULL;
$day  = 4;
$time = NULL;

Then write a query where every variable is used twice

$sql = "SELECT * pro_files WHERE name=?s 
(AND ?s is NULL OR year = ?s)
(AND ?a is NULL OR type = ?a)
(AND ?a is NULL OR room = ?a)
(AND ?s is NULL OR day  = ?s)
(AND ?s is NULL OR time = ?s)";

And finally run the query:

$data = $db->getAll($sql,$name,$year,$year,$room,$room,$type,$type,$day,$day,$time,$time);
It will return the search result based on non-null values provided.

18

@elizine First of all, what’s a DBAL??
2) Where did you get the $db/getval variables from?
3) Why are those values doubled when I’m checking if they themselves have values?
4) Lastly, what does the s in WHERE name=?s mean? String?

PS: sorry if I’m coming off as noob-ey

19

I like both the library and the clever method of selecting things you are using, but it’s a bit irrelevant tot he question, as the OP is not using SafeMysql at the moment and his problem is not SELECT but INSERT query with unknown number of parameters.

20

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.