Codegrrl.com Forums > [resolved] Archive tutorial problem

Skaterstu

Jan 31 2007, 03:38 AM

Hi Guys,

I built the Archive page (blog_Archive.php). Then I went into my Blog Index page (blog_Index.php) and added the code into the sidebar so that I could access Archive blogs from my index page. But in the sidebar I get the following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\htdocs\WebgorillaPHP\blog_Ind.php on line 84

My code in the sidebar of my Blog Index page is as follows:

CODE

<div id="rightcol">
<div class="label">
<h1>Blog Archive</h1>
</div>
<div class="notice">
<?php

// construct query to display archive blog entry links
$result = mysql_query("SELECT FROM_UNIXTIME(timestamp, '%Y') AS get_year, COUNT (*) AS entries FROM blog GROUP By get_year");

// while loop
while ($row = mysql_fetch_array($result)) {
    $get_year = $row['get_year'];
    $entries = $row['entries'];
    echo "<ul>";
    echo "<li><a href=\"blog_Archive.php?year=" . $get_year . "\">Entries from " . $get_year . "</a> (" . $entries . ")</li>";
    echo "</ul>";
}
?>
</div>

I'd greatly appreciate if someone could lend me a hand on this issue.

One more thing. In my blog_Archive page, what page should I be referencing? In the example it is journal.php... this is the single entry page, right??

Just when I start getting confident with PHP I get knocked by something completely confusing... but I guess that's how you learn isn't it.

Cheers

Stuart

Amelie

Feb 1 2007, 01:09 PM

It looks like you haven't connected to the database... Make sure there is a mysql_connect() call at the top of the file, if there isn't one already included in the parent page.

And yes, the page you should be referencing is the single entry page.

Skaterstu

Feb 2 2007, 11:15 AM

QUOTE(Amelie @ Feb 1 2007, 09:09 PM)

It looks like you haven't connected to the database... Make sure there is a mysql_connect() call at the top of the file, if there isn't one already included in the parent page.

And yes, the page you should be referencing is the single entry page.

Hi Amelie,

Thanks for the reply. I actually have the database connection info in the main content area of the webpage. I did add the connection info into the right column, above the php code to display the list of years but it still wouldn't work. Do you have any other ideas as to why this is not working?

Many thanks, I appreciate your help

Stuart

Amelie

Feb 2 2007, 12:20 PM

Does it work if you run the page on its own (i.e., not included into your index page)?

I'm not sure why it isn't working...

Change the code to the following for debugging purposes:

CODE

$result = mysql_query("SELECT FROM_UNIXTIME(timestamp, '%Y') AS get_year, COUNT (*) AS entries FROM blog GROUP BY get_year") or die("Could not select from MySQL. " . mysql_error());

// while loop
while ($row = mysql_fetch_array($result)) {
    $get_year = $row['get_year'];
    $entries = $row['entries'];
    echo "<ul>";
    echo "<li><a href=\"blog_Archive.php?year=" . $get_year . "\">Entries from " . $get_year . "</a> (" . $entries . ")</li>";
    echo "</ul>";
}
?>

What error do you get after running that?

Skaterstu

Feb 2 2007, 01:43 PM

QUOTE(Amelie @ Feb 2 2007, 08:20 PM)

Does it work if you run the page on its own (i.e., not included into your index page)?

I'm not sure why it isn't working...

Change the code to the following for debugging purposes:

CODE

$result = mysql_query("SELECT FROM_UNIXTIME(timestamp, '%Y') AS get_year, COUNT (*) AS entries FROM blog GROUP BY get_year") or die("Could not select from MySQL. " . mysql_error());

// while loop
while ($row = mysql_fetch_array($result)) {
    $get_year = $row['get_year'];
    $entries = $row['entries'];
    echo "<ul>";
    echo "<li><a href=\"blog_Archive.php?year=" . $get_year . "\">Entries from " . $get_year . "</a> (" . $entries . ")</li>";
    echo "</ul>";
}
?>

What error do you get after running that?

Hi Amelie,

Thanks again... After running your code I get the following error message in the rightCol:

Could not select from MySQL. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS entries FROM blog GROUP BY get_year' at line 1

Actually, putting your code in screws up the display of my page a little too, is this because of the MYSQL error? I'll include the full list of code for the Blog Index page. Oh, by the way, the archive page on its own just displays the message 'Invalid year specified.'

Here is the full code for my Blog_Ind.php page:

CODE

