Does the db class support bind variables?

[raindog308]

Does the MyBB database class have any support for bind variables?

Something like:

->prepare ("INSERT INTO sometable ( field1, field2 ) VALUES ( ?, ? )")

and then later

->execute ( $val1, $val2 );
->execute ( $val3, $val4 );
->execute ( $val5, $val6);

etc. One prepare, many executions without having to to write_query every time a variable changes.

For busy databases, bind variables save a ton of CPU power because the query only needs to be parsed once instead of on every query. It also avoids the need to handle string escaping, etc. (I know the db class has a string escaping but I'm saying with bind variables you don't need to run it).

php supports bind variables (mysqli_bind_param, etc.)

[Euan T]

The core DB classes do not currently support binded parameters I'm afraid. It's a feature I'd love to see personally though.

[raindog308]

BTW, I forgot to add that insery_query helps but is not 100% there and of course only handles inserts.

Thanks for your info, Euan.

[Euan T]

2.0 will have an entirely new DB class but it's a while off yet. I doubt 1.8 will have many changes as it's a big job to take on. I did start writing a PDO based implementation of the DB classes but never got far with it due to time constraints.

[Dannymh]

Hmm I know it doesnt do what you want but in a situation like you presented I build queries that look like

$query = "insert into table (field1, field2)  values".

While () {
$ query .= "($var1, $ var2),";
}

That way it builds one insert query that can be pretty long and insert multiple rows with a single execution of the insert query.

Saves a tonne of time but not that much cpu. Still its worth trying in your situation with no binds available.

I guess you could extend the db class or drag in your own though via a plugin.

[pavemen]

Hmm I know it doesnt do what you want but in a situation like you presented I build queries that look like

$query = "insert into table (field1, field2)  values".

While () {
$ query .= "($var1, $ var2),";
}

That way it builds one insert query that can be pretty long and insert multiple rows with a single execution of the insert query.

Saves a tonne of time but not that much cpu. Still its worth trying in your situation with no binds available.

I guess you could extend the db class or drag in your own though via a plugin.

there is a insert_query_multiple( ) function in the mysql and mysqli DB classes that does what you want

	/**
	 * Build one query for multiple inserts from a multidimensional array.
	 *
	 * @param string The table name to perform the query on.
	 * @param array An array of inserts.
	 * @return int The insert ID if available
	 */
	function insert_query_multiple($table, $array)
	{
		if(!is_array($array))
		{
			return false;
		}
		// Field names
		$fields = array_keys($array[0]);
		$fields = "`".implode("`,`", $fields)."`";

		$insert_rows = array();
		foreach($array as $values)
		{
			$insert_rows[] = "('".implode("','", $values)."')";
		}
		$insert_rows = implode(", ", $insert_rows);

		$this->write_query("
			INSERT 
			INTO {$this->table_prefix}{$table} ({$fields}) 
			VALUES {$insert_rows}
		");
	}

[Dannymh]

I didnt even look for that. Much easier to build an array rather than my method where you need to trim the last comma

[Euan T]

Yeah, insert_query_multiple is quite useful