CODEWITHSUNDEEP
phpmysqlpdofetch
Gijs
Gijs

Reputation: 885

PDO fetch multiple records from database

I am writing a pretty basic piece of code to fetch one or (in most cases) multiple rows from a mysql database. 

function getschema($mysqli){
    $id = $_SESSION['user_id'];
    $query = $mysqli->prepare("SELECT a.naam
                            FROM schemes AS a, aankoop AS b
                            WHERE b.aankoop_username_id = :userid && b.aankoop_schema_id = a.id");
    $query->bind_param(':userid', $id, PDO::PARAM_INT);
    $query->execute();
    $result = $query->fetchAll();
    echo ($result);
}

I get the user id from the session and pull the data with the query in the prepared statement. This statement is correct. I tried it in phpmyadmin and it returns the correct values.

Now I want to use this function in my HTML like so...

<?php echo getschema($mysqli); ?>

But my code does not return a thing, it even messes up the layout of my html page where I want to show the code.

I think it probably is something with the fetchAll command. I also tried the PDO::Fetch_ASSOC but that did not work either.

In addition, I cannot see the php errors, even when they are enabled in the php.ini file.

Upvotes: 1

Views: 937

Answers (2)

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

Here's what's going on; you're mixing MySQL APIs/functions and those do not intermix.

Replace the :userid (PDO) bind in b.aankoop_username_id = :userid with a ? placeholder 

b.aankoop_username_id = ?

Then this line:

$query->bind_param(':userid', $id, PDO::PARAM_INT);

Replace :userid by $id and remove , PDO::PARAM_INT but adding i

$query->bind_param("i", $id);

Sidenote: Make sure that column is int type. If not, use s instead of i.

Replace the line for fetchAll with the loop as outlined in AbraCadaver's answer.

Read up on mysqli with prepared statements and how it works:

Checking for errors would have outlined the errors.

Upvotes: 1

AbraCadaver
AbraCadaver

Reputation: 78994

Instead of echo ($result); do return $result; in your function.

Then to use it you have to loop over the array of rows and echo the column that you want:

foreach(getschema($mysqli) as $row) {
    echo $row['naam'];
}

Or assign the function return to a variable and loop over that:

$rows = getschema($mysqli);

Upvotes: 1

Related Questions