Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

What's wrong with this select statement

Posted on 2009-04-07
9
Medium Priority
?
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

722 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