[itdoell]

For some reason when I try to join favorites with inner join the query doesn't work. I'm not getting a message telling me there's a problem, it just won't display results.

$sql = "SELECT id, poster_id, poster_name, file_name, title, message, date,
 MATCH(title, message, file_name) AGAINST('" . $search . "') AS score
 FROM posts INNER JOIN favorites ON favorites.post_id = posts.id
 WHERE favorites.user_id = '$user_id' AND MATCH(title, message, file_name) AGAINST('" . $search . "')
 ORDER BY rank DESC, date DESC, score DESC";

Yet when I do this, without joining favorites, it works just fine.

$sql = "SELECT id, poster_id, poster_name, file_name, title, message, date,
 MATCH(title, message, file_name) AGAINST('" . $search . "') AS score
 FROM posts
 WHERE MATCH(title, message, file_name) AGAINST('" . $search . "')
 ORDER BY rank DESC, date DESC, score DESC";

To be clear, I'm sure that the posts I'm searching for are in the favorites table. So I'm positive it's an issue with my query. Can anyone pin point what I'm doing wrong here?

This post has been edited by itdoell: 02 December 2011 - 05:57 AM

[e_i_pi]

Have you tried a LEFT OUTER JOIN to see if you get the same set of results back? If you don't, then it may be that you need to precede your attributes with their respective table names.

[itdoell]

e_i_pi, on 01 December 2011 - 09:21 PM, said:

Have you tried a LEFT OUTER JOIN to see if you get the same set of results back? If you don't, then it may be that you need to precede your attributes with their respective table names.

LEFT OUTER JOIN didn't seem to make a difference, neither did adding "posts." before each attribute, I have no duplicating attributes either. The only columns in the favorites table is "post_id" and "user_id", neither of which appear in the posts table.

[e_i_pi]

Hmm that's a bit of an ambiguous answer hehe, I could take it to mean two different things.

I'll explain why I asked you to do a LEFT OUTER JOIN. LEFT OUTERs are used to augment the dataset, and can produce extra rows of data, but do not reduce the number of rows. So, your original query should produce at least the number of rows that the LEFT OUTER JOIN query produces. If the LEFT OUTER JOIN query produces less, or no rows, then there's a syntax problem with the query.

Secondly, I assume this query is against a database of forum tables. I'm not sure what forum it is, but if it runs on the INNO_DB engine, then there may well be foreign key constraints. If there are foreign key constriants, then we can investigate how the JOINs should be made. If my first suggestion doesn't work, try this query:

SELECT
	TABLES.TABLE_NAME,
	COLUMNS.COLUMN_NAME,
	COLUMNS.DATA_TYPE,
	KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME,
	KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME
FROM information_schema.TABLES
INNER JOIN information_schema.COLUMNS ON
	COLUMNS.TABLE_SCHEMA = TABLES.TABLE_SCHEMA
	AND COLUMNS.TABLE_NAME = TABLES.TABLE_NAME
LEFT OUTER JOIN information_schema.KEY_COLUMN_USAGE ON
	KEY_COLUMN_USAGE.TABLE_SCHEMA = TABLES.TABLE_SCHEMA
	AND KEY_COLUMN_USAGE.TABLE_NAME = TABLES.TABLE_NAME
	AND KEY_COLUMN_USAGE.COLUMN_NAME = COLUMNS.COLUMN_NAME
WHERE TABLES.TABLE_NAME = 'favorites'
ORDER BY COLUMNS.ORDINAL_POSITION

This query will yield information on each of the columns of the 'favorites' table. Post that information back here so I can have a look at how the schema meshes up.

Also, what forum software are you using? I may be able to go onto the forum software site and get a database map.

[itdoell]

I'm not using any forum software, I built everything myself. Also, the returned information was NULL, except for the name, column, and datatype (varchar). However, if I have set up the columns in both tables for "FULLTEXT" and the engine type is MyISAM, not INNO_DB.

Also, "LEFT OUTER JOIN" produced no results, but did not warn me of a syntax problem. I cannot for the life of me seem to get this search query working while trying to combine tables. I can search both tables individually, just can't seem to combine them.

This post has been edited by itdoell: 02 December 2011 - 12:45 AM

[e_i_pi]

Hmm, just noticed you've got quotes areound your user_id, which means it's trying to match a string. user_id is likely an int. Try this:

