Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

What's wrong with this select statement

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
brucegust
Asked:
brucegust
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the data type of entry_date?
0
 
brucegustPHP DeveloperAuthor Commented:
Date - 2009-04-06
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.

 
Roger BaklundCommented:
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
 
NerdsOfTechTechnology ScientistCommented:
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
 
NerdsOfTechTechnology ScientistCommented:
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
 
NerdsOfTechTechnology ScientistCommented:
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
 
brucegustPHP DeveloperAuthor Commented:
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
 
NerdsOfTechTechnology ScientistCommented:
(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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now