Foreach in a prepared statement

[John_Betong]

@designtrooper

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:

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