Simple INNER JOIN not returning results

[jeffiec]

So i have product type forklift, when adding a new product, adds a ton of extra fields to a table using products_id = forklift_id,
I did it a few years ago and its working great, now they are adding a new forklift notification system, (its own little nightmare in another post),
I am setting up listeners that when a new forklift is added, if someone signed up for notifications fitting the specs of the forklift.

I am also setting up a manual notification system and on page load, i want to see which forklifts have been added.

$sel_forklifts = "select pf.forklift_id, pf.forklift_cpac, pf.forklift_fuel, pf.forklift_tires, pf.forklift_make, pd.products_id from "
        .TABLE_FORKLIFT. " pf" .
        " INNER JOIN " .TABLE_PRODUCTS. " pd".
        " ON pf.forklift_id = pd.products_id ".        
        " where pd.products_date_added between '".$tdate."' and '".$edit_time."'"; 

Even pasting into the mysql backend:
SELECT pf.forklift_id, pf.forklift_cpac, pf.forklift_fuel, pf.forklift_tires, pf.forklift_make, pd.products_id
FROM zen_forklift pf
INNER JOIN zen_products pd ON pf.forklift_id = pd.products_id
WHERE pd.products_date_added
BETWEEN '2016-07-04'
AND '2016-07-14'
LIMIT 0 , 30

No results

Seems to not return any hits, even though i know there are at least 6 that should come up.
Can anyone give me a little guidance, im not the best with joins.
Thanks a ton.
Jeff

[mc12345678]

Thinking that because the dates are actual date fields, that need to address them similar to the following:

SELECT pf.forklift_id, pf.forklift_cpac, pf.forklift_fuel, pf.forklift_tires, pf.forklift_make, pd.products_id
FROM zen_forklift pf
INNER JOIN zen_products pd ON pf.forklift_id = pd.products_id
WHERE pd.products_date_added
BETWEEN CAST('2016-07-04' AS DATE)
AND CAST('2016-07-14' AS DATE)
LIMIT 0 , 30;

[DrByte]

Ya, I'd test by removing the "where" clause first. If that returns results then you'll know it's date-related.

[mc12345678]

Ya, I'd test by removing the "where" clause first. If that returns results then you'll know it's date-related.

Thinking a little further on it (and to help with the above test), might want to (temporarily) add the pd.products_date_added field on the return set just so it shows in you results so you can confirm your "suspicion" of having those at least 6 product.


.

[jeffiec]

Thinking that because the dates are actual date fields, that need to address them similar to the following:

SELECT pf.forklift_id, pf.forklift_cpac, pf.forklift_fuel, pf.forklift_tires, pf.forklift_make, pd.products_id
FROM zen_forklift pf
INNER JOIN zen_products pd ON pf.forklift_id = pd.products_id
WHERE pd.products_date_added
BETWEEN CAST('2016-07-04' AS DATE)
AND CAST('2016-07-14' AS DATE)
LIMIT 0 , 30;

Worked perfectly, thank you!

[mc12345678]

Worked perfectly, thank you!

Quite welcome! :) good luck!