[RESOLVED] check if database has result?

[middleman666]

hey people,

how do you check if a result in a mySQL database actually exists?

heres what im trying to do but its not working...

<?
$result=mysql_query("SELECT * FROM myTable ORDER BY category = 'Live' DESC LIMIT 1"); 
while($row=mysql_fetch_array($result)) 

if (mysql_num_rows($result)) {

print '

<!-- Start of : '.$row['title'].' : by : '.$row['reviewer'].' : -->

';

}

else {
   echo 'There are no results yet.';
}


?>

this is trying to display the title and reviewer when there isnt a entry yet and i want it to display the else echo if there isnt an entry.

whats wrong with my IF function? am i asking the wrong question?

many thanks.

[cahva]

FIRST check the num rows and only then you start to loop through the results:

<?
$result=mysql_query("SELECT * FROM myTable ORDER BY category = 'Live' DESC LIMIT 1");

if (mysql_num_rows($result)>0) {
	while($row=mysql_fetch_array($result)) {
		print '<!-- Start of : '.$row['title'].' : by : '.$row['reviewer'].' : -->';
	}
} else {
	echo 'There are no results yet.';
}
?>

[middleman666]

thanks for the reply, i tried what you suggested but it doesnt seem to be working.

heres what i have:

<?
$result=mysql_query("SELECT * FROM myTable ORDER BY category = 'Album' DESC LIMIT 1"); 

if (mysql_num_rows($result)>0) { 
    while($row=mysql_fetch_array($result)) { 

print '

<!-- Start of : '.$row['title'].' : by : '.$row['reviewer'].' : -->

<table width="100%" height="100%" cellpadding="2"  cellspacing="0" bgcolor="#FFFFFF">
  <tr>
    <td valign="top">
      <img width="120" border=1 alt="'.$row['title'].'" src="'.$row['image'].'"><br>
      <span class="reviewtitle">'.$row['title'].'</span><br>
      '.substr($row['review'],0,200).'... 
      (<a href="showreview.php?id='.$row['id'].'">more</a>)<br>
      '.$row['rating'].'<br></td>
  </tr>
</table>

<!-- End of : '.$row['title'].' : by : '.$row['reviewer'].' : -->

';
}
}
else {
  echo 'There are no Album reviews yet.';  

}
?>

have I followed your instructions wrong?

thanks

[msk_1980]

Try this:

try to echo the $mysql_num_rows to give you an idea how many rows are there in the output.

$no_row = mysql_num_rows( $result );
echo "No of rows of output : " . $no_row . "<br \>";

[middleman666]

it says 1, but there are no entries in my database where category = 'Album' - maybe I'm getting that bit wrong?

With category = 'Album' i was trying to display the latest entry that has the category equal to the word Album - is it wrong how im doing it?

[middleman666]

If it helps to know... i am trying to run four versions of this script, the difference in each is what the category equals to.

[msk_1980]

Well if it says 1, then it is returning 1 record from the database as you LIMIT 1. so it seems to have some records with category='Album'.

With category = 'Album' i was trying to display the latest entry that has the category equal to the word Album - is it wrong how im doing it?

I dont think the query you got there is what you want it to be doing because you are not telling it to order desending on time (or whatever field you want it to be).

[middleman666]

i think i should try it again once i fix my query to do what i want.

could you tell me what i would have to query to get the latest entry with a certain category?
i have an id field to work with so would it be something liike:

$result=mysql_query("SELECT * FROM myTable ORDER BY id and category = 'Album' DESC LIMIT 1");

thanks for your help

[middleman666]

ok, after much trial and error if got this far:

<?
$result=mysql_query("SELECT * FROM MyTablw WHERE category = 'Album' ORDER BY id DESC LIMIT 1"); 
while($row=mysql_fetch_array($result)) {

if ($row['category']==Album){ 
	print '

	<!-- Start of : '.$row['title'].' : by : '.$row['reviewer'].' : -->

	<table width="100%" height="100%" cellpadding="2"  cellspacing="0" bgcolor="#FFFFFF">
      <tr>
        <td valign="top">
          <img width="120" border=1 alt="'.$row['title'].'" src="'.$row['image'].'"><br>
          <span class="reviewtitle">'.$row['title'].'</span><br>
          '.substr($row['review'],0,200).'... 
          (<a href="showreview.php?id='.$row['id'].'">more</a>)<br>
          '.$row['rating'].'<br></td>
      </tr>
	</table>

	<!-- End of : '.$row['title'].' : by : '.$row['reviewer'].' : -->

	';
}
else { 
	print 'There are no Album reviews yet.';   
}
}
?>

