Query table for 1data OR 2data

matthewst

In job_log I'm querying an ad_id, not every record has an ad_id. For those records I would like to query for table_id.

I need something like:
$query="SELECT * FROM ad_order LEFT JOIN (job_log) ON (job_log.ad_id=ad_order.id) WHERE ad_order.cust_id=$table_id ||||OR table_id=$table_id|||| ORDER BY company,time";

$query="SELECT * FROM ad_order LEFT JOIN (job_log) ON (job_log.ad_id=ad_order.id) WHERE ad_order.cust_id=$table_id ORDER BY company,time";

matthewst

The full story:

I work for a company that sells ad space on restraunt table tops. Some of the restraunts and advertisers are pickier than others. The longer we spend on an ad the less we make (time is money). I have to find a way to determine how much time each ad and table is taking.

I can pull the ad time per ad by searching the job_log table for the ad_id's. I also need to know how long it takes to put the table together. This information is also in the jog_log table, but the table records don't have any information in the ad_id column so when I run the query it skips those records. The $table_id comes from a dropdown menu on a previous page.

select_table.php

<?php
	include('include/user_check.php');
	include('include/db_con.php');
	$id = $_SESSION['track_id'];

$abc_current_tables = mysql_query("SELECT table_id , rest_name , set_no , status_3, dist_id FROM abc_tables WHERE status_2<'6' order by rest_name", $db_link);
	$num_current_tables = mysql_num_rows($abc_current_tables);
?>

<HTML>
<HEAD>
<link rel="stylesheet" type="text/css" href="main.css">
<link rel="stylesheet" type="text/css" href="styles.css">
<TITLE>:: In-House Projects ::</TITLE>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
</HEAD>
<BODY BGCOLOR=#FFFFFF leftmargin="0" marginwidth="0" topmargin="0" marginheight="0">
		<div align="center">
			<TABLE WIDTH=758 BORDER=0 CELLPADDING=0 CELLSPACING=0>

			<? include('include/top.php'); ?>
			<TR height="516">
				<TD valign="top" height="516">
					<div align="center">
						<form name="FormName" action="table_ad_time.php" method="post">
							<b>Restaurants </b>(<? echo $num_current_tables; ?>)<br>
							<select name="table_id" class="formTextbox" size="1">
								<option value="">-- Select a Restaurant --</option>
							<?  while($row_current_tables = mysql_fetch_array($abc_current_tables)) {	

									$table_id = $row_current_tables['table_id'];
									$rest_name = $row_current_tables['rest_name'];
									$set_no = $row_current_tables['set_no'];
									$dist_id = $row_current_tables['dist_id'];
									$query_dist = mysql_query("SELECT fname, lname, sales_id FROM sales_rep WHERE sales_id = '$dist_id'", $db_link);
									while ($row_dist_name = mysql_fetch_array($query_dist)){

									$dist_lname = $row_dist_name['lname'];
									$dist_fname = $row_dist_name['fname'];
									} 

							?>
								<option value="<?=$table_id;?>"><? echo $rest_name. " Set " .$set_no. "--" .$dist_lname. ", " .$dist_fname;?></option>
								<? } ?>
							</select><br>
						    <input type="submit" class="formTextbox" name="submit" value="Get Table Info"><br>
							</form>
</body>
</html>

table_ad_time.php

<?php
	include('include/user_check.php');
	include('include/db_con.php');
	$id = $_SESSION['track_id'];
?>

<html>
<BODY BGCOLOR=#FFFFFF leftmargin="0" marginwidth="0" topmargin="0" marginheight="0">
		<div align="center">
			<TABLE WIDTH=758 BORDER=0 CELLPADDING=0 CELLSPACING=0>
				<? include('include/top.php'); ?>
				<TR height="516">
				  <TD valign="top" height="516">
						<div align="center">

<?php
$query="SELECT rest_name FROM abc_tables WHERE table_id=$table_id";
$result=mysql_query($query);
while ($row = mysql_fetch_assoc($result))
{
$rest_name = $row['rest_name'];
}
?>

<?php
echo "<center>$rest_name<br><br></center>";
echo "<center>Table ID #$table_id<br><br></center>";
echo "<center><table border = '1' cellspaceing = '2' cellpadding = '4' width = '100%' bgcolor = '#999999'>";
echo "<tr><td width = '40%'><font color='#ffffff'>Company Name</font></td><td width = '20%'><font color='#ffffff'>Employee Name</font></td><td width = '20%'><font color='#ffffff'>Time</font></td><td width = '25%'><font color='#ffffff'>Action</font></td></tr>";
echo "</table><br></center>";

$query="SELECT ad_order.company company, job_log.employee_id employee_id, job_log.time time, job_log.action action FROM ad_order LEFT JOIN (job_log) ON (job_log.ad_id=ad_order.id) WHERE ad_order.cust_id=$table_id ORDER BY company,time";
$result=mysql_query($query);
while ($row = mysql_fetch_assoc($result))