<?php require_once('Includes/css_function.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>TheWebGorilla.com - Blog Index Page</title>
<?php require_once('Includes/cssstylerules.php'); ?>
</head>
<body>
<?php require_once('Includes/header_temp.php '); ?>
<div id="strap"></div>
<div id="top"></div>
<div id="content">
<h1>Blog Index</h1>
<?php

// Assign connection data to variables
$host     =  "localhost";
$user     =  "*****";
$pass     =  "*****";
$db       =  "*****";

// Connect to MySQL
$connection = mysql_connect($host, $user, $pass) or die('Error:  Could not connect you to MySQL');

// Connect to database
mysql_select_db($db) or die('Error: Could not process query because: '.mysql_error());

// Query to display entries contained on the database
$query = "SELECT * FROM blog ORDER BY timestamp DESC LIMIT 10";
$query_rs = mysql_query($query) or die('Error:  Query could not be processed because '.mysql_error());

// use a while loop to display the queries on the page.
// While loop basically says 'while our query is true, do this.'
while($row = mysql_fetch_array($query_rs)) {

    $date = date("l F d Y", $row['timestamp']);

    $title = stripslashes($row['title']);
    $entry = stripslashes($row['entry']);
    $password = $row['password'];
    $id = $row['id'];

    if ($password == 1) {
        echo "<div class='diaryExtract'>";
        echo "<p><strong>" . $title . "</strong></p>";
        printf("<p>This is a password protected entry. If you have a password, log in below.</p>");
        printf("<form method=\"post\" class=\"blog\" action=\"blog_singleEntry.php?id=%s\"><p><strong><label for=\"username\">Username:</label></strong><br /><input type=\"text\" name=\"username\" id=\"username\" /></p><p><strong><label for=\"pass\">Password:</label></strong><br /><input type=\"password\" name=\"pass\" id=\"pass\" /></p><p><input type=\"submit\" name=\"submit\" id=\"submit\" value=\"submit\" /></p></form>",$id);
        echo "</div>";
        print "<hr />";
    }
    else { ?>
        <div class="diaryExtract">
        <p><strong><?php echo $title; ?></strong><br /><br />
        <?php echo $entry; ?><br /><br />
        Posted on <?php echo $date; ?>
        <hr /></p>
        </div>

        <?php
    }
}
?>
</div>
<div id="rightcol">
<div class="label">
<h1>Blog Archive</h1>
</div>
<div class="notice">
<?php
// Assign connection data to variables
$host     =  "localhost";
$user     =  "*****";
$pass     =  "*****";
$db       =  "*****";

// Connect to MySQL
$connection = mysql_connect($host, $user, $pass) or die('Error:  Could not connect you to MySQL');

// Connect to database
mysql_select_db($db) or die('Error: Could not process query because: '.mysql_error());

// construct query to display archive blog entry links
$result = mysql_query("SELECT FROM_UNIXTIME(timestamp, '%Y') AS get_year, COUNT (*) AS entries FROM blog GROUP BY get_year") or die("Could not select from MySQL. " . mysql_error());

// while loop
while ($row = mysql_fetch_array($result)) {
    $get_year = $row['get_year'];
    $entries = $row['entries'];
    echo "<ul>";
    echo "<li><a href=\"blog_Archive.php?year=" . $get_year . "\">Entries from " . $get_year . "</a> (" . $entries . ")</li>";
    echo "</ul>";
}
?>
</div>
<div class="label">
<h1>Features</h1>
</div>
<dl>
<dt>About Us</dt>
<dd class="image"><a href="Aboutus.php"><img src="Images/about.jpg" width="51" height="50" border="0" alt="About Us" /></a></dd>
<dd>More information about the authors of this website.</dd>
<dt>Web Design</dt>
<dd class="image"><a href="webdesign.php"><img src="Images/webdesign.jpg" width="51" height="50" border="0" alt="Web Design" /></a></dd>
<dd>Need a good quality website designed quickly?</dd>
<dt>Interpreting</dt>
<dd class="image"><a href="interpreting.php"><img src="Images/interpreting.gif" width="51" height="50" border="0" alt="Interpreting" /></a></dd>
<dd>Bridging the gap between Chinese and English.</dd>
<dt>Site Map</dt>
<dd class="image"><a href="sitemap.php"><img src="Images/sitemap.jpg" width="51" height="50" border="0" alt="Site Map" /></a></dd>
<dd>A comprehensive list of all of the pages on this site.</dd>
</dl>
<br />
</div>
<?php require_once('Includes/footer_temp.php'); ?>
</div>
</body>
</html>

Thanks for your help

Stuart

... and here is the coding for my archive page

CODE

<?php require_once('Includes/css_function.php'); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>TheWebGorilla.com - Blog Archive Page</title>
<?php require_once('Includes/cssstylerules.php'); ?>
</head>
<body>
<?php require_once('Includes/header_temp.php '); ?>
<div id="strap"></div>
<div id="top"></div>
<div id="content">
<h1>Blog Index</h1>
<?php

// Assign connection data to variables
$host     =  "localhost";
$user     =  "*****";
$pass     =  "*****";
$db       =  "*****";

// Connect to MySQL
$connection = mysql_connect($host, $user, $pass) or die('Error:  Could not connect you to MySQL');

// Connect to database
mysql_select_db($db) or die('Error: Could not process query because: '.mysql_error());

if (!isset($_GET['year'])) {
    die("Invalid year specified.");
}
else {
    $year = (int)$_GET['year'];
}

$result = mysql_query("SELECT timestamp, id, title FROM php_blog WHERE FROM_UNIXTIME( timestamp, '%Y' ) = '$year' ORDER BY id DESC");

while ($row = mysql_fetch_array($result)) {
    $date = date("l F d Y", $row['timestamp']);
    $id = $row['id'];
    $title = stripslashes($row['title']);

    ?>

    <p><?php echo $date; ?><br /><a href="blog_singleEntry.php?id=<?php echo $id; ?>"><?php echo $title; ?></a></p>
    <?php
}

?>
</div>
<div id="rightcol">
<div class="label">
<h1>Blog Archive</h1>
</div>
<?php

// construct query to display archive blog entry links
$result = mysql_query("SELECT FROM_UNIXTIME( timestamp, '%Y' ) AS get_year, COUNT(*) AS entries FROM blog GROUP BY get_year");

// while loop
while ($row = mysql_fetch_array($result)) {
    $get_year = $row['get_year'];
    $entries = $row['entries'];

    echo "<li><a href=\"blog_Archive.php?year=" . $get_year . "\">Entries from " . $get_year . "</a> (" . $entries . ")</li>";
}
?>
<div class="label">
<h1>Features</h1>
</div>
<dl>
<dt>About Us</dt>
<dd class="image"><a href="Aboutus.php"><img src="Images/about.jpg" width="51" height="50" border="0" alt="About Us" /></a></dd>
<dd>More information about the authors of this website.</dd>
<dt>Web Design</dt>
<dd class="image"><a href="webdesign.php"><img src="Images/webdesign.jpg" width="51" height="50" border="0" alt="Web Design" /></a></dd>
<dd>Need a good quality website designed quickly?</dd>
<dt>Interpreting</dt>
<dd class="image"><a href="interpreting.php"><img src="Images/interpreting.gif" width="51" height="50" border="0" alt="Interpreting" /></a></dd>
<dd>Bridging the gap between Chinese and English.</dd>
<dt>Site Map</dt>
<dd class="image"><a href="sitemap.php"><img src="Images/sitemap.jpg" width="51" height="50" border="0" alt="Site Map" /></a></dd>
<dd>A comprehensive list of all of the pages on this site.</dd>
</dl>
<br />
</div>
<?php require_once('Includes/footer_temp.php'); ?>
</div>
</body>
</html>

Amelie

Feb 2 2007, 02:14 PM

That code will mess up your layout because of the error - don't worry, it's not permanent.

It looks like your version of MySQL doesn't like the COUNT(*) part... Perhaps try changing it to COUNT(entry) instead?

The query should look like this (in both your archive page and your sidebar archive page)

CODE

$result = mysql_query("SELECT FROM_UNIXTIME( timestamp, '%Y' ) AS get_year, COUNT(entry) AS entries FROM blog GROUP BY get_year");

Skaterstu

Feb 2 2007, 02:24 PM

QUOTE(Amelie @ Feb 2 2007, 10:14 PM)

That code will mess up your layout because of the error - don't worry, it's not permanent.

It looks like your version of MySQL doesn't like the COUNT(*) part... Perhaps try changing it to COUNT(entry) instead?

The query should look like this (in both your archive page and your sidebar archive page)

CODE

$result = mysql_query("SELECT FROM_UNIXTIME( timestamp, '%Y' ) AS get_year, COUNT(entry) AS entries FROM blog GROUP BY get_year");

Amelie,

You are a star, thanks!! I'm gonna read over these tutorials again tomorrow to try and get a better understanding. Also I am going to try and convert to categories if I can...

All is working well, and for a very hungover person I am happy.

Stuart