www.webdeveloper.com
Results 1 to 3 of 3

Thread: Need some advice on a union select mysql statement that only half seems to work

Hybrid View

  1. #1
    Join Date
    Sep 2006
    Location
    Wixom, MI
    Posts
    292

    Question Need some advice on a union select mysql statement that only half seems to work

    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?

    Thanks

    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.

    Code:
    (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.
    Last edited by BWWebDesigns; 08-10-2013 at 10:24 AM.
    OriginalFundRaisingWidget - Free "Non Commercial" PayPal FundRaising Widget, for Charities, Organizations and Individuals, Hosted Free. Check It Out

  2. #2
    Join Date
    Sep 2006
    Location
    Wixom, MI
    Posts
    292
    I have noticed after testing that when the code is run without the limit for pagination, directly in the mysql admin of the database it returns 336 results which would suggest about 34 pages when split by 10 (which sounds right).

    But when you go to page 29 now and the code is
    Code:
    (SELECT * FROM `cms_product` WHERE (`model_names` LIKE '%mazak%' OR `product_description` LIKE '%mazak%' OR `capacity` LIKE '%mazak%' OR `control` LIKE '%mazak%' ) AND `enddate` >= 1376148650 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` >= 1376148650 AND `status` = 'active' AND `manufacturing_date` NOT RLIKE '^[-+0-9.]+$') ORDER BY `manufacturing_date` DESC,`model_names` ASC LIMIT 280, 10
    And the limit starts at result 280 and displays 10, it doesn't seem to do anything, I get a mysql executed successfully but no results and no errors and nothing saying no results found???

    but change it one back and do 270 and it works fine?
    Last edited by BWWebDesigns; 08-10-2013 at 11:20 AM.
    OriginalFundRaisingWidget - Free "Non Commercial" PayPal FundRaising Widget, for Charities, Organizations and Individuals, Hosted Free. Check It Out

  3. #3
    Join Date
    Sep 2006
    Location
    Wixom, MI
    Posts
    292
    Never mind seems to have started working now must have been something else causing the problem?
    OriginalFundRaisingWidget - Free "Non Commercial" PayPal FundRaising Widget, for Charities, Organizations and Individuals, Hosted Free. Check It Out

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles