seeing double...results from search query

vaska

it's giving me the results i want PLUS...it's doubling the results of some other things in shouldn't be getting from the search...

this is what i have...

	if ($text != '') {
$query_s = mysql_query ("SELECT DISTINCT udate,title,text,status,section FROM main,sections WHERE (section LIKE '%$text%' or title LIKE '%$text%' or text LIKE '%$text%' or status LIKE '%$text%') ORDER BY id DESC" ) or die ("No, it ain't working you idiot!");
}

i have a relation between the main and sections...where main.secid=sections.secid...i bet this is the problem...but i don't know how to tighten this up...

help...thanks...jv

dawson1323

Try this query instead:

$query_s = mysql_query ("SELECT DISTINCT m.udate,m.title,m.text,m.status,m.section FROM m main,sections WHERE (section LIKE '%$text%' or title LIKE '%$text%' or text LIKE '%$text%' or status LIKE '%$text%') ORDER BY id DESC" ) or die ("No, it ain't working you idiot!");

Basically, you are calling this information from both tables and getting both results. If you add the obve you can use "m" or "s" to select the fields from one specific table. of maybe you want some fields from main and the other from sections:

$query_s = mysql_query ("SELECT DISTINCT m.udate,s.title,s.text,s.status,m.section FROM main m,sections s WHERE (section LIKE '%$text%' or title LIKE '%$text%' or text LIKE '%$text%' or status LIKE '%$text%') ORDER BY id DESC" ) or die ("No, it ain't working you idiot!");

-D

vaska

hmmm...that doesn't work...i'm getting the exact same output...

is it an issue that these two tables have a relationship between their data?

i used this query...

$query_s = mysql_query ("SELECT DISTINCT m.id,m.udate,m.title,m.text,m.status,s.section FROM main m,sections s WHERE (section LIKE '%$text%' or title LIKE '%$text%' or text LIKE '%$text%' or status LIKE '%$text%') ORDER BY id DESC" ) or die ("Wrongo");

dawson1323

Try something like this:

change:

"ORDER BY id DESC" )

to:

ORDER BY m.id DESC" )

or try something like:

ORDER BY id DESC WHERE m.id = s.id" )

and with the above add "s.id" to you list at the begining of the query.

-D

vaska

argh...it was working before...not i just get mysql error messages when i submit the search..."Unexpected error for MySQL database"

this is what i have...

$query_s = mysql_query ("SELECT DISTINCT s.secid,m.mainsecid,m.id,m.udate,m.title,m.text,m.status,s.section FROM main m,sections s WHERE (section LIKE '%$text%' or title LIKE '%$text%' or text LIKE '%$text%' or status LIKE '%$text%') ORDER BY m.id WHERE m.mainsecid=s.secid" ) or die ("Wrongo");

vaska

i was pretty close with this thing...and i figured it out...i'm posting the solution just in case somebody in the future comes across this thread (i hate threads that don't have solutions)...

this was it...had two WHERE's...didn't notice that...now it's only got one...

$query_s = mysql_query ("SELECT DISTINCT sections.secid,main.mainsecid,main.id,main.udate,main.title,main.text,main.status,sections.section FROM main,sections WHERE (section LIKE '%$text%' or title LIKE '%$text%' or text LIKE '%$text%' or status LIKE '%$text%') AND main.mainsecid=sections.secid ORDER BY main.id DESC " ) or die ("Wrongo");