Error in simple "On this day" script

[Mark Barrett]

Friends

I tried my hand at a simple script to print fields from a database when a historic date field e.g. November 7 1800 matches with today's date e.g. November 7 2003.
(n.b. for simplicity there will only ever be one record with the November 7 date.)
Instead of matching the dates at the SELECT stage I decided to do SELECT * and then PRINT IF (i.e. print if there is a match).
This is what I came up with:

$Now=time();
$Date=date("F j",$Now);
$sql = "SELECT * FROM Datetable";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
if ($Date == $row[Comparedate])
print
("$row[Date], $row[Year]<br>
$row[Anniversary]");

Fantastic - IT WORKED - BUT only if the record with November 7 (1800) is the first record in the database.
If it is not - the script does not fail, but it just doesn't return any data.

I wondered if I need to use a WHILE statement in there but unfortunately I haven't enough PHP knowledge yet to build that in.

OR should I start again and do the match at the SELECT stage - seems a shame when what I have NEARLY works.

OR can anyone point me to an existing "On this day" script that will do the job for me. I am sure there is one out there, but if there is I can't locate it.

Many, many thanks if someone can help.

Mark Barrett

P.S. to simplify the match my table has November 7 in one field (it is a text field and named Comparedate) and 1800 in another

[Robber]

If you were going to stick with what you have you are correctin that you need a while loop to iterate the multiple results from the database query, eg:

while($row = mysql_fetch_array($result)){

other stuff here

}

But you are better off doing the comparison in the sql:

SELECT * FROM Datetable WHERE datefield = 2003-11-07

You might want to check that date format.

And welcome to WebmasterWorld

[Mark Barrett]

Robber

Many thanks for your time.

I have decided I will do my data selection in the SQL.

I am trying to work this through logically, but here is what happens.

I start out with this script:

$sql = "SELECT * FROM Datetable";
$result = mysql_query($sql);
while $row = mysql_fetch_array($result)
{
print
etc. etc.

It works fine and returns all the rows in the database.

As soon as I put a WHERE statement into the SQL, something quite simple like:

$sql = "SELECT * FROM Datetable WHERE ID=1";
$result = mysql_query($sql);
while $row = mysql_fetch_array($result)
{
print

- it throws up the error message "Warning mysql_fetch_array(): supplied argument is not a valid MySQL result resource"

Is this some small thing I am doing wrong?

If I can just resolve this I think I can find my own way from there.

Mark Barrett

[coopster]

If ID is a string or date field, you should enclose it in quotes as MySQL specifies that

String and date values are specified as quoted strings.

$sql = "SELECT * FROM Datetable WHERE ID='1'";

[Mark Barrett]

Thanks Coopster

- but no, it is an integer, auto-incrementing.
But "just in case" I had already tried it in quotes, and the same error message threw up.

I tried everything I could before I prevailed on you guys for help.

How frustrating are these things! - but thanks anyway!

Mark Barrett

[bobnew32]

Well if your doing a while $row = mysql_fetch_array($result)

where your only selecting one row, then you don't need that. Just use $row = mysql_fetch_array($result);

and reference your data $row['column_value_inrow'];

[Robber]

The "while" doesn't look quite right - should have extra brackets, try:

$sql = "SELECT * FROM Datetable WHERE ID=1";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
do stuff;
}

[Mark Barrett]

Just dropping back to say THANKS for your help and advice - the script is now bug free and live on my website!

It looks like this:

$Now=time();
$Date=date("F j",$Now);
$sql = "SELECT * FROM Datetable WHERE Comparedate='$Date'";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
print etc. etc.

How could something so simple have caused me so much grief?

I have dropped the WHILE statement because at the moment it isn't needed - but I feel reasonably confident that I could build it in if neccessary.

My website is:
<snip>

I've got 5 PHP driven boxes now. 3 just pull the most recent item from a table, one makes a random selection, and this last is date driven.

Still a long way to go, but I guess I am on the first rung of the "PHP ladder".

Thanks again

Mark Barrett

[edited by: jatar_k at 6:22 pm (utc) on Nov. 8, 2003]
[edit reason] no personal urls thanks [/edit]