Jump to content

ragax

Members
  • Posts

    186
  • Joined

  • Last visited

Posts posted by ragax

  1. For the many of you who, like me, are big fans or Jan's RegexBuddy, today is a big day: RegexBuddy 4 is out!

    It's a spectacular release. I've been waiting for some of the features for a long time (\K, recursion), but Jan went even further. For instance you can convert between different languages.

    Free demo: http://yu8.us/rbdemo
    Glowing review:
    http://www.rexegg.com/regex-tools.html
    Release notes: http://yu8.us/rb4release


    It's big news in the world of regex so will be cross-posting my two other favorite regex boards.

    W00t! :happy-04:

  2. It depends on how much data you have: there's a cost-benefit balance to maintaining a fulltext index.

     

    If you know exactly what you want to match and not match, and you don't have thousands of rows, then LIKE or (REGEXP if you have complex but precise match conditions may perform best---you'd have to test it. Understand that with those, searching for text within text, you'll have to read the entire field, the optimizer cannot use an index. For exact matching, the other option is a fulltext MATCH in boolean mode. If you have a lot of rows, that's probably going to perform better than reading every single row.

     

    If you want to "fuzzy match," then  you're looking at a fulltext MATCH. In the default mode it ranks the matches, and you have to choose a threshold.

     

    Do you have MySQL 5.6? InnoDB now has full text search. I have no experience with it.

     

    My initial thoughts, and not a full answer: someone more experienced than me will probably have better information for you. :)

  3. The same, but more compact:

    SELECT `date`
    FROM anni_reminder
    WHERE `date` BETWEEN 
    (@p := TIMESTAMPADD(YEAR,TIMESTAMPDIFF(YEAR,CURDATE(),`date`)-1,CURDATE()) )
    AND ( @p + INTERVAL 7 DAY )
    
    
  4. Hi again gerkintrigg,

     

    After browsing through the big bag of MySQL time and date functions to refresh my memory, I came up with something neater.

    SELECT `date`
    FROM
    (
    SELECT `date`,
    @d:=TIMESTAMPDIFF(YEAR,CURDATE(),`date`),
    @p:=TIMESTAMPADD(YEAR,@d-1,CURDATE() ) AS pastanniv
    FROM anni_reminder
    ) t
    WHERE `date` BETWEEN pastanniv AND pastanniv +INTERVAL 7 DAY
    

    If you need other fields in the outer query, remember to add them to the inner query as well.

    Please let me know if this does the trick. :)

     

     

  5. Okay, I think this will work:

    SELECT thedate,
    @d := DATE_FORMAT( CURDATE(),
        CONCAT(
            CASE
            WHEN DAYOFYEAR(CURDATE()) >358 THEN YEAR(thedate) - 1
            ELSE YEAR(thedate)
            END
            ,
            '-%m-',
            CASE WHEN DAY(CURDATE())=29 AND MONTH(CURDATE()) = 2
            THEN 28
            ELSE DAY(CURDATE())
            END
        )
    )
    
     AS pastcurdate ,
     @d + INTERVAL 7 DAY AS laterdate
     
    FROM
    date
    GROUP BY thedate
    HAVING thedate BETWEEN pastcurdate AND laterdate

    Let me know if it does. :)

  6. Here are some thoughts.
    Assuming your date column is indexed (let's call it mydate for clarity), then we want the WHERE condition to apply to mydate without any transformation, to allow the optimizer to use the index.

    So we want a WHERE for mydate that falls between pastcurdate and pastcurdate + 7, where pastcurdate is the current date transformed to an equivalent date in the past, close to mydate.

    How do we transform CURDATE() to pastcurdate? We can't just date_format it with the year of mydate, first for the reason Barand put forth (year-end cases), second because that may result in an invalid date when CURDATE() is Feb 29 and the year of mydate is not a leap year.

    So my feeling is the transformation from CURDATE to pastcurdate will involve a couple of CASE... WHEN clauses.

    That's as far as I got (after first trying, and failing, to think of a suitable modulo).

    If that direction seems promising I'm sure you can finish it up. But someone may come up with a better idea.

    Wishing you a fun weekend. :)

  7. Oh, building on what Mac_Gyver said, the most important thing is to make sure your integer column for the ip address is UNSIGNED. Otherwise a number of ip addresses will result in negative values that won't convert back.

     

    For conversion, in my view best is to use the native MySQL INET_ATON and INET_NTOA function.

    But in a WHERE, always contrive to place functions on the right / value side of the operator, not on the left / column side, e.g.

    $query = "... WHERE ip = INET_ATON('".$string."').....";

    NOT

    $query = "... WHERE INET_NTOA(ip) = '".$string."'.....";

    This is because a function on the left side of the operator will make it impossible for the optimizer to use the index on the ip column (assuming there is one).

     

    The other option is to use the php ip2long and long2ip functions, with some modification:

    $trueIPint = sprintf('%u', ip2long($ip));

  8. Hi Royal,

     

    Before doing anything in PHP, I would recommend making sure that your SQL works by speaking directly to your database.

    You can do that for instance in PhpMyAdmin, or, better,  on a desktop MySQL client (there are excellent free ones).

    This will help you debug faulty syntax, such as the two WHERE clauses in your SELECT.

     

    Then, when your SQL works, take it to PHP. At that stage, you know that what you're debugging is your PHP, not the SQL.

    When writing your first PHP query, you may want to look at the manual and modify a working example.

    Byt the way there are several ways of talking to the database in PHP, and in the long run, you may want to consider working with the PDO methods rather than the older functions.

     

    This approach of "divide and conquer" when debugging will help you at any level of skill in both SQL and PHP. It makes it much easier to know where your problem is.

     

    ps

    As a hint, if the WHERE looks at integer columns, this would be valid SQL, without needing to add any quotes around the column and table names.

     

    SELECT somecolumn

    FROM mytable
    WHERE anothercolumn <= 320000 AND athirdcolumn >=3210000

  9. @trq, g'day from across the Tasman,

     

    You mean, sign something with the client, and leave the source code wide open on their server?

    Aarrg, not what I'm hoping to hear. :)

     

    An unrelated question, but which also has to do with php configuration. I clicked on thorpesystems and saw mentioned a "static site generator". Is the idea to transform a set of dynamic pages into static pages, rebuilding them as needed, to make the site load faster? A friend mentioned he makes his pages that way, and the idea has been tantalizing me ever since... but I haven't yet found the courage to tackle it.

     

    Recently got back into projects after about a year's break so I have a number of questions.

     

    @PravinS: Thank you very much, I will have a look!

     
  10. What trq said. Here's a starter program:

     

    0. Sounds like you need to work on your SQL skills. Head over to SQLZoo.

     

    1. Install a free program such as SQLyog community edition or HeidiSQL (Win) or SequelPro (OSX) and run some queries against your database. Once you have the queries you need,

     

    2. Make a "Run My Query" button on a page, check for "Run My Query" in the POST data, execute the query, display the results.

  11. If you're installing code on a client's server, what is a good configuration to protect your code from getting ripped off?

     

    A few years ago I remember reading about obfuscation, and about some options to install compiled PHP.

     

    Do some of you know what commercial outfits use nowadays to protect their source code? What are the best free solutions? And the best paid solutions?

     

    Many thanks in advance. :)

     

  12. CSS has been a major blind spot of mine. I mean to buy a book soon (recommendations welcome.)

     

    In the meantime, wondering if one of the aces could help me troubleshoot a problem.

    On this page, at the start of the first comment, you see the poster's name: "Daniel".

    Then there is a huge gap until the body of the comment starts.

    This gap corresponds to the height of the left menu left to display.

     

    http://www.asiteaboutnothing.net/c_decode-url.html#comlist

     

    Any pointers and insights warmly welcome.

     

    Cheers!

     

  13. You also don't need to capture the last part of the match.

    A starting point to modify the regex would be

    elseif( ! preg_match("~^[A-Z]'?[- a-zA-Z](?: [a-zA-Z])*$~", $_POST['lastNameForm']) )
    

    but as requinix said, the match criteria are way loose.

  14. Updating the post to share how it went, in case anyone is on the same track.

     

    I tried a few things but didn't like any. The CATO generator was a mess to install. The phpscaffold online solution didn't generate anything I liked.

    So I ended up coding it from the ground up, and I'm glad I did: something I fully understand and that perfectly meets my needs. I still have a couple of days of work left on it.

     

    For me, interfaces have always been the annoying part of database work. Now (I hope) it's going to be a treat.

     

    :)

  15. To clarify, I don't want to use a framework at all.

     

    It's a perfect solution for a lot of people, no doubt about that. But not for me, given how my time is invested. I'm not in a position to monitor another code base---updating, etc.

     

    That's the context for my question. A simple CRUD generator that is nicely self-contained.

     

    Thanks in advance for your thoughts.

  16. Hi all,

     

    What, a CRUD question? Yes, CRUD questions have been asked before, but everyone's needs are different and I couldn't find an answer on older threads.

     

    With that out of the way...  ;)

     

    I am starting on a new project, and I will need several CRUD interfaces for a number of mySQL tables.

     

    I'm reluctant to start with the last CRUD class I built, as it's a fairly big mess of code spaghetti.

    So I wonder if any of you would know of something pre-made, a nice class that can churn out simple CRUD interfaces without too much bending. Including some nice validation methods.

     

    Don't need Ajax and a whole lot of JS hoopla, this is just for me.

    And I really don't feel like installing a heavy framework.

     

    At this stage I have one lead (a script from a Lynda tut), but I'd rather take the temperature with the experts here and take my time before jumping in.

    If nothing suits I might have to go the DYI route again, but I have a feeling that someone is going to know about just the right thing.

     

    Much looking for your insights.

  17. If find it least confusing to explain what you are telling the regex engine to do.

     

    Dot-star (.*) tells the regex engine: "Match any character, zero or more times, as many times as possible". The dot-star will bulldoze its way to the end of the subject. Then, if needed to allow a match, it will backtrack, one character at a time.

     

    Dot-star-question-mark (.*?) tells the regex engine:  "Match any character, zero or more times, as few times as possible". The engine will start out by matching zero characters, then, because it cannot return a match (since "WORD 2" has not been found), it will match one more character, then one more, and so on.

     

    For more details, you may like to check out my tut about the degrees of regex greed, and Jan's page on repetition.

     

    This is a very cool but crucial concept to grasp, please don't hesitate to ask for clarifications.

     

     

  18. Hi sowna,

     

    "/user/"

     

    Your regex needs to grab everything after user-slash and before the closing quotes. At the moment, it just matches "user" (since your slashes are delimiters, as opposed to the slashes in the code).

     

    Here are two ways to do it ($regex and $regex2).

     

    $regex='~stuff/([^"]+)~';
    $regex2='~stuff/\K[^"]+~';
    $string='my stuff/85"';
    if(preg_match($regex,$string,$m)) echo $m[1].'<br />';
    if(preg_match($regex2,$string,$m)) echo $m[0].'<br />';
    

     

    This code will echo 85 twice. Run this to see how it works.

     

    Hint: replace stuff with user and you will be in good shape.

     

    Let me know if you have any questions.

    In the meantime:

    [^"] means any character that is not a double quote, the plus means "match as many of these characters as possible". The first example uses a capture (with the parentheses), the second uses \K, a feature of PCRE that resets the reported match.

     

    :)

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