{
$company = $row['company'];
$time = $row['time'];
$employee_name2 = $row['employee_id'];
$action3 = $row['action'];
$showtime = date('m/d/y-h:m:s',$time);
	if ($showtime=="12/31/69-06:12:00")
	$showtime = "No Entry";
	else
	$showtime = date('m/d/y-h:i:s',$time);

    $query2 = "SELECT * FROM employees WHERE employee_id = '$employee_name2'";
    $result2 = mysql_query($query2);
    while($row2 = mysql_fetch_assoc($result2)) 
    {
			$employee_name2 = $row2['fname']." ".$row2['lname'];
			if ($employee_name2=="")
			$employee_name2 = "No Entry";
			else
			$employee_name2 = $row2['fname']." ".$row2['lname'];

    }

	$query3 = "SELECT * FROM job_actions WHERE action_id = '$action3'";
    $result3 = mysql_query($query3);
    while($row3 = mysql_fetch_assoc($result3)) 
    {
             $action3 = $row3['action_name'];
			 	if ($action3=="")
				$action3 = "No Entry";
				else
				$action3 = $row3['action_name'];
    }

echo "<center><table border = '0' cellspaceing = '0' cellpadding = '1' width = '100%' bgcolor = '#ffffff'>";
echo "<tr><td abbr = abbr_text width = '40%'>$company</td><td align = 'center' width = '20%'>$employee_name2</td><td width = '20%'>$showtime</td><td align = center width = '25%'>$action3</td></tr>";
echo "</table></center>";
}
echo "<br><br><br>";
mysql_close();
?>

</TD>
</TR>
</table>
</body>
</html>

here is a sample of my job_log table

laserlight

How does $table_id come into the picture? I think you are better off describing what exactly you want to select with what conditions than writing pseudo-SQL.

matthewst

so far I've tried:

$query="SELECT 
                 ao.* 
              FROM 
                 ad_order ao LEFT JOIN job_log jl ON (jl.ad_id=ao.id) 
              WHERE 
                 ao.cust_id=$table_id OR ao.table_id=$table_id 
              ORDER BY 
                 ao.company, ao.time";

That gave me an error:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

I've also tried:

if (ad_id=="")
	 $query="SELECT ad_order.company company, job_log.employee_id employee_id, job_log.time time, job_log.action action FROM ad_order LEFT JOIN (job_log) ON (job_log.table_id=ad_order.id) WHERE 			     ad_order.cust_id=$table_id ORDER BY company,time";
	 else
	 $query="SELECT ad_order.company company, job_log.employee_id employee_id, job_log.time time, job_log.action action FROM ad_order LEFT JOIN (job_log) ON (job_log.ad_id=ad_order.id) WHERE 	     ad_order.cust_id=$table_id ORDER BY company,time";

$query="SELECT * FROM ad_order AS a LEFT JOIN job_log AS j ON j.ad_id = a.id WHERE (CASE WHEN ad_id = NULL OR ad_id = '' THEN ad_id = $table_id ELSE table_id = $table_id END) ORDER BY company,time";

but those lock up my server

matthewst

laserlight wrote:
How does $table_id come into the picture? I think you are better off describing what exactly you want to select with what conditions than writing pseudo-SQL.

$table_id comes from table_select.php

table_id#1 has ad1 and ad2 and ad3
table_id#2 has ad4 and ad5 and ad6

If I need to know how long it took to make table 2 then I need all the ad making time and the table "put together time". All this is in the job_log table. With my query (which currently looks for ad_id only) I can get the amount of time it takes per ad for a selected table but I can't get the table"put together time" because there is no ad_id for those records. So I need my query to determine if a record has an ad_id. If it dosen't then get the data for the table_id that matches $table_id.

laserlight

So I need my query to determine if a record has an ad_id. If it dosen't then get the data for the table_id that matches $table_id.

What happens if it does? I am not an expert at SQL, but I guess that you want something like:

$query = "SELECT * FROM ad_order LEFT JOIN (job_log) ON (job_log.ad_id=ad_order.id)
          WHERE job_log.ad_id IS NOT NULL
             OR (job_log.ad_id IS NULL AND job_log.table_id=$table_id)
          ORDER BY company, time";

matthewst

The problem is I need to be able to calculate the total build time per table (real life table). I can get the build time for individual ads, but I can't get the build time for the table itself since there is no data in the ad_id field of the records pertaining to the actual tables (real life tables). What I do have is the table_id.

I need a query to: A. Search ad_order and job_log for a particular ad_id when found display the relevant information. B. While searching the job_log table if the query comes across a record without an ad_id then look for the table_id for that record. If the table_id and the $table_id match then display the relevant information.

matthewst

I ended up just adding another query

<?php
	include('include/user_check.php');
	include('include/db_con.php');
	$id = $_SESSION['track_id'];
?>

