Page 1 of 1

SQL troubles

Posted: Mon Jul 25, 2005 6:27 pm
by psychotomus
the number of results turns out to be over 70,000 when theres really only 6 results. its giving duplicate results. any suggestions?


$sentence turns out to be

SELECT vfunk_pages.id, vfunk_pages.url, vfunk_pages.title, vfunk_pages.description, vfunk_pages.keywords, vfunk_pages.accepted, vfunk_pages.ip, vfunk_pages.category, vfunk_categories.name AS category_name FROM vfunk_pages, vfunk_categories WHERE vfunk_categories.id = vfunk_pages.category AND vfunk_pages.title LIKE '% music festival %' OR vfunk_pages.keywords LIKE '% music festival %' OR vfunk_pages.description LIKE '% music festival %' OR vfunk_pages.url LIKE '% music festival %'


code is.

Code: Select all

$sentence = &quote;SELECT {$prefix}pages.id, {$prefix}pages.url, {$prefix}pages.title, {$prefix}pages.description, {$prefix}pages.keywords, {$prefix}pages.accepted, {$prefix}pages.ip, {$prefix}pages.category, {$prefix}categories.name AS category_name FROM {$prefix}pages, {$prefix}categories WHERE {$prefix}categories.id = {$prefix}pages.category AND &quote;.$sentence;
	print $sentence.&quote;<br>&quote;;	
	$sql = mysql_query($sentence);
	$n_results = mysql_num_rows($sql);

Posted: Mon Jul 25, 2005 6:51 pm
by hawleyjr
Either change your where statement or add a distinct(vfunk_pages.id) to your select statement.

Posted: Mon Jul 25, 2005 6:54 pm
by psychotomus
what you mean distinct? never used that before. what should I replace my where with?

Posted: Mon Jul 25, 2005 6:56 pm
by hawleyjr
http://dev.mysql.com/doc/mysql/en/disti ... ation.html

Code: Select all

$sentence = &quote;SELECT distinct({$prefix}pages.id) as id, {$prefix}pages.url, {$prefix}pages.title, {$prefix}pages.description, {$prefix}pages.keywords, {$prefix}pages.accepted, {$prefix}pages.ip, {$prefix}pages.category, {$prefix}categories.name AS category_name FROM {$prefix}pages, {$prefix}categories WHERE {$prefix}categories.id = {$prefix}pages.category AND &quote;.$sentence;
	print $sentence.&quote;<br>&quote;;	
	$sql = mysql_query($sentence);
	$n_results = mysql_num_rows($sql);

Posted: Mon Jul 25, 2005 7:09 pm
by psychotomus
thanks. i'll have to try this out tomorrow. guy wont give me access to his server so i have to send him the files to upload =)

Posted: Tue Jul 26, 2005 2:15 pm
by psychotomus
that doesnt seem to work. take a look.


http://www.vfunk.com/search_test.php

do exact phrase search for "music festival"

the SQL statement will also be posted befor the search.


which is

SELECT distinct(vfunk_pages.id) as id, vfunk_pages.url, vfunk_pages.title, vfunk_pages.description, vfunk_pages.keywords, vfunk_pages.accepted, vfunk_pages.ip, vfunk_pages.category, vfunk_categories.name AS category_name FROM vfunk_pages, vfunk_categories WHERE vfunk_categories.id = vfunk_pages.category AND vfunk_pages.title LIKE '% music festival %' OR vfunk_pages.keywords LIKE '% music festival %' OR vfunk_pages.description LIKE '% music festival %' OR vfunk_pages.url LIKE '% music festival %'