Solved

What's wrong with this select statement

Posted on 2009-04-07
9
256 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

628 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