Jump to content

kickstart

Staff Alumni
  • Posts

    2,707
  • Joined

  • Last visited

Posts posted by kickstart

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

     

     

  8. 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

  9. the table need to be temporary?

     

    Possible I could create a single non temp table, although I am a little averse to creating temporary things on a live database.

     

    insert 1 time

     

    then

     

    create temporary table myTemp2 select * from myTemp1;

     

    This will then duplicate the table, BUT it won't create any keys you have on the temp table.

     

    Interesting, hadn't thought of being able to do the create from a select. Thank you.

     

    Are you saying creating two temporary tables and joining them onto a query is faster than using something like

     

    INSERT INTO tbl1 (Col1, Col2, Col3) SELECT (Col1, Col2, Col3) FROM tbl2
    

     

    I'm shocked if joining two temporary tables is faster than that.

     

    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

  10. 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

  11. 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

  12. 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

  13. Hi

     

    Looks generally OK but would expect your GROUP BY field to be one you have selected (although really you should have all the non aggregate fields in the GROUP BY clause - some flavours of SQL insist on it).

     

    Biggy I suspect is the missing spaces around the GROUP BY clause

     

    All the best

     

    Keith

  14. Hi

     

    Mikosiko has given you a good answer, but just to follow it up and comment on the issue you had with your solution.

     

    $q = "SELECT photos.thumb, product.id_prod, product.title, photos.thumb_width, photos.thumb_height

    FROM members, product LEFT JOIN photos

    ON product.id_prod = photos.id_prod

    AND photos.main_photo  =  '1'

    WHERE product.publish = '1'

    AND product.id_mem = members.id_mem

    AND members.mem_group >=1 AND members.mem_group <100

    ORDER BY product.id_prod DESC"

     

    The first bit highlighted in red is doing a join between those 2 tables. The columns it does the join on are highlighted in the 2nd bit in red. So if you remove the WHERE clause you remove the part that tells it which columns to join on.

     

    This should still work, but what it will do is join every row from those 2 tables (a CROSS JOIN), irrespective of the id_mem fields. This is one way to get a massive number of records returned (if each table had 100 records, you would get 10000 rows brought back).

     

    Cross joins are useful. For example if what you wanted to produce was a grid of all the members and all the products, highlighting how many each member has bought of each product you could use a CROSS JOIN between the members and products table, with a LEFT OUTER JOIN to get the matching data (if it exists).

     

    All the best

     

    Keith

  15. Hi

     

    The sub query is not 100% essential. The query did use a conventional join against the table and then excluded records in the normal WHERE clause, but the sub query was an attempt to sort a few records out earlier in the process.

     

    In the SQL here it is the one with the sub query that is quickest but unfortunately it makes stuff all difference to performance either way :'( .

     

    All the best

     

    Keith

     

     

  16. Hi

     

    GROUP BY is for aggregate fields. Bit dodgy to use it to just get a unique row.

     

    Your code checking for a po option group of 6 is having the issue you mention as the check for that field is in the WHERE clause. You can put it in the ON clause. This way is there is a row which has that option group you will get fields from the joined table, if not you will get nulls from the joined table.

     

    SELECT pID, pName, poOptionGroup 
    FROM products 
    LEFT JOIN prodoptions 
    ON products.pID = prodoptions.poProdID 
    AND prodoptions.poOptionGroup = 6 
    ORDER BY pName 

     

    All the best

     

    Keith

  17. Hi

     

    You will probably need to use a subselect to get the relevant sub rows.

     

    Ie, something like (and this will be wrong as I am not 100% clear on what you want):-

     

    SELECT *
    FROM outcomes parent
    INNER JOIN outcomes child ON parent.id=child.sid
    INNER JOIN (SELECT nid, MAX(id) AS ChildId FROM outcomes GROUP BY nid) ChildMax ON child.nid = ChildMax.nid
    INNER JOIN outcomes child2 ON ChildMax.ChildId = child2.id

     

    Ie, find the max id you are interested in, grouped by the relevant field, and then join that back against the table to get the other fields from that row.

     

    While you are getting the highest rated child id, that is probably not certain (ie, the row is probably random).

     

    However I can't give any more info as your example SQL is only half there

     

    All the best

     

    Keith

  18. Hi

     

    It is possible to do a JOIN on VARCHAR fields, just not as efficient and not really necessary.

     

    What you should have is the cats details with an INT primary key. Other details fields migth be one for each of the parents, but NOT storing the parents names (you can easily get these).

     

    This way you take the cat table, you JOIN it against itself where the queen = ID and also JOIN it against itself where the tom = ID (this way you can get the queens and the toms name from their rows). You can do this more times to get the grand parents as well if you want.

     

    If you possibly do not have a cats details for the parent then leave the queen / tom ID field as NULL and you use a LEFT OUTER JOIN. This way if there is no matching field the base row will still be brought back but the fields from the JOINed table will be NULL.

     

    If you want to enter the details you have a drop down select list where the display field is the name but the VALUE on the drop down is the ID field.

     

    All the best

     

    Keith

  19. Hi

     

    I have the following query which runs adequately:-

     

    SELECT Rep.RepID, Rep.Forname + ' ' + Rep.Surname AS Name, ROUND(dbo.Haversine(OutcodePostCodes1.lat, OutcodePostCodes1.lng, OutcodePostCodes2.lat, OutcodePostCodes2.lng), 0) AS RepDistance 
    FROM Rep INNER JOIN RepQuery ON Rep.RepID = RepQuery.RepID AND RepQuery.QueryId = 7 
    CROSS JOIN (SELECT * FROM OUTLETS WHERE OUTLETS.OutletID = SOMEOUTLETID ) AS OUTLETS 
    LEFT OUTER JOIN OutcodePostCodes AS OutcodePostCodes1 ON dbo.OutboundPostcode(OUTLETS.PostCode) = OutcodePostCodes1.Outcode 
    LEFT OUTER JOIN OutcodePostCodes AS OutcodePostCodes2 ON Rep.OutPostcode = OutcodePostCodes2.Outcode 
    ORDER BY RepDistance

     

    This is OK, but it lands up returning reps who are no longer employed. However occasionally I will want them returned if they are assigned to the current job. So I tried the following:-

     

    SELECT Rep.RepID, Rep.Forname + ' ' + Rep.Surname AS Name, ROUND(dbo.Haversine(OutcodePostCodes1.lat, OutcodePostCodes1.lng, OutcodePostCodes2.lat, OutcodePostCodes2.lng), 0) AS RepDistance 
    FROM Rep INNER JOIN RepQuery ON Rep.RepID = RepQuery.RepID AND RepQuery.QueryId = 7 
    CROSS JOIN OUTLETS 
    LEFT OUTER JOIN OutcodePostCodes AS OutcodePostCodes1 ON dbo.OutboundPostcode(OUTLETS.PostCode) = OutcodePostCodes1.Outcode 
    LEFT OUTER JOIN OutcodePostCodes AS OutcodePostCodes2 ON Rep.OutPostcode = OutcodePostCodes2.Outcode 
    WHERE OUTLETS.OutletID = SOMEOUTLETID AND Rep.Disabled = 0 "
    UNION 
    SELECT Rep.RepID, Rep.Forname + ' ' + Rep.Surname AS Name, 6000 AS RepDistance 
    FROM Rep 
    CROSS JOIN OUTLETS 
    WHERE OUTLETS.OutletID = SOMEOUTLETID AND Rep.Disabled = 1 AND Rep.RepID = SOMEREP
    ORDER BY RepDistance"

     

    This does work, but runs unbelievably slowly.

     

    Taking a step back I modified the first query to just exclude the ex reps

     

    SELECT Rep.RepID, Rep.Forname + ' ' + Rep.Surname AS Name, ROUND(dbo.Haversine(OutcodePostCodes1.lat, OutcodePostCodes1.lng, OutcodePostCodes2.lat, OutcodePostCodes2.lng), 0) AS RepDistance 
    FROM Rep INNER JOIN RepQuery ON Rep.RepID = RepQuery.RepID AND RepQuery.QueryId = 7 
    CROSS JOIN (SELECT * FROM OUTLETS WHERE OUTLETS.OutletID = SOMEOUTLETID ) AS OUTLETS 
    LEFT OUTER JOIN OutcodePostCodes AS OutcodePostCodes1 ON dbo.OutboundPostcode(OUTLETS.PostCode) = OutcodePostCodes1.Outcode 
    LEFT OUTER JOIN OutcodePostCodes AS OutcodePostCodes2 ON Rep.OutPostcode = OutcodePostCodes2.Outcode 
    WHERE Rep.Disabled = 0 
    ORDER BY RepDistance

     

    This also takes ages, despite the only difference being a check of a bit field (set to is indexable) to dis-guard maybe 25% of the ~25 records returned.

     

    Unfortunately I have no tools for investigating SQL Server queries properly so I am a bit stumped with something that looks like a minor change (and which even if it triggered a table scan, is only scanning a couple of dozen records) which is causing dramatically worse performance.

     

    All the best

     

    Keith

  20. Hi

     

    Without a sample of you data and table layout I can't really test it.

     

    The %s bits are placeholders, and sprintf replaces them with the variables.

     

    You can remove them and sprintf:-

     

    <?php
    
    $colname_sellsearch = "-1";
    if (isset($_POST['s'])) {
      $colname_sellsearch = trim($_POST['s']);
    }
    mysql_select_db($database_scholarexpress, $scholarexpress);
    $SearchItem = "%".mysql_real_escape_string($colname_sellsearch)."%";
    $query_sellsearch = "SELECT * FROM selltable WHERE (`btitle` LIKE '$SearchItem') OR (`bisbn` LIKE '$SearchItem') OR (`bauthor` LIKE '$SearchItem') OR (`bpubyear` LIKE '$SearchItem') OR (`bpublisher` LIKE '$SearchItem') AND `archive`=0 ORDER BY $sort LIMIT $startRow_sellsearch, $maxRows_sellsearch ";
    $sellsearch = mysql_query($SearchItem, $scholarexpress) or die(mysql_error());
    $row_sellsearch = mysql_fetch_assoc($sellsearch);
    
    if (isset($_GET['totalRows_sellsearch'])) {
      $totalRows_sellsearch = $_GET['totalRows_sellsearch'];
    } else {
      $all_sellsearch = mysql_query($query_sellsearch);
      $totalRows_sellsearch = mysql_num_rows($all_sellsearch);
    }
    $totalPages_sellsearch = ceil($totalRows_sellsearch/$maxRows_sellsearch)-1; 
    
    ?>

     

    All the best

     

    Keith

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.