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