Solved

What's wrong with this select statement

Posted on 2009-04-07
9
253 Views
Last Modified: 2013-12-12
Code is attached...

It doesn't matter if I have a "<" or a ">" when I'm making my comparison between the entry date and the "DATE_SUB" stuff.

What am I doing wrong?
$query = "SELECT * FROM sweepstakes WHERE contestant_id = '{$contestant_id}' AND entry_date > DATE_SUB(CURRENT_DATE, INTERVAL 24 HOUR);";
		$query_vivian=mysqli_query($cxn,$query)
		or die("Couldn't execute query.");
		$query_count=mysqli_num_rows($query_vivian);
		//if there are no records returned by this select statement, that means every entry is less than 24 hours and thus the user CAN enter at this time
		echo $query;
		echo "insert me!";
	}

Open in new window

0
Comment
Question by:brucegust
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24092220
what is the data type of entry_date?
0
 

Author Comment

by:brucegust
ID: 24092231
Date - 2009-04-06
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24092280
ok, so what is the "problem", actually?
please clarify!
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24092549
You are not doing anything with $query_count. It should be in an if-statement:


if($query_count==0)
  echo "insert me!";
else
  echo "found, count=".$query_count;

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24092784
Try this instead
$query = 'SELECT * FROM sweepstakes WHERE contestant_id = "' . $contestant_id . '" AND (DATEDIFF(entry_date, (DATE_SUB(CURRENT_DATE, INTERVAL 24 HOUR)) > 1);";
                $query_vivian=mysqli_query($cxn,$query) or die("Couldn't execute query.");
                $query_count=mysqli_num_rows($query_vivian);
                //if there are no records returned by this select statement, that means every entry is less than 24 hours and thus the user CAN enter at this time
                echo $query;
                echo "insert me!";
        }

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24092791
Corrected. USE THIS INSTEAD
$query = 'SELECT * FROM sweepstakes WHERE contestant_id = "' . $contestant_id . '" AND (DATEDIFF(entry_date, (DATE_SUB(CURRENT_DATE, INTERVAL 24 HOUR)) > 1);';
                $query_vivian=mysqli_query($cxn,$query) or die("Couldn't execute query.");
                $query_count=mysqli_num_rows($query_vivian);
                //if there are no records returned by this select statement, that means every entry is less than 24 hours and thus the user CAN enter at this time
                echo $query;
                echo "insert me!";
        }

Open in new window

0
 
LVL 19

Accepted Solution

by:
NerdsOfTech earned 500 total points
ID: 24092801
Recorrected. :)

Use this instead
$query = 'SELECT * FROM sweepstakes WHERE contestant_id = "' . $contestant_id . '" AND (DATEDIFF(entry_date, (DATE_SUB(CURRENT_DATE, INTERVAL 24 HOUR))) > 1);';
                $query_vivian=mysqli_query($cxn,$query) or die("Couldn't execute query.");
                $query_count=mysqli_num_rows($query_vivian);
                //if there are no records returned by this select statement, that means every entry is less than 24 hours and thus the user CAN enter at this time
                echo $query;
                echo "insert me!";
        }

Open in new window

0
 

Author Comment

by:brucegust
ID: 24097493
NerdsOfTech...

I like your solution. I want to know WHY it works. Here's what I've been able to learn by looking through some PHP documentation

DATEDIFF -  you're making a comparison between two date values
DATE_SUB - you're subtracting one date value from another

So with your solution, you're looking for a record in the database that contains both the contestant's id number and an entry date that results from the DATEDIFF and DATE_SUB combo that you've authored.

So if today's date is the 7th, the DATEDIFF is looking at the contestant's entry date which, for example, is the 6th. The date_sub is going to subtract 24 hours from the todays date (the 7th) which produces a result of the 6th of April. Now, here's where I'm getting a little lost. The DATEDIFF is going to look at the difference between the entry date (the 6th) and the DATE_SUB result which is the 6th so the final value for that part of your code is "0" which is less than "1" so there is a record that fits the criteria where the if statement is concerned...yes?

I don't want to just copy and paste, I want to understand. If you could correct me where I'm needing some counsel, I would love that.

Thanks!
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24109976
(DATEDIFF(entry_date, (DATE_SUB(CURRENT_DATE, INTERVAL 24 HOUR))) > 1);';

Gets the difference between:

the date in "entry date"
AND
today's date MINUS 24 hours via DATE_SUB()
AKA "yesterday" (exactly 24 before now)

SO it is the (absolute number in days) difference between yesterday and the entry date that DATEDIFF() calculates

IF THIS difference is greater than 1 day (more than 24 hours has past) then the record(s) is/(are) SELECTED.

//if there are no records returned by this select statement, that means every entry is less than 24 hours and thus the user CAN enter at this time

Hope that helps
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question