Solved

What's wrong with this select statement

Posted on 2009-04-07
9
255 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
can i read my emails on lamp ftp 4 71
[form-control] Retain values after a POST action 21 76
XML loaded in a form with dropdown 6 57
Format Date 7 28
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This article discusses how to create an extensible mechanism for linked drop downs.
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 dynamically set the form action using jQuery.

752 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