[SOLVED] Query with OR statement

matthewst · April 25, 2007

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 · April 25, 2007

This was my original query, I just changed to * to see if it would help any...it didn't.

$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";

I know one of you freaks can help me solve this one.

marmite · April 25, 2007

I'm not clear on what the actual problem is.

The join is failing?

obsidian · April 25, 2007

Without knowing all your columns precisely, I think your pseudo-code has almost nailed it:

SELECT * FROM ad_order AS a
LEFT JOIN job_log AS j ON j.ad_id = a.id
WHERE (a.cust_id = $table_id OR table_id = $table_id)
ORDER BY company,time

matthewst · April 25, 2007

I'm pulling data from two tables at once. The problem is that in job_log I'm looking ad_id, but not all records have an ad_id. For those records I want to use the table_id (in the job_log table) that is equal to $table_id (in the ad_order table).

obsidian · April 25, 2007

I'm pulling data from two tables at once. The problem is that in job_log I'm looking ad_id, but not all records have an ad_id. For those records I want to use the table_id (in the job_log table) that is equal to $table_id (in the ad_order table).

That's a bit more complex since you'll want to actually check to see if there is a value in the ad_id column first before you know which column to match. Try using a CASE and see if that will work:

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

Not positive this is exactly what you're after, but it's a start.

matthewst · April 26, 2007

we're on the right track but the page doesn't load

the counter just keeps counting and opra just stays

on my table selector page

matthewst · April 26, 2007

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

[attachment deleted by admin]

matthewst · April 26, 2007

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 · April 26, 2007

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 · April 30, 2007

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  #$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>

Sign In

[SOLVED] Query with OR statement

Recommended Posts

matthewst

Link to comment

Share on other sites

matthewst

Link to comment

Share on other sites

marmite

Link to comment

Share on other sites

obsidian

Link to comment

Share on other sites

matthewst

Link to comment

Share on other sites

obsidian

Link to comment

Share on other sites

matthewst

Link to comment

Share on other sites

matthewst

Link to comment

Share on other sites

matthewst

Link to comment

Share on other sites

matthewst

Link to comment

Share on other sites

matthewst

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information