phdphd

Hi All, I do not understand why the following stored procedure does not work. DELIMITER $$ DROP PROCEDURE IF EXISTS `proc` $$ CREATE PROCEDURE `proc` ( ) BEGIN DECLARE result LONGTEXT; SET SESSION group_concat_max_len =4294967295; SET result = (SELECT GROUP_CONCAT( DISTINCT ids ORDER BY ids ASC SEPARATOR ',' ) FROM table1); INSERT INTO `table2 ` (`id`, `concat`) VALUES (NULL, result); END $$ DELIMITER ; When I run it, I get no error, but the insert instruction seems ignored. This insert instruction bears a variable, but even though I use a hard-coded value in it instead of the "result" variable, it still gets ignored (whereas it would work if executed alone). Please note that when executed alone the SELECT GROUP_CONCAT instruction DOES return a concatenation. Thanks for your help!

Yes, the ID column is the primary key, and it is the only one there.

I noticed that PhpMyAdmin reports a processing time of 0.0246 sec. But actually it displays the first set of rows only after more or less 15 seconds. The Explain reports the following : id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE ma_table ALL PRIMARY NULL NULL NULL 8211 Using where (If I run the same query without the IN clause, both the processing and the rendering are immediate.)

Let me rephrase the context of my issue. I have a PHP processing that ends with the building of a series of ids. Then there is a SQL query based on this series of ids, that I run against a table with an Id column, in order to get all the data in rows matching the ids provided.

An index is already created. As for "self-joining", this approach seems suggested here : http://stackoverflow.com/questions/10023117/select-where-in-subquery-slow

Hi All, I use a query that gets rows according to specified ids. The typical syntax of the query is as follows: SELECT * FROM table WHERE id IN(1,2,3,....,100,...) The query is very slow (more than 10 seconds) if the IN clause contains thousands of ids. (The same query without the IN clause runs instantaneously.) Note that the number of ids is random and depends of choices from the user. I read here and there than using an "inner join" might be a solution. I would need some help in converting this query into an inner join-based query. Thanks a lot.

Thank you very much mac_gyver!

The code producing the $dates_event arrays looks as follows : $ids = array(); $date_b = array(); $date_f = array(); while($tab = mysql_fetch_assoc($rs)) { array_push($ids, $tab['ids']); array_push($date_b, $tab['event_start_date']); array_push($date_f, $tab['event_end_date']); } $dates_event = array(); $dates_event = array( 'id_event' => $ids, 'date_b' => $date_b, 'date_f' => $date_f, ); And here is a sample of the data in the $dates_event array: Array ( [id_event] => Array ( [0] => 7 [1] => 10 [2] => 11 [3] => 12 [4] => 14 [5] => 15 .... [8280] => 8605 [8281] => 8606 ) [date_b] => Array ( [0] => 2013-02-13 [1] => 2012-12-09 [2] => 2012-12-09 [3] => 2012-12-09 [4] => 2012-12-11 [5] => 2012-12-13 ... [8280] => 2013-06-26 [8281] => 2013-06-26 ) [date_f] => Array ( [0] => 2014-01-02 [1] => 2013-12-31 [2] => 2013-12-31 [3] => 2013-12-31 [4] => 2014-01-12 [5] => 2013-12-31 [8280] => 2013-09-24 [8281] => 2013-09-24 ) ) Thanks again.

I query lines where end dates are greater than or equal to today (to ignore finished events) and where start dates are lower than today + 30 (to ignore events that start more than 30 days from today) event_end_date >= CURDATE() and event_start_date<(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) This way I have all (but only) event Ids for events occurring during at least one day from today to today+30. However I think the issue is not the number of IDs retreived. Here is a code you can play with, that leads to the creation of an multidimensional array of more o less the same size as the one mentioned in my first message, but with different results in terms of processing time and memory consumption. $limit = 250000; error_reporting(-1); $time = microtime(TRUE); $mem = memory_get_usage(); // we create a huge array $arr = array(); for($i=0; $i<=$limit; $i++) { $arr['a'][]= 'blablabla'; $arr['b'][]= 'blablabla'; } echo '250000 loop results (adapted from http://gonzalo123.com/2011/08/15/checking-the-performance-reading-arrays-with-php/)<br />'; print_r(array('memory' => (memory_get_usage() - $mem) / (1024 * 1024), 'microtime' => microtime(TRUE) - $time)); echo '<br />COUNT_RECURSIVE :' .count($arr, COUNT_RECURSIVE); Results : 250000 loop results (adapted from http://gonzalo123.com/2011/08/15/checking-the-performance-reading-arrays-with-php/) Array ( [memory] => 51.591873168945 [microtime] => 0.54914712905884 ) COUNT_RECURSIVE :500004 Here is another example $limit = 250000; error_reporting(-1); $time = microtime(TRUE); $mem = memory_get_usage(); // we create a huge array $arr = array(); for($i=0; $i<=$limit; $i++) { $arr['a'][]= 'blablabla'; $arr['b'][][]= 'blablabla'; } echo '250000 loop results (adapted from http://gonzalo123.com/2011/08/15/checking-the-performance-reading-arrays-with-php/)<br />'; print_r(array('memory' => (memory_get_usage() - $mem) / (1024 * 1024), 'microtime' => microtime(TRUE) - $time)); echo '<br />COUNT_RECURSIVE :' .count($arr, COUNT_RECURSIVE); Results : 250000 loop results (adapted from http://gonzalo123.com/2011/08/15/checking-the-performance-reading-arrays-with-php/) Array ( [memory] => 91.647163391113 [microtime] => 0.49137902259827 ) COUNT_RECURSIVE :750005 As you can see that the mere fact of adding/removing subarrays In the for loop will change the results. And if you place the for loop in another for loop, you will get a Fatal error: Allowed memory size of 134217728 bytes exhausted message. This is why I think the issue is linked to the loops nesting and/or to the dimension of the arrays.

