I can capture and display the correct results of my query - but I only want a subset of the query, and the problem I'm running into has to do with the date formatting and that columns in two different tables have the same name. Thus:
// if the client has logged in
<?}elseif($date_choice=="Submit"&&$option!="login"){
// take a look at the dates and compare them
if(!empty($target_date)&&!empty($target_date2)) {
list($month,$day,$year) = explode("/",$target_date);
list($month2,$day2,$year2) = explode("/",$target_date2);
}
$mysqltime = "$year" . "$month" . "$day" . "000000";
$mysqltime2 = "$year2" . "$month2" . "$day2" . "235959";
// show which clients were created between a set of dates
$sql = "SELECT * FROM client WHERE DATE_FORMAT(date_added,'%Y%m%d%H%i%s') BETWEEN '$mysqltime' AND '$mysqltime2' ORDER BY date_added ASC";
// store the results
$res = mysql_db_query($GLOBALS[mydb],$sql,$conn);
/ what I want to do is take the results (a client's id number) and grab data from another table that also contains the client id number - the problem resides in that they both also contain the same column called "date_added" and it confuses the system I think - I tried specifying the table name, but I think my syntax is wrong /
[B][COLOR=RoyalBlue]$sql2 = "SELECT client.client_id, client_follow.comment FROM client, client_follow WHERE client.client_id = client_follow.client_id And client_id in '$res'";
$res2 = mysql_db_query($GLOBALS[mydb],$sql2,$conn);[/COLOR] [/B]
// if there is no data, error
if (!$res) {
echo( mysql_error());
}
// do a new search for clients
echo "<a href=\"./clientsignup_dsearch.php?\">New Search</a><br>";
// count the number of rows
$total = mysql_num_rows($res);
// if there are rows
if($total>0){
// if they are the same display one message, different display another message
if($target_date!=$target_date2){
echo "$total clients have been created between $target_date and $target_date2.<br>Here is the list:";
}else{
echo "$total clients have been created this day ($target_date).<br>Here is the list:";
}
?>
<br><br><br>
// display the results in a table
<table width=95%>
<tr>
<td align="left" width=40%>Name</td>
<td align="left" width=40%>Marketing Notes</td>
<td align="center" width=25%>Date Created</td>
</tr>
// show and hyperlink the results of the first query
<?
while($row = mysql_fetch_array($res)){
$link = "<a href=\"./client_edit.php?client_id=$row[client_id]\" target=\"_blank\">$row[firstname] $row[lastname]</a>";
$mktg = "<a href=\"./marketing_follow.php?client_id=$row[client_id]\" target=\"_blank\">$row[lastname]</a>";
$notes_detail = "$row2[comment]";
if(substr($row[date_added],8,2) % 2){$color = "blue";}else{$color = "red";}
?>
How can I store the results of the first, original query, and then take one piece (the client id number) and go get the data from the other table?
I've spent all day troubleshooting this - whatever help you can give ...
Thanks!
