1

I need to query the database table using multiple strings. A row should be selected only if the "tags" column matches all the strings. I don't know the amount of strings beforehand (it's dynamic).

I think I'm close with this code but I'm not getting any matches from the database:

 function getMatchingRecipes()  {


    // get the search query:
    $tags = ($_POST["tagsToSearchWith"]);
    
    // clean it up:
    $tags = sanitize_text_field( $tags);

    //explode to an array based on commas:
    $explodedTagsArray = explode(',', $tags);

    //construct query dynamically using the array
    $myDynamicQuery = "SELECT * FROM recipesTable WHERE recipetags like %s";

    $appendFormatIndicatorsWithLoop = ""; //empty the placeholder on every query

    $i = 0; //for limiting the foreach loop to produce one less than the array has (because one like %s is already there by default)
    foreach ($explodedTagsArray as $singleTag) {
        $appendFormatIndicatorsWithLoop .= " AND recipetags like %s"; //do this as many times we have tags in the array
        if(++$i > (count($explodedTagsArray)-2)) break;
      }

      $myDynamicQuery .= $appendFormatIndicatorsWithLoop; //appending


        // ... do stuff with it
    global $wpdb;

    $recipe = $wpdb->get_results( $wpdb->prepare($myDynamicQuery, $explodedTagsArray), ARRAY_A ); //prepare now needs a minimum of two arguments. ARRAY_A is for get_results, and defines the return type to be an assosiative array

    //log current status to console:
    echo("<script>console.log('PHP: " . $myDynamicQuery . " array was: " . json_encode($explodedTagsArray) . "');</script>");

    echo json_encode($recipe); 


};

This is what the entire query and the array looks like in the console:

<script>console.log('PHP: SELECT * FROM recipesTable WHERE recipetags like %s AND recipetags like %s array was: ["butter","bread"]');</script>[]0

In my database table there is a column called recipetags. One row has "butter,bread" in that column. Why is my query not matching that row? Is a comma in the wrong place somewhere or am I formatting something incorrectly?

0

Got it working, had to format the LIKE statements like this:

//construct query dynamically using the array
    $myDynamicQuery = "SELECT * FROM recipesTable WHERE recipetags LIKE '%%%s%%'";


    $appendFormatIndicatorsWithLoop = ""; //empty the placeholder on every query

    $i = 0; //for limiting the foreach loop to produce one less than the array has (because one like %s is already there by default)
    foreach ($explodedTagsArray as $singleTag) {
        $appendFormatIndicatorsWithLoop .= " AND recipetags LIKE '%%%s%%'"; //do this as many times we have tags in the array
        if(++$i > (count($explodedTagsArray)-2)) break;
      }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.