SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query with OR statment

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


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

  2. #2
    SitePoint Evangelist Alluvian's Avatar
    Join Date
    Jan 2007
    Location
    Orlando, FL
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have two tables here, so you really should explicitly call each table out when you reference a field. What table are 'company, time, table_id' in?

    Code:
      SELECT distinct *
        FROM ad_order
        LEFT OUTER
        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
    Try that. Not sure if it will work with * in the select.

  3. #3
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This was my original query, I just changed to * to see if it would help any...it didn't.

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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what table is table_id in? what's it for?

    aside: alluvian, please don't ever write "select distinct star"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    <?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
    Code:
    <?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&#37;' 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
    Attached Files Attached Files

  6. #6
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so far I've tried:
    Code:
    $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:

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

  7. #7
    SitePoint Evangelist Alluvian's Avatar
    Join Date
    Jan 2007
    Location
    Orlando, FL
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what table is table_id in? what's it for?

    aside: alluvian, please don't ever write "select distinct star"
    LOL, I figured that would make your head explode! It is like saying bloody mary three times in the bathroom with the lights out.

    I can see it now, years from now...

    Person 1: Legends say that the spirit of a the great mysql guru rudy will appear if you type 'select distinct *' into your holopad three times... His fury will know no bounds, and he will violently provide you with more appropriate and well written code.

    Person 2: *phhht* that's stupid! *writes* "Select DISTINCT *", "Select DISTINCT *", "Select DISTINCT..." I can't do it man!

    Person 3: CHICKEN!!!!


    Ahem, sorry for the sidetrack, my mind wanders horribly. I only used it because I had no idea what fields he actually WANTED returned. I know it was bad when writing it, but didn't know what else to put there as an example. Bad of me to spread crappy crappy sql. Sorry.

  8. #8
    SitePoint Evangelist Alluvian's Avatar
    Join Date
    Jan 2007
    Location
    Orlando, FL
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Matt, somebody should be able to get you the right query string if you provide us with your actual table structure and what fields you want returned from the query.

    Do you have access to mysql directly through something like phpmyadmin or a command line? Getting the query working in mysql directly before putting the php code around it would be the place to start.

    But give us your table structure so we have something more to work with.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Alluvian View Post
    I can't do it man!
    that's pretty amusing, you're a good writer

    for writing syntax with unknowns, i prefer to use an ellipsis
    Code:
    select ...
      from ...
     where foo = 'bar'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select ...
      from ...
     where foo = 'bar'
    I tried that...my xserve gave me the finger.

    Alluvain
    I've posted both the table selection page and the table ad time page as well as a sample of my job log table.

  12. #12
    SitePoint Addict
    Join Date
    Apr 2007
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I ended up just adding another query

    Code:
    <?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>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •