date query challenge

[proctk]

Hi below is a query That I'm trying to get all entry that have the matching month in a a date column (Yes the column is set to date format ).  For some reason its returning all found entries but only for the table children.

 

the query

 

$qryDate = " AND (month(parent.parentdob) = $eventMonth) OR (month(children.childdob) =  $eventMonth) OR (month(sibling.siblingdob) = $eventMonth)  OR (month(users.spousedob) =  $eventMonth) OR (month(users.DOB) =  $eventMonth)";
//
$sql = "SELECT users.first_name, users.last_name, children.childdob, children.childfirstname, children.childlastname, sibling.siblingdob, sibling.siblingfirstname, sibling.siblinglastname, parent.parentdob, parent.parentfirstname, parent.parentlastname, users.DOB, users.spousedob, users.spousefirstname, users.spouselastname FROM users LEFT JOIN children ON children.owner_id = users.user_id LEFT JOIN parent ON parent.owner_id = users.user_id LEFT JOIN sibling ON sibling.owner_id = users.user_id WHERE" .$newQryDate." AND users.user_id = '$id'";

 

out put see anything wrong

 

thanks

SELECT users.first_name, users.last_name, children.childdob, children.childfirstname, children.childlastname, sibling.siblingdob, sibling.siblingfirstname, sibling.siblinglastname, parent.parentdob, parent.parentfirstname, parent.parentlastname, users.DOB, users.spousedob, users.spousefirstname, users.spouselastname FROM users LEFT JOIN children ON children.owner_id = users.user_id LEFT JOIN parent ON parent.owner_id = users.user_id LEFT JOIN sibling ON sibling.owner_id = users.user_id WHERE (month(parent.parentdob) = 03) OR (month(children.childdob) = 03) OR (month(sibling.siblingdob) = 03) OR (month(users.spousedob) = 03) OR (month(users.DOB) = 03) AND users.user_id = '63'

[roopurt18]

I'm not going to dissect your query in depth, but here are a few steps I take whenever a query is misbehaving.

 

First perform a sanity check on your data.  Make sure that the data in the DB is correct, otherwise you might have a perfectly valid query but an application that is inserting bad data.

 

The next step is to build your query in pieces.  I usually start with a simple select on the first table and the columns from it.  I run it in phpMyAdmin.  Looks good?  Add the first of my JOINs.  Still look good?  Add the next JOIN.  I repeat this until I'm happy that it is returning the correct data set.  Then I add my constraints and grouping if necessary.

 

Finally I take my final query and slap it in my code.  This is how I write my queries in code now, I find that it is very easy to read and maintain.

 

<?php
$sql = "
  SELECT
    col1, col2, col3,
    DATE_FORMAT(dtCol, 'format') AS `dispDateField',
    MAYBE_ANOTHER_FUNC() AS `whatever`
  FROM `table1` t
  INNER JOIN `table2` t2 ON t.`id`=t2.`owner_id`
  INNER JOIN `table3` t3 ON t2.`id`=t3.`owner_id`
  WHERE
    constraint_one AND
    constraint_two AND
    /* To demonstrate how I inserted PHP data */
    someCol={$Clean['someCol']}
";
?>

 

The real power to this approach is the query is almost intact in my code.  Later when I need to debug or test it I can copy and paste the query as is right from the code, change the areas that say {$Clean['col']} to a real value, and test it quickly in phpMyAdmin.

 

Hopefully that will help you pinpoint your problem.

[proctk]

thank you for the post and guidance it helped me solve my problem