MySQL prepared statement from PDO prepared statement

There’s a script that prepares, binds and executes a prepared SQL statement using PDO, I tried to convert it to use MySQLi but am getting a Fatal error: Call to a member function bind_param()… on line 50.

<?php
// Username and password
require("dbcredentials.php");

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

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

// Test parameters
$lat=45.8152919;
$lng=15.981615;
$dst=20;

// Haversine SQL statement
$haversine = "
SELECT  id, name, address, lat, lng, phone,
        ( 3959 *
                acos(
                        COS(RADIANS(?)) *
                        COS(RADIANS(?)) * 
                        COS(RADIANS(lat)) * 
                        COS(RADIANS(lng)) 
                        +
                        COS(RADIANS(?)) * 
                        SIN(RADIANS(?)) * 
                        COS(RADIANS(lat)) * 
                        SIN(RADIANS(lng)) 
                        + 
                        SIN(RADIANS(?)) * 
                        SIN(RADIANS(lat))
                    )
                ) AS distance 
FROM markers
HAVING distance <= ?
ORDER BY distance
LIMIT 0 , 20;
";


// Prepare
$stmt = $conn->prepare($haversine);

// Bind parameters
$stmt->bind_param("iiiiii", $lat, $lng, $lat, $lng, $lat, $dst);

// Execute stmt
$stmt->execute();

$conn->close();
?>

In your example, localhost should be quoted:

$conn = new mysqli("localhost", $username, $password, $dbname);

Also, why are you changing the script away from using PDO to use mysqli? I don’t see the benefit of doing that.

Because I’ve started learning MySQLi and not PDO. Nevertheless, the following PDO version gives me a Fatal error: Call to undefined method PDO::execute()

<?php
// Username and password
require("dbcredentials.php");

// Create connection
try {
    $conn = new PDO("mysql:host=localhost;dbname=$database", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 

// Test parameters
$lat=45.8152919;
$lng=15.981615;
$dst=20;

// Haversine SQL statement
$haversine = "
SELECT  id, name, address, lat, lng, phone,
        ( 3959 *
                acos(
                        COS(RADIANS(?)) *
                        COS(RADIANS(?)) * 
                        COS(RADIANS(lat)) * 
                        COS(RADIANS(lng)) 
                        +
                        COS(RADIANS(?)) * 
                        SIN(RADIANS(?)) * 
                        COS(RADIANS(lat)) * 
                        SIN(RADIANS(lng)) 
                        + 
                        SIN(RADIANS(?)) * 
                        SIN(RADIANS(lat))
                    )
            ) AS distance 
FROM markers
HAVING distance <= ?
ORDER BY distance
LIMIT 0 , 20;
";

// prepare PDO query
$statement = $conn->prepare($haversine);

// bind parameters
$statement->bindValue(1, $lat, PDO::PARAM_STR);
$statement->bindValue(2, $lng, PDO::PARAM_STR);
$statement->bindValue(3, $lat, PDO::PARAM_STR);
$statement->bindValue(4, $lng, PDO::PARAM_STR);
$statement->bindValue(5, $lat, PDO::PARAM_STR);
$statement->bindValue(6, $dst, PDO::PARAM_INT);

// execute query
$conn->execute();



    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?>

The same error regardless if I use PARAM_STR or PARAM_INT for $lat and $lng

execute should be run against the $statement object rather than $conn:

$statement->execute();

I thought of that but assumed the github script worked so didn’t bother to try, yup that was the problem. Thank you.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.