Prepare () vs query () mysqli

Question

I am trying to understand the difference between mysqli query () and prepare (). I have the code as shown below and I would like to get the same results from both. However, the prepare () command does not work as expected. What for?

  // this works

  if ($query = $this->db->query("SELECT html FROM static_pages WHERE page = 'cities'")) {

     $result = $query->fetch_row();
     echo $result[0];

  $query->close();

  }

  //this does not work
  //result is empty

     $cities = 'cities';

     $stmt = $this->db->prepare("SELECT html FROM static_pages WHERE page = ?");
     $stmt -> bind_param("s", $cities);
     $stmt->execute();
     $stmt->bind_result($result);
     $stmt->fetch();
     echo $result;
     $stmt->close();

     $this->db->close();

these are my server settings as requested:

OS

Vista 64bit / PHP version 5.2.9

Mysql

MysqlI support included

API Client Library version 5.0.51a

Client API header version 5.0.51a

MYSQLI_SOCKET / tmp / mysql.sock

Directive Local Value

mysqli.default_host no value no value

mysqli.default_port 3306 3306

mysqli.default_pw no value no value

mysqli.default_socket no value no value

mysqli.default_user no value no value

mysqli.max_links Unlimited Unlimited

mysqli.reconnect Off Off

Answer 1

$stmt->store_result(); $stmt->execute(); $stmt->bind_result($result);?

Answer 2

mysqli_ * . false error/errno mysqli mysqli_stmt . script (, , ).

<?php
$foo = new Foo;
$foo->init();
$foo->bar();
class Foo {
  public function bar() {
    $cities = 'cities';  
    $stmt = $this->db->prepare("SELECT html FROM soTest WHERE page = ?");
    if ( !$stmt ) {
      echo "prepare failed\n";
      echo "error: ", $this->db->error, "\n";
      return;
    }

    $rc = $stmt->bind_param("s", $cities);
    if ( !$rc ) {
      echo "bind_param failed\n";
      echo "error: ", $stmt->error, "\n";
      return;
    }

    $rc=$stmt->execute();
    if ( !$rc ) {
      echo "execute failed\n";
      echo "error: ", $stmt->error, "\n";
      return;
    }

    $rc = $stmt->bind_result($result);
    if ( !$rc ) {
      echo "bind_result failed\n";
      echo "error: ", $stmt->error, "\n";
      return;
    }

    $rc = $stmt->fetch();
    if ( !$rc ) {
      echo "no such record\n";
    }
    else {
      echo 'result: ', $result, "\n";
    }
    $stmt->close();
  }

  public function init() {
    $this->db = new mysqli('localhost', 'localonly', 'localonly', 'test');
    if ($this->db->connect_error) {
      die('connection failed: ' . $this->db->connect_error);
    }
    $rc = $this->db->query('CREATE TEMPORARY TABLE
      soTest (id int auto_increment, html varchar(16), page varchar(16),primary key(id))'
    );
    if ( !$rc ) {  die('error: '.$this->db->error); }
    $rc = $this->db->query("INSERT INTO soTest (html,page) VALUES ('htmlFoo','foo'),('htmlCities','cities')");
    if ( !$rc ) {  die('error: '.$this->db->error); }
  }
}

CWE-209: . script . , die().

Answer 3

Must echo $result;not beecho $results;