how to ignore empty results from MySQL query in PHP?

[stevet70]

I'm planning to try a few approaches to pulling out information from a MySQL database using PHP that will break things down into groups according to year - it's for an art gallery archive.

Each group of results will have the year displayed top left (only once), then on the right the list of artists who exhibited in that year going down.

So far I have a series of arrays like this:

<div id="archive_text">
	<?php
	$sql = 'SELECT * FROM exhibitions WHERE archive="yes" AND year="2009" ORDER BY year DESC, month DESC';
	$result = mysql_query($sql) or die(mysql_error());
	?>
<div id="archive_year"><?php echo $row['year']; ?></div>
<div id="archive_event">
	<?php
	while($row = mysql_fetch_assoc($result)) {
	?>
<a href="exhibitions/index.php?id=<?php echo $row['id']; ?>"><?php echo $row['title']; ?></a><br />
	<?php } ?>
    </div>
    </div>

Probably not the most elegant way of doing this, but it's a starting point. My problem is that it isn't showing the year even though there are results.

Any ideas why?

I can't hard code the year into each array because I want any year with no results to be ignored - so it doesn't mess up the vertical spacing

thanks
Steve

 

[lemon-tree]

Use the mysql_num_rows() to check that there are some results.
E.g.

if(mysql_num_rows($result)>0){
echo "<div id='archive_year'>" . $row['year'] . "</div>";
}

Or something along those lines.

EDIT: Also, your trying to call the variable $row['year'] before it has been defined by while($row = mysql_fetch_assoc($result)) {

 

[stevet70]

thanks for that, I'd just tried a slightly fudged

<div id="archive_text">
<?php
	$year = '2010';
	$sql = 'SELECT * FROM exhibitions WHERE archive="yes" AND year="2010" ORDER BY year DESC, month DESC';
	$result = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($result)>0){ 
echo '<div id="archive_year">' . $year . '</div>'; 
} 
?>
<div id="archive_event">
	<?php
	while($row = mysql_fetch_assoc($result)) {
	?>
<a href="exhibitions/index.php?id=<?php echo $row['id']; ?>"><?php echo $row['title']; ?></a><br />
	<?php } ?>
    </div>
	</div>

but obviously if I implement the year after it's been defined then I can get rid of the $year quick fix

nice one!

 

[xav0989]

Here is a updated version for you

<div id="archive_text">
<?php
	$year = '2010';
	$sql = "SELECT * FROM exhibitions WHERE archive='yes' AND year='$year' ORDER BY month DESC, artist DESC';
	$result = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($result) > 0){ 
echo '<div id="archive_year">' . $year . '</div>';
?>
	<div id="archive_event">
	<?php while($row = mysql_fetch_assoc($result)) { ?>
	<a href="exhibitions/index.php?id=<?php echo $row['id']; ?>"><?php echo $row['title']; ?></a><br />
	<?php } ?>
    </div>
</div>
<?php } ?>

I've changed the SQL part, as you don't need to go order the year, since there is a WHERE clause specifying the year.
I've also moved some of your code around in the end so that you don't get a lone </div> if there is no result.
You should think in separating your code and your layout. It looks way better that way.