sunwukung Posted November 25, 2008 Share Posted November 25, 2008 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 More sharing options...
boen_robot Posted November 25, 2008 Share Posted November 25, 2008 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 More sharing options...
sunwukung Posted November 25, 2008 Author Share Posted November 25, 2008 Thanks a lot for the tip boen_robot Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.