Why PHP Mysql query inside a foreach loop always returns the first result from database?

Question

I'm trying to run a MYSQL query inside a foreach loop.

here's the scenario:

I have a comma separated string with some names in it.

I use explode() and foreach() to get the separate values/names from this comma separated string.

Then I need to search mysql database for each of these values/names that I get from this string and if that value exists in the database, I then get its ID and create a new recrord in another table in the database.

However, when I run my code, I only get the ID of the first instance from the comma separated string.

my mysql database looks like this:

id      category_name 
3         Hotel
4         Restaurants

This is my code:

    //My comma separated string///
    $biz_cat = 'Hotel, Restaurants';


   ///i do the explode and foreach here///
    $arrs = explode(',', $biz_cat);

    foreach($arrs as $arr){

    $sql99 = "SELECT * FROM categories WHERE category_name='$arr'";
    $query99 = mysqli_query($db_conx, $sql99);
    while($row99 = mysqli_fetch_array($query99, MYSQLI_ASSOC)){

    $catIDS = $row99['id'];

   }

  //this is where i need to insert my new data in different tabel.
  echo $catIDS.'<br>;

   }

so when the i run my code, I get the ID of the Hotel twice like so:

3
3

I'm expecting it to be like below based on what I have in MYSQL:

3
4

Could someone please advice on this issue?

You overwrite that value each time. You need to put that scho statement in the loop or keep track of those values a different way. — John Conde
– John Conde, Commented Oct 6, 2019 at 20:34
@JohnConde, if you mean something like this: $variable .= $CatIDS; then that is not usefull for me. in my real world code, I have another INSERT query instead of that echo statement. — sam sexton
– sam sexton, Commented Oct 6, 2019 at 20:37
You should be getting an parse error for the echo. Bad paste was it? It's missing a quote. — Funk Forty Niner
– Funk Forty Niner, Commented Oct 6, 2019 at 20:45

Dharman · Accepted Answer · 2019-10-06 21:04:22Z

First of all such things should be done using prepared statements. Not only it is easier and faster, but also more secure. Remember to always use prepared statements.

//My comma separated string///
$biz_cat = 'Hotel, Restaurants';

$stmt = $db_conx->prepare('SELECT * FROM categories WHERE category_name=?');
$stmt->bind_param('s', $cat);

foreach(explode(',', $biz_cat) as $cat){
    $cat = trim($cat); // remove extra spaces at the beginning/end
    $stmt->execute();
    // we fetch a single row, but if you expect multiple rows for each category name, then you should loop on the $stmt->get_result()
    $row99 = $stmt->get_result()->fetch_assoc();

    // echo it in the loop or save it in the array for later use
    echo $row99['id'];
}

In the example here I prepare a statement and bind a variable $cat. I then explode the string into an array on which I loop straight away. In each iteration I execute my statement, which in turn produces a result. Since you seem to be interested only in the first row returned, we do not need to loop on the result, we can ask for the array immediately. If you would like to loop just replace

$row99 = $stmt->get_result()->fetch_assoc();

with

foreach($stmt->get_result() as $row99) {
    echo $row99['id'];
}

Once you get the id in the array, you can either print it out or save it into an array for later use.

GMB · Accepted Answer · 2019-10-06 20:53:42Z

0

As of now, you are re-assigning a new value to scalar variable $catIDS for each record returned by the query, then you echo it one you are done looping. You would need to put the echo/insert logic inside the loop (or maybe store the values in array).

Another thing to note is that you are splitting with , (a single comma), but you have a space between the two words. As a result, the second value (Restaurant) starts with a space, which will cause the query to return an empty resultset. You probably want to split with , (a comma followed by a space).

$biz_cat = 'Hotel, Restaurants';
$arrs = explode(', ', $biz_cat);
foreach($arrs as $arr){    
    $sql99 = "SELECT * FROM categories WHERE category_name='$arr'";
    $query99 = mysqli_query($db_conx, $sql99);
    while($row99 = mysqli_fetch_array($query99, MYSQLI_ASSOC)){            
        $catIDS = $row99['id'];
        //this is where i need to insert my new data in different tabel.
        echo $catIDS.'<br>';    
    }
}

edited Oct 6, 2019 at 20:53

answered Oct 6, 2019 at 20:37

GMB

224k25 gold badges103 silver badges151 bronze badges

7 Comments

sam sexton Over a year ago

I have already tried that too. still only inserts the ID from the first name in the comma separated string.

GMB Over a year ago

@samsexton: if you run this code with the echo, you will see that you will get a different category ID for each iteration of the loop. Give it a try.

sam sexton Over a year ago

with your code, echo $catIDS.'<br>; only returns 3 once.

GMB Over a year ago

There is also an issue with the additional space after the comma. See my updated answer.

sam sexton Over a year ago

@GMB, Yes, the issue was only with the spaces in the string.

|

Emre Rothzerg · Accepted Answer · 2019-10-07 03:03:49Z

0

The code below can do what you need.

Update INSERT YOUR NEW DATA HERE

$biz_cat = 'Hotel, Restaurants';
$arrs = explode(',', $biz_cat);
foreach ($arrs as $arr) {
    $query99 = mysqli_query($db_conx, "SELECT * FROM categories WHERE category_name='$arr'");
    while ($row99 = mysqli_fetch_array($query99, MYSQLI_ASSOC)) {

        $catIDS = $row99['id'];

        // INSERT YOUR NEW DATA HERE
        echo $catIDS . '<br/>';

    }
}

answered Oct 7, 2019 at 3:03

Emre Rothzerg

2891 silver badge5 bronze badges

Collectives™ on Stack Overflow

Why PHP Mysql query inside a foreach loop always returns the first result from database?

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related