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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

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

Post 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? :?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Post by Crashin »

Thanks, Mac! I did just that and it helped me find the problem! :)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

First rule of debugging: echo everything... :D

Mac
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

*cough* echo mysql_error() *cough*
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

oops...hehe..
:oops:
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Post by Crashin »

What information does mysql_error supply? :?:
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

The last MySQL Error
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Post by Crashin »

It's all in a name! :D
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

twigletmac wrote:First rule of debugging: echo everything... :D

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