JOIN

Hello Bob,

Without any experience of how SphinxSE works it's a bit tricky to work out what's going on; but the way I would debug this is to print out the final SQL statement being generated by search.php, and then compare that to what you had working through the command line.

The final search query is constructed by the time of the following line:

If you add the following at this point:

..that will display the fully constructed SQL, which you can study and perhaps try manually from the command line. I always use square brackets when printing out SQL for debug purposes so that I can see exactly where the string value begins and ends.

My immediate thought is that the JOIN statement may be in the wrong place; because of the way the LIMIT statement is appended to $sql...

Hope this helps,
Cheers,
David.

Hi David,

Thanks for the reply. Let me ask you the same thing in a different way: If you needed to modify search.php in order to add a JOIN how would you do it?

In this case 'searchse' is the second table which is being used by SphinxSE as a MySQL plug to comminicate and perform search through an external index. Everything is working fine and I can get data out of myproducts table that has been created by the PT. Through the command line. I just have to correctly modify search.php in order to display those results on the website. This is the final hurdle I have to overcome.

This is how I did it but it is not giving me the results back:

if (strlen($parts[0]) > 3)

{

$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products` JOIN searchse ON (myproducts.id=searchse.id) WHERE MATCH name AGAINST ('".database_safe($parts[0])."') GROUP BY name";

I know it's the JOIN statement that is not in the right place or need something else also but I am struggling to see where I should put it and how.

I tried that debug option and I got a 'correct' version of the statement I think:

[SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('whatever...') AS relevance FROM `myproducts` JOIN searchse ON (myproducts.id=searchse.id) WHERE MATCH name AGAINST ('whatever...') GROUP BY name ORDER BY relevance DESC LIMIT 0,10]

Many Thanks.

Bob

Hi Bob,

How does the SQL constructed by the script compare to the SQL that you used manually from the command line to text the index?

Cheers,
David.

David,

Sorry I missed that. Yes I got this when running it through the command line:

Column 'id' in field list is ambiguous.

Hmmm...Not sure why. If I use that statement from the first post I get results and id part of the statement is the same.

Many Thanks.

Bob

Hi Bob,

It sounds like you need to resolve id from COUNT( id ) - try changing it to:

Cheers,
David.

David,

Thanks for that. It is now saying: 'empty set' through the command line.

Many Thanks.

Bob

David,

Maybe I need to change other similar SQL statements in search.php as well in the same way to reflect this change?

Many Thnaks.

Bob

Hi Bob,

Can you post the SQL that you originally had working from the command line? I think what's needed is to remove the MySQL full text code so that the indexing is left to SphinxSE...

Cheers,
David.

Hi Bob - I just sent an email to your address that you have registered on this forum - let me know if you're no longer receiving email to that address...

Cheers,
David.

Hi David,

You mean the SQL I used for indexing? Here it is:

sql_query = \
SELECT id, merchant, name, description, search_name, category, brand, dupe_hash \
FROM myproducts

or the last SQL I used to test it:

SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( myproducts.id ) AS numMerchants, MATCH name AGAINST ('james') AS relevance FROM `myproducts` JOIN searchse ON (myproducts.id=searchse.id) WHERE MATCH name AGAINST ('james') GROUP BY name ORDER BY relevance DESC LIMIT 20,10;

Many Thanks.

Bob

Hi Bob,

In the original message, where you mentioned "I have finally installed everything and tested SphinxSE and PT by using JOIN through the command line. It's working."

Did you actually use any SQL to query to the products table making use of the SphinxSE index? That's what it would be useful to see, then i'll try and work out how that should be converted to work in search.php...

Cheers,
David.

Hi David,

I used this when testing it:

SELECT * FROM myproducts
JOIN searchse ON (myproducts.id=searchse.id)
WHERE query='james;mode=any';

I got excpected results and all the data, links, price etc.. from that. Now I do not know whether that is coming from the index I created with Sphinx or just directly but I did use the example shown in the documentation and then modified it to reflect my tables' names. In this case myproducts was created with the PT and searchse is a special Sphinx table that is being used as a MySQL plug for Sphinx search through the index.

Many Thanks.

Bob

Hi Bob,

Thanks for that - it makes sense (I think) and I can see how it should be working to use the Sphinx index. I also think I can convert that for use in search.php...

Could you email me your latest search.php and i'll try and make the changes in one go, then if that doesn't work we'll build up to it...!

Cheers,
David.

Hi David,

Thanks very much for your help. I just wanted to let you know that is working now! I want to say big THANK YOU for all your support and help. I wish you all the best in 2008! And beoynd.

Many Thanks.

Bob