MySql

Problems, solutions and everything in between.
Latest entries

MySql Select All Minus X Days - Unix Timestamp

If you want to select all entries minus X period from a unix timestamp perform the following $query = "SELECT * FROM members AND join_date > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 WEEK));"; A list of parameters that can be used: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH So you could perform the following... $query = "SELECT *…

HY000] [2002] No such file or directory - MAMP & PDO

If you're seeing the error HY000] [2002] No such file or directory I solved my issue by running the following commands. sudo mkdir /var/mysql sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /var/mysql/mysql.sock Source: stackoverflow

SQL Group By Month with a timestamp field

In order to convert a timestamp to a date managed column, you need to wrap the column with "FROM_UNIXTIME()" see below SELECT YEAR(FROM_UNIXTIME(join_date)) AS YearDate, MONTH(FROM_UNIXTIME(join_date)) AS MonthDate, count(*) FROM members GROUP BY YEAR(FROM_UNIXTIME(join_date)), MONTH(FROM_UNIXTIME(join_date)) We can trim this down a little further by formatting our query... note the "GROUP BY" is now filtering on column 1,2 if we removed 2 it would filter just on the first column and likewise if…

How to Check and Repair MySQL Tables Using Mysqlcheck

mysqlcheck -c database_name -u database_user -p Additional options -A, –all-databases Consider all the databases -a, –analyze Analyze tables -1, –all-in-1 Use one query per database with tables listed in a comma separated way –auto-repair Repair the table automatically it if is corrupted -c, –check Check table errors -C, –check-only-changed Check tables that are changed since last check -g, –check-upgrade Check for version dependent changes in the tables -B, –databases Check…

How do I know if a mysql table is using myISAM or InnoDB Engine?

SELECT * FROM INFORMATION_SCHEMA.TABLES

Convert Unix timestamp into human readable date using MySQL

In order to convert a column from a timestamp to a human readable date in sql you can use the "from_unixtime()" function. SELECT title, from_unixtime(entry_date) FROM table You can go one step further and format the date to how you'd like. SELECT title, from_unixtime(entry_date, '%Y %D %M %h:%i:%s') FROM table

CURRENT_TIMESTAMP on update within mysql

To set the current timestamp on a column within MySQL, simply create a column with the "TIMESTAMP" datatype. Then you need to ensure the default is as below CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP You can run the following query to alter your table. ALTER TABLE `table.name` CHANGE `column.name` `column.name` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;