SQL troubles

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

SQL troubles

Post 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);
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Either change your where statement or add a distinct(vfunk_pages.id) to your select statement.
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post by psychotomus »

what you mean distinct? never used that before. what should I replace my where with?
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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);
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post 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 =)
psychotomus
Forum Contributor
Posts: 487
Joined: Fri Jul 11, 2003 1:59 am

Post 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 %'
Post Reply