Page 1 of 1

Can you see a problem with this query??? Please take a peek.

Posted: Wed May 29, 2002 10:48 pm
by Crashin
Okay...I'm baffled. If I have a query:

Code: Select all

<?
	include("kb_includes.php");
	
	$title = "Test";
	head($title,$title);
	
	chec36_db_connect() or die("Cannot connect");
	
	$query = "SELECT DISTINCT kb_record.question, kb_record.resolution, kb_record.kb_record_id, kb_record.recordno, complete_record.kb_record_id 
		FROM kb_record, complete_record 
		WHERE kb_record.kb_record_id = complete_record.kb_record_id 
		AND kb_record.comp_manual='T'";
	
	//search for manual records with a specific date
	if(IsSet($man_date))
		$query .= " AND kb_record.dateadd_manual='$man_date'";
	
	
	//group by and order by
	$query .= " ORDER BY kb_record.recordno";
									
	$result = mysql_query($query);
	echo mysql_num_rows($result);
?>
I have 4 rows returned. If I then use the query:

Code: Select all

<?
	include("kb_includes.php");
	
	$title = "Test";
	head($title,$title);
	
	chec36_db_connect() or die("Cannot connect");
	
	$query = "SELECT DISTINCT kb_record.question, kb_record.resolution, kb_record.kb_record_id, kb_record.recordno, complete_record.kb_record_id 
		FROM kb_record, complete_record 
		WHERE kb_record.kb_record_id = complete_record.kb_record_id 
		AND kb_record.comp_manual='T'";
	
	//search for a specific application, if applicable
	if($app_id <> 'all_apps')
		$query .= " AND complete_record.app_id='$app_id'";
	
	//search for a specific category, if applicable
	if($cat_id <> 'all_cats')
		$query .= " AND complete_record.cat_id='$cat_id'";
	
	//search for manual records with a specific date
	if(IsSet($man_date))
		$query .= " AND kb_record.dateadd_manual='$man_date'";
	
	
	//group by and order by
	$query .= " ORDER BY kb_record.recordno";
									
	$result = mysql_query($query);
	echo mysql_num_rows($result);
?>
I have 0 rows returned. Why is this happening? All that's been added are the two conditional variables ($app_id and $cat_id), which don't exist in this case, and so should be passed-over by the script (so I think). Notice that the query still returns correct values with the other conditional variable ($man_date) missing. Ugh! What am I doing wrong? :?

Posted: Thu May 30, 2002 3:06 am
by twigletmac
Have you tried echoing the SQL statement before running the query, ie.

Code: Select all

echo $query.'&lt;br /&gt;';
$result = mysql_query($query);
then you can see if it looks as you expect it to.

Mac

Posted: Thu May 30, 2002 7:04 am
by Crashin
Thanks, Mac! I did just that and it helped me find the problem! :)

Posted: Thu May 30, 2002 7:08 am
by twigletmac
First rule of debugging: echo everything... :D

Mac

Posted: Thu May 30, 2002 7:17 am
by jason
*cough* echo mysql_error() *cough*

Posted: Thu May 30, 2002 7:21 am
by twigletmac
umm... mysql_error wasn't appropriate here as there wasn't an error executing the query as mysql_num_rows returned 0 so the query executed fine.

If you have 0 rows returned and the SQL is syntactically correct mysql_error won't help you.

Mac

Posted: Thu May 30, 2002 8:22 am
by jason
oops...hehe..
:oops:

Posted: Thu May 30, 2002 8:59 am
by Crashin
What information does mysql_error supply? :?:

Posted: Thu May 30, 2002 9:01 am
by jason
The last MySQL Error

Posted: Thu May 30, 2002 9:02 am
by twigletmac
It gives a more meaningful error than the generic PHP ones when there's an error connecting to a MySQL database server, selecting a MySQL database or executing a SQL query.

http://www.php.net/manual/en/function.mysql-error.php

Mac

Posted: Thu May 30, 2002 9:03 am
by Crashin
It's all in a name! :D

Posted: Thu May 30, 2002 12:56 pm
by mikeq
twigletmac wrote:First rule of debugging: echo everything... :D

Mac
How many times do we have to tell people :D