Combine SQL with PHP

Question

$months = array('January', ... , 'December');
$sql='
SELECT *
FROM `residencies`
WHERE `Year` = 2010
ORDER BY array_search($months,`MonthFrom`) `DayFrom`';

Doesn't work (i already feared that), it's supposed that the elements are sorted by first the position of MonthFrom in the array $months, and those who have the same position should be sorted by DayFrom. I know there are other way's to treat dates but for this query i am bound to this date structure, any help is appreciated

Answer 1

I'm not entirely sure what this has to do with PHP, all you've posted is SQL code. SQL code is not PHP. I would recommend this tutorial to help you include SQL in your PHP.

Answer 2

$sql='
SELECT *
FROM `residencies`
WHERE `Year` = 2010
ORDER BY DATE_FORMAT(`MonthFrom`,"%M") `DayFrom`';

But this will sort them according to the alphabetical order of the name of the month ? Do you really need that ?

You can also sort them according to MonthFrom and them convert it to text in your php code."

Answer 3

It looks like you're trying to use a PHP function within an SQL statement. If you need to sort by MonthFrom, which is the name of the month, try something like this:

SELECT *
FROM `residencies`
WHERE `Year` = 2010
ORDER BY FIELD(`MonthFrom`, 'January', ..., 'December'), `DayFrom`;

(you can fill in the rest of the months)

Answer 4

$month_arr = array("January","February","March");

$months = implode("', '", $month_arr);

$query="SELECT * FROM residencies WHERE year = 2010 ORDER BY FIELD('MonthFrom', '$months'), `DayFrom`;

Answer 5

I presume from the question that you have a month field in the database which is stored in string format, which would, as other commenters have said, make sorting tricky, hence why you were trying to put the months in order using the array.

Others have come up with valid ways to achieve this given that constraint (I think luckytaxi's answer is probably the best so far), but I would say that if you do have months stored in the database in string format, then you definitely have a poor database design, and if you have the option, you should consider changing it.

MySQL can store full dates using the DATE or DATETIME data types, so if you're storing your days, moths and years separately at the moment as it appears, you should change to storing them together in a single field. You can still query them separately, eg if you just need the month, or in any combination - MySQL has very powerful date handling features (and so does PHP for that matter).