Jump to content

Multiple SQL queries


sunwukung
 Share

Recommended Posts

Is it better (i.e: more efficient) to concatenate an array of data into a string for a one shot MySQL query, or is it acceptable to just loop the array and hit the DB multiple times? I wrote this piece of code, and it works fine (well it might not now, i've removed a few bits for securitah reasons) to concatenate all the values entered in a form into a single string. Perhaps a bit unwieldy in places, but hey I'm a beginner!

if (isset($_POST['submitted']))	{	#....................Function Definition...................................	#concatenates iterations of the array into a single string   #....................Function Definition...................................	function sqlstrArray($array)		{		#array to string iterator 		$n = count($array, 0);#count number of elements		$i = 1;#set counter		foreach($array as $element)			{			if($i <= 1)				{				$sqlstr = $element;				}			else				{				$sqlstr = $sqlstr . ',' . $element;				}			$i++;			}		return($sqlstr);		}	 	//.................PROCESS TEXT BASED INPUTS WITH A LOOP........................	$upd_tab = array('value1',					 'value2',					 'value3',					 'value4',					 'value5'					 );		#create an array from all added values	foreach($upd_tab as $upd_t)		{		if (!empty($_POST[$upd_t]))			{			$upd_data = mysqli_real_escape_string($dbc,($_POST[$upd_t]));#clean input						$upd[$upd_t] = $upd_data; #concatenate query segment			$key_list[] = $upd_t; 			}		}	 	$array = $key_list;#pass input values to function variable	$key_list = sqlstrArray($array);#call array to string function			#acquire current data for those keys from the database	$q_data_db = "SELECT $key_list FROM database WHERE condition = $condition";	$r_data_db =  @mysqli_query($dbc,$q_data_db);

So is it more efficient to just loop the original array - or is a string useful?

Link to comment
Share on other sites

With a certain special exception case*, it's more efficient to hit the DB as few times as possible. I see that you're using MySQLi, so fortunatly for you, you can cast multiple queries with mysqli::multy_query(). And if there's a way in the SQL language whereby you can combine multiple queries into a single query, all the better.* If you perform a lot of interactions (i.e. select and display large amounts of data), your page will appear to load faster (from an end-user's point of view) if you output its contents in parts. To do that, you'd use output buffering. Before you flush the buffer each time though, the current interaction with the DB must be completed. In this case, in order to finish the interaction more quickly and trasmit less data (thus process it faster), it will be better to keep stuff down to single simple queries. Alternatively, you can still interact with the DB once, but flush the buffer after processing each part of the large data set. In this case though, your server will have to have lots of RAM in order to keep all data in PHP during the process, and that amount for each request to PHP.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...