How to modify or replace in PHP my SQL query containing "LIKE" and "bindValue" by CQL (Cassandra DataStax PHP DRIVER)?

Question

Looking for how to do a LIKE in a CQL query (using the following Cassandra PHP Driver: https://github.com/datastax/php-driver) replacing my following SQL code:

    $con->execute("CREATE TABLE IF NOT EXISTS images(siteUrl varchar PRIMARY KEY, imageUrl varchar, alt varchar, title varchar, description varchar, 
                    keywords varchar, textFromWebPage varchar, site_lang varchar, width_of_image float, height_of_image float, image_type varchar, 
                    image_extension varchar, image_attribute varchar, clicks bigint, broken int, centroidScore float, graphBasedScore float, scrapeScore float, 
                    centroidWeightedScore float, created_date timestamp) WITH caching='ALL';");
        
        $con->execute("CREATE CUSTOM INDEX images_prefix ON images(siteUrl, alt, title, keywords, description, textFromWebPage) USING 'org.apache.cassandra.index.sasi.SASIIndex'");
        
    
        $query = $this->con->prepare("SELECT * 
                                        FROM images 
                                            WHERE (title LIKE :term 
                                            OR alt LIKE :term 
                                            OR siteUrl LIKE :term 
                                            OR keywords LIKE :term 
                                            OR description LIKE :term
                                            OR textFromWebPage LIKE :term
                                            AND broken=0 ORDER BY clicks DESC LIMIT :fromLimit, :pageSize");
        
                    $searchTerm = "%". $term . "%";
                    $query->bindValue(":term", $searchTerm);
                    $query->bindValue(":fromLimit", $fromLimit, PDO::PARAM_INT);
                    $query->bindValue(":pageSize", $pageSize, PDO::PARAM_INT);
                    $query->execute();
        
                    $resultsHtml = "<div class='imageResults'>";
        
                    $count = 0;
                    
                    while($row = $query->fetch(PDO::FETCH_ASSOC)) {
                         ...
                         ...
                    }

I saw this on the following DataStax documentation link: https://docs.datastax.com/en/dse/5.1/cql/cql/cql_using/useSASIIndex.html, that it needs to do a CREATE CUSTOM INDEX to create a prefix on the Table concerned and specifying the column on which the LIKE request must be made. But in my case, I need to apply LIKE query to multiple column of images Table.

So how can I modify my Code above so that it adapts correctly to DataStax's CASSANDRA PHP Driver knowing that originally it was SQL that I am trying to replace with CQL and that it contains above all a bindValue ???

Please help me out as this has been a headache for me for several hours.

nazim · Accepted Answer · 2022-05-28 13:32:29Z

0

These lines don't need quotes, the string type PARAM binding will be enough (see below):

OR strcmp(soundex(title), soundex(:shorterm)) = 0
OR strcmp(soundex(alt), soundex(:shorterm)) = 0)
OR strcmp(soundex(siteUrl), soundex(:shorterm)) = 0
OR strcmp(soundex(keywords), soundex(:shorterm)) = 0
OR strcmp(soundex(description), soundex(:shorterm)) = 0
OR strcmp(soundex(description), soundex(:shorterm)) = 0

You have define the params as strings.

$query->bindValue(":term", $searchTerm, PDO::PARAM_STR);
$query->bindValue(":shorterm", $term, PDO::PARAM_STR);

answered May 28, 2022 at 13:32

nazim

1,5792 gold badges16 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to modify or replace in PHP my SQL query containing "LIKE" and "bindValue" by CQL (Cassandra DataStax PHP DRIVER)?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related