SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Regex: Find patterns

    Hi,

    I'm trying to grab titles which has the pattern "copy (#) of $survey" in survey.title ($survey will be a variable in PHP).

    Titles:
    -------
    You in 10 years
    copy (1) of You in 10 years
    copy (3) of You in 10 years
    copy (1) of another survey
    copy (5) of another survey
    another survey

    I tried doing
    Code:
    SELECT survey.title regexp "^(copy \()[0-9]*(\) of You in 10 years)$"
    FROM survey
    WHERE survey.rel_uid = 4
    but that doesn't seem to work (my regex returns 0's for all titles)

    [edit]
    Strange,

    I can't even do a literal
    Code:
    SELECT survey.title, survey.title regexp "copy \(1\) of You in 10 years"
    FROM survey
    WHERE survey.rel_uid = 4
    wont work either

    [edit2]
    Okay, that's strange, i had to double escape

    (working query below)
    Code:
    SELECT survey.title, survey.title regexp "^(copy \\()[0-9]*(\\) of You in 10 years)$"
    FROM survey
    WHERE survey.rel_uid = 4
    Last edited by GeekSupport; Dec 19, 2002 at 18:57.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want only the row with the highest number, can't you use this?

    SELECT title
    FROM survey
    where title regexp "^(copy \\()[0-9]*(\\) of You in 10 years)$" = 1
    order by right(concat("0000", mid(title, 7, instr(title, ") of You in 10 years") - 7)), 4) desc
    limit 1

  3. #3
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it shows my first copy of

    Copy (1) of You in 10 Years

    This is my solution
    PHP Code:
        // Finds next# to enter the survey "Copy of (#) $title"
        
    function next_copy_num($title)
        {    
            
    // strips "copy (#) of " from the title
            
    $_SESSION['title'] = $regex_title eregi_replace('^(copy \()[0-9]*\) of ',null,$title);
            
            
    $SQL 'SELECT title, title regexp "^(copy \\\()[0-9]*(\\\) of '.$regex_title.')$" as regex
                            FROM survey
                            WHERE rel_uid = '
    .$_SESSION['user'].'
                            HAVING regex > 0'
    ;
                        
            
    $result mysql_query($SQL);
                    
            
    // retrieves all survey titles that match the pattern: copy (#) of $title
            
    $count 0;
            while (
    $sql_assoc mysql_fetch_assoc($result))
            {    
                
    $count++;
                
    $db_title[$count] = $sql_assoc['title'];    
            }
            
            
    // finds the largest # of all the retrieved titles        
            
    while ($count// uses $count from the mysql_fetch
            
    {    
                
    // doesn't allow user to make more than 99 duplicates
                
    if ((int) substr($db_title[$count],6,2) == 99)
                    return 
    0;
                if ((int) 
    substr($db_title[$count],6,2) > $num)
                {                
                    
    $num = (int) substr($db_title[$count],6,2);    
                }
                
    $count--;
            }
            
            return 
    $num+1;
        } 
    Last edited by GeekSupport; Dec 20, 2002 at 01:44.

  4. #4
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why does regexp require different number of escapes for types of characters?

    ex:
    \" (1) to escape double quote
    \\) (2) to escape parenthesis
    \\\\ (3) to escape back slash

    SELECT form.title, form.title regexp "^(copy \\()[0-9]*(\\) of Copy \\(1<b>\\) of\\\\ fi</b>rst for\"m)$" as regex
    FROM form
    WHERE form.rel_uid = 9
    HAVING regex > 0

    it looks for Copy (1) of Copy (1<b>) of\ fi</b>rst for"m
    Last edited by GeekSupport; Jun 14, 2003 at 18:57.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •