CODEWITHSUNDEEP
phpmysql
sam sexton
sam sexton

Reputation: 193

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

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?

Upvotes: 0

Views: 3007

Answers (3)

Emre Rothzerg
Emre Rothzerg

Reputation: 289

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

    }
}

Upvotes: 0

Dharman
Dharman

Reputation: 33237

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.

Upvotes: 1

GMB
GMB

Reputation: 222472

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

Upvotes: 0

Related Questions