mysql SELECT not working.

[joe92]

The following query does not work and I have no idea why. I have a feeling that it is something to do with the LIKE?

$search_query = mysql_query("SELECT DISTINCT
				threadID
			    FROM
				forum_posts
			    WHERE
				postSubject LIKE '%find me%'
				AND posterID = '1'");

 

The error is in that it will get all DISTINCT threadID's regardless of the posterID that got sent. I.e. If you searched 'find me' by bob, it will get 'find me' by bob and 'find me' by fred and 'find me' by me and 'find me' you. It is only meant to display 'find me' by bob.

 

Any help is greatly appreciated.

Joe.

[AMcHarg]

I don't see anything wrong with your LIKE syntax, or the rest of the query for that matter. Perhaps it's actually pulling out the correct thing and it's the database records which are incorrect. Have you double checked the posterID of each record to make sure they aren't actually all equal to 1?

 

[joe92]

It was nothing to do with this query Ha, sorry.

 

I had a follow up query further down the page to get the information to display. Since the query was selecting the information based on the threadID sent from the above query it had no idea which posterID to use and would just get the one that was sent from that query. The threadID's are the same in an entire thread so if bob had responded 5 posts down the page the above query would find that and carry on. The second one (due to a stupid error on my behalf) wasn't checking the posterID again and would therefore search according to the threadID that was given to it. This would mean that the information it found was the opening post in the thread that bob had posted in.

 

$get_result_query = mysql_query("SELECT
				*
			FROM
				forum_posts
			WHERE
				threadID = '$search_query_row[0]'
				AND posterID = '1'"); <!-- was missing this line.