Discussion Forum

daansk44

Member

From: Amersfoort, The Netherlands

Registered: September 12, 2016

Posts: 42

Search based on resources in page

Hey,

When you make a page (template) with some resources in it.
Is it possible to search based on a specific resource (using a function)?

timbuckingham

Administrator

From: Baltimore, MD

Registered: April 2, 2012

Posts: 978

Re: Search based on resources in page

You could do a MySQL LIKE query to search based on resources. Something such as:

$pages = SQL::fetchAll("SELECT * FROM bigtree_pages WHERE resources LIKE '%\"AK47\"'");

daansk44

Member

From: Amersfoort, The Netherlands

Registered: September 12, 2016

Posts: 42

Re: Search based on resources in page

Hey Tim,

Thanks for the response.

What if we want to search using $_GET variable (and escape it in the fist place)
When we do this

        $parameter1 = $_GET['cat'];
        $parameter2 = $_GET['Brand'];
        $paramenters = array ($parameter1, $parameter2);
        $pages = SQL::fetchAll("SELECT * FROM `bigtree_pages` WHERE `resources` LIKE '%XNCatDesc\": \"%?%\"%' and `resources` LIKE '%Brand\": \"%?%\"%'", $paramenters);
        return $pages;

It will throw an error

Fatal error: Uncaught Exception: SQL::query error - wildcard and argument count do not match (2 '?' found, 1 arguments provided) in C:\xampp\htdocs\projecten\froets\Source\core\inc\bigtree\sql-class.php:1396 Stack trace: #0 C:\xampp\htdocs\projecten\froets\Source\core\inc\bigtree\sql-class.php(974): SQL::query('SELECT * FROM `...', Array)

daansk44

Member

From: Amersfoort, The Netherlands

Registered: September 12, 2016

Posts: 42

Re: Search based on resources in page

We have tried this

 $cat = $_GET['cat'];
        $brand = $_GET['Brand'];
        $product = $_GET['product'];
        
        $pages = SQL::fetchAll("SELECT * FROM `bigtree_pages` WHERE `resources` like \'%\"XNCatDesc\": \"%?%\' and `resources` like \'%\"Brand\": \"%?%\' and `resources` like \'%\"ItemDesc\": \"%?%\'", $cat, $brand, $product);
        var_dump( $pages); 

but still throws an error

Fatal error: SQL::fetchAll called on invalid query resource. The most likely cause is an invalid query call. Last error returned was: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '\'%"XNCatDesc": "%'41'%\' and `resources` like \'%"Brand": "%'none'%\' and `reso' at line 1 in C:\xampp\htdocs\projecten\froets\Source\core\inc\bigtree\sql-class.php on line 959

timbuckingham

Administrator

From: Baltimore, MD

Registered: April 2, 2012

Posts: 978

Re: Search based on resources in page

It looks like you're escaping the ' in the like query. Also, I don't believe the auto escaping in the SQL class works with LIKE statements. Try this:

$cat = SQL::escape($_GET['cat']);
$brand = SQL::escape($_GET['Brand']);
$product = SQL::escape($_GET['product']);
$pages = SQL::fetchAll("SELECT * FROM `bigtree_pages` WHERE `resources` like '%\"XNCatDesc\": \"$cat\"%' and `resources` like '%\"Brand\": \"$brand\"%' and `resources` like '%\"ItemDesc\": \"$product\"%'");