How to use multiple ? in prepared statement in PHP

Question

I want to run a select statement with PHP using multiple '?'. How do I do that? I think the following code should do that, what is wrong?

$con = mysql_connect("database","login","password");
if (!$con){
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("posdictionary", $con);

$stmt = $con->prepare("SELECT * FROM WordPOS WHERE WORD LIKE '?%' AND pos LIKE BINARY '%?%'");
$stmt->bind_param('ss', $pattern, $pos);
$pattern = $_POST["pattern"];
$pos = $_POST["pos"];

$value = "";
if ($stmt->execute()) {
    while ($row = $stmt->fetch()) {
        $value = $value . $row['word'] . " " . $row['pos'] . "<br />";
    }
}

UPDATE:

The answers below are great. I had an error in my PHP the '?%' is not legal in php sql statement. The query should look like this:

  $pattern = $_POST["pattern"] ."%";
  $pos = "%". $_POST["pos"] ."%";
  $stmt = $con->prepare("SELECT word, pos FROM WordPOS WHERE word LIKE ? AND pos LIKE BINARY ?");

Then follow the rest of the answers.

I cannot use mysqli because Dreamhost uses mysql, and does not support the mysqli commands. — Whitecat, Dec 20 '12 at 02:35
@Whitecat [This seems to suggest](http://wiki.dreamhost.com/Talk:Installing_PHP5) that Dreamhost's PHP5 does support MySQLi. — Michael Berkowski, Dec 20 '12 at 14:18
Thank you for your help. I got the prepared statement working wonderfully now! — Whitecat, Dec 21 '12 at 21:37

Michael Berkowski · Accepted Answer · 2012-12-20T02:13:22.797

Connect with new MySQLi() instead of mysql_connect(), which is not compatible with MySQLi API.

$con = new MySQLi("database","login","password","posdictionary");

Your prepare() and execute() calls are correct, with your two parameters correctly bound. However, your fetch() isn't going to work because MySQLi expects result columns to be bound in variables via bind_result(), not into an array like $row = $stmt->fetch()) as would be done with the old mysql_*() API or PDO.

  $pattern = $_POST["pattern"];
  $pos = $_POST["pos"];

  // Substituting explicit columns in the SELECT list
  $stmt = $con->prepare("SELECT word, pos FROM WordPOS WHERE WORD LIKE '?%' AND pos LIKE BINARY '%?%'");
  $stmt->bind_param('ss', $pattern, $pos);

  // Bind result columns into vars $word, $pos
  $stmt->bind_result($word, $pos);

  // Then execute and fetch 
  $value = "";
  if ($stmt->execute()) {
  while ($row = $stmt->fetch()) {
    // Using the bound variables...
    $value .=  $word . " " . $pos . "<br />";
    }
  }

I am using mysql_connect. Should i change to use mysqli? are the interchangeable? — Whitecat, Dec 20 '12 at 02:12
@Whitecat Oh I didn't even notice that.... Yes, you need to be calling `new MySQLi()` — Michael Berkowski, Dec 20 '12 at 02:13
I copied your code into mine and it does not work still. The non prepared statement works though. I cannot add it below but I just have a mysql_query statement. — Whitecat, Dec 20 '12 at 02:27

score 2 · Answer 2 · answered Dec 20 '12 at 02:11

2

You are connecting using the deprecated mysql_* functions and then switching to mysqli syntax. That is not going to work, you need to change:

$con = mysql_connect("database","login","password");
 if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

  mysql_select_db("posdictionary", $con);

to:

$con = mysqli_connect("database","login","password");
 if (!$con)
  {
  die('Could not connect: ' . mysqli_error());
  }

  mysqli_select_db("posdictionary", $con);

answered Dec 20 '12 at 02:11

jeroen

91,079
21
114
132

@Whitecat no, you don't have an mysqli connection open so you cannot do anything in mysqli. – jeroen Dec 20 '12 at 02:15
I cannot use mysqli because Dreamhost uses mysql, and does not support the mysqli commands. – Whitecat Dec 20 '12 at 02:34
@Whitecat Then you would need to switch to all `mysql_` functions, switch to PDO or look for a different host. I would switch if I could not use PDO either as the `mysql_*` functions are deprecated and do not support prepared statements (insecure). By the way, I cannot imagine that a host does not support mysqli nor PDO. – jeroen Dec 20 '12 at 02:36
What is PDO and how do I find if it is supported? Where are examples of PDO? – Whitecat Dec 20 '12 at 02:41
@Whitecat Look for the PDO section when you run `phpinfo();`. You need to have driver support for mysql. – jeroen Dec 20 '12 at 02:43

score 0 · Answer 3 · answered Dec 20 '12 at 02:08

0

I will first assign, then bind

$pattern = $_POST["pattern"];
$pos = $_POST["pos"];
$stmt->bind_param('ss', $pattern, $pos);

answered Dec 20 '12 at 02:08

freedev

25,946
8
108
125

How to use multiple ? in prepared statement in PHP

3 Answers3