<html>
<BODY BGCOLOR=#FFFFFF leftmargin="0" marginwidth="0" topmargin="0" marginheight="0">
		<div align="center">
			<TABLE WIDTH=758 BORDER=0 CELLPADDING=0 CELLSPACING=0>
				<? include('include/top.php'); ?>
				<TR height="516">
				  <TD valign="top" height="516">
						<div align="center">

<?php
$query="SELECT rest_name FROM abc_tables WHERE table_id=$table_id";
$result=mysql_query($query);
while ($row = mysql_fetch_assoc($result))
{
$rest_name = $row['rest_name'];
}
?>

<?php
echo "<center>$rest_name<br><br></center>";
echo "<center>Table ID &nbsp;#$table_id<br><br></center>";
echo "<center><table border = '1' cellspaceing = '2' cellpadding = '4' width = '100%' bgcolor = '#999999'>";
echo "<tr><td width = '40%'><font color='#ffffff'>Company Name</font></td><td width = '20%'><font color='#ffffff'>Employee Name</font></td><td width = '20%'><font color='#ffffff'>Time</font></td><td width = '25%'><font color='#ffffff'>Action</font></td></tr>";
echo "</table><br></center>";

$query4="SELECT * FROM job_log WHERE $table_id=table_id";

$result4=mysql_query($query4);
while ($row4 = mysql_fetch_assoc($result4))

{
$time_table4 = $row4['time'];
$employee_name5 = $row4['employee_id'];
$action6 = $row4['action'];
$showtime2 = date('m/d/y-h:i:s',$time_table4);
	if ($showtime2=="12/31/69-06:00:00")
	$showtime2 = "No Entry";
	else
	$showtime2 = date('m/d/y-h:i:s',$time_table4);

    $query5 = "SELECT * FROM employees WHERE employee_id = '$employee_name5'";
    $result5 = mysql_query($query5);
    while($row5 = mysql_fetch_assoc($result5)) 
    {
			$employee_name5 = $row5['fname']." ".$row5['lname'];
			if ($employee_name5=="")
			$employee_name5 = "No Entry";
			else
			$employee_name5 = $row5['fname']." ".$row5['lname'];

    }

	$query6 = "SELECT * FROM job_actions WHERE action_id = '$action6'";
    $result6 = mysql_query($query6);
    while($row6 = mysql_fetch_assoc($result6)) 
    {
             $action6 = $row6['action_name'];
			 	if ($action6=="")
				$action6 = "No Entry";
				else
				$action6 = $row6['action_name'];
    }

echo "<center><table border = '0' cellspaceing = '0' cellpadding = '1' width = '100%' bgcolor = '#ffffff'>";
echo "<tr><td align = 'center' abbr = abbr_text width = '40%'>$table_id</td><td align = 'center' width = '20%'>$employee_name5</td><td width = '20%'>$showtime2</td><td align = center width = '25%'>$action6</td></tr>";
echo "</table></center>";
}

$query="SELECT ad_order.company company, job_log.employee_id employee_id, job_log.time time, job_log.table_id table_id, job_log.action action FROM ad_order LEFT JOIN (job_log) ON (job_log.ad_id=ad_order.id) WHERE ad_order.cust_id=$table_id ORDER BY company,time";

$result=mysql_query($query);
while ($row = mysql_fetch_assoc($result))

{
$company = $row['company'];
$time = $row['time'];
$employee_name2 = $row['employee_id'];
$action3 = $row['action'];
$showtime = date('m/d/y-h:i:s',$time);
	if ($showtime=="12/31/69-06:00:00")
	$showtime = "No Entry";
	else
	$showtime = date('m/d/y-h:i:s',$time);

    $query2 = "SELECT * FROM employees WHERE employee_id = '$employee_name2'";
    $result2 = mysql_query($query2);
    while($row2 = mysql_fetch_assoc($result2)) 
    {
			$employee_name2 = $row2['fname']." ".$row2['lname'];
			if ($employee_name2=="")
			$employee_name2 = "No Entry";
			else
			$employee_name2 = $row2['fname']." ".$row2['lname'];

    }

	$query3 = "SELECT * FROM job_actions WHERE action_id = '$action3'";
    $result3 = mysql_query($query3);
    while($row3 = mysql_fetch_assoc($result3)) 
    {
             $action3 = $row3['action_name'];
			 	if ($action3=="")
				$action3 = "No Entry";
				else
				$action3 = $row3['action_name'];
    }

echo "<center><table border = '0' cellspaceing = '0' cellpadding = '1' width = '100%' bgcolor = '#ffffff'>";
echo "<tr><td align = 'center' abbr = abbr_text width = '40%'>$company</td><td align = 'center' width = '20%'>$employee_name2</td><td align = 'center' width = '20%'>$showtime</td><td align = center width = '25%'>$action3</td></tr><td align = 'center' abbr = abbr_text width = '40%'>$table_time4</td>";
echo "</table></center>";
}
echo "<br><br><br>";
mysql_close();
?>

</TD>
</TR>
</table>
</body>
</html>