php foreach loop search mysql table with several words

Question

I have a mysql table called foods with columns "id, name, addinfo, picname, mg1cup, mg100g". I have a form where the user can submit between 1-20 food names. The php file takes the submitted food names in an array called $terms[]. I need to search the sql table for all terms and return results for all columns for each of the terms.

However, the results are only showing the first term submitted, repeated as many times as there are inputs (for example, if two words were inputted, the first term gets outputted in the results twice - instead of first word results, then second word results).

I don't know what I'm doing wrong. Here's my code (I haven't added the function to sanitize the strings yet):

<?php

error_reporting(E_ALL);

ini_set('display_errors', '1');

//connect to the wordpress (bluehost)DB 

require_once '../../../wp-config.php';

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die('Could not connect to mysql');

$Db = mysql_select_db(DB_NAME, $link) or die('Could not select database.');   

//check to see if the search term has a value, matches "name" column in DB, and if so, put it into an array called terms
if (isset($_POST['terms'])) {     

    $terms = ($_POST['terms']);

        if (count($terms) > 0 ) { 

            $results=array();

            foreach($terms as $term) {

                $sql = 'SELECT * from `wpqr_foods` WHERE `name` like  "%'. $term .'%" ORDER BY name ASC';


                $Q1 = mysql_query($sql);

                if(mysql_num_rows($Q1)) {

                    while ($Res = mysql_fetch_assoc($Q1)) {

                        $results[] = $Res; 

                    }

                }

                //$results = mysql_query($sql);


                $sql = 'SELECT * from wpqr_foods WHERE name LIKE "%'. $term .'%" ORDER BY name ASC';

                $Q2 = mysql_query($sql);

                //get results

                if(mysql_num_rows($Q2)) {

                    while ($Res = mysql_fetch_assoc($Q2)) {

                        $results[] = $Res; 

                    }

                }

            }

            if (count($results) > 0 ) {

                foreach ($results as $CurRes ) {

                    echo $CurRes['name']. "<br/>"; 
                    echo $CurRes['addinfo']. "<hr/>"; 

                    /* 

                    [id] => 5
                    [name] => Apples
                    [value] => Yes
                    [addinfo] => They can eat apples.  They can also eat the skin of the apple and the leaves of the apple tree, but the tree leaves are high in calcium, so limit their intake.  They can also chew on the apple tree branches.
                    [picname] => apple
                    [mgc1cup] => 5.8
                    [mgc100g] => 4.6

                    */
                }

            }

            else {

                echo "(nothing entered)";//MEANS $TERM IS EMPTY

            }


        }
        else {

            echo "(nothing entered)";//means $_POST IS NOT SET (end of if(isset($_POST['term'])) { )

        }             
}

//function to sanitize array values from form
function sanitizeString($var) {
         if (is_array($val))
           {
           foreach ($val as $k => $v)
               {
               $val[$k] = htmlentities(strip_tags($v),ENT_QUOTES);
               }
           }
         else
           {
             $val = htmlentities(strip_tags($terms),ENT_QUOTES);
           }
        return $val;
}
?>

The HTML is

<div class="my-form">
        <form role="form" id="step1" method="post" action="/wp-content/themes/mia/vitcdata.php">
            <p class="text-box"><label for="box1">Food <span class="box-number">1&nbsp;</span></label><input type="text" name="terms[]" value="" placeholder="apples" id="box1" />&nbsp;<a class="add-box" href="#">Add More</a></p>
            <p><input type="submit" value="Submit" /></p>
        </form>

The form has dynamic form fields which users can add via javascript, and they can add up to 20 text inputs, in which all inputted terms get added to the $terms[] array.

So, if I test it, use two fields, and input "apples" and "bananas", the results show all the data for "apples" repeated twice.

Answer 1

Check this, you can check a related question here: Using OR in LIKE Query in MySQL to compare multiple fields

You might have to do something like this in your query.

$sql = 'SELECT * from wpqr_foods WHERE name LIKE "%'. $term .'%" OR addinfo LIKE "%'. $term .'%" ORDER BY name ASC';

OR,

$sql = 'SELECT * FROM `wpqr_foods` WHERE CONCAT(name, addinfo ) LIKE "%'. $term .'%" ORDER BY name ASC';

Answer 2

I would recommend the following two ways forward:

1. It looks like the code block that queries the database and pushes results into $results is duplicated. You probably want to get rid of the duped block as that will create 2x results for each distinct term.
2. I suspect what's actually getting passed to the server through the form is a string containing whitespace-separated (or commas, or whichever separator the user chose to enter) terms. Even though you've chosen to pass it (and receive it server-side) as an array, it is actually a string and you will have to explode it (or some other method) yourself into an array. To check if that's the case (and if you haven't do so already), somewhere inside the foreach ($terms as $term) loop you should inspect $term to see if it is what you expect it to be.