Filldb.php error

[Sarge2013]

Hello ! Very interested to test your SFacetedExample - but met the following problem running

[root@f228 su]# php filldb.php PHP Notice: Undefined variable: ln_sph in /var/www/html/su/filldb.php on line 25 PHP Fatal error: Call to a member function exec() on a non-object in /var/www/html/su/filldb.php on line 25

As I see - line 25 calls $stmt = $ln_sph->exec etc

My environment - Centos 6.5, PHP 5.5.12 with pdo_mysql installed. Could you help with that error ?

P.S. As a variant - could you also upload a dump of "facetdemo" MySQL database right here, on Github ? for somebody else who can meet same problems with filldb.php ?

Thank you in advance ! Regards, Serge

[adriannuta]

Hi, can you paste from common.php the "$ln_sph = new PDO( 'mysql:host=127.0.0.1;port=9306' );" in filldb.php and if works then? Do you have the mysql pdo php extension installed?

[Sarge2013]

Hi, thank you for comment ! I've pasted two variants to FILLDB.php Variant 1 //commented require_once 'common.php'; $ln_sph = new PDO( 'mysql:host=127.0.0.1;port=9306;dbname=facetdemo', 'usernameXXX', 'passwordXXX' ); Variant 2 //commented require_once 'common.php'; $ln_sph = new PDO( 'mysql:host=127.0.0.1;port=9306' );

With both I have a similar error php filldb.php PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2002] Connection refused' in /var/www/html/su/filldb.php:3 Stack trace:

0 /var/www/htmlsu/filldb.php(3): PDO->__construct('mysql:host=127....')

1 {main}

thrown in /var/www/html/su/filldb.php on line 3

According to phpinfo.php I have pdo_mysql installed and enabled Additional .ini files parsed - /etc/php.d/pdo.ini, /etc/php.d/pdo_mysqlnd.ini, /etc/php.d/pdo_odbc.ini, /etc/php.d/pdo_pgsql.ini, /etc/php.d/pdo_sqlite.ini API Extensions - mysql,mysqli,pdo_mysql PDO drivers mysql, odbc, pgsql, sqlite PDO Driver for MySQL enabled

Dear Adrian, if you have no much time to find a solution for FILLDB.PHP - may be really easier to dump your SQL database used for http://sphinxdemos.adriannuta.com/SphinxFacetingExample/ ? BY THE WAY - something wrong is there with your demo ! Check it...because it lost the parameters of BRANDS, CATEGORIES, PROPERTY on left side !

Thank you in advance for your next reply ! Best regards, Serge

[Sarge2013]

Good morning ! I changed your $ln_sph = new PDO( 'mysql:host=127.0.0.1;port=9306' ); because 9306 is busy with Sphinx searchd to $ln_sph = new PDO( 'mysql:host=127.0.0.1;port=3306' ); because MySQL listen on 3306 port, right ?

After that I run in shell [root@f228 su]# php filldb.php No Errors, it just pauses for a second and comes back to [root@f228 su]#

Database facetdemo not created and not filled with data. Could you give a hint what is the reason of this problem ? Thank you in advance,

[adriannuta]

No, the port must be the SphinxQL port from Sphinx. You need to enable that in sphinx.conf, you should have a line like 'listen = localhost:9306:mysql41'. This demo works on a RT index, so no need for a MySQL table, filldb.php fills a RT index. There is no need to upload a dump of the data for facet, because filldb.php genereates it.

[Sarge2013]

Dear Adrian, thank you for this explanation ! It was very new for me, so I did not catch how it works. Now your demo works based on PDO emulating RT index.

Could you hint what files must be corrected to use your demo with a normal MySQL database ? May be you could give a sample of files ? Thank you in advance !

[adriannuta]

You mean to use a plain index that takes data from a MySQL table? You need to create the plain index in sphinx.conf ( check documentation on plain [ or on-disk ] indexes) and index the data. There is no change needed in the search files ( except changing the index name ).

[Sarge2013]

Dear Adrian, First of all - THANK YOU FOR YOUR HINTS !

I try to use your idea - but I met a problem with sql_attr_multi. Please, take a look.

I've created dbase facetdemo with table DATA - simple MySQL to correspond data like on your demo site

id (primary, unique - values = 1,2,3 and so on) title (values = Product One One, Product One Two and so on ) property (values = one, two, three and so on) brand_name (values = First, Second and so on) brand_id (values = 1,2,3 and so on) //brand_id generated to correspond brand_name First = brand_id 1, brand_name Second = brand_id 2 and so on categories (values = Category10, Category11, Category12, Category13, Category14, Category15) price (type = decimal, length/values - 10,0)

Plain index with name facetdemo source facetdemo { type = mysql

sql_query       = \
    SELECT id, title, price, property, brand_name, categories, brand_id \
    FROM data

    sql_attr_string  = title
    sql_attr_string  = brand_name
    sql_attr_string  = property

    sql_attr_uint    = price
    sql_attr_uint    = brand_id

// NOW we need sql_attr_multi

I tried sql_attr_multi = uint categories from query; SELECT id, categories // RESULT = ERROR: index 'facetdemo': multi-valued attribute 'categories' of wrong source-type found in query; must be 'field'.

I tried sql_attr_multi = uint categories from field // RESULT = ERROR: index 'facetdemo': No fields in schema - will not index.

I also tried sql_field_string = categories sql_attr_multi = uint categories from field // RESULT = ERROR: duplicate attribute name: categories // ERROR: index 'facetdemo': failed to configure some of the sources, will not index.

So, I'm in a dead point with sql_attr_multi for Categories.

Could you give a hint how to solve it to use with your search files ? (I did not make any changes in search files + also my plain index is the same name facetdemo)

Thank you in advance !

[Sarge2013]

Dear Adrian, I found a solution for the sql_attr_multi with right config of sql_query :) so it's matter of Sphinx knowledge.

But now I see a problem with your paginator, because your link looks like http://sphinxdemos.adriannuta.com/SphinxFacetingExample/facetmulti.php?query=&start=20

my link looks like http://example.com/%3C?echo%20$url;?%3E?query=&start=20 or http://example.com/<?echo $url;?>?query=&start=20 Could you hint with this error ? Best regards Serge

[adriannuta]

Use <?php instead of <?, you must have shortcodes disabled