Selecting records greater than yesterday throws an error 3

[Evil8]

I have the following record selection (thank you again r937):

$query = "SELECT event.eventdate
     , event.eventtime
     , venue.name
     , venue.address
     , venue.city
     , venue.state
     , venue.zip
     , companyplan.company
     , companyplan.plantype
  FROM event
INNER
  JOIN venue
    ON venue.venueid = event.eventplaceID
INNER
  JOIN companyplan
    ON companyplan.planid = event.eventplanID
ORDER
    BY event.eventdate
     , event.eventtime
     , venue.city
     , venue.state";

This works great, but now they only want to pull records with events dates from today on forward.

I tried:

FROM event WHERE eventdate = DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 1 DAY)

That throws an error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ... line 109

line 109 is:

while ($row = mysql_fetch_array($result)):

Can anyone tell me what I'm doing wrong?

Thank you!

 

[vacunita]

No way to tell.
Basically what that error means is that your variable $result is not a valid resource from which to get records, so something went wrong in your query that returned likely a false value.

From the PHP side, perhaps asking mysql for a more detailed error may work.

$result = mysql_query(...) [red]or die(mysql_error());[/red]

It could be the query is not returning any rows. Have you tried to run it directly on the database?




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[Evil8]

Hey Vacunita, I resolved the error by moving the WHERE just before the ORDER statement. The problem now is that it returned no data. I added an event record this morning dated for the 19th to make sure I'd get at least one record during the testing. The DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 1 DAY) must be wrong. I'll have to work on that now.

After I got this working I was going to ask you in the php forum where/how to place code to return a message for the user if the array comes back empty like it seems to be now.

Have a great day!

 

[Evil8]

I got it working! I'm used:

WHERE eventdate >= DATE(NOW())

Returns only the test data with the eventdate of 12/19.

Now for a sorry no events message on an empty record set....

 

[vacunita]

When modifying queries its always a good idea to run them directly on the DB via any interface you want so you remove PHP from the equation.

Once you know the query works and returns results, you can then plug it into the PHP.

As for errors, mysql_query returns false when it fails so you can check whether your variable in this case $results is false and issue a user friendly message about it.
mysql_error() can be used to debug.


You can also check for amount of rows returned by using mysql_num_rows().

Or use mysql_affected_rows() if you performed an update or insert in which no rows would be returned but you still need to know how many where changed by the operation.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[Evil8]

I'm getting a syntax error.

<?php
  $colfill=0;
   while ($row = mysql_fetch_array($result)): 
          if(mysql_num_rows($result)==0){
          echo "Sorry there are no meetings scheduled. Please check back soon."
          }
?>

Hmmm....

 

[Evil8]

putting a ; on the echo line changed the error from a syntax error to

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ... line 110

Here's the full code:

<?php 
	mysql_connect($host,$username,$password);
	@mysql_select_db($database) or die("Unable to select database");
		
	$query = "SELECT event.eventdate
                  ...

	$result=mysql_query($query);
	$plan = $city = $state = ''; 
	$first = true;   
?>

<style type="text/css">
...
</style>
  
<div class="event">

<?php
  $colfill=0;
   while ($row = mysql_fetch_array($result)): 
          if(mysql_num_rows($result)==0){
          echo "Sorry there are no meetings scheduled. Please check back soon.";
          }
?>
<?php if ($plan !== $row['plantype'] || $state !==$row['state'] || $city !== $row['city']):
          $plan = $row['state'];    
          $city = $row['city'];    
          $plan = $row['plantype'];    
      if (!$first) echo '</div>'; 
      else $first = false;
        if($colfill==0){
?>

<div class="citystateplancolumn">

<?php } ?>
  
  <div class="citystateplan">
   several div statements...

<?php endif; ?>
     
  </div>

<?php 
    $colfill++;
    if($colfill==3){
    $colfill=0;
    echo "</div>";
    }
    endwhile;
?>

 

[vacunita]

You are checking for rows while trying to fetch rows. If there are no rows to fetch because the query returned a false due to a bad query then attempting to fetch any rows would cause the error.
If the query succeeded but returned no rows then your while loop will never execute.

Try this:

<?php 
    mysql_connect($host,$username,$password);
    @mysql_select_db($database) or die("Unable to select database");
        
    $query = "SELECT event.eventdate
                  ...

    $result=mysql_query($query);
    $plan = $city = $state = ''; 
    $first = true;   
?>

<style type="text/css">
...
</style>
  
<div class="event">

<?php
  $colfill=0;
[red]if(!$result){
echo "There's an error with the mysql query: " . $query . "<br>The Error is:" . mysql_error()";
die(); 
}
elseif(mysql_num_rows($result)==0){
          echo "Sorry there are no meetings scheduled. Please check back soon.";
die();
          }
[/red]
   while ($row = mysql_fetch_array($result)): 
          
?>
<?php if ($plan !== $row['plantype'] || $state !==$row['state'] || $city !== $row['city']):
          $plan = $row['state'];    
          $city = $row['city'];    
          $plan = $row['plantype'];    
      if (!$first) echo '</div>'; 
      else $first = false;
        if($colfill==0){
?>

<div class="citystateplancolumn">

<?php } ?>
  
  <div class="citystateplan">
   several div statements...

<?php endif; ?>
     
  </div>

<?php 
    $colfill++;
    if($colfill==3){
    $colfill=0;
    echo "</div>";
    }
    endwhile;
?>

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[Evil8]

Excellent! Thank you very much!

Phase two is complete... but I'm done for the day.

Have a great weekend.

 

[r937]

f.y.i. this is incorrect --

DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 1 DAY)

CURDATE() is mysql's synonym for the sql standard CURRENT_DATE, which mysql also supports, so you should always use CURRENT_DATE

and CURRENT_DATE can of course be used in a context where a datetime value is required, it has an implicit time of midnight, so you don't have to "concat" midnight to it

(by the way, you can only CONCAT strings, so what you were doing involved conversion from date to string, and then back again from string to date)

mysql supports date arithmetic directly, so you don't have to use the DATE_SUB function, and the equivalent to the above is

CURRENT_DATE - INTERVAL 1 DAY


you also used DATE(NOW()) at one point

NOW() is mysql's synonym for the sql standard CURRENT_TIMESTAMP, which mysql also supports, so you should always use CURRENT_TIMESTAMP

but there's no need to take the current datetime value and extract the date from it, when you can just use

CURRENT_DATE



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Evil8]

Thanks r937! I changed the code.

DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 1 DAY)

Never worked correctly to begin with.

There is a lot of incorrect information out on the web and some of the correct information is sometimes difficult to follow. I really appreciate eveyony here in the forums taking the time to help us noobs do things the right way.