Make results of first query the NOT IN part of a second

Question

I have two MySQL tables:

table officehours (ID, dayslot, appt_time) table appt (ID, appt_date, appt_time)

I would like to select ALL office hours for a particular day (example 2016-01-26) that do NOT have an appointment at that time. (available appt times)

In my queries code I have:

// get BOOKED appointment times
mysql_select_db($database_IHC, $IHC);

$query_booked18 = "SELECT appt_time FROM appt WHERE appt_date = 
'".$monthyear."-".$monthnum."-18'";

$booked18 = mysql_query($query_booked18, $IHC);

// MAKE BOOKED QUERY PART OF NOT IN STATEMENT FOR AVAILABLE QUERY

while($row = mysql_fetch_array($booked18)){
$temp[] = '"'.$row[0].'"';

// PUT COMMAS BETWEEN VALUES
$bookedstmt18 = implode(",",$temp);

// AVAILABLE APPOINTMENTS QUERY
$query_available18 = "SELECT * FROM officehours WHERE dayslot = 
(dayofweek('".$monthyear."-".$monthnum."-18')-1) AND appt_time NOT IN 
(".$bookedstmt18.") ORDER BY (date) desc";

$available18 = mysql_query($query_available18, $IHC);

$row_available18 = mysql_fetch_assoc($available18);
$totalRows_available18 = mysql_num_rows($available18);

On the section of my calendar displaying appointments (18.php), I have:

//check to see if closed first
if ($totalRows_closed18 > 0 ) {
echo "<td>CLOSED: </td><td>".$row_closed18['reason']."</td>";
echo $query_available18;
} else {  // SHOW AVAILABLE APPOINTMENTS
echo '<td width="92" height="75" align="left" valign="top" style="text-    align: left">';
do { 
<a href="https://innerhealerchiropractic.com/index.php/appt/VIPschedule/"  class="w3-link"><?php echo     $row_available18['appt_time']; ?></a></br>
 <?php } while ($row_available18 = mysql_fetch_assoc($available18)); 
  echo '</td><td width="92" align="left" valign="top" style="text-  align:left">&nbsp;</td>';
       } // END SHOW APPOINTMENTS ?>

I'm getting an Error-

"Error 500 mysql_fetch_assoc() expects parameter 1 to be resource, boolean given"

Can someone please help me figure this one out?

Get rid of the first query and just do this: `$totalquery = "SELECT * FROM officehours WHERE dayslot = (dayofweek('".$monthyear."-".$monthnum."-18')-1) AND appt_time NOT IN (SELECT appt_time FROM appt WHERE appt_date = '".$monthyear."-".$monthnum."-18') ORDER BY date desc";` — Matt, Jan 25 '16 at 06:45
try to make one query... show me your both query (on browser output) not include php code — Monty, Jan 25 '16 at 07:21

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

Your error is due to your SQL query having an error in it

There is a better way to do this.

Essentially, what you're doing is just the same as doing a sub query as you're getting a single result and imploding. So, lets break this down into easily readable lines of code:

$totalquery = "
SELECT * FROM officehours 
WHERE 
    dayslot = (
        dayofweek('".$monthyear."-".$monthnum."-18')-1
    ) AND
    appt_time NOT IN (
        SELECT appt_time FROM appt 
        WHERE appt_date = '".$monthyear."-".$monthnum."-18'
    ) 
ORDER BY date desc";

The subquery in not in acts the same way as doing another select then imploding the values.

Then we just query and fetch assoc

$totalresults = mysql_fetch_assoc($totalquery);
$totalrows = mysql_num_rows($totalquery);

This should replace your whole first section of code and resolve your error.

That is true, I totally forgot to state it on this post. I was just continuing with OPs db connection. — Matt, Jan 25 '16 at 08:20

Make results of first query the NOT IN part of a second

1 Answers1

There is a better way to do this.