Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: How to do this?

  1. #1
    Senior Coder DJCMBear's Avatar
    Join Date
    Mar 2010
    Location
    United Kindom
    Posts
    1,173
    Thanks
    14
    Thanked 136 Times in 136 Posts

    How to do this?

    Hello

    Just trying to Join two tables along with ordering and counting from one table I have gotten so far but not close enough lol. Here is what I have right now.

    Code:
    SELECT `orders`.`Date`, COUNT(`orders`.`Date`) As `Count`, `franchises`.`Calendar`
    FROM `orders`
    JOIN `franchises` ON `franchises`.`ID` = `orders`.`Franchise`
    WHERE `orders`.`Date` <> '' && `orders`.`Date` > :DATE && `orders`.`Franchise` = :FRANCHISE
    GROUP BY `orders`.`Date`
    HAVING COUNT(`orders`.`Date`) < `franchises`.`MaxOrders`
    ORDER BY `orders`.`Date`
    The bit I get an error on is the HAVING clause which I need to obtain the max-orders which the specific franchise has set which could be like '5' orders at max, now I need to count the amount of jobs which are per each date so like on '20-10-2011' could have '3' jobs and '21-10-2011' could have '10' jobs now if the franchise has set their max-orders to '10' i don't want the second date to show up in the results has that has hit the max amount of jobs on a date, how can I do this?

    Thank you
    - DJCMBear
    Last edited by DJCMBear; 10-18-2011 at 01:47 PM.
    Official BinPress hand picked coder.
    For anyone worried about SQL injection go have a look at my small yet powerful script here.
    Go Pledge for Light Table, if it hit's $300,000 Python and other languages will get added.
    I am 1 of 65,608 people to get a Pebble Watch :P

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    You have been suggested to use date format for Date in one of previous posts.
    Do as suggested. Allso you have been adviced not to use field names equal to mysql functions.
    You can, but things are then confusing.


    mybe, if error, which you don't tell what it is
    Code:
    HAVING `Count` < `franchises`.`MaxOrders`
    out of head.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Senior Coder DJCMBear's Avatar
    Join Date
    Mar 2010
    Location
    United Kindom
    Posts
    1,173
    Thanks
    14
    Thanked 136 Times in 136 Posts
    Quote Originally Posted by BubikolRamios View Post
    You have been suggested to use date format for Date in one of previous posts.
    Do as suggested. Allso you have been adviced not to use field names equal to mysql functions.
    You can, but things are then confusing.


    mybe, if error, which you don't tell what it is
    Code:
    HAVING `Count` < `franchises`.`MaxOrders`
    out of head.

    I am using `..` around the names so that doesn't confuse the SQL query also I am using PDO which uses prepare and execute for stopping these types of problems so that is why I am using them and it works perfectly, I have also managed to fix the problem I was having which was using PHP to check like this $row['Count'] >= $row['MaxOrders'] so it works fine now.
    Official BinPress hand picked coder.
    For anyone worried about SQL injection go have a look at my small yet powerful script here.
    Go Pledge for Light Table, if it hit's $300,000 Python and other languages will get added.
    I am 1 of 65,608 people to get a Pebble Watch :P


  •  

    Posting Permissions

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