Grabbing data from multiple tables for multiple requests

[bdmovies]

I thought I was on a roll, but I'm not. I can't figure this out.

 

Here's what I'm trying to get at.

 

A table that displays all the Process Server's name, the number of summons's he has out within 7 days, 7-14 days and 15+ days...pretty simple, it's just a report so management can stay on top of how many papers are out.

 

I've got the server_information table with the server name and ID. and a case_information table with the serverID.

 

I've got a while loop that displays all the server's name, the problem is when I try to get the # of cases....here's what i've got so far.

 

<?php

include('includes/connect.php');

$query = "SELECT * FROM server_information";
$result = mysql_query($query) or die(mysql_error());


echo "<table border=1 cellpadding=5 style=\"border-collapse:collapse;\"><tr><th>Server Name</th><th>1-7</th><th>8-14</th><th>15+</th></tr>";
while($row = mysql_fetch_assoc($result)) {
$serverID = $row['serverID'];
$query2 = "SELECT * FROM case_information WHERE serverID = '$row[serverID]'";
$result2 = mysql_query($query2);


echo "<tr><td>" . $row['fname'] . " " . $row['lname'] . "</td><td>{$row['serverID']}</td><td>$nums</td><td> </td>";
}
?>

[bdmovies]

bump.....I'm really confused on this one, and not to sure where to go.

 

Maybe several queries that do:

<?php
SELECT * FROM case_information ;
$today = gregoriantojd(date('Y-m-d')); //Get today and turn it to JD
$within_seven = $today - 7; // Subtract 7 to give me the JD number a week ago
$date_created_number = gregoriantojd($row['date_created']); 
SELECT * FROM case_information WHERE $date_created_number > $within_seven;
// ^-- this gives me all the cases inside of a week
?>

 

Then repeat that for 8-14 and 15+.....yes, no, dumb, good?

[teng84]

and whats the question?

[bdmovies]

Efficiency. Is that a wise coding choice? Is there a "better" way to do it.

[bdmovies]

This is a stupid syntax question. How do I get the difference of dates from a DB and today? I know how to do it w/ 2 PHP dates, just use gregoriantojd and subtract. But does this mean my values in the DB need to be JD to start with? Also, I've read some about gregorian and JD dates, but I'm confused about the .5 stuff....

[teng84]

sql

 

DATEDIFF()

[bdmovies]

Ok, so now I've got that part down. But can you help me finish the page. I have a while loop that loops through the servers fname and lname and puts them in a table. I want the # of open cases to appear next to that (one column for 1-7 days, one for 8-14, and one for 15+) I can make it loop through and I can get the # of cases for each set of days, my trouble is combining them. Would I put the # of days queries inside of the while loop do, if so, would it be something like this? SELECT....WHERE serverID = '$row['serveriD']?

[bdmovies]

anybody?

[bdmovies]

bump....

[bdmovies]

How can I show all the servers and their particular cases out, right now, it just displays one number for all servers....

 

<?php

include('includes/connect.php');

//Query the Servers
$query1 = "SELECT * FROM server_information";
$result1 = mysql_query($query1) or die(mysql_error());

//This gets the current cases
$month = date('m');
$day = date('d');
$year = date('Y');
$today = gregoriantojd($month, $day, $year);
$seven_days_ago = $today - 7;
$seven_days_ago = jdtogregorian($seven_days_ago);
$seven_days_ago = ereg_replace("/", "-", $seven_days_ago);
$seven_days_ago = explode("-", $seven_days_ago);
$seven_days_ago = $seven_days_ago[2] . "-" . $seven_days_ago[0] . "-" . $seven_days_ago[1];
$today = date('Y-m-d');
$query = "SELECT * FROM case_information WHERE DATEDIFF('$today', '$seven_days_ago')";
$result = mysql_query($query) or die(mysql_error());
$nums = mysql_num_rows($result);
//End Current Cases

while($server_row = mysql_fetch_assoc($result1)) {

//This gets the current cases
$month = date('m');
$day = date('d');
$year = date('Y');
$today = gregoriantojd($month, $day, $year);
$seven_days_ago = $today - 7;
$seven_days_ago = jdtogregorian($seven_days_ago);
$seven_days_ago = ereg_replace("/", "-", $seven_days_ago);
$seven_days_ago = explode("-", $seven_days_ago);
$seven_days_ago = $seven_days_ago[2] . "-" . $seven_days_ago[0] . "-" . $seven_days_ago[1];
$today = date('Y-m-d');
$query = "SELECT * FROM case_information WHERE DATEDIFF('$today', '$seven_days_ago')";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$nums = mysql_num_rows($result);
//End Current Cases

echo "{$server_row['fname']} {$server_row['lname']} $nums<br>";
}

?>	

[bdmovies]

Bump.....

 

What about doing a while loop to get all the servers and then a foreach loop inside of the while loop that gets the cases for each server. I just don't know how to go about the foreach loop....

[bdmovies]

Please, someone.....