PDO fetch multiple records from database

Question

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.

@AbraCadaver, do you mean asign it as a value to a textarea or do you mean something else? I know it will be an array so i have to loop through it in my html page? — Gijs
– Gijs, Commented Jul 30, 2015 at 21:31
Look at your code very carefully; what doesn't belong together? Checking for errors would have told you that. Plus, which API are you using to connect with in the first place? Plus, you're running this inside a custom function. — Funk Forty Niner
– Funk Forty Niner, Commented Jul 30, 2015 at 23:00
@Fred-ii-, the session has started, i'm able to echo it's contents on a test page. Further i'm using the mysqli api to connect with the database and I finaly was able to print out the error's and the one i'm getting is Call to undefined function getschema() when i call the function on my test page: foreach(getschema($mysqli) as $row) { echo $row['naam'];}. Also i made a small change to the query: && became AND, not that this matters in the execution i guess. — Gijs
– Gijs, Commented Jul 30, 2015 at 23:24

Community · Accepted Answer · 2017-05-23 11:58:19Z

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.

You can't mix MySQL APIs/function, read the following on Stack:
Can I mix MySQL APIs in PHP?

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

http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

Checking for errors would have outlined the errors.

Thank you, i'm finaly able to do a var dump from my array. Thank you for the help!

AbraCadaver · Accepted Answer · 2015-07-30 21:31:22Z

1

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);

answered Jul 30, 2015 at 21:31

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

5 Comments

Gijs Over a year ago

i'm trying it with the foreach solution you provided. But for some reason, my layout is ruined that way. I put this output in a javascript tab, but i cannot open it anymore when i put the code in.

Gijs Over a year ago

when I make a new php file and include all the necessary config file. I call the getschema function with the code you provided but i can't get any output lines on the screen. Do you maby know what this causes? I think my pdo statement is correct but i don't know for sure.

Funk Forty Niner Over a year ago

There's a problem with OP's code and it looks a bit like rum & coke. See what they're doing? They're using the wrong ingredients ;-)

AbraCadaver Over a year ago

@Fred-ii-: Well I'll be damned.

Funk Forty Niner Over a year ago

@AbraCadaver Yeah. I stumbled on the question earlier and thought you may haven't been around, so I thought I'd put in an addition to yours.

Collectives™ on Stack Overflow

PDO fetch multiple records from database

2 Answers 2

1 Comment

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Linked

Related