[SOLVED] mysql_num_rows

[greens85]

Hi all,

 

Can anyone advise how i would add a mysql_num_rows statement to my existing code, so that if the query returns no rows, i can echo a message like: 'sorry no results, please alter your search terms'.

 

However if results are present just display them without the message above.

 

My current code is:

 

<?php

$connection = mysql_connect("host", "user", "pass") or die (mysql_error());
$db = mysql_select_db("mydb", $connection) or die (mysql_error());

$locations = $_POST['location'];

$sql = "SELECT * FROM jobs WHERE subcounty = '$locations' ORDER BY jobid DESC";
	$result = mysql_query($sql) or die(mysql_error());
		while ($row = mysql_fetch_array($result))
{

	echo '<font color="#246494"><strong>Job Title:</strong></font> ';
	echo $row['position'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Description:</strong></font> ';
	echo $row['description'];
	echo '<br/>';
	echo '<br/>';
	echo '<font color="#246494"><strong>Job Reference:</strong></font> ';
	echo $row['jobref'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Hours:</strong></font> ';
	echo $row['hour'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Location:</strong></font> ';
	echo $row['subcounty'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Working Term:</strong></font> ';
	echo $row['contract'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Salary:</strong></font> ';
	echo $row['salary'];
	echo '<br/>';
	echo '<font color="#246494"><strong>Application Deadline:</strong></font> ';
	echo $row['deadline'];
     	echo '<br />';
	?>
	<div align="right"><a href="http://www.mysite.com/jobseekers/info.php?jobid=<?=$row['jobid']?>" target="_blank">Apply Now</a></div>
	<?
       echo '<br/>';
	echo '<hr style="background-color: rgb(204, 204, 204);" width="100%" size="1" noshade="noshade" color="#246494">';
   }
?>
[code]

[Yesideez]

<?php
$connection = mysql_connect("host", "user", "pass") or die (mysql_error());
$db = mysql_select_db("mydb", $connection) or die (mysql_error());

$locations = $_POST['location'];

$sql = "SELECT * FROM jobs WHERE subcounty = '$locations' ORDER BY jobid DESC";
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result)>0) {
  while ($row = mysql_fetch_array($result)) {
    echo '<font color="#246494"><strong>Job Title:</strong></font> ';
    echo $row['position'];
    echo '<br/>';
    echo '<font color="#246494"><strong>Description:</strong></font> ';
    echo $row['description'];
    echo '<br/>';
    echo '<br/>';
    echo '<font color="#246494"><strong>Job Reference:</strong></font> ';
    echo $row['jobref'];
    echo '<br/>';
    echo '<font color="#246494"><strong>Hours:</strong></font> ';
    echo $row['hour'];
    echo '<br/>';
    echo '<font color="#246494"><strong>Location:</strong></font> ';
    echo $row['subcounty'];
    echo '<br/>';
    echo '<font color="#246494"><strong>Working Term:</strong></font> ';
    echo $row['contract'];
    echo '<br/>';
    echo '<font color="#246494"><strong>Salary:</strong></font> ';
    echo $row['salary'];
    echo '<br/>';
    echo '<font color="#246494"><strong>Application Deadline:</strong></font> ';
    echo $row['deadline'];
    echo '<br />';
    ?>
    <div align="right"><a href="http://www.mysite.com/jobseekers/info.php?jobid=<?php echo $row['jobid']; ?>" target="_blank">Apply Now</a></div>
    <?php
    echo '<br/>';
    echo '<hr style="background-color: rgb(204, 204, 204);" width="100%" size="1" noshade="noshade" color="#246494">';
  }
} else {
  echo 'No rows to return';
}
?>

 

EDIT: Removed short <? tags as well.

[Yesideez]

Instead of ECHOing out all the HTML you can use <?php and ?> whenever you want, just just at the start and at the end:

<?php
$connection = mysql_connect("host", "user", "pass") or die (mysql_error());
$db = mysql_select_db("mydb", $connection) or die (mysql_error());

$locations = $_POST['location'];

$sql = "SELECT * FROM jobs WHERE subcounty = '$locations' ORDER BY jobid DESC";
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result)>0) {
  while ($row = mysql_fetch_array($result)) { ?>
    <font color="#246494"><strong>Job Title:</strong></font> <?php echo $row['position']; ?><br/>
    <font color="#246494"><strong>Description:</strong></font> <?php echo $row['description']; ?><br/><br/>
    <font color="#246494"><strong>Job Reference:</strong></font> <?php echo $row['jobref']; ?><br/>
    <font color="#246494"><strong>Hours:</strong></font> <?php echo $row['hour']; ?><br/>
    <font color="#246494"><strong>Location:</strong></font> <?php echo $row['subcounty']; ?><br/>
    <font color="#246494"><strong>Working Term:</strong></font> <?php echo $row['contract']; ?><br/>
    <font color="#246494"><strong>Salary:</strong></font> <?php echo $row['salary']; ?><br/>
    <font color="#246494"><strong>Application Deadline:</strong></font> <?php echo $row['deadline']; ?><br />
    <div align="right"><a href="http://www.mysite.com/jobseekers/info.php?jobid=<?php echo $row['jobid']; ?>" target="_blank">Apply Now</a></div>
    <br/>
    <hr style="background-color: rgb(204, 204, 204);" width="100%" size="1" noshade="noshade" color="#246494">';
  <?php  }
} else {
  echo 'No rows to return';
}
?>

[greens85]

Thanks for that,

 

it works perfect!!!

 

Also thanks for the advice about not having to echo out all the HTML, I did this because i didnt know you could 'close off' the php while you were running an IF Statement.

 

i.e. while the curly brace is still not closed.

 

{ ?>

 

Thanks once again