Dilema with date field data types for comparison

[showman13]

Good Afternoon,

I have a bit of a dilema, maybe 2 or 3...

I have a members table in MySql, that includes a field called

join_date which is designated as DATE type field

I have 7 position tables named position_1 thru position_7

These each have a field called

create_date which is designated as DATETIME type field

Now the issue is that I need to do a count of the number of records in each of the position tables
WHERE the positions were created within a specified date range
AND the members that own those positions registered/joined within that same date range

This basically should give me a count of the New Members that Actually made a Purchase within the same date range that they joined...

This is the Query that I am trying to use, but I'm getting Zeros across the board, and I know this isn't correct...

The from and to dates are selected and passed from the previous report selection page and are formatted like this: 07/01/2011

This is what the query looks like when it is run:

SELECT count(p.pos_id) FROM position_1 AS p, members AS m WHERE p.create_date>='07/01/2011' AND p.create_date<='03/14/2012' AND p.mem_id=m.mem_id AND m.join_date>='07/01/2011' AND m.join_date<='03/14/2012' GROUP BY p.pos_id

Here is the relavent portion of the script:

for ($level=1; $level<8; ++$level){
        $sql_ct = "
        SELECT count(p.pos_id)
        FROM position_".$level." AS p, members AS m
        WHERE p.create_date>='".$from."'
        AND p.create_date<='".$to."'
        AND p.mem_id=m.mem_id
        AND m.join_date>='".$from."'
        AND m.join_date<='".$to."'
      ";
        $result_ct=mysql_query($sql_ct);
        $request_ct=mysql_fetch_array($result_ct);
        $display_ct[$level]=$request_ct[0];
      }// at this point display_ct [1-7] should hold the numbers to display


<table align="center" width="700" height="300">

  <tr>
    <td align="center" colspan="7"><h3>New Member Purchases</h3></td>
  </tr>
  <tr>
    <td align="center" colspan="7"><h3>Made Between <?php print $from; ?> and <?php print $to; ?> </h3></td>
  </tr>
  <tr>
    <td align="center"><?php print $display_ct[1]; ?></td>
    <td align="center"><?php print $display_ct[2]; ?></td>
    <td align="center"><?php print $display_ct[3]; ?></td>
    <td align="center"><?php print $display_ct[4]; ?></td>
    <td align="center"><?php print $display_ct[5]; ?></td>
    <td align="center"><?php print $display_ct[6]; ?></td>
    <td align="center"><?php print $display_ct[7]; ?></td>
  </tr>

  <tr>
    <form name="reset" action="<? echo $_SERVER['PHP_SELF']; ?>" method="POST">
        <td align="center" colspan="7">
        <input type="image" name="submit" value="Another Selection" src="../images/button_next.png" width="225" height="68" alt="Select Another Report" />
        </td>
    </form>
  </tr>


</table>

Could someone give me an idea of where I'm going wrong with this Query?

Or if it could be because the comparisons are against different data types in the database? And if this is the case, any suggestions on how to get around it?

Thanks in advance for your feedback.

Douglas

[hericles]

Have you checked how the date is formatted in the table? MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM: SS' format so values such as 03/14/2012 may not exist in the table.

[showman13]

Have you checked how the date is formatted in the table? MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM: SS' format so values such as 03/14/2012 may not exist in the table.

Hi,

Yes, I was aware of the formatting of the datetime field in mysql...

I was for some reason under the understanding that multiple formats were readable and understandable for comparison purposes, but apparently I was wrong.

So, I guess the only solution is to convert the entered from and to dates to both formats necessary to compare to both the datetime format and the date format in the two tables used for comparison.

Just honestly wasn't sure if that is what the issue was, or if it was something in the structure of the query itself.

Guess the only way to find out is to do it..

Thanks
Douglas

[showman13]

Hi,

Yes, I was aware of the formatting of the datetime field in mysql...

I was for some reason under the understanding that multiple formats were readable and understandable for comparison purposes, but apparently I was wrong.

So, I guess the only solution is to convert the entered from and to dates to both formats necessary to compare to both the datetime format and the date format in the two tables used for comparison.

Just honestly wasn't sure if that is what the issue was, or if it was something in the structure of the query itself.

Guess the only way to find out is to do it..

Thanks
Douglas

OK, so I messed with the formatting of the date and datetime, and I believe I have that worked out...

I'm certain that someone has a better solution, but this works for now...

Here is what I did with that.
since the Date Selection module returns a date in the format 11/01/2011 I converted it as follows:

$from_date = date("Y-m-d", strtotime($from)); 
$from_datetime = date("Y-m-d H:i:s", strtotime($from . " -1 second"));
$to_date = date("Y-m-d", strtotime($to));  // yyyy-mm-dd
$to_datetime = date("Y-m-d H:i:s", strtotime($to . " +24 hour"));  // yyyy-mm-dd hh:mm:ss

I subtracted 1 second from the 'from' date to ensure that I covered every second of that day, and added 24 hours to the 'to' date to ensure that I cover every second of that day as well, so the 'from' and 'to' selections are all inclusive.

Then I used those date variables in the query

if($select_report=='1'){// New Member Purchases
      for ($level=1; $level<8; ++$level){
        $sql_ct = "
        SELECT count(p.pos_id)
        FROM position_".$level." AS p, members AS m
        WHERE p.create_date>='".$from_datetime."'
        AND p.create_date<='".$to_datetime."'
        AND p.mem_id=m.mem_id
        AND m.join_date>='".$from_date."'
        AND m.join_date<='".$to_date."'
        GROUP BY p.pos_id
        ";
    print"<br>".$sql_ct;
        $result_ct=mysql_query($sql_ct);
        $request_ct=mysql_fetch_array($result_ct);
        $display_ct[$level]=$request_ct[0];
      }// at this point display_ct [1-7] should hold the numbers to display

Now I am actually getting a 1 across the board for the 7 tables, and while it is better than the zero I was getting, it still isn't right.

So, I have to think that it is the structure of the query.

Any suggestions on the query structure itself?

Thanks again, in advance.

Douglas

[showman13]

$sql_ct = "
        SELECT count(p.pos_id)
        FROM position_".$level." AS p, members AS m
        WHERE p.create_date>='".$from_datetime."'
        AND p.create_date<='".$to_datetime."'
        AND p.mem_id=m.mem_id
        AND m.join_date>='".$from_date."'
        AND m.join_date<='".$to_date."'
        ";

Realized the issue was that I was using a GROUP BY pos_id, and every record was going to have a different pos_id...
Didn't need to Group By anything...

At least I don't think I needed to...
If someone could look at the query and let me know if it sounds correct, I would certainly appreciate it.

Douglas

[smantscheff]

Having 7 position tables with the same structure is most likely a bad design flaw.
If your query returns a count of 1 with the GROUP BY clause and a higher count without it, that means that all records share the same pos_id and it doesn't make sense to count the different ones.
Better show some test data. Still better, show a complete test case (with CREATE TABLE, INSERT and SELECT statement.

[showman13]

Having 7 position tables with the same structure is most likely a bad design flaw.
If your query returns a count of 1 with the GROUP BY clause and a higher count without it, that means that all records share the same pos_id and it doesn't make sense to count the different ones.
Better show some test data. Still better, show a complete test case (with CREATE TABLE, INSERT and SELECT statement.

Actually having 7 position tables with the same structure makes for a very efficient means of extracting and comparing data.

With the queries set up in a for loop, there is no way to have the data extraction cross over from one to the other.

It is working perfectly now since I took out the Group By statement, since it wasn't necessary for this simplistic query.