Tag Archives: php

Count occurrences both greater than, less than, or equal in MySQL

Results of the query

  SELECT COUNT(recycled) AS “Total”, SUM(CASE WHEN recycled = 1 THEN 1 ELSE 0 END) AS “New”, SUM(CASE WHEN recycled > 1 THEN 1 ELSE 0 END) AS “Recycled” FROM (a SELECT phone_number, count(*) AS recycled, sale_date FROM ( SELECT DISTINCT * FROM ( SELECT customers.phone_number, customers.sale_date FROM customers INNER JOIN ( SELECT customers.phone_number FROM customers WHERE sale_date BETWEEN ’2014-05-23′ AND ’2014-05-30′) list on customers.phone_number = list.phone_number ORDER BY phone_number DESC, sale_date DESC ) AS multiple_sales ) AS sale_dates_analysis GROUP

Enable PHP error reporting on Turnkey LAMP Stack

Set Display Error Message to Yes and save your settings! :-)

Recently while using Turnkey Linux I had some PHP scripts that were totally blank. This was just a local test server and I had a pretty good idea there were some PHP errors so I enabled PHP error reporting. If you are on a production server you would probably NOT want to do this. However, if you are like me and just doing some testing, here are the steps to enable error reporting: PHP error reporting is disabled – won’t

Turnkey SSH tunnel for MySQL PHP access

Very useful examples at the Linux Journal: http://www.linuxjournal.com/content/creating-secure-tunnels-ssh and I finally found a connection specification that would work: root@lamp ~# mysql -h localhost -P 3308 -u root -psomepassword vlmdatabase ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysq ld.sock’ (2) root@lamp ~# mysql -h localhost -P 3308 -u root -psomepassword vlmdatabase ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysq ld.sock’ (2) root@lamp ~# mysql -h 127.0.0.1 -P 3308 -u root -psomepassword vlmdatabase The last one

Data truncated for column ‘status’ at row 1 [SOLVED]

Showing the existing enum values for the status field

Recently I encountered the following error: Data truncated for column ‘status’ at row 1 This was specifically in a PHP based web hotel/reservations app that uses MySQL for the database backend. When trying to add a new dropdown option value of ‘departed’, the form submission would always fail with the aforementioned error. Thankfully, the solution was easy once I checked the table/field definitions. The column ‘status’ was of type enum and thus needed to have the ‘departed’ value specifically enumerated in

MySQL: Count the total occupancy rate for a time period (month)

Recently I encountered a somewhat interesting MySQL/PHP task: determine occupancy for a reservation system. This entailed determining how many reservation days fall within a particular month. It was not possible to simply determine each reservation length (datediff from/to) and sum them because some reservations started before the month in question and other reservations lasted until the month was over. Therefore, one solution is to get the reservation lengths then compare the start and end of the reservation to the start

Reverse order of individual items in a delimited string

Topic: Reversing the order of individual items in a string. Specifics: I had a date contained in a GET query string that was formatted as follows: 01/27/2013 and I needed it reversed to 2013/01/27 Here is a simple method to do the reversing in PHP: $toDate = htmlentities($_GET['to']); $toDateNew = implode(“/”,(array_reverse(explode(“/”,$toDate)))); Explanation of code: First you explode the $toDate variable, then reverse the resulting array, finally you implode that array back into a string. Of course there are other date

Set your own date format in MySQL

Use DATE_FORMAT in MySQL to create your date strings formatted however you like: SELECT CONCAT(YEAR(res_hotel_booking_bookings.created),’-’,DATE_FORMAT(res_hotel_booking_bookings.created,’%m’) ,’-’,DATE_FORMAT(res_hotel_booking_bookings.created,’%d’)) AS creationDate, Count(res_hotel_booking_bookings.id) As reservationsMade FROM res_hotel_booking_bookings WHERE res_hotel_booking_bookings.created >= ’2013-10-15 00:00:00′ AND res_hotel_booking_bookings.created <= ’2013-10-17 24:00:00′ GROUP BY YEAR(res_hotel_booking_bookings.created), MONTH(res_hotel_booking_bookings.created), DAY(res_hotel_booking_bookings.created) ORDER BY creationDate DESC There are more concise ways to do this, but this was adapted from an earlier method that used CONCAT so I stuck with that…

Changing the php file upload limit in Funtu Linux

To increase the PHP upload limit on a Funtoo based server, I needed to adjust these two settings in php.ini: php_value upload_max_filesize 15M php_value post_max_size 15M More related resources: upload – Changing upload_max_filesize on PHP – Stack Overflowhttp://stackoverflow.com/questions/1122418/changing-upload-max-filesize-on-php<?php ini_set(‘upload_max_filesize’, ’10M’); echo ini_get(‘upload_max_filesize’), “, ” , ini_get(‘post_max_size’) I end up with: Changing the php file upload limit in Ubuntu Linux | miscellaneous.debrishttp://www.miscdebris.net/blog/2008/04/14/changing-the-php-file-upload-limit-in-ubuntu-linux/sudo nano /etc/php5/apache2/php.ini search for “upload_max_filesize” with Ctrl-W and change “2M” to “20M”. Save the file with Ctrl-O and exit…

Page 1 of 212