Can't figure out why this isn't working

PHP
1 
$sql = 'SELECT visitor_name, comment, COUNT(id) as comment_id
				FROM ' . VISITOR_TABLE;
		$result = $db->sql_query($sql);
		$id_count = (int) $db->sql_fetchfield('comment_id');
		if($id_count == 0)
		{
			$template->assign_var('NO_COMMENT', '<p>There are no comments</p>');
		}
		else
		{
		
			while($row = $db->sql_fetchrow($result))
			{
				$template->assign_block_vars('visitor', array(
					'NAME'		=>	$row['visitor_name'],
					'COMMENT'	=>	$row['comment'],
				));
			}
		}

The if statement works just fine, but when I add data to the database the else isn’t working, although I got a feeling that I’m screwing up somehow within the while statement… anybody got any ideas?

2

if your question is php related, perhaps you could try the php forum

however, your query looks bad, and it appears you aren’t checking for syntax errors

are you trying to count comments for each visitor?

you should really test your query first, outside of php, before putting it into your php code

3

Sorry, i wasn’t sure which forum to put it in as it has both PHP and SQL in it, but anyway, I’m trying to show the data in the database, and if there isn’t any data show the “no comments”

4

okay, click on the little red flag under your avatar and ask the moderators to move this thread to the php forum

meanwhile, remove the COUNT(id) as comment_id from the query

5

Once I removed that part of the query, the data from the database showed up, but if I delete the data the “no comment” doesn’t show up anymore.

6

You are using a Database Abstraction Layer that I am not familiar with, so I’d suggest something a bit simpler with the introduction of a counter.



$sql = 'SELECT visitor_name, comment FROM ' . VISITOR_TABLE; 
        $result = $db->sql_query($sql); 
        $count = 0; // start a counter in PHP

            while($row = $db->sql_fetchrow($result)) 
            {  
                $count++;  // add one to the counter for each iteration

                $template->assign_block_vars('visitor', array( 
                    'NAME'        =>    $row['visitor_name'], 
                    'COMMENT'    =>    $row['comment'], 
                )); 
            } 
        
 // if the counter is still at 0 then do your other thing
 // always try and do a strict check with three ===
if( $count === 0 ){

    $template->assign_var('NO_COMMENT', '<p>There are no comments</p>'); 

}

There is probably an easier way to test the number of rows returned, but this ought to work UNTESTED

7

Whatever class you’re using to access your DB should have a method that will tell you the number of results returned. Something similar to mysql_num_rows, PDOStatement::rowCount, or mysqli_stmt_num_rows.