CODEWITHSUNDEEP
phpprepared-statement
Silly Question Boy
Silly Question Boy

Reputation: 49

Using prepared statements in functions

Hi I'm trying to use prepared statements within functions. I have the following function which is supposed to return the details of a country in a DB based on inputting the ID of the country - I then use an array to get the company name after. I don't know how to output the data to be used in the array from the function when I use prepared statements. I know I'm missing something basic. Please see below.

function findCountryname($countryID){
include 'connect.php';

$stmt = $conn->prepare("SELECT * FROM countries WHERE id=? and 
pic!='NULL'");
$stmt->bind_param("i", $countryID);
$stmt->execute();
}

The 'connect.php' file consists of the following:

<?php  
global $conn, $dbname, $username,$servername, $password;
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "country";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>

This was the original function:

function findCountryname($countryID){
$result = mysql_query("SELECT * FROM `countries` WHERE `id`=' $countryID '    
and `pic` != 'nothing' ");
return $result;
}

Upvotes: 0

Views: 1907

Answers (2)

Akshay
Akshay

Reputation: 2229

You can use mysqli's fetch_assoc to fetch the resulting data from the query and store it in an array, then return that array.

So your function now becomes :-

function findCountryname($countryID){
include 'connect.php';

$stmt = $conn->prepare("SELECT * FROM countries WHERE id=? and 
pic!='NULL'");
$stmt->bind_param("i", $countryID);
$stmt->execute();
$data = $stmt->fetch_assoc();
return $data;
}

Upvotes: 0

Devon Bessemer
Devon Bessemer

Reputation: 35337

There is no reason to use globals or an include inside a function. If you insist on procedural code, you should inject the mysqli object into the function as an argument.

After the statement execution, you need to retrieve the mysqli result object for manipulation. If you're just getting the first row, the below example will work. If you expect multiple rows, you will have to call fetch_assoc in a loop as it will only retrieve one row at a time.

function findCountryname($countryID, mysqli $conn) {

    $stmt = $conn->prepare("SELECT * FROM countries WHERE id=? and pic!='NULL'");
    $stmt->bind_param("i", $countryID);
    $stmt->execute();
    // Get the mysqli result object
    $result = $stmt->get_result();
    // Return the first row of data in an associative array
    $data = $result->fetch_assoc()
    return $data;

}

Upvotes: 1

Related Questions