Problems with my first prepared MySQLi

Question

Following a question earlier about sanitising a string, I'm now attempting to use the principles seen at How can I prevent SQL injection in PHP?

$connection = mysqli_connect('localhost', 'user', 'xxxxx');
$database = mysqli_select_db($connection, 'xxxxx');

$param1 = $_GET['q'];

//prepared mysqli statement
$stmt = mysqli_stmt_init($connection);    
$stmt = $connection->prepare('SELECT * FROM CONTACTS WHERE SURNAME = ?');
$stmt->bind_param('s', $param1); // 's' specifies the variable type => 'string'

$stmt->execute();
$result = $stmt->get_result();
$num_rows = mysqli_num_rows($result);

echo "Records Found:".$num_rows."<br/><br/><hr/>";

while ($row = $result->fetch_assoc()) {
   echo $result['COMPANY']." ".$result['FORENAME']." ".$result['SURNAME'];
}

However, although $connection and $database are both processing correctly, I'm getting the following error:

Fatal error: Call to undefined method mysqli_stmt::get_result() in /my_first_mysqli.php on line xxxx

Am I not getting the syntax correct or does it have more to do with the php version 5.2.0 I'm rocking. (Yes, I'm upgrading code before upgrading server).

If it's the latter, is there a simpler MySQLi method I can use that will work before I upgrade the php version?

EDIT

---

I've updated this now which is a bit cleaner:

 $servername = "localhost"; $username = "xxxx"; $password = "xxxx"; $dbname = "xxxx";

 // Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

 // Check connection
 if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$param1 = $_GET['q'];

$stmt = mysqli_prepare($conn, "SELECT CONTACTID, COMPANY, FORENAME, SURNAME FROM CONTACTS WHERE SURNAME = ?");

/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "s", $param1);

/* execute query */
mysqli_stmt_execute($stmt);

/* bind result variables */
mysqli_stmt_bind_result($stmt, $CONTACTID, $COMPANY, $FORENAME, $SURNAME);

/* fetch value */
mysqli_stmt_fetch($stmt);

$num_rows = mysqli_num_rows($stmt);
echo "Records Found:".$num_rows."<br/><br/><hr/>";


/* close statement */
mysqli_stmt_close($stmt);


mysqli_close ($conn);

I'm obviously not getting a recordset result to loop through and don't know how to... The rest appears to work without throwing an error.

Answer 1

Thanks for all the contributions. I now have a working procedural solution that I thought I'd post for reference.

It's a bit cumbersome but it's fine and I believe it follows good modern practice.

$servername = "localhost"; $username = "xxxx"; $password = "xxxx"; $dbname = "xxxx";
$conn = new mysqli($servername, $username, $password, $dbname);// Create connection

// Check connection
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
}

$param1 = $_GET['q'];

$stmt = mysqli_prepare($conn, "SELECT CONTACTID, COMPANY, FORENAME, SURNAME FROM CONTACTS WHERE SURNAME = ?");
mysqli_stmt_bind_param($stmt, "s", $param1);// bind parameters for markers
mysqli_stmt_execute($stmt);// execute query
mysqli_stmt_bind_result($stmt, $CONTACTID, $COMPANY, $FORENAME, $SURNAME);// bind result variables

// fetch values
while (mysqli_stmt_fetch($stmt)) {
   echo $CONTACTID."<br>";
   echo $COMPANY."<br>";
   echo $FORENAME."<br>";
   echo $SURNAME."<br>";
   echo "<hr/>";
}

mysqli_stmt_close($stmt);// close statement

mysqli_close ($conn);

Feedback welcome if you can see any improvements.