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?

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?

• wpdb

1 Answer 1

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;
      }