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.

It looks like you're hitting the database twice and pushing result sets onto $results twice through each iteration. Is that intentional? — rchang
– rchang, Commented Dec 24, 2014 at 20:21
Try to avoid queries in a loop, for the love of efficient working. — Tosfera
– Tosfera, Commented Dec 24, 2014 at 20:37
There are 2 SELECT statements you might be getting the same rows for apples twice. Which i don't think is wrong, so that part of your script is functioning correctly i reckon. Do you have any rows for bananas inside your DB? Could you just go to the DB and see if there are rows present for Bananas? — Ashish
– Ashish, Commented Dec 24, 2014 at 20:37
yes there are rows for bananas. See the test here (enter password hello) abyssinianguineapigtips.com/vitamin-c-calculator If it's a fruit or a vegetable, it's in there. — Michele
– Michele, Commented Dec 24, 2014 at 20:46

Community · Accepted Answer · 2017-05-23 11:49:28Z

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

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Dec 24, 2014 at 22:01

Ashish

6,8092 gold badges20 silver badges18 bronze badges

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

Comments

rchang · Accepted Answer · 2014-12-24 20:39:20Z

0

I would recommend the following two ways forward:

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.
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.

answered Dec 24, 2014 at 20:39

rchang

5,2441 gold badge18 silver badges26 bronze badges

2 Comments

Michele Over a year ago

You are correct. When I echo $term I only get the first result.

Michele Over a year ago

Thank you. When I remove the second $sql= SELECT * from....$Q2=mysql_query($sql).... section, it works as it should. Wow. Thanks again.

Collectives™ on Stack Overflow

php foreach loop search mysql table with several words

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related