I have this database which is a simple product table that gets searched based on keyword, this is something someone else set up and I am trying to modify the order it displays the results.
Currently displayed by a date value but some values in the date field ate a 4 digit year and some are left blank or - in the field instead.
What I need is to order by year descending starting with the most recent year and then at the end of all that show everything with blank or - or other words or things that shouldn't be there etc.
I figure the way to do this might be a union select to first select everything with a date so everything that didnt have a - or blank and then select everything that did after with the union.
Then order it by date descending and then by product name in alphabetical order.
There is also on the page a pagination code that allows it to be split into 10 results per page.
Here is the basic sql being used to show the first 10 results, all seems fine, but when you get to page 20 or so, for some reason it displays no results, and I know when just pulling the info normally without trying to union select bits at a time there was well over 20 pages.
Any ideas what might be going wrong, or a simpler way to do it?
Below shows an example echo of the fully formed sql when someone searches for a specific word which generates over 30 pages of results but stops displaying anything after page 20 and does not even get to the ones that have blank or - set as manufacturing_date value.
(SELECT * FROM `cms_product` WHERE (`model_names` LIKE '%mazak%' OR `product_description` LIKE '%mazak%' OR `capacity` LIKE '%mazak%' OR `control` LIKE '%mazak%' ) AND `enddate` >= 1376146343 AND `status` = 'active' AND `manufacturing_date` RLIKE '^[-+0-9.]+$') UNION (SELECT * FROM `cms_product` WHERE (`model_names` LIKE '%mazak%' OR `product_description` LIKE '%mazak%' OR `capacity` LIKE '%mazak%' OR `control` LIKE '%mazak%' ) AND `enddate` >= 1376146343 AND `status` = 'active' AND `manufacturing_date` NOT RLIKE '^[-+0-9.]+$') ORDER BY `manufacturing_date` DESC,`model_names` ASC LIMIT 0, 10
All i want to do is search cms_product for anything with a valid end date that is like the keyword entered and has status active and order it by manufacturing_date with numbers coming first and any empty blank space or other non 4 digit year value coming after and must be in reverse chronological order so cant just simply set it to ASC and have it go from earliest date first then all the none dates after.
Thanks in advance.