Neil,<br><br>It is clear you like vtiger, I have one question, what made your company choose Sugar instead of vtiger?, so sooner someone can make vtiger offer such things.<br><br>Regards,<br><br>Jorge<br><br><div><span class="gmail_quote">
On 6/10/07, <b class="gmail_sendername">Neil Temperley</b> <<a href="mailto:neil.temperley@exemail.com.au">neil.temperley@exemail.com.au</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Dear Developers,<br><br>In the end my company went with Sugar, but I still keep an eye on vtiger<br>because I love you guys :-).<br><br>Anyway here is an idea that I will post on a Sugar forum, but I'd thought<br>I'd post it here first so that if you choose to adopt it you can't be
<br>accused of copying :-). While I haven't checked the vtiger code for a while<br>I suspect this issue is applicable to you too.<br><br>-----------------------------------<br><br>Storage of Multi-Select Items for Quicker MYSQL Searching
<br><br>Consider a custom multi-select field with items:<br>cat<br>dog<br>horse<br>bobcat<br>cattle<br><br>An example of a selection of the above is stored in the database as a string<br>like this (ignore the ""):
<br>"cat#,#horse#,#bobcat#,#cattle"<br><br>And if "cat" is the only item the string saved looks like:<br>"cat"<br><br>This storage scheme has some problems when it comes to doing an SQL searches
<br>on strings formatted this way. To search for uniquely for "cat" we have to<br>search for four different possibilities: (The MYSQL search string is shown<br>in ""):<br><br>1) cat at the beginning of the multi-select database string, "cat#%",
<br>2) cat in the middle, "%#cat#%",<br>3) cat at the end, "%#cat", and finally,<br>4) cat by itself, "cat".<br><br>If there are 1000 records and none have "cat" selected, the search needs to
<br>check all four conditions on each record before rejecting each as 'not<br>matching'.<br><br>However if we choose to store the selection differently we only need to<br>search for one match not four matches per record.
<br><br>Choosing '#' as the bounding character, we store the multi-selected items as<br>a string like this instead (ignore the ""):<br>"#cat##horse##bobcat##cattle#"<br><br>And if "cat" is the only item the string saved looks like:
<br>"#cat#"<br><br>The advantages of this system are:<br><br>* Now the one MYSQL search "%#cat#%" will match "cat" regardless of where it<br>is in the string or whether it is the only item.<br>
<br>* A side benefit is that is possible to delete (or replace) the "cat" item<br>using a simple PHP string operation, e.g.<br>str_replace( "#cat#", "", "#cat##horse##bobcat##cattle#" );
<br><br>* Also MYSQL searches are simpler -- if/when you ever need to do a search<br>using a database admin tool.<br><br>Note that to search for 'no selection' you need to check for ## or an empty<br>string.<br><br>
I suggest the best way to implement the change is with dedicated explode()<br>and implode() functions and using a minimum of hardwired strings. Examples<br>are given below.<br><br>that's all,<br>regards and good luck,
<br>Neil<br><br><br>Sample Code...<br><br>/* ########################################################## */<br>define("MULTIPLE_SELECT_BOUNDING_CHAR", '#');<br>define("MULTIPLE_SELECT_BOUNDING_CHAR_RE", '\#'); // Char for regexps.
<br><br>/**<br> * Formats a string of multi-select items for saving to a database.<br> *<br> * @param array $multiple_select_array<br> * @return string formatted multi-select string<br> *<br> */<br>function MultipleSelectImplode( $multiple_select_array ) {
<br> $bounding_char = MULTIPLE_SELECT_BOUNDING_CHAR;<br> return $bounding_char . implode("$bounding_char$bounding_char",<br>$multiple_select_array) . $bounding_char;<br>}<br><br>/**<br> * Splits a formatted string of multi-select items into an
<br> array. Returns an empty array if there are no multi-select items.<br> *<br> * @param string $multiple_select_string<br> * @return array multi-select array<br> *<br> */<br>function MultipleSelectExplode( $multiple_select_string ) {
<br> $bounding_char = MULTIPLE_SELECT_BOUNDING_CHAR;<br> $bounding_char_re = MULTIPLE_SELECT_BOUNDING_CHAR_RE; // Char for<br>regexps<br> // Remove $bounding_char at each end of the string:<br> $trimmed_string = preg_replace( array("/^\s*$bounding_char_re/",
<br>"/$bounding_char_re\s*$/"), '',<br> $multiple_select_string );<br> if ( empty($trimmed_string) ) {<br> // (An explode() here would return a one element array with empty
<br>element):<br> return array();<br> }<br> return explode("$bounding_char$bounding_char", $trimmed_string);<br>}<br>/* ########################################################## */<br><br>_______________________________________________
<br>Reach hundreds of potential candidates - <a href="http://jobs.vtiger.com">http://jobs.vtiger.com</a><br></blockquote></div><br>