I am 99% convinced that the combination of foreach+while loops and/or the use of mutidimensional arrays are responsible for the time elapsed. The problem is I do not know about any other alternative to using these in order to obtain the same results.

$pattern_preg_match="#^[A-Z][0-9][A-Z] [0-9][A-Z][0-9]$#"; if (preg_match($pattern_preg_match, $_POST['postal'])).... Does this help ?

Hi All, I am facing a big challenge right now. My website will let people look for events from a variety of criteria, one of them being the date of the events among the next 30 coming days from a tabular-looking calendar. It works very well but I noticed that from the user's point of view it takes too much time to build the calendar if there are hundreds of events in the DB. After retrieving the data (ID events, start and end dates) from the events table, my PHP code does a series of operations, the first one being building a global array containing -for each event- two subarrays : one for the date range and another one for the corresponding days of week. To get a better idea of which points in my code the processing was particularly slow at, I ran just the code portion that builds the global array. It turns out that the building of the global array accounts for 95% of the whole processing time. Every operation before (including retrieval of the data) or after (including processing the created array, and displaying the calendar) is done quickly. Below is the PHP code that builds the global array and tests time and memory usage. The 3 arrays $dates_event['id_event'], $dates_event['date_b'] and $dates_event['date_f'] store the event ids, event start dates and event end dates retreived from the DB. The testing spans more than 8.000 events and shows the following results : Array ( [memory] => 87.805824279785 [microtime] => 19.967195987701 ) Number of values in the final global array : 537404 As you can see, memory consumption and -above all- time are pretty high. Does anyone have an idea about how I could significantly reduce both figures ? Thank you for your help ! $time = microtime(TRUE); $mem = memory_get_usage(); $global_array = array(); $today=date('Y-m-d'); $todayonemonth = strtotime(date("Y-m-d", strtotime($today)) . " +1 month"); $todayonemonth = date ("Y-m-d", $todayonemonth); $step = '+1 day'; $format1 = 'd/m/Y'; $format2 = 'w'; foreach ($dates_event['id_event'] as $k=>$v) { if ($dates_event['date_b'][$k]<$today) { $first=$today; } else { $first=$dates_event['date_b'][$k]; } if ($dates_event['date_f'][$k]<=$todayonemonth) { $last=$dates_event['date_f'][$k]; } else { $last=$todayonemonth; } $current = strtotime($first); $last = strtotime($last); $global_array[$v]['dates'] = array(); $global_array[$v]['days'] = array(); while($current <= $last) { $global_array[$v]['dates'][]= date($format1, $current); $global_array[$v]['days'][] = date($format2, $current); $current = strtotime($step, $current); } } print_r(array('memory' => (memory_get_usage() - $mem) / (1024 * 1024), 'microtime' => microtime(TRUE) - $time)); echo '<br />Number of values in the final global array :' .count($global_array, COUNT_RECURSIVE);

Thanks to both of you for your quick & detailed answers, and a special thanks to you Xaotique for saving me much coding time.

Hi All, I have a list of countries, with a sublist of cities associated with each country. There is also an associate + or - sign associated with each country name. When I click the minus sign, the associated list closes and the minus sign converts to plus sign. The reverse works also. I also have a global "-" sign that when clicked collapses all the data, leaving just the country names displayed, and converts to a plus sign. This will enable the user to scroll down the data until the desired country without having to see the cities of countries that are higher in the list. The reverse works also : when the global plus sign is clicked, all data are displayed. My problem is the following. If I click the global minus sign, I would like any minus signs associated with countries to appear as "+" signs. And if I click the global plus sign, I would like any plus signs associated with countries to appear as "-" signs. Here is jsfiddle for your convience : http://jsfiddle.net/qX6k8/ Thanks a lot you for your help.

thanks for your reply requinix.