$sql = "SELECT id, poster_id, poster_name, file_name, title, message, date,
 MATCH(title, message, file_name) AGAINST('" . $search . "') AS score
 FROM posts INNER JOIN favorites ON favorites.post_id = posts.id
 WHERE favorites.user_id = $user_id AND MATCH(title, message, file_name) AGAINST('" . $search . "')
 ORDER BY rank DESC, date DESC, score DESC";

[itdoell]

The quotes are needed, the $user_id is set in a session $user_id = $_SESSION['USER_ID'] which is used for logging in etc. When I try running the query you gave me I just get...

SQL query failed. Check your query.

Error Returned: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a97c98be27962fb65711ab4eb0a5 AND MATCH(title, message, file_name) AGAINST('bloo'' at line 4You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a97c98be27962fb65711ab4eb0a5 AND MATCH(title, message, file_name) AGAINST('bloo'' at line 4

Yes, my search query was "bloo".

This post has been edited by itdoell: 02 December 2011 - 01:11 AM

[Ionut]

Your query seems correct. I would recommand you to "debug" the query(how I like to say). To see where is the problem, try to add at your basic query step by step a clause.

$sql = "SELECT id, poster_id, poster_name, file_name, title, message, date,
 MATCH(title, message, file_name) AGAINST('" . $search . "') AS score
 FROM posts
 WHERE MATCH(title, message, file_name) AGAINST('" . $search . "')
 ORDER BY rank DESC, date DESC, score DESC";

You said this is working.
Add the inner join clause. See if there are still coming results. If no, keep the inner join and delete the where clause. If yes, add the user condition to where (favorites.user_id = '$user_id' ).

Did you try to run the query directly in a db gui(MysqlAdmin I think is one of them) with the values you except your query to execute? It is ok there?

[itdoell]

Okay, this works (sort of)

SELECT id, poster_id, poster_name, file_name, title, message, date,
 MATCH(title, message, file_name) AGAINST('bloo') AS score
 FROM posts INNER JOIN favorites
 WHERE MATCH(title, message, file_name) AGAINST('bloo') AND favorites.user_id = '0305470e07a97c98be27962fb65711ab4eb0a5'
 ORDER BY rank DESC, date DESC, score DESC

It is returning the results, but it is duplicating the same post 15 times... which is the same number of rows in favorites where the user_id is the one above.

[itdoell]

It is also returning results no matter if it is in favorites or not, and whenever I try to add "AND favorites.post_id = posts.id" it returns absolutely nothing... Same thing happens when I try to do "INNER JOIN favorites ON favorites.post_id = posts.id"

This post has been edited by itdoell: 02 December 2011 - 02:02 AM

[Ionut]

If there are exact the same records in your record set, you may try using Distinct

This post has been edited by Ionut: 02 December 2011 - 02:08 AM

[itdoell]

Ionut, on 02 December 2011 - 02:07 AM, said:

If there are exact the same records in your record set, you may try using Distinct

I did, but that still doesn't change the fact it is returning records for all posts, not just the one in the user's favorites.

[Ionut]

Ok, I missed that in Inner join you didn't put "on" condition.
What happens when you run this queries?

SELECT id, poster_id, poster_name, file_name, title, message, date,
 MATCH(title, message, file_name) AGAINST('bloo') AS score
 FROM posts INNER JOIN favorites on favorites.post_id = posts.id
 ORDER BY rank DESC, date DESC, score DESC

2.

SELECT id, poster_id, poster_name, file_name, title, message, date,
 MATCH(title, message, file_name) AGAINST('bloo') AS score
 FROM posts INNER JOIN favorites on favorites.post_id = posts.id
 WHERE MATCH(title, message, file_name) AGAINST('bloo') 
 ORDER BY rank DESC, date DESC, score DESC

[itdoell]

The first one is returning all the favorites of existing posts, but is not running the search. The second one is returning absolutely nothing.

[itdoell]

Alright guys, I figured out the problem. As I was falling asleep it occurred to me that it probably had to do with the whole "natural language" search and the number of results being returned compared to the number of posts I had favorited. Turns out I was right... so I pretty much just wasted hours trying to figure out what was wrong with my query when nothing was wrong at all, just that the ratio of what was being searched for was too high... and when I tried searching for other things it was after I had broken the query trying to "fix it". Thanks for all your help guys, sorry to waste your time.

This post has been edited by itdoell: 02 December 2011 - 02:02 PM