Solved

What's wrong with this select statement

Posted on 2009-04-07
9
250 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 142

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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24092280
ok, so what is the "problem", actually?
please clarify!
0
 
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
Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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 …

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now