Hi All, (I apologize if I did not publish this post in the right section of the forum) The index.html page of my site will be organized into tabs. One of them will contain an introduction to the web site, with as much descriptive information as possible for referencing purposes in search engines. Another tab will contain a search section enabling users to look for events. Since users -even users that never visited the site before- will probably want to make use of this search section without reading the introduction first, the tab containing the search section will be the default tab that displays on connecting to the site. Does this approach negatively affect the referencing of the site in search engines ? Thanks.

Thanks a lot Requinix. This is very, very impressive, at least for me ;-)

Hi All, I have been unsuccessfully trying to find a solution to delete a value from a multidimensional array. I found some tips here and there (e.g. http://stackoverflow.com/questions/4466159/delete-element-from-multidimensional-array-based-on-value), but was not able to adapt it to my case. Basically, let say we have this initial print_r : Array ( [v0] => Array ( [w1] => Array ( [x2] => Array ( [y3] => Array ( [0] => hello [1] => world ) ) ) ) ) I want to find the value "world", and delete it so that the array looks like this : Array ( [v0] => Array ( [w1] => Array ( [x2] => Array ( [y3] => Array ( [0] => hello ) ) ) ) ) The data initially known are the array name and the value to look for. Note that a given sub-array will contain only one sub-array or only one or more values. Thanks for your help !

Thank you all.

Hi Kicken, Thank you for your quick reply and solution. However, as shown below I enclosed the div section between the html "body" tags to make P a child of Body and it still does not work. <html> <head> <title>Untitled</title> <style type="text/css"> .has-js .hide-if-js { display: none; } </style> <script type="text/javascript"> //Use jquery to run a function on dom ready and add the has-js class. $(function(){ $('body').addClass('has-js'); }); </script> </head> <body> <div> <h1>Some title here</h1> <p class="hide-if-js">blah blah blah</p> </div> </body> </html> I am quite new to JS. There must be something obvioulsy wrong/missing to gurus that I am still not aware of . I would much appreciate your help again. Thanks.

Here is the complete code. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" /> <title>Display/Hide div in javascript</title> <script> function display_hide(id) { if(document.getElementById(id).style.display=="block") { document.getElementById(id).style.display="none"; document.getElementById('button_'+id).value='+'; } else { document.getElementById(id).style.display="block"; document.getElementById('button_'+id).value='-'; } return true; } </script> <noscript><style>donotdisplay { display:none; }</style></noscript> </head> <body> <br />Title1<donotdisplay> <input type="button" id="button_text1" onclick="javascript:display_hide('text1');" value="+" /></donotdisplay><div style="display: none" id="text1" > Detailed info related to title1</div><br />Title2<donotdisplay> <input type="button" id="button_text2" onclick="javascript:display_hide('text2');" value="+" /></donotdisplay><div style="display: none" id="text2" > Detailed info related to title2</div><br />Title3<donotdisplay> <input type="button" id="button_text3" onclick="javascript:display_hide('text3');" value="+" /></donotdisplay><div style="display: none" id="text3" > Detailed info related to title3</div><br />Title4<donotdisplay> <input type="button" id="button_text4" onclick="javascript:display_hide('text4');" value="+" /></donotdisplay><div style="display: none" id="text4" > Detailed info related to title4</div> </body> </html> I tried to use a <noscript></noscript> tag with some php in it to clear style="display: none" but this does not work. Thanks

Hi All, I have a series of titles to which detailed info is associated, as well as a button that allows to display this info, then to hide it. By default, detailed info is hidden. The problem is that if javascript is disabled, only the titles display, with no means for the user to display detailed info. I would like detailed info to display by default if JS is not enabled on the user's PC. I unsuccessfully tried to find a way to automatically modify the 'display:none' setting of the div section of the detailed info when JS is disabled. Thanks for you help.

Thank you Barand, that is just perfect !

Hi All, I found a statement in a forum that enables to send an array via mail. mail($to, $subject, print_r($array, 1), $headers); The problem is that all the key/value pairs appear in a single line. I would like them to appear in seperate lines. (One solution could be adding a '<br />' at the end of each value, but I do not know how to do that with multidimensional arrays.) Thanks.

Hi All, After reading over your answers, I got to the following solution, that tests both export to db and import from db. Hope it helps other newbies like me . if (isset($_POST['user_input'])) { $user_input=$_POST['user_input']; $link = @mysql_connect('localhost', 'root', ''); $user_input=mysql_real_escape_string ($user_input); mysql_select_db('textareachl', $link); $insert_textarea_into_db_field='INSERT INTO `textareachl`.`textarea` (`text`) VALUES (\''.$user_input.'\')'; $rs = mysql_query($insert_textarea_into_db_field, $link); $sql = 'SELECT * FROM textarea Where text="'.$user_input.'"'; $rs = mysql_query($sql, $link); $arr = mysql_fetch_array($rs); $txt =$arr['text']; echo '<br />Retreiving from DB into textarea '; echo '<br /><textarea name="text" cols="100" rows="15" >'.$txt.'</textarea>'; } Thanks again to all of you.