0

I have a sqlquery which I have been playing around with in my php page BUT it isn't pulling out accurate results! I have starred at this so long now I can't see anything wrong! I am sure there is just a " or ' or ` in the wrong place!

<? 
$result = mysql_query("SELECT ReReferral_ID FROM ReReferral Where (`ReReferral_Date` >= '" . $_POST["report_date_from"] ."-04-01 00:00:00' AND `ReReferral_Date` <= '" . $_POST["report_date_from"] ."-06-30 00:00:00') AND ReReferral_ReReferral ='1'");
$num_rows = mysql_num_rows($result);
echo $num_rows;
?>

Thanks for the help!

echo_Me
  • 37,078
  • 5
  • 58
  • 78
5ummer5
  • 153
  • 1
  • 13
  • 1
    Basic debugging is called for. Store the SQL into a string variable and `echo $variable;` to see its contents. Does it look like you expect it to? What are the contents of `$_POST`? ([which is vulnerable to SQL injection, by the way](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)) – Michael Berkowski Apr 08 '14 at 19:37
  • Put `or die(mysql_error())` after your query. That could give you a hint. :) – NoLifeKing Apr 08 '14 at 19:38
  • 1
    Also, you are using `report_date_from` for both conditions. Do you intend for one of them to be a different value (`report_date_to` for example)? – Michael Berkowski Apr 08 '14 at 19:38
  • @NoLifeKing The query completes without error, but returns different rows than expected... – Michael Berkowski Apr 08 '14 at 19:39
  • What's an example value for report_date_from? – Karl Kieninger Apr 08 '14 at 19:40
  • Hi, wow thanks all for the replies! There is a report_date_to. An example value is just the year. 2013. The SQL injection is not an issue as it will finish up on an internal server. I have it returning the query as a number which is fine but I do not think it is correct by the amount of records on the db – 5ummer5 Apr 08 '14 at 19:42
  • What are the expected the results? The query seems right. May be the logic isn't? – Santosh Achari Apr 08 '14 at 20:01
  • The results seem much lower than expected! What I might have to do is export all and run some excel functions to count and see, then I will know for certain. The this that made me think it is not right as well as the low number was also the fact in my editor it did not colour the code correctly. I will upload images – 5ummer5 Apr 08 '14 at 20:25

2 Answers2

1

try this:

 $rdate = mysql_real_escape_string($_POST["report_date_from"]);
 $result = mysql_query("SELECT ReReferral_ID FROM ReReferral 
          WHERE `ReReferral_Date` BETWEEN  CONCAT('$rdate','-04-01 00:00:00') 
          AND CONCAT('$rdate','-06-30 00:00:00') 
          AND ReReferral_ReReferral ='1' ");
echo_Me
  • 37,078
  • 5
  • 58
  • 78
1

There's not enough information to give a good answer to your question.

One step in debugging is verifying that the string your are constructing is the SQL statement you want to execute. As a rudimentary example:

$sqltext = "SELECT ReReferral_ID FROM ... ";

# for debugging echo out the SQL text to be executed:
echo $sqltext;

# also check the return from mysql_query
$result = mysql_query($sqltext) or die(mysql_error());

NOTE I'm twitching at the usage of the deprecated mysql_ interface. For the love of all that is good and beautiful in this world, don't use mysql_ for new development. Use mysqli or PDO instead.

If you have to include user provided data in the SQL text (as opposed to using bind variables), then at the very least use mysql_real_escape_string function to thwart SQL injection vulnerabilities.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Haha! Sorry for making you twitch! I don't code php and mysql and I don't intend to! I have been dropped "in it" with this project when my developer said he doesn't have time to do it 1 day before the deadline!! I am also twitching but for very different reasons! ;-) I have used your tip and it has returned Resource id #45 – 5ummer5 Apr 08 '14 at 20:14