[Resolved] MySQL Newbie Query Problem

[freakyG]

Hi all,

So I am making a news thing and I want to design it so that people can select a month and year to look at the archives of the news.

Here's the problem:

I am using a form so that people can select the month and the year (I tried to figure out a way that php would list the months and years that there are archives for, but I was getting too frustrated with it. I might try to do it again later.)

Now I have intentionally entered in dates for several different months and several different years, and using phpmyadmin, I know that these news posts are in the database. But the problem is that if there is only one item in the database for a particular month and year, then it will not be pulled up through the query. But if there is more than one news article for the month and year, then the code will pull up the most recent article, but not the earliest article. In fact it doesn't even state the if (!row) language. I don't really know what is going on. 🙁

Here's the code:

<?

$query = "select id, title, source, sourceURL, text, month, day,
 year from news where month = '$month' and year = '$year' limit 0,100";

$result = mysql_query($query);

$row = mysql_fetch_array($result);

if (!$row)
{
    echo "Sorry, there are no archives for that particular month 
and year<P>";
    echo ("Here is the mySql Error: ".mysql_error());
    exit;
}

if ($row = mysql_fetch_array($result))
{
    do
    {
        echo ("The following are archived documents: <P>");
        require "dateCheck.inc";
        require "mklink.inc";
    }
    while
    ($row = mysql_fetch_array($result));
}

?>

This is the dateCheck.inc code:

<?

//day, month, year variables

$day = $row["day"];
$month = $row["month"];
$year = $row["year"];
$id = $row["id"];
$title = $row["title"];
$sourceURL = $row["sourceURL"];
$source = $row["source"];
$date = date("l F jS, Y", mktime(0,0,0,$month,$day,$year));

//time doc vars

$docTS = mktime(0,0,0,$month,$day,$year);
$docdate = getdate($docTS);
$docmonth = $docdate['mon'];
$realmonth = $docdate['month'];
$docyear = $docdate['year'];

//time now vars

$today = getdate();
$nowmonth = $today['mon'];
$nowrealmonth = $today['month'];
$nowyear = $today['year'];

//date difference

$monthdiff = $nowmonth - $docmonth;
$yeardiff = $nowyear - $docyear;

?>

This is the mklink.inc code:

<?

printf ("<a href=\"%s?id=%s\">%s</a><BR>\n", 
"postdata.php", $id, $title);
echo ("<I>".$date."</I><BR>");
echo ("<a href = \"".$sourceURL."\" target = \"_blank\"><I>".$source."</I></a> <BR>");
echo ("\$monthdiff = ".$monthdiff);
echo "<BR>";
echo ("\$yeardiff = ".$yeardiff);
echo "<P><HR><P>";

?>

Any help with this problem would be greatly appreciated. One last thing so that people know what the form looks like, it is a drop down menu that gives the numerical representation of the month and the year. i.e., April is 04 and 2002 is 2002.

Thanks in advance!

[YappyDog]

Your first fetch where you are checking to see if you got anything effectively throws away that row when your next fetch is done.

Try:

if ($row = mysql_fetch_array($result)) {
do {
echo ("The following are archived documents: <P>");
require "dateCheck.inc";
require "mklink.inc";
} while ($row = mysql_fetch_array($result));
} else {
echo "Sorry, there are no archives for that particular month
and year<P>";
echo ("Here is the mySql Error: ".mysql_error());
exit;
}

[freakyG]

Originally posted by YappyDog
Your first fetch where you are checking to see if you got anything effectively throws away that row when your next fetch is done.

Try:


if ($row = mysql_fetch_array($result)) {
do {
echo ("The following are archived documents: <P>");
require "dateCheck.inc";
require "mklink.inc";
} while ($row = mysql_fetch_array($result));
} else {
echo "Sorry, there are no archives for that particular month
and year<P>";
echo ("Here is the mySql Error: ".mysql_error());
exit;
}

Thank you, YappyDog. Your recommendation worked perfectly. 😃 Thank you so much for your help.

-G