Category Archives: MySQL

MySQL database related

One easy way to select earlier dates in MySQL

I recently read a post asking how to select entries in MySQL based on a date field. The original poster only wanting to choose those records that were newer than 7 days old.

This seems quite a common challenge that new PHP coders have, so here is an easy way to achieve it.

  • Firstly, check the date field is indeed a date (or datetime) field and not a varchar. Date comparisons cannot be done on varchar fields
  • Use the MySQL function: DATE_SUB(date, INTERVAL expr units)

One example is:

SELECT myField FROM myTable WHERE myDateField >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

However if the date field you want to use is actually a DateTime field, the above code may not work as the time throws things. In this case, you need to just be using the date part. For this we can use the MySQL DATE() command. So, the query now looks like this:

SELECT myField FROM myTable WHERE DATE(myDateField) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

Here is the link for the official docs on DATE_SUB() and DATE_ADD();

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

 


 

If you found this post helpful, sign up to my newsletter and get PHP tips sent directly to your inbox:

 

How secure is storing your MySQL connection data in a separate file?

Storing your MySQL database connection information in a separate file is secure and safe as long as you follow best practice:

  • The file must have a .php extension
  • All the connection information should be inside your PHP code, and none of it should be output to the browser.

If the file has the .php extension, it will be run as server code and is not visible to any would-be snoopers as there is no client-side output.

You can test this yourself by pointing your browser to your connection file (e.g. www.yourdomain.com/yourconnectionfile.php).

Make sure there is no browser output and check the page’s source code. If it is just a blank page then all is good.

If you see any of your connection data, you will need to check the above two points have been implemented correctly.

A further recommendation would be to locate the connection file in a folder that cannot be accessed publicly. Often, this can be a folder on your web server higher up in the tree that is accessible to the scripts but not the public.


If you want PHP tips and tricks delivered directly to you, make sure you sign up to my newsletter: