PHP Mysqli set variables from 2 different tables to use in result query

[gamma1itman]

Hi,

 

I am trying to produce a result that requires 2 seperate queries to get the values required to produce the result. It works if I hard code the BETWEEN date values but when I try to pull them from the `Reportrange` table no results are returned.

 

I am using includes to set variables, open and close the connection. (also works if BETWEEN dates are hard coded)

See below:

 

The idea is that it can search the `DMCstats` table for a count of each distinct 'gender' value WHERE the 'encdate' matches the range set in `Reportrange` record#1(---->bdate and edate)

 

I think the issue is with the way I am calling the variables $bdat , $edat for $result

 

My apologies in advance if this post does not conform to this forums expectations, its my first post here, feel free to enlighten me! 

 

Thanks in advance to all those donating their time for the greater good!

 

 

Open Connection Include:

<?php
//Conection Info
$con=mysqli_connect("localhost","******","******","******");
// Check connection
if (mysqli_connect_errno())
echo "Failed to connect to MySQL: " . mysqli_connect_error();
?>

Close Connection Include:

<?php
mysqli_close($con);
?> 

Date Variables include:

<?php
include 'openconnection.php';
$bdat=mysqli_query($con,"SELECT bdate, FROM `ReportRange` WHERE cf_id=1");
$edat=mysqli_query($con,"SELECT edate, FROM `ReportRange` WHERE cf_id=1");
include 'closeconnection.php';
?>

Gender Query:

//Open Connection
include 'openconnection.php';

//Set Variables
include 'datevars.php';

//Query
$result=mysqli_query($con,"SELECT gender,encdate, COUNT(gender) AS gender_count FROM `DMC_Stats` WHERE encdate BETWEEN " . $bdat . " AND " . $edat . " GROUP BY gender");

//Output to html
echo "<table border='1'>
<tr>
<th>Gender</th>
<th>Total</th>
</tr>";

while($row=mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['gender'] . "</td>";
  echo "<td>" . $row['gender_count'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

//Close connection
include 'closeconnection.php';
?>

Regards

Edited June 12, 2013 by gamma1itman

[AbraCadaver]

Obviously not tested, but you can use one query and then you still have to fetch the row from the result:

include 'openconnection.php';
$result = mysqli_query($con,"SELECT bdate, edate FROM `ReportRange` WHERE cf_id=1");
$row = mysqli_fetch_assoc($result);
$bdat = $row['bdate'];
$edat = $row['edate'];
include 'closeconnection.php';

Or you could do it in one query like this or similar in Gender Query:

$result = mysqli_query($con, "
SELECT gender, encdate, COUNT(gender) AS gender_count FROM `DMC_Stats`
WHERE encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY gender
");

[gamma1itman]

Awsome! Thanks for the speedy reply.....I will try this shortly and post my findings.

[gamma1itman]

AbraCadaver......You let me sleep!  THANKS

 

I went with option 2 because I am using Joomla with Chronoforms to populate the tables and then building the reports with php via sourcerer as articles.

 

If I was coding from scratch I would have used the first option because I think it would be less repetitive coding.

 

This is the single file used to produce the required output.

<?php

// Conection Info
$con=mysqli_connect("localhost","******","*******","******");

// Check connection
if (mysqli_connect_errno())
echo "Failed to connect to MySQL: " . mysqli_connect_error();

// Query
$result = mysqli_query($con, "
SELECT gender, encdate, COUNT(gender) AS gender_count FROM `DMC_Stats`
WHERE encdate BETWEEN
(SELECT bdate FROM `ReportRange` WHERE cf_id=1)
AND
(SELECT edate FROM `ReportRange` WHERE cf_id=1)
GROUP BY gender");

// Output to html
echo "<table border='1'>
<tr>
<th>Gender</th>
<th>Total</th>
</tr>";

while($row=mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['gender'] . "</td>";
  echo "<td>" . $row['gender_count'] . "</td>";
  echo "</tr>";
  }
echo "</table>";
// Close Connection
mysqli_close($con);
?>

Once Again,

 

Many Thanks!!!

[Barand]

or

SELECT gender, COUNT(*) as total 
FROM dmc_stats
JOIN ReportRange ON encdate BETWEEN bdate AND edate
WHERE cf_id = 1
GROUP BY gender;

[gamma1itman]

In Response to Barand....

 

Thx for the MYSQL statement.......

 

From PHPmyadmin SQL Tab

#1052 - Column 'cf_id' in where clause is ambiguous

I understand this to mean that ID and/or Title exist in both tables - 

 

The fix was......instead of:

SELECT gender, COUNT(*) as gender_count 
FROM dmc_stats
JOIN ReportRange ON encdate BETWEEN bdate AND edate
WHERE cf_id = 1
GROUP BY gender;

I used

SELECT gender, COUNT(*) as gender_count 
FROM `DMC_Stats`
JOIN `ReportRange` ON encdate BETWEEN bdate AND edate
WHERE ReportRange.cf_id = 1
GROUP BY gender;

Must use the table name or alias name then column name like: 

Note this WHERE statement uses table.field instead of just field

 

Of course you would need to see the tables to know that was going to be an issue so.........

 

Great solution just the same.