|
PHP - Performance On Thousands Of Selects In While Loop
Hello,
Any ideas why server resources not exhausted on the code below, when my.cnf and php.ini are both set to use half the resources each? PHP & MySQL will both utilize all allotted resources on other scripts, so it's not a tuning problem. There is no script-side tuning.
The bottleneck: pdo select as shown below
foreach($bigList as $listObject) {
$sql = $dbl->prepare("SELECT * FROM fewMillionRows WHERE indexedCol1=:indexedCol1 AND indexedCol2=:indexedCol2 AND indexedCol3=:indexedCol3 AND indexedCol4=:indexedCol4 LIMIT 1");
$sql->execute($preparedValues);
$return = $sql->fetchAll(PDO::FETCH_ASSOC);
}
On a dedicated server with 8GB RAM, the server uses >5% cpu/ram but takes a long time to finish the script.
Second Question
What are some alternative designs?
Because the column values all happen to be alphanumeric, I could select the entire table and store it in an array. Accessing the keys like so: indexedCol1indexedCol2indexedCol3indexedCol4.
Results:
MyISAM - Select Whole Table: 30 seconds
MyISAM – Select Individual Rows (10k times) – 68 seconds
InnoDB – Select Whole Table: 30 seconds
InnoDB – Select Individual Rows (10k times) – 131 seconds
I am surprised it takes so long to select a whole table. The server resources use 1% for about 20 seconds, then cpu/ram jump to 30%+ for about 10 seconds. This is still drastically faster than individual selects.
In this instance, $bigList is over 500k items. At 68 seconds per 10k rows it’s absurdly long. Building an array with key/values is the only realistic way I currently know of, but I suspect there is a much better way of doing this.
As far as I know, I cannot do a select like so: SELECT * FROM t1 WHERE (column1,column2,column3) IN ((val1, val2,val3), (val4, val5,val6))
There is no way to determine whether a row was found for each entry as far as I know.
Similar TutorialsHey.
So the issue I'm having is consecutive loops on semi-large arrays, over and over. Consider this array:
$firstArray = array(
'row1' => array(
'dates' => array(
'2014-01-01' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-01-02' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-01-03' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-01-04' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-01-05' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-01-06' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-01-07' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
)
),
'row2' => array(
'dates' => array(
'2014-02-01' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-02-02' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-02-03' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-02-04' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-02-05' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-02-06' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-02-07' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-02-08' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
'2014-02-09' => array('key1' => 'value1', 'key2' => 'value2', 'key3' => 'value3', 'key4' => 'value4', 'key5' => 'value5', 'key6' => 'value6', 'key7' => 'value7', 'key8' => 'value8', 'key9' => 'value9', 'key10' => 'value10'),
)
)
);
Originally the data comes from ~2-3 database tables, of course. But to ilustrate the point, this is how the main array looks like. This array usually contains anywhere between 10-50 rows, each row containing at least 10 dates, with 10 key/values each.
And after setting up all the data, it needs to be processed. Currently this is how a friend of mine did it..
$placeDataHere = array();
foreach($firstArray as $key => $dates) {
foreach($dates as $date => $values) {
foreach($values as $key => $value) {
$placeDataHere['DV_' . $date]['SM_' . $key] = 'KS_' . $value;
//Followed by another ~50-70 lines of processing the 3 loop's data..
...
...
....
....
....
....
....
....
}
}
}
Obviously this isn't good practise, but we can't seem to figure out a better way of doing it, since both the data and the loops are horribly nested.
This loop and setup of $firstArray is run anywhere between 10-20 times/request, due to amount of users we wish to process. So, the result is that this code can take up to over 2-3 minutes to complete, which isn't really optimal performance.
In short my question is, are there any better methods of handling this with the data setup we currently have?
Hey guys there is probably a simple fix to this but i am just not seeing it ... Im createing a form that has error catching and self submitting valuse. I am trying to use a for loop to create all of the values and if the form is returned with an error then the correct values will still be filled out but i cant seem to get this loop to work. Every time i submit the form it doesnt matter what option i select it always comes back with the last one in the list or 30 in this case. the problem code is below.. Any help would be appreciated. Code: [Select] <?php for($i=1; $i<=30; $i++){ echo'<option value="'.$i.'"'; if(isset($_POST['petsAge'])== $i){ echo ' selected="selected"';} echo '>'.$i.'</option>'; } ?> Hello,
Anyone can suggest me what is the best way to update table records. I'm kinda scared to make an update on a table because it may damage the system and I am not really good on SQL commands.
Right now I just made a process on PHP to update table records. It does update email records on but it stop because of the limit of loading the page. Is there anyway accurately update all the data of the table.
Here is what I want to achieve.
I have a table with an "email" field on it, those email data from "email" field on tb_one must be change to cID records base on tb_two.
tb_one
Field =
- id
- email
- etc.
tb_two
Field =
- id
- email (same as on tb_one)
- cID
Below is the code I have so far for only 2 terms with '$word" being the keyword of the site and all the other words are suppose to be linked if they appear in the text. Okay I have thousands of different possible words from the same table. In the table 'word' is the term to be defined and 'definition' is the main content. I want a code that recognizes any word that is listed in my table and replaces it. <?php $find2 = array ('/$word/i', '/bond/i'); $replace2 = array ('<a href=http://www.subjectmoney.com/definitiondisplay.php?word=$word><b>$word</b></a>', '<a href=http://www.subjectmoney.com/definitiondisplay.php?word=Bond>bond</a>'); Echo preg_replace ($find2, $replace2, $definition); ?> Hi Guys, I am a typical newbie with a twist, i program in VB, recently moved to PHP. I need help, preferable an example, but first let me explain what i have. I have a .tpl page that has 3 drop down boxes, one of these is populated from within the .tpl page itself. The other 2 i waant to pupulate from a SELECT on a database from a .php file. The .TPL file i have so far is:- Code: [Select] <style type="text/css"> <!-- .style7 { color: #006600; font-weight: bold; font-size: 18px; } .style14 {color: #003300; font-weight: bold; } .style16 { color: #000066; font-style: italic; } .style25 { color: #FF0000; font-weight: bold; font-size: 12px; } .style28 { font-size: 12px; color: #330000; } .style29 { font-size: 12px } .style31 {color: #003300; font-weight: bold; font-size: 16px; } .style33 { color: #000099; font-weight: bold; font-style: italic; font-size: 18px; } .style34 { color: #FF0000; font-weight: bold; font-size: 24px; } .style35 {font-size: 12px; color: #003300; } .style36 { color: #FF0000; font-weight: bold; font-size: 14px; font-style: italic; } .style38 {color: #003300; font-weight: bold; font-size: 16px; font-style: italic; } --> </style> <script src="Scripts/AC_RunActiveContent.js" type="text/javascript"></script> <h1 align="center" class="style34">Scotbirds Alertz - Rare and Scarce</h1> <h3 align="center" class="style36">Soon Only VIP Members will be able to Access this Page</h3> <h3 align="center" class="style14">If you have seen something unusual / rare then please call it in on -- <span class="style33">Hotline: 0333 5772473</span></h3> <p align="center" class="style14"> <table width="90%" border="0" align="center" cellpadding="5"> <tr> <?php print $_SERVER['PHP_SELF']; $the_date_filter = $_GET["DATE_FILTER"];?> <td width="1%"><td width="5%"><td width="2%"><td width="2%"><td width="2%"><form action="alertz_VIP.php" method="post"> <td width="2%"><td width="2%"><td width="5%"><span class="style38">Date</span><td width="3%"></td> <td width="12%"><span class="style14"> <select name=DATE_FILTER size="1" id=DATE_FILTER onchange="this.form.submit()"> <option value="All Dates">All Dates</option> <option value="Today">Today</option> <option value="Last 48hrs">Last 48hrs</option> <option value="Last week">Last Week</option> <option value="last month">Last Month</option> </select> </span></td> <td width="1%"> </td> <td width="7%"><span class="style16"><span class="style14"><span class="style31">Region</span></span></span></td> <td width="22%"><span class="style16"><span class="style14"> <select name=REGION_FILTER size="1" id=REGION_FILTER onchange="this.form.submit()"> <option value="AllRg">All Regions</option> <option value={REGION_FILTER}></option> </select> </span></span></td> <td width="1%"> </td> <td width="8%"><span class="style16"><span class="style14"><span class="style31">Species</span></span></span></td> <td width="20%"><span class="style16"><span class="style14"> <select name=SPECIES_FILTER size="1" id=SPECIES_FILTER onchange="this.form.submit()"> <option value="AllSpec">All Species</option> <option value={SPECIES_FILTER}></option> </select> </span></span></td> <td width="9%"><input type="Submit" name="submit2" value="Search" method="get" action="alertz_VIP.php" /></td> </table> <p> </p> <table width="870" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#99CC99"> <tr> <th width="178" class="style31" scope="col">Region</th> <th width="184" class="style31" scope="col">Species</th> <th width="96" class="style31" scope="col">Date</th> <th width="96" class="style31" scope="col">Time</th> <th width = "304" class="style31" scope="col">Comments</th> </tr> <!-- BEGIN alerts --> <tr> <th class="style7 style29" scope="col">{alerts.REGION}</th> <th class="style25" scope="col">{alerts.SPECIES}</th> <th class="style35" scope="col">{alerts.DATE} </th> <th scope="col"><span class="style35">{alerts.TIME} </span></th> <th scope="col"><span class="style28">{alerts.COMMENTS} </span></th> </tr> <!-- END alerts --> </table> <p align="center" class="style14"> </p> Now from here i expect the user to select a date from the date box, ( Ideally i would like the other 2 options to only become visible once a selection on the date has been made, i would also like to retain the selected in the date box once the form has been submited. Now the code i have for the PHP so far is quite long winded as my PHP skills are not so good, although i am learning fast. Code: [Select] <?php /*************************************************************************** * Alertz.php * ------------------- * begin : 30/10/04/10 * copyright : (C) 2010 Andy Guppy * email : webmaster@scotbird.co.uk * * ***************************************************************************/ define('IN_ICYPHOENIX', true); if (!defined('IP_ROOT_PATH')) define('IP_ROOT_PATH', './'); if (!defined('PHP_EXT')) define('PHP_EXT', substr(strrchr(__FILE__, '.'), 1)); // Include files include(IP_ROOT_PATH . 'common.' . PHP_EXT); include_once(IP_ROOT_PATH . 'includes/functions_groups.' . PHP_EXT); include(IP_ROOT_PATH . '/alerts/alertsconfig.'.PHP_EXT); // Page Authorise $cms_page_id = 'scotalertz'; $cms_page_nav = (!empty($cms_config_layouts[$cms_page_id]['page_nav']) ? true : false); $cms_global_blocks = (!empty($cms_config_layouts[$cms_page_id]['global_blocks']) ? true : false); $cms_auth_level = (isset($cms_config_layouts[$cms_page_id]['view']) ? $cms_config_layouts[$cms_page_id]['view'] : AUTH_ALL); check_page_auth($cms_page_id, $cms_auth_level); // Obtain Select Criteria $temp_region_filter = $_REQUEST["REGION_FILTER"]; $temp_species_filter = $_REQUEST["SPECIES_FILTER"]; $temp_date_filter = $_REQUEST["DATE_FILTER"]; // Filter characters if required $region_filter =str_replace(" & ", "&", $temp_region_filter); $species_filter = $temp_species_filter; $date_filter = $temp_date_filter; // standard session management $userdata = session_pagestart($user_ip); // Check to see if user is logged in if ((!$userdata['session_logged_in']) ) // No he isnt { redirect(append_sid(LOGIN_MG . '?redirect=alerts.' . PHP_EXT)); } else // Yes they are { init_userprefs($userdata); // set page title $page_title = "ScotBird alerts - VIP's ONLY !! "; // standard page header include(IP_ROOT_PATH . 'includes/page_header.'.PHP_EXT); // Connect to the database $db = new sql_db($alerts_mysql_host,$alerts_mysql_username,$alerts_mysql_password,$alerts_mysql_db,false); if(!$db) { die("Database Connection Failed:- Please Contact Site Admin" . mysql_error()); } $template->set_filenames(array('body' => 'alertz_VIP.tpl')); if (!isset($_REQUEST['DATE_FILTER'])) { // if date is NOT set then perform this echo 'Date has not been set'; $sql2 = "SELECT * FROM alerts ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts GROUP BY region"; echo $sql2; } else { // if date is set then perform this $year =date("Y"); $month = date("m"); $day = date("d"); $theenddate = $year . '-' .$month . '-' . $day ; echo 'the choice selected was :- '.$_REQUEST['DATE_FILTER']; switch ($date_filter) { case "All Dates": $sql2 = "SELECT * FROM alerts ORDER BY Date DESC, time DESC "; break; case "Today": $sql2 = "SELECT * FROM alerts WHERE Date = '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date = '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date ='".$theenddate ."' GROUP BY region"; break; case "Last 48hrs": $year =date("Y"); $month = date("m"); $day = date("d")-1; $thestartdate = $year . '-' .$month . '-' . $day ; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region"; break; case "Last week": $year =date("Y"); $month = date("m"); $day = date("d")-7; $thestartdate = $year . '-' .$month . '-' . $day ; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region"; break; case "last month": $year =date("Y"); $month = date("m")-1; $day = date("d"); $thestartdate = $year . '-' .$month . '-' . $day ; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region"; break; case "last 3 months": $year =date("Y"); $month = date("m")-3; $day = date("d"); $thestartdate = $year . '-' .$month . '-' . $day ; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region"; break; default: // Default is the last 48hrs $year =date("Y"); $month = date("m"); $day = date("d")-1; $thestartdate = $year . '-' .$month . '-' . $day ; $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' ORDER BY Date DESC, time DESC "; $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species"; $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region"; } } $result2 = $db->sql_query($sql2); if (!($result2 = $db->sql_query($sql2))) { die("Database Query Failed" . mysql_error()); } $i = 1; while ( $row2 = $db->sql_fetchrow($result2) ) { $template->assign_block_vars('alerts', array( 'POS' => $i , 'REGION' => str_replace("&", " & ", $row2['region']), 'SPECIES' => str_replace("%", "'",$row2['species']), 'DATE' => $row2['Date'], 'TIME' => $row2['time'], 'COMMENTS' => str_replace("'", "%",$row2['comments']), ) ); $i++; } $template->assign_vars(array( 'USERNAME' => htmlspecialchars($userdata[username]), 'REGION_FILTER' => str_replace("&", " & ", $region_filter_options), 'SPECIES_FILTER' => $species_filter_options, ) ); $result = $db->sql_query($sql); if (!($result = $db->sql_query($sql))) { die("Database Query Failed" . mysql_error()); } // This is where you would add a new VARS Array if you intend to use your own custom VARS. while ($row = $db->sql_fetchrow($result)) { $region_filter_options .= '<option value="' . $row['region'] . '">' . $row['region'] . '</option>'; } $result = $db->sql_query($sql3); if (!($result = $db->sql_query($sql3))) { die("Database Query Failed" . mysql_error()); } // This is where you would add a new VARS Array if you intend to use your own custom VARS. while ($row = $db->sql_fetchrow($result)) { $species_filter_options .= '<option value="' . $row['species'] . '">' . $row['species'] . '</option>'; } $template->assign_vars(array( 'USERNAME' => htmlspecialchars($userdata[username]), 'REGION_FILTER' => str_replace("&", " & ", $region_filter_options), 'SPECIES_FILTER' => $species_filter_options, ) ); // Build the page $template->pparse('body'); // standard page footer include(IP_ROOT_PATH . 'includes/page_tail.'.PHP_EXT); } ?> Now my questions are these:- 1) In the .tpl file hows can i submit from any of the dropdown boxes and retain the selection after submission 2) How can i have it so that the second and third drop down boxes are only visible after the previous one, ie the first box ( date ) has to have a selection before the Region ( 2nd one ) is visible and so 3) How can i reduce the amount of code to cover all options in building a select statement or is Switch - case the best way. I would greatly appreciate help with this and even more so for some example of what i am asking for so i can learn from them. Okay, I currently have a query that is like: Code: [Select] $sql = "SELECT SQL_CALC_FOUND_ROWS a.ItemID, a.spec, a.description, a.sport, a.category, a.price, a.unit_qty, a.is_visible, a.AdminID, b.name, b.ItemID as catID, c.Name as nameSport, c.ItemID as sportID FROM purchase_request_category_item as a LEFT JOIN purchase_request_category as b ON b.itemID = a.category LEFT JOIN ro_school_sports as c ON c.ItemID = a.sport WHERE a.AdminID = '".$dist."' GROUP BY a.ItemID, b.name, c.Name ORDER BY a.ItemID DESC LIMIT ". $start.",".$limit; I am building a custom search function which I intend to be able to change the WHERE line to: WHERE a.AdminID = '".$dist."' ".$query $query will be created via if statements for every element in the form which will be called either by the button on the form, or the links in my pagination. The form would have the following: Level(s): dynamic field, Multi Select Status: Both/Enabled/Disabled single select Sports: dynamic field, Multi Select Category: dynamic field, single select. the sport column is a single selected item on the add page, its of type int(11) the levels table, is also a multi select on the add item page, and is of the type set('1','2','3','4','5','12','13') So real question is I guess, how on earth do I work with these 2 multi selects to create this search? Im going to use a large array of arrays, each of one having a lot of values and some sub arrays. My question is... is faster to use arrays or is better to have a object to acces using methods and all? i suppose objects are slower... Also i was planing in use arrays with string keys in nearly all places, normally these are slower, but in php hashes and arrays are the same tipe so i dont know... Hi I would like to know how to make a link select certain stuff from the database. For example the link called cape town with the id 11 is selected then the link must search through the events table for the city_id 11 and take that information and display it in another page. I would like to know how to do that or can you let me know of place that I can look. I have tried google but I don't know how to put what I'm looking for in words Hi guys, thank for any help. This does not appear like a difficult problem but it has me stymied. I have several selects, the 1st item of each is "--Specify--" so that the user has to specifically choose an item. Once an item is selected on any select the others should be set to the 1st item or "--Specify--" to avoid confusion. If you want to see the complete code it on this web page: http://chordsandguitar.com/progressions-test.php The relevant code is: Code: [Select] if ($prog1_menu != "--Specify1--") { $prog2_menu = "--Specify--"; echo "set prog2 to Specify"; } if ($prog2_menu != "--Specify2--") { $prog1_menu = "--Specify--"; echo "set prog1 to Specify"; } echo "<form action = \"progressions-test.php?s_sort_all=$sort_all&s_prog1_menu=$prog1_menu&s_prog2_menu=$prog2_menu&s_prog1_trigged=$prog1_trigged&s_prog2_trigged=$prog2_trigged\" method = \"GET\">"; //also stores the values of the drop-downs when Select button is pushed so they can be retrieved when the web page is loaded again echo " Ascending bass line"; echo "<br />"; echo "Select a progression: <a href=\"http://www.chordsandguitar.com/progressions-test.php?s_sort_all=All&s_prog1_menu=--Select--&s_prog2_menu=--Select--\"\"><big>ALL</big></a>"; echo " "; echo "<select name=\"prog1_menu\" onchange=\"this.form.submit();\">"; echo "<option value=\"--Specify1--\">--Specify--</option>"; if ($prog1_menu == "ii-iii-IV-V progression") echo "<option SELECTED value=\"ii-iii-IV-V progression\">ii-iii-IV-V progression</option>"; else echo "<option value=\"ii-iii-IV-V progression\">ii-iii-IV-V progression</option>"; if ($prog1_menu == "I-ii-IV-V progression") echo "<option SELECTED value=\"I-ii-IV-V progression\">I-ii-IV-V progression</option>"; else echo "<option value=\"I-ii-IV-V progression\">I-ii-IV-V progression</option>"; if ($prog1_menu == "I-iii-IV-V progression") echo "<option SELECTED value=\"I-iii-IV-V progression\">I-iii-IV-V progression</option>"; else echo "<option value=\"I-iii-IV-V progression\">I-iii-IV-V progression</option>"; echo "</select>"; echo " "; echo "<select name=\"prog2_menu\" onchange=\"this.form.submit();\">"; echo "<option value=\"--Specify2--\">--Specify--</option>"; if ($prog2_menu == "I-sharpIo7-ii7-sharpIIo7 progression") echo "<option SELECTED value=\"I-sharpIo7-ii7-sharpIIo7 progression\">I-#Io7-ii7-#IIo7 progression</option>"; else echo "<option value=\"I-sharpIo7-ii7-sharpIIo7 progression\">I-#Io7-ii7-#IIo7 progression</option>"; if ($prog2_menu == "I-sharpIo7-ii7-V7 progression") echo "<option SELECTED value=\"I-sharpIo7-ii7-V7 progression\">I-#Io7-ii7-V7 progression</option>"; else echo "<option value=\"I-sharpIo7-ii7-V7 progression\">I-#Io7-ii7-V7 progression</option>"; echo "</select>"; echo "</form>"; I am trying to identify which select box has been used so that I can set all the other boxes to the 1st item but I wind up re-setting all the boxes. Cheers, Jacques Hi,
I would like to store sql query strings in a database. A query might look this like:
SELECT * from ListTable where id = '$id'
The idea would be to retrieve the string and run it as a SQL string. $id would be defined in the procedure so the idea is that when I send the SQL String through, $id would be replaced with its value and everything would work.
Under normal circumstances, the code would look like this:
$id = "somevalue"; $lcSQL = "SELECT * from ListTable where id = '$id'";I would pass the SQL select off to mySQL and all is right with the world. I just want to store the string in a database, retreieve it and use it. Thanks for your help in advance... I am very new to PHP and have tried various techniques but I am getting a 500 error when clicking on the export button to download a csv report. I'm not sure why the previous developer did it this way. Is there a better why in PHP to make this code better? Willing to understand and learn from an PHP expert. The database is MYSQL.
$coursefilterid = $_GET['course'];
$conn = new mysqli($host, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sqluserenrolled = "select mdl_user.username, mdl_user_enrolments.userid as enrolleduserid, mdl_enrol.courseid from mdl_user_enrolments
Inner Join
mdl_enrol
on
mdl_enrol.id = mdl_user_enrolments.enrolid
Inner Join
mdl_user
on
mdl_user.id = mdl_user_enrolments.userid
where
mdl_enrol.courseid = '" . $coursefilterid . "'
order by mdl_user.username
";
$queryenrolleduser = mysqli_query($conn, $sqluserenrolled);
?>
<html>
<head>
</head>
<body>
<form method="post"
action="<?php echo "userlistssiexport.php?id=$coursefilterid"?>">
<input type="hidden" name="exportcourseid"
value="<?php echo $coursefilterid;?>"> <input type="hidden"
name="sessid" value="<?php echo $USER->sesskey;?>"> <input
class="btn btn-primary" type="submit" name="submit"
value="<?php echo "Export";?>">
</form>
<?php
$noteid = "";
$cmId = "";
?>
<table class="data-table">
<caption class="title">User info</caption>
<thead>
<tr>
<th>Username</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Email</th>
<th>Last login</th>
<th>Createddate</th>
<th>Position</th>
<th>Organization</th>
<th>Certificate Request Date</th>
<th>Role1</th>
<th>Role2</th>
<th>Role3</th>
</tr>
</thead>
<tbody>
<?php
while ($row = mysqli_fetch_array($queryenrolleduser)) {
$enrolleduserid = $row['enrolleduserid'];
$sql = "select mdl_user.username as username,
mdl_user.firstname as firstname,
mdl_user.lastname as lastname,
mdl_user.email as email,
mdl_user.lastlogin as lastaccess,
mdl_user.timecreated as createddate,
mdl_user_info_data.data as position
from mdl_user
Inner Join
mdl_user_info_data
on
mdl_user_info_data.userid = mdl_user.id
Inner Join
mdl_user_info_field
on
mdl_user_info_field.id = mdl_user_info_data.fieldid
Inner Join
mdl_user_lastaccess
on
mdl_user_lastaccess.userid = mdl_user.id
where mdl_user_info_field.id = 1
and
mdl_user.deleted = 0
and
mdl_user.id = '" . $enrolleduserid . "'
group by mdl_user.username
order by mdl_user.username
";
$query = mysqli_query($conn, $sql);
if (! $query) {
die('SQL Error: ' . mysqli_error($conn));
} else {}
?>
<?php
$no = 1;
$total = 0;
$username = '';
$coursename = '';
$content = '';
$modulename = '';
$organization = '';
$userid = '';
$certificatedate = '';
$userrole = '';
$enrolleduserid = '';
while ($row = mysqli_fetch_array($query)) {
// Do something here
$username = $row['username'];
$coursename = $row['coursename'];
$content = $row['content'];
$noteid = $row['noteid'];
// $notedatetime = date("d/m/y g:i (A)", $row['notedate']);
$notedatetime = date("D M j Y G:i A", $row['notedate']);
$lastaccess = date("D M j Y G:i A", $row['lastaccess']);
$createddate = date("D M j Y G:i A", $row['createddate']);
$datafile = $username . $coursename . $content;
echo '<tr>
<td>' . $row['username'] . '</td>
<td>' . $row['firstname'] . '</td>
<td>' . $row['lastname'] . '</td>
<td>' . $row['email'] . '</td>
<td>' . $lastaccess . '</td>
<td>' . $createddate . '</td>
<td>' . $row['position'] . '</td>
';
$modid = $row['contextid'];
// Get module name
$sqlmodule = "select mdl_user.username as username,
mdl_user.firstname as firstname,
mdl_user.lastname as lastname,
mdl_user.email as email,
FROM_UNIXTIME(mdl_user_lastaccess.timeaccess) as lastaccess,
FROM_UNIXTIME(mdl_user.timecreated) as createddate,
mdl_user_info_data.data as organization
from mdl_user
Inner Join
mdl_user_info_data
on
mdl_user_info_data.userid = mdl_user.id
Inner Join
mdl_user_info_field
on
mdl_user_info_field.id = mdl_user_info_data.fieldid
Inner Join
mdl_user_lastaccess
on
mdl_user_lastaccess.userid = mdl_user.id
where mdl_user_info_field.id = 3
and
mdl_user.deleted = 0
and
mdl_user.username ='" . $username . "'";
$querymodule = mysqli_query($conn, $sqlmodule);
?>
<?php
$modulenamelink = "";
while ($row = mysqli_fetch_array($querymodule)) {
$organization = $row['organization'];
}
echo '<td>' . $organization . '</td>';
$sqlCertificateDateuid = "select id from mdl_user where username = '" . $username . "'";
$queryCertificateDateuid = mysqli_query($conn, $sqlCertificateDateuid);
while ($row = mysqli_fetch_array($queryCertificateDateuid)) {
$userid = $row['id'];
}
$sqlcertificatedate = "select * from mdl_certificateemail where userid = '" . $userid . "'
and courseid = '" . $coursefilterid . "'";
$querycertificaterequestdate = mysqli_query($conn, $sqlcertificatedate);
while ($row = mysqli_fetch_array($querycertificaterequestdate)) {
$certificatedate = date("D M j Y g:i:s A", $row['unixdatetimecertificate']);
}
echo '<td>' . $certificatedate . '</td>';
$sqluserrole = "select mdl_role_assignments.userid, mdl_role_assignments.roleid,mdl_course_modules.course, mdl_role.shortname as rolename,FROM_UNIXTIME(mdl_role_assignments.timemodified) from mdl_role_assignments
Inner Join
mdl_context
on
mdl_context.id = mdl_role_assignments.contextid
Inner Join
mdl_course_modules
on
mdl_course_modules.instance = mdl_context.instanceid
Inner Join
mdl_role
on
mdl_role.id = mdl_role_assignments.roleid
where mdl_course_modules.course = '" . $coursefilterid . "'
and
mdl_role_assignments.userid = '" . $userid . "'
group by
mdl_role_assignments.userid,
mdl_role_assignments.roleid,
mdl_course_modules.course,
mdl_role.shortname,
mdl_role_assignments.timemodified
order by mdl_role_assignments.timemodified
";
$userlistrole = '';
$queryuserrole = mysqli_query($conn, $sqluserrole);
while ($row = mysqli_fetch_array($queryuserrole)) {
$userrole = $row['rolename'];
$userlistrole = array(
array(
$userrole
)
);
// echo '<td>'.$userrole.'</td>';
}
foreach ($userlistrole as $listrole) {
// echo $listrole;
}
$teacherrole = array(
'student'
);
foreach ($teacherrole as $rolename) {
$role = $DB->get_record('role', array(
'shortname' => $rolename
));
$context = get_context_instance(CONTEXT_COURSE, $coursefilterid);
// $context = context_course::instance($cid1);
$teachers = get_role_users($role->id, $context);
foreach ($teachers as $teacher) {
$teacherid = $teacher->id;
if ($teacherid == $userid) {
echo '<td>student</td>';
}
}
}
$teacherrole = array(
'editingteacher'
);
foreach ($teacherrole as $rolename) {
$role = $DB->get_record('role', array(
'shortname' => $rolename
));
$context = get_context_instance(CONTEXT_COURSE, $coursefilterid);
// $context = context_course::instance($cid1);
$teachers = get_role_users($role->id, $context);
foreach ($teachers as $teacher) {
$teacherid = $teacher->id;
if ($teacherid == $userid) {
echo '<td></td>';
echo '<td>editingteacher</td>';
}
}
}
$teacherrole = array(
'manager'
);
foreach ($teacherrole as $rolename) {
$role = $DB->get_record('role', array(
'shortname' => $rolename
));
$context = get_context_instance(CONTEXT_COURSE, $coursefilterid);
// $context = context_course::instance($cid1);
$teachers = get_role_users($role->id, $context);
foreach ($teachers as $teacher) {
$teacherid = $teacher->id;
if ($teacherid == $userid) {
echo '<td>manager</td>';
}
}
}
echo '</tr>';
}
}
?>
</tbody>
<tfoot>
</tfoot>
</table>
</body>
</html>
<?php
}
}
else {
header("Location:/index.php");
// echo "something";
die();
}
}
else {
header("Location:/index.php");
die();
}
?>
A few questions that if I was more knowledgeable about MySQL (such as the query log that I've heard of). - Does mysqli::multi_query() make multiple requests to the server or just one? - If it is just splitting the queries on the semicolon and then making multiple requests, is it still faster than looping and querying in PHP? - Does it take less memory than, lets say, mysql_query() foreach query? - Is it faster than, lets say again, mysql_query() looped? The last 2 I can probably just benchmark myself but I'm hoping someone will know off had. Background (blah blah blah) I'm working on a lean rapid development framework for myself (and maybe others eventually) to use. Much like Cake and other frameworks it does a lot of queries, often times more then you need. The sacrifice of course is performance VS ease of development, but I'm trying to not make a martyr out of my framework :-) I've observed on many occasions that simple queries can take more time than a complex query on seemingly random occasions. My understanding is that this is because of the connection, not the real amount of time it took for the server to query. I figure if I avoid multiple requests to the server I will cut down on the time everything takes. My theory is based on the same concept of combining all your CSS and JS files into single files to avoid dozens of HTTP requests which slow down page load time. I have the following simple code to test against collision on a primary key I am creating: Code: [Select] $machine_ids = array(); for($i = 0; $i < 100000; $i++) { //Generate machine id returns a 15 character alphanumeric string $mid = Functions::generate_machine_id(); if(in_array($mid, $machine_ids)) { die("Collision!"); } else { $machine_ids[] = $mid; } } die("Success!"); Any idea why this is taking minutes to run? Anyway to speed it up? Alright guys, I see people recommending prepared statements (PDO/MySQLi) and saying that they are way to go these days. However upon doing a bit more research, I've found that prepared statements, PDO in particular, is lacking in terms of performance, especially using SELECT statements. Now I'm starting a new project, which is basically a text based game with lots of queries and DB interaction, so I'm really interested in knowing what's the best approach for me. I was leaning towards PDO but I don't want it crawling my server under heavy load. I appreciate any advices or first hand experiences on this. I am wondering since in php when you write string in " " quotes php will look if there is any variable and if it is it will read that variable and replace variable name with that value inside the string. However when i use ' ' quotes php will not look for any variables inside that string. So my question is when you write a really big application is it good to always use ' ' quotes when you can instead of " " ones. Does that have an impact on performance. Thanks I want to do a test while refreshing the page 100 times with 500 rows in a table, and then without 500 rows in a table and with different kind of php code, i need to do some type of testing to get results back in to show which way is faster for mysql/php. Any idea how to do this any scripts out there or a built in php/mysql function? Thanks Do I really have to care about the amount of spaces/tabs I have in my program? Is my code good? Example: Code: [Select] class SportListout{ public $start; public $db; private $junk = 'This is junk data. Unable to find it \'s parents. It is recommended that you delete this data.'; function __construct($db){ $this -> db = $db; $this -> setStartingParent(); } function searchListout($sp){ $notif = 'There are no search results that match that criteria.'; if(strlen($sp) > 0){ if(strlen($sp) < 3){ This is my common text format. Is this ok or should I not make spaces in between functions? Hello folks, So, not sure where to start, but I'm wondering if it's at all possible to change a prepared statement / sql based on whether or not we're displaying for desktops, or for smartphone/tablets? I'm not sure what to start looking at since I'm pretty new in PHP and all... I've managed to figure out how to change CSS if I'm on phone
@media only screen and (max-width: 767.98px){
// PUT CSS FOR PHONES...
}
Any directions on how I would change my SQL, would be awesome! I'd like to narrow down the columns returned if I'm on phone, otherwise breaks my pages looks too much... Much thanks in advance! Pat
I do not have code yet, i just want a general idea how to get started.
I have a database with +/- 15 columns. 6 people are using this database. Not all columns are relevant for each user.
So my idea was that each user will be able to make his preference by checking checkboxes for each column -> store this in the database so that when he logs in , his preference will be retrieved and used to make the right query.
second question, how do i make the headers so that they are the same als the preferences from the user ?
Just put me on the right track (or if someone has it on the shelf, post the code)
thanks in advance
Hi i have this drop down list for date which contain 3 selects DAY MONTH YEAR hwo can i make so that when update form select keeps the same value has before help please <?php $months = array('','January','February','March','April','May','June','July','August','September','October','November','December'); echo '<select name="month_of_birth">'; for ($i=1;$i<13;++$i) { echo '<option value="' . sprintf("%02d",$i) . '">' . $months[$i] . '</option>'; } echo '</select>'; echo '<select name="day_of_birth">'; for ($i=1;$i<32;++$i) { echo '<option value="' . sprintf("%02d",$i) . '">' . $i . '</option>'; } echo '</select>'; echo '<select name="year_of_birth">'; $year = date("Y"); for ($i = $year;$i > $year-50;$i--) { $s = ($i == $year)?' selected':''; echo '<option value="' . $i . '" ' . $s . '>' . $i . '</option>'; } echo '</select>'; ?> |
|||||||