We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of

Hello, I'm knew to coding in php and need some help cause I can't fix the problem.

I'm trying to read the httpd-vhosts.conf and compare some entries with the table vhosts in my db tst_vhost. If I a find an entry I'll do nothing, if the entry doesn't already exist I want to insert a new entry. so far so good, the list works fine but the search within the db makes problems. the connection to the db is saved in a file named j_conn.inc.php and contains the following code:

<?php
// Zugangsdaten zur Datenbank
$DB_HOST = "localhost"; // Host-Adresse
$DB_NAME = "tst_vhost"; // Datenbankname
$DB_BENUTZER = "root"; // Benutzername
$DB_PASSWORT = ""; // Passwort

// Zeichenkodierung UTF-8 bei der Verbindung setzen (Optional)
// Infos: www.webbausteine.de/tipps/schriftzeichen_richtig_darstellen.php
$OPTION = [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"];

try {
 // Verbindung zur Datenbank aufbauen
 $verbindung = new PDO("mysql:host=" . $DB_HOST . ";dbname=" . $DB_NAME,
  $DB_BENUTZER, $DB_PASSWORT, $OPTION);
}
catch (PDOException $e) {
 // Bei einer fehlerhaften Verbindung eine Nachricht ausgeben
 exit("Verbindung fehlgeschlagen! " . $e->getMessage());
}
?>

The code to search for looks like:

<?php
$server_dir = "../../";
$configurationFile = '../../wampmanager.conf';

$ar_info = array();
$ar_info['Info'] = "";
$ar_info['DocRoot'] = "";
$ar_info['ServerName'] = "";
$ar_info['ServerAlias'] = "";

require $server_dir.'scripts/wampserver.lib.php';
require "includes/j_conn.inc.php";
// Loading Wampserver configuration

$wampConf = @parse_ini_file($configurationFile);
$c_apacheVersionDir = $wampConf['installDir'].'/bin/apache';

$c_apacheVhostConfFile = $c_apacheVersionDir.'/apache'.$wampConf['apacheVersion'].'/'.$wampConf['apacheConfDir'].'/extra/httpd-vhosts.conf';

$pageContents = <<<MPAGE
   <!DOCTYPE html>
   <html>
   <head>
      <title>Übersicht</title>
      <meta charset="UTF-8">
      <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
      <meta name="viewport" content="width=device-width">
      <link id="stylecall" rel="stylesheet" href="wampthemes/classic/style.css" />
      <link rel="shortcut icon" href="index.php?img=favicon" type="image/ico" />
   </head>

   <body>
MPAGE;
echo '<p>Die Datei httpd-vhost.conf unter ' . $c_apacheVhostConfFile . ' wird ausgelesen und mit der Datenbank ' . $DB_NAME . ' abgeglichen. Sind in der DB Datensätze nicht vorhanden, werden diese übernommen und können danach bearbeitet bzw. ergänzt werden</br></p>';
// Datei zeilenweise auslesen PHP 7
  $file = fopen($c_apacheVhostConfFile, 'r');
  if ($file) {
    while(!feof($file)) {
      $line = fgets($file);
      // Zeile nach Informationen durchsuchen
      $pos = strpos($line, "Info: ");
         if ($pos > 0 ) {
            $l = strlen($line);
            // echo $pos . " - " . $line . ' - Zeilenlänge: ' . $l . ' Zeichen<br>';

            $ar_info['Info'] = substr($line, $pos+6, $l - ($pos + 8));
            // echo $ar_info['Info'] . '<br>';
         }
         if (!empty($ar_info['Info'])) {
            $pos = strpos($line, "DocumentRoot");
            if ($pos > 0) {
               $l = strlen($line);
               $ar_info['DocRoot'] = substr($line, $pos+13, $l - ($pos + 13));
               // echo $ar_info['DocRoot'] . '<br>';
            }
            $pos = strpos($line, "ServerName");
            if ($pos > 0) {
               $l = strlen($line);
               $ar_info['ServerName'] = substr($line, $pos+11, $l - ($pos + 11));
               // echo $ar_info['ServerName'] . '<br>';
            }
            $pos = strpos($line, "ServerAlias");
            if ($pos >0) {
               $l = strlen($line);
               $ar_info['ServerAlias'] = substr($line, $pos+12, $l - ($pos + 12));
               // echo $ar_info['ServerAlias'] . '<br>';
            }
         }
         if (!empty($ar_info['ServerAlias'])) {
         echo 'Info: ' . $ar_info['Info'] . ' - DocumentRoot: ' . $ar_info['DocRoot'] . ' - ServerName: ' . $ar_info['ServerName'] . ' - ServerAlias: ' . $ar_info['ServerAlias'] . '<br>';

         // In der DB nachsehen, ob es schon einen entsprechenden Datensatz gibt
         $suchbegriff = trim($ar_info['ServerName']);
         echo "<p>Gesucht wird nach: <b>$suchbegriff</b></p>";
         $suche_nach = '%'.$suchbegriff.'%';
         $suche = $verbindung->prepare("SELECT v_id, v_info, v_root, v_server, v_alias, v_icon_title, v_icon_path, v_create, v_change, v_dev
                      FROM vhosts
                      WHERE v_server LIKE ? OR v_alias LIKE ? OR v_info LIKE ?");
         $suche->bindParam('s', $suche_nach);
         $suche->execute();
         $suche->bind_result($v_id, $v_info, $v_root, $v_server, $v_alias, $v_icon_title, $v_icon_path, $v_create, $v_change, $v_dev);
         while ($suche->fetch()) {
             $daten[] = (object) array('v_id' => $v_id, 
                              'v_info'   => $v_info, 
                              'v_root'  => $v_root, 
                              'v_server' => $v_server, 
                              'v_alias'  => $v_alias,
                              'v_icon_title' => $v_icon_title,
                              'v_icon_path' => $v_icon_path,
                              'v_create' => $v_create,
                              'v_change' => $v_change,
                              'v_dev' => $v_dev);
         }
         echo count($daten);
         exit;
         //echo 'Suche nach: ' . $ar_info['ServerName'] . ' findet ' . $daten[];
         if (empty($sql)){
            $statement = $verbindung->prepare("INSERT INTO `vhosts` (`v_info`, `v_root`,`v_server`,`v_alias`) VALUES (:Info, :DocRoot, :ServerName, :ServerAlias)");
            $statement->execute($ar_info);   
         }

         $ar_info['Info'] = "";
         $ar_info['DocRoot'] = "";
         $ar_info['ServerName'] = "";
         $ar_info['ServerAlias'] = "";
         }
    }
    fclose($file);
  } else {
    echo 'The file '.$c_apacheVhostConfFile.' could not be opened for reading';
  }
?>

Hope someone can point me to the error and how to resolve it.

Best regards Franz-Georg



77.7k
Accepted
answer

You are binding a named parameter, but using unnamed placeholders in the query:

$suche = $verbindung->prepare("SELECT v_id, v_info, v_root, v_server, v_alias, v_icon_title, v_icon_path, v_create, v_change, v_dev
                      FROM vhosts
                      WHERE v_server LIKE ? OR v_alias LIKE ? OR v_info LIKE ?");
         $suche->bindParam('s', $suche_nach);
         $suche->execute();

This should fix it:

$suche = $verbindung->prepare("SELECT v_id, v_info, v_root, v_server, v_alias, v_icon_title, v_icon_path, v_create, v_change, v_dev
                      FROM vhosts
                      WHERE v_server LIKE :s OR v_alias LIKE :s OR v_info LIKE :s");
         $suche->bindParam('s', $suche_nach);
         $suche->execute();

(This is not related to Phalcon by the way, it's an "issue" with PDO binding. Don't forget that Phalcon uses a different syntax for placeholders: :paramName: and ?0 respectively)

Thanks a lot, that did it for me.