kickstart

Hi Yes, but only dead 6 months. And to correct a minor bug with a useful function that people are likely to find should they google for such a solution All the best Keith

Hi Just a note to say that there is a minor bug in the above function. It checks for a key being false in the array of rules (to cope with a string ending ss), yet it needs to check for the value instead. Minor change to correct this <?php function depluralize($word){ // Here is the list of rules. To add a scenario, // Add the plural ending as the key and the singular // ending as the value for that key. This could be // turned into a preg_replace and probably will be // eventually, but for now, this is what it is. // // Note: The first rule has a value of false since // we don't want to mess with words that end with // double 's'. We normally wouldn't have to create // rules for words we don't want to mess with, but // the last rule (s) would catch double (ss) words // if we didn't stop before it got to that rule. $rules = array( 'ss' => false, 'os' => 'o', 'ies' => 'y', 'xes' => 'x', 'oes' => 'o', 'ies' => 'y', 'ves' => 'f', 's' => ''); // Loop through all the rules and do the replacement. foreach($rules as $key=>$value){ // If the end of the word doesn't match the key, // it's not a candidate for replacement. Move on // to the next plural ending. if(substr($word, (strlen($key) * -1)) != $key) continue; // If the value of the key is false, stop looping // and return the original version of the word. if($value === false) return $word; // We've made it this far, so we can do the // replacement. return substr($word, 0, strlen($word) - strlen($key)) . $rules[$key]; } return $word; } ?> Useful function but this minor issue caught me out. All the best Keith

Hi I do want to be able to sort them, but it is also useful to see how it is rating matches. Problem appears to be that match assumes a hyphen separates words. Also it ignores words less than 4 characters long so D-LINK and TP-LINK are taken as being the same. All the best Keith

Hi Not 100% sure on what you want, but think something like this:- SELECT b.itemID, b.price, c.MinPrice FROM tracked_item a INNER JOIN item_detail b ON a.user_item_ = b.itemID INNER JOIN (SELECT track_item_id, MIN(price) AS MinPrice FROM item_detail GROUP BY track_item_id) c ON a.id = c.track_item_id All the best Keith

Hi I have a full text search I am trying to use to search against a string of important search terms. However the ranking of the results is a bit strange. For example, search for "d-link router" against this column it is bringing back a fair few rows, but ranks a row containing tp-link but not d-link higher than one that contains d-link. If, this row is ranked 9.4198112487793 Routers-and-Switches TP-Link TL-MR3220 TP-TL-MR3220 ROUTER tlw&tlw tlwAVtlw BUNDLE tlw3Gtlw N-LITE ADSL ROUTER tlw&tlw tlw1YRtlw BULLGUARD tlwAVtlw TP-LINK TP-Link TL-MR3220 3G/3.75G 150Mbps Wireless Lite tlwNtlw Router 6935364051501 while this row is ranked 8.55044555664062 Routers-and-Switches D-Link DSL-2680/UK DL-DSL-2680 D-LINK ADSL ROUTER WIRELESS tlwNtlw tlw150tlw ADSL2+ ROUTER DLINK D-Link DSL-2680 Wireless tlwNtlw tlw150tlw ADSL2+ Modem Router 790069334535 The match statement is as follows:- SELECT item_keyword_search, MATCH (item_keyword_search) AGAINST ('d-link* router*' ) FROM item_import AND MATCH (item_keyword_search) AGAINST ('d-link* router*' ) Eliminating the * wildcards doesn't change this, nor does splitting the words with a comma. Any suggestions? All the best Keith

Hi Afraid I think it is, to cope with entries delimited by either double quotes or single quotes, and to ignore any single quotes within double quotes. All the best Keith

Hi Played with Barands script to cope with both single and double quoted strings. function xQuotes($str) { $str_array = str_split($str); $j = 0; $res = array(); $insinglequotes = false; $indoublequotes = false; foreach($str_array AS $str_item) { switch(true) { case $str_item == "'" AND !$indoublequotes: if (!$insinglequotes) ++$j; $insinglequotes = !$insinglequotes; break; case $str_item == '"' AND !$insinglequotes: if (!$indoublequotes) ++$j; $indoublequotes = !$indoublequotes; break; default: $index = (($insinglequotes || $indoublequotes) ? $j : 0); if (isset($res[$index])) $res[$index] .= $str_item; else $res[$index] = $str_item; break; } } if ($insinglequotes || $indoublequotes) { $res[0] .= $res[$j]; unset($res[$j]); } return $res; } All the best Keith

Hi Cheers. I thought a regex would have been the quickest, but it is a non trivial one it would seem. I will have a play with Barands solution, but modify it to cope with single quote delimited strings as well as double quote delimited strings. As an aside '/(".*?")|(\'.*?\')/' works to extract single and double quoted strings (just not found a way to extract things other than those). Thank you All the best Keith

Hi Trying to work out how to do this, but I am very weak at regular expressions. If I have a string as follows:- The quick "brown fox" jumped over the "lazy dog" I would like to extract the quotes strings (ie brown fox and lazy dog) Think I can do this with the following:- preg_match_all( '/".*?"/', 'The quick "brown fox" jumped over the "lazy dog"', $matches ) which will put each of the quoted strings into an array. However I would also like a way to extract the rest of the string separately - preferably without looping round the extracted array. Also, if I wanted to cope with strings enclosed in single quotes as well as double quotes, how can I expand it to cover that? Thank you All the best Keith

Hi Not tested, but give something like this a try, which avoids having a subselect and moves some of the selection of rows to the ON clauses. SELECT DISTINCT(so.user_id), lu.cname FROM selected_operators AS so INNER JOIN selected_operators AS so2 ON so.user_id = so2.user_id AND so2.operator_id = 3 INNER JOIN login_users AS lu ON so.user_id = lu.user_id AND lu.user_level = 3 WHERE so2.user_id IS NULL ORDER BY so.user_id; All the best Keith

Possible I could create a single non temp table, although I am a little averse to creating temporary things on a live database. Interesting, hadn't thought of being able to do the create from a select. Thank you. Sorry, I possibly mislead you. I needed 2 copies of the same temp table (used in different subselects of a single large select). Populating them both using a conventional insert statements was faster than populating one and then populating the other using INSERT...... SELECT.... syntax. All the best Keith

Hi Minor and strange idea, and something I am a bit lumbered with due to some fairly nasty existing tables I am working with. I have a fairly complex piece of SQL which unfortunately depends on some data derived elsewhere (a list of users, but with the ids manipulated). I have generated this list of users and put them into a temp table. Unfortunately my main piece of SQL needs to join onto this table of data twice (once for each of a pair of unioned sub queries). MySQL will not allow a temp table to be used more than once in a single query, hence I have just created 2 identical temp tables and populated them twice. This works fine but I am wondering if there are any efficient tricks to populate the data into 2 tables. At the moment I am just executing each of the INSERT statements twice (there could be multiple insert statements to each table - I am adding 1000 rows at a time). This isn't too bad. I did try inserting to the first table conventionally, and then inserting the the seconds using a SELECT of the first table for the values but this appeared to be slower. Any ideas (short of rewriting a large system to eliminate the derivation of the manipulated ids, which I would dearly love to do but can't justify the massive amount of work it would entail)? All the best Keith

Hi Think the problem is that your columns are date time fields and you are comparing them with dates. MySQL is taking 2012-02-01 and interpreting that as 2012-02-01 00:00:00. Which means 2012-02-01 05:25:00 is outside that range. Would still expect it to be pickup up by the check for NULL though. All the best Keith

Hi You are using LEFT JOINs (although INNER JOINs would have much the same issue here). Yo are getting back one row per related image. If you want just a single row you need to either concatenate the differing columns together (ie, maybe use GROUP_CONCAT) or specify which of the JOINed rows you want to bring back (ie, in this case which image). A 3rd option, which is pretty messy, would be to join the table against the images, once for each possible image. This is a pretty nasty way to do it (and would need to cope with the max number of images, so coping with 100 possible images when 99% of the time there were only 4 would be very messy and inefficient). Normally if you want all to display all the image rows you would bring back one row per image and sort out the display in PHP. All the best Keith

Hi Had more of a play and it seems to be an indexing issue. All the best Keith