This gets the latest entry depending on the category.

If there isnt an entry it appears to output nothing, even though ive put an else function in there.

can anyone see why my else function wouldnt be working?

[msk_1980]

can you post your database table structure.

[middleman666]

its this:

create table myTable
(
id int not null primary key auto_increment,
date text,
reviewer varchar (150),
category varchar (10),
title text,
review text,
image text,
rating text,
artist text,
recordlabel text,
venue text,
location text
);

[msk_1980]

try:

SELECT * from myTable where category='Album' ORDER BY id DESC LIMIT 1;

does it help.

[middleman666]

Sorry, you must not have seen my previous post:

middleman666 wrote:

ok, after much trial and error if got this far:

<?
$result=mysql_query("SELECT * FROM MyTablw WHERE category = 'Album' ORDER BY id DESC LIMIT 1"); 
while($row=mysql_fetch_array($result)) {

if ($row['category']==Album){ 
	print '

	<!-- Start of : '.$row['title'].' : by : '.$row['reviewer'].' : -->

	<table width="100%" height="100%" cellpadding="2"  cellspacing="0" bgcolor="#FFFFFF">
      <tr>
        <td valign="top">
          <img width="120" border=1 alt="'.$row['title'].'" src="'.$row['image'].'"><br>
          <span class="reviewtitle">'.$row['title'].'</span><br>
          '.substr($row['review'],0,200).'... 
          (<a href="showreview.php?id='.$row['id'].'">more</a>)<br>
          '.$row['rating'].'<br></td>
      </tr>
	</table>

	<!-- End of : '.$row['title'].' : by : '.$row['reviewer'].' : -->

	';
}
else { 
	print 'There are no Album reviews yet.';   
}
}
?>

This gets the latest entry depending on the category.

If there isnt an entry it appears to output nothing, even though ive put an else function in there.

can anyone see why my else function wouldnt be working?

[msk_1980]

sorry, i missed that just got back home from work.....and its a bit late night.

I did try to replicate the whole thing on my server. guess what... the mistake is the table name being spelt wrong ie. instead of myTable you spelt it as myTablw

Ok, always try to use error handling it would have saved a lot of time in this case.

Did it work?

[middleman666]

sorry, ive been changing the table name from the original because I didn't want to post it on the forum. The table name is correct in my originals (the ones im testing on).

It just seems to be the else function which is not working and i am unsure why and it looks fine to me.

can you see any reason why it wouldnt be reading out the else function when theres no entry with the category 'Album' ?

[msk_1980]

Ok, This is the script i tried and it did print the else part.
I just included Disco instead of Album to get 0 records from the db.

<?php


$result=mysql_query("SELECT * FROM MyTable WHERE category = 'Disco' ORDER BY id DESC LIMIT 1") or die ("Cannot execute the query"); 

$no_row = mysql_num_rows( $result );


if ( $no_row > 0 )
{

   $row = mysql_fetch_assoc( $result );
    print ' 

    <!-- Start of : '.$row['title'].' : by : '.$row['reviewer'].' : --> 

    <table width="100%" height="100%" cellpadding="2"  cellspacing="0" bgcolor="#FFFFFF"> 
      <tr> 
        <td valign="top"> 
          <img width="120" border=1 alt="'.$row['title'].'" src="'.$row['image'].'"><br> 
          <span class="reviewtitle">'.$row['title'].'</span><br> 
          '.substr($row['review'],0,200).'... 
          (<a href="showreview.php?id='.$row['id'].'">more</a>)<br> 
          '.$row['rating'].'<br></td> 
      </tr> 
    </table> 

    <!-- End of : '.$row['title'].' : by : '.$row['reviewer'].' : --> 

    '; 
} 
else { 
      print 'There are no Album reviews yet.';    
} 

?> 

[middleman666]

Worked a treat! 🙂 thank you very much for all your help.