Foreach in a prepared statement

PHP
1

I am tying to use a foreach loop inside a prepared statement…and that because I have to work with an array…here is the code…shortened for brevity.

 foreach ($favorites as $key => $value) {
             if($stmt = $connection->prepare('SELECT servicename FROM services_list,busers_services
                    WHERE services_list.serviceID=busers_services.serviceID
                    AND b_user=?'))
                    {
                  ....
                 ....
                           if($stmt->num_rows > 0)
                          {
                         $stmt->bind_result($servicename);
                         while ($stmt->fetch()) 
                                  {
                                        $services[]=['servicename'=>$servicename];
                                  }

                      }                     
                  .....
                   }`

The code you see above produces a multidimensional array…(it retrieves from the db the services associated with a business…a hair salon for example might offer a haircut and a maniciur)

And here is my problem,this multidimensional array must have as a key(in the first dimension) an integer which identifies the business user and which is found inside the favorites array and can be accessed with value[‘id’]…if I pass this to the array like this though:

$services[value[id]]=['servicename'=>$servicename];

overwriting will take place…How I can avoid this:
And get something like this:

[4]=> array(1) { ["servicename"]=>  "haircut" } [1]=> array(1) { ["servicename"]=> "maniciur" }

Above… value[id] has the value of 4…as you might guessed.

2

  1. preparing the statement does not belong inside the loop. neither does parameter binding.

  2. there is no evidence in the code that you execute the statement (though getting a result indicates that)

  3. if you were using PDO that would only be a matter of selecting the right fetch mode.

  4. $services[$value[id]][] = ['servicename'=>$servicename];

3

never say never :grinning:

public function create(array $records = NULL, $created_by = NULL, $urlDate, $page) {
$this->records = $records;
if (is_array($this->records)) {
$db = Database::getInstance();
$pdo = $db->getConnection();

    $this->sql = 'INSERT INTO calendar (created_by, security_level, date, time, name, info) VALUES ';

    foreach ($this->records as $this->record) {
        $this->query[] = '(:created_by' . $this->n . ', :security_level' . $this->n . ', :date' . $this->n . ', :time' . $this->n . ', :name' . $this->n . ', :info' . $this->n . ')';

        $this->record = (object) $this->record;
        $this->iData['created_by' . $this->n] = $created_by;
        $this->iData['security_level' . $this->n] = 'sysop';
        $this->iData['date' . $this->n] = $this->record->date;
        $this->iData['time' . $this->n] = $this->record->time;
        $this->iData['name' . $this->n] = $this->record->name;
        $this->iData['info' . $this->n] = trim($this->record->info);

        $this->n += 1;
    }

    if (!empty($this->query)) {
        $this->sql .= implode(', ', $this->query);
        $this->stmt = $pdo->prepare($this->sql);
        $this->result = $this->stmt->execute($this->iData);
    }
}
header("Location: calendar.php?urlDate={$urlDate}&page={$page}");
exit();

}

I just want to say I agree with you, but it can be done. :wink:

4

this code doesn’t look like it belongs here.

5

…exactly

6

I agree. The prepare statement NEVER belongs in a loop. The main point in using prepare is that you run it only once and so save having to build the entire query every time.

7

1…got that

2.I omitted some code for brevity…here is it all of it:code

3.But I don’t

4.Yes that is the solution…

Nonetheless…there is something left to see(a test to make)…so I am not closing the topic yet

8

I don’t think this is the best approach. You could just construct the snippet properly using the proper procedures, select all rows (optional can be included with a WHERE clause), loop your data in the while loop and for the final step, create a multidimensional array and append the data appropriately with appropriate keys.

I have never seen a prepared statement being nested inside a foreach loop before. It seems very repetitive if you’d ask me because you’ll be looping multiple times depending on how many values are in the array. You should only need to prepare and do a database call once. Your data should then be handled accordingly.

9

The whole point of prepare is to allow you to prepare once and then do multiple database calls binding different data for each.

1 Like
10

Today I updated an Ajax Live Search Routine from Php Mysqli to PDO:

The source of the SQL statement is from a form’s input text field that uses Ajax to pass the content to a PHP routine to display results.

Complete source code is available in the Online Tutorial

//=================================
//
//  Validate and clean input text, return array
//    
//=================================
function getParams( string $params=NULL)
:array
{
  $result = NULL;

  while (strpos($params, '  ') ) {
    $params = str_replace('  ',  ' ', $params);
  }
  $result = explode(' ', $params);

  if( empty($result) ):
    $result[] = $params;
  endif;

  return $result;
}//


//=================================
//
// dynamic build SQL statement
//
//=================================
function getSqlPdo(array $aParams=NULL)
:string
{
  $sql = "SELECT title, memo, xrl FROM jokes WHERE concat(title,memo)"; 

  foreach($aParams as $i2 => $param):
    if($i2==0):
      $sql = $sql .' LIKE ? ';
    else:  
      $sql = $sql .' AND concat(title,memo) LIKE ? ';
    endif;  
  endforeach;  
  
  return $sql;
}//

Above functions are called here:


  $db = getPdoConnection();
  if($db):

    // if and ONLY if NO JavaScript is selected
    if( isset($_POST['submit']) ): 
      $aParams = getParams( $_POST['search'] );

    else:
      $search  = isset($_GET['q']) ? $_GET['q'] : '';
      $aParams = getParams($search);
    endif;
    $sql = getSqlPdo($aParams);

    try {
        $result   = $db->prepare($sql);
        $aParamsX = [];
          foreach($aParams as $param):
            $aParamsX[] = '%' .$param .'%';
          endforeach;
        $result->execute($aParamsX);
        $rowTot = $result->rowCount();

        echo '<b class="fs2">Total Jokes: ' .$rowTot .'</b>'; 
        if(12 <= $rowTot):
          echo '<i class="flr fss clg">but only showing the first dozen</i>';
          $sqlXXX = $sql .' LIMIT 0,12;';
          $result = $db->prepare($sqlXXX);
          $result->execute($aParamsX);
        endif;
        echo '<hr class="hr1" />';

        echo '<dl>';
          foreach($result as $row):
            $link = '<a href="http://www.johns-jokes.com/' .$row['xrl'] .'">' .$row['title'] .'</a>';
            $joke = substr( strip_tags( $row['memo']),0,88) ;
            $joke = empty( trim($joke) ) ? '<b>Has Funny Picture</b>' : $joke;

            echo '<dt>' .$link .'</dt>';
              echo '<dd>' .$joke . '...</dd>';
          endforeach;          
        echo "</dl>";
        // $db = null; // Unnecessary
    }catch (PDOException $e){
      print "Error!: " . $e->getMessage() . "<br/>";
      die();
    }
  endif;# $conn  
  # pdo_close($con); // Not required ???
11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.