[SOLVED] Query isnt outputting the correct details I asked for.. How naughty!

[nwoottonn]

Hi all!

 

I've been trying to get my head around this for hours, but I'm utterly confused.

 

I'm trying to query from a database by date.

 

In a nutshell, I want to retrieve information from a 'courses' table, that is OLDER than today, but between 2 dates... please see the image here: http://loanjumper.co.uk/problem.gif (the query is echo'd here so you can see it).

 

Problem is, as you can see by the image - it just displays all the information in the table - as far as I can see. It doesnt seem to take into consideration my query by the dates I supplied.

 

Here is my code:

 

$queryx = "SELECT * FROM courses WHERE date > '$day' AND date >= '$fromm' AND date <= '$too'";
echo $queryx;
$resultx = mysql_query($queryx);

while($rowx = mysql_fetch_array($resultx, MYSQL_ASSOC))
{



?>              
            <table border="0" cellspacing="0" cellpadding="2">
              <tr>
                <td width="127"  bgcolor="#FFFFFF"><span class="style9"><? echo ($rowx['date']);  ?></span></td>
                <td width="132"  bgcolor="#FFFFFF"><span class="style9"><? echo ($rowx['title']);  ?></span></td>
              </tr>
              <tr>
         <td colspan="2" bgcolor="#FFFFFF"><span class="style7"><? echo ($rowx['description']);  ?></span></td>
              </tr>
            </table>       



            <? } ?>

 

I'd appreciate any help whatsoever. I'm pulling my hair out!

 

Thanks guys,

 

Nick

[craygo]

why bother with the day if you already know which dates you want to search in. Doesn't make sense.

 

Also date is a reserved word so you have to put it in backticks.

 

$queryx = "SELECT * FROM `courses` WHERE `date` BETWEEN '$fromm' AND '$too'";

 

Ray

[doni49]

To add to that--validate the dates before running the query--make sure they are older than today or don't run the query at all.

[nwoottonn]

Hi craygo!

 

I don't know what particular dates I want to search by, these are determinted dependant on choices.. see here:

 

^{$fromm = ($row['fromm']);}

^{$too = ($row['too']);}

^{$day = date("Y-m-d");}

 

I tried using backticks but I have the exact same trouble ...

 

Here is a screenshot of the database if that might help? -->  http://loanjumper.co.uk/db.gif

 

Thanks so far craygo!

 

Nick

[nwoottonn]

Hi Doni,

 

It's a bit weird how it works.

 

The page is listing future courses, it queries the database and the first thing it asks is "is the query date in the future"? .. Then it asks for certain dates so that I can output in different timeframes after the first "is the query date in the future" question...

 

If that makes sense!

 

Thanks Doni,

 

Nick

[craygo]

you can do something like this

 

<?php
$fromm = ($row['fromm']);
$too = ($row['too']);
$day = date("Y-m-d");
if(empty($fromm)) || empty($too)){
$where = "`date` < '$date'";
} else {
$where = "`date` BETWEEN '$from' AND '$too'";
}

$queryx = "SELECT * FROM `courses` WHERE $where";
?>

 

Ray

[nwoottonn]

I've done it ray!!

 

Thanks so much for your help.

 

Simply by reading your solution I was able to adapt my code to fit, and it works fine

 

Thanks again,

 

Nick