Function to retrieve newest record

[Mike Miller]

Hi there,

 

Before I begin, I want you all to know that I've been learning PHP for nearly a month now, so apologies for the poorly-written code that's going to follow. Two of my buddies and I are working on building a small website that allows us to play around with PHP and MySQL, since we work on an actual site that always in need of coding help.

 

I tried searching for similar threads on this forum, but I couldn't find anything that answers my questions. I'm trying to accomplish a few things here. I have a form that allows us to add a record into a table (hunting consisting of the following columns: id, title, date, data, added_by. What I want to do is write a function that retrieves the newest record added to that table, call that function in a different file (it'll appear on the home page), and add a link to that record. If that sounds confusing, then this is what I want to do:

 

1. Retrieve last record added to the Hunters DB with a function.

2. Call that function in main.php, where it displays the record and format it like "Latest News: Title of the record"

3. I want to embed the link in the "Latest News: <Title of the record>", so it's clickable (this should be easy for me to do). But I need it to use the id (this is the primary key) column, which changes if a new record is added. Format: www.mysite.com/hunters.php?id=1 (the id value changes if a record with an ID 3 is added, so all I have to do is change the 1 to 3 in my browser to access that new record).

 

 

This is what I wrote (functions.php):

 

function getNewHunterNews()
{
global $huntersdb; // Global that connects to the Hunters db
$query = mysql_query( "SELECT `id`, `title` FROM `hunters_news` ORDER BY `id` DESC LIMIT 1", $huntersdb );

while ( $row = mysql_fetch_assoc( $query ) )
{
	$id = $row['id'];
	$title = $row['title'];
}
return $row;	
}

 

In main.php (functions.php is included in this file):

 

<?php $huntnews = getNewHunterNews(); ?>
                                <div id="news">
                                    Latest News:<br /><br />
                                    <span class="bolddark"><?php echo "<a href="\"/hunters.php?id={$huntnews['id']}\">" . $huntnews['title'] . "</a>"; ?></span>
                                </div>

 

I make the above changes, and my main page white screens. D: I know it isn't my function, because I've written simpler functions that are called in main.php, and those work. The white screen happens whenever I make the change above to main.php. What am I doing wrong? I'm fairly certain the change to main.php is causing problems, but I can't seem to point out what it is. Is there a different approach I can should take? I'm still in the learning process, so please guide me in the right direction. Additionally, if there any suggestions or constructive criticism you'd like to throw at me, please feel free. Every bit helps.

 

Thanks in advance for your help!

[Fadion]

Everyone's been in your phase and there's nothing wrong with it. The important is to keep trying

 

I would rewrite your function code like this:

<?php
function latestHunterNews () {
     //I put the or die(mysql_error()); part so you know if any error occurs
     $results = mysql_query("SELECT id, title FROM hunter_news ORDER BY id DESC LIMIT 1") or die(mysql_error());
     
     //There's not need to run a while() loop on a result-set with only 1 row
     $row = mysql_fetch_assoc();

     return $row;
}
?>

 

While in main.php

<?php
include('includes/connection.php'); //this is your standard db connection code
include('includes/functions.php'); //your functions file

$latest_news = latestHunterNews();
?>
<div id="news">
     Latest News:<br /><br />
     <span class="bolddark"><a href="hunters.php?id=<?php echo $latest_news['id']; ?>"><?php echo $latest_news['title']; ?></a></span>
</div>

 

As a note on the code I wrote: database connection doesn't have scope. It's kept alive as long as the script runs and queries can be made drop-in inside functions and classes, as long as they're included in the main file. If there isn't any error in your query and you've set up the database connection correctly, the script should run fine.

[Mike Miller]

Everyone's been in your phase and there's nothing wrong with it. The important is to keep trying

 

I would rewrite your function code like this:

<?php
function latestHunterNews () {
     //I put the or die(mysql_error()); part so you know if any error occurs
     $results = mysql_query("SELECT id, title FROM hunter_news ORDER BY id DESC LIMIT 1") or die(mysql_error());
     
     //There's not need to run a while() loop on a result-set with only 1 row
     $row = mysql_fetch_assoc();

     return $row;
}
?>

 

While in main.php

<?php
include('includes/connection.php'); //this is your standard db connection code
include('includes/functions.php'); //your functions file

$latest_news = latestHunterNews();
?>
<div id="news">
     Latest News:<br /><br />
     <span class="bolddark"><a href="hunters.php?id=<?php echo $latest_news['id']; ?>"><?php echo $latest_news['title']; ?></a></span>
</div>

 

As a note on the code I wrote: database connection doesn't have scope. It's kept alive as long as the script runs and queries can be made drop-in inside functions and classes, as long as they're included in the main file. If there isn't any error in your query and you've set up the database connection correctly, the script should run fine.

 

GuiltyGear: Thanks very much for the quick response! Your code is much easier to read and understand.

 

However, most of it works well, except for the "Latest News:" link part. The title and id aren't being echoed, for some reason, i.e., the part under "Latest News:" appears to be blank. I've added the includes as well. I replaced the PHP code that echoes the title with plain text, just so I could see the embedded link. Upon hovering over the plain text, the link looks like: www.mysite.com/hunters.php?id=

 

Any idea what might be causing it? Once again, thanks for your help!

[trq]

You need to pass $results to mysql_fetch_assoc.

[Mike Miller]

You need to pass $results to mysql_fetch_assoc.

 

Jesus Christ, I can't believe I missed that! Thanks, thorpe!

 

It works flawlessly now. Thanks again, you two!