Jump to content

ragax

Members
  • Posts

    186
  • Joined

  • Last visited

Profile Information

  • Gender
    Male
  • Location
    Nelson, NZ

ragax's Achievements

Member

Member (2/5)

0

Reputation

  1. I am guilty of often failing to think of solutions like the one proposed by .josh, a neat solution which is a great example of when not to use regex. For example, with a regex that aimed to retrieve the 7th string by counting commas, the "30,832" would really throw things off.
  2. 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!
  3. 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.
  4. Cross-posted on DevShed. Make up your mind.
  5. Time waster. This is a duplicate from DevShed, where the OP hasn't given sign of live after several replies.
  6. 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 )
  7. 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.
  8. Odd, though, because NOW() and datetime are normally compatible. Any idea about what might be causing this, Pravin?
  9. 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.
  10. 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.
  11. Thank you for explaining, trq. What's there to excuse, it looks great. Simple is beautiful. Wishing you a fun weekend.
  12. 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));
  13. 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
  14. But now that I've read about Idezender and similar tools, I think I know why you said that "Obfuscating code won't achieve much".
  15. @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!
×
×
  • 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.