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, Jul 30 '15 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, Jul 30 '15 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, Jul 30 '15 at 23:24
*"i'm using the mysqli api to connect with"* - there are 3, which one? `mysqli_`? `mysql_`? PDO? Edit: Ok `mysqli_` got it. — Funk Forty Niner, Jul 30 '15 at 23:25
@Fred-ii-, i'm using mysqli_. This is my connection string: $mysqli = new mysqli(HOST, USER, PASSWORD, DATABASE); — Gijs, Jul 30 '15 at 23:26
well you're mixing them and you can't do that. It seems I'm the only one who spotted that. — Funk Forty Niner, Jul 30 '15 at 23:26
@Fred-ii-, could you point out to me where i'm mixing the pdo and mysqli statements? — Gijs, Jul 30 '15 at 23:28
`WHERE b.aankoop_username_id = :userid` and `bind_param(':userid', $id, PDO::PARAM_INT)` read up on it http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php — Funk Forty Niner, Jul 30 '15 at 23:28
oh and `fetchAll` too. I edited my comment above with a link to. That ought to get you started ;-) question should be closed as a duplicate with http://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php — Funk Forty Niner, Jul 30 '15 at 23:32

score 1 · Answer 1 · answered Jul 30 '15 at 21:31

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 '15 at 21:31

AbraCadaver

78,200
7
66
87

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 Jul 30 '15 at 21:39
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. – Gijs Jul 30 '15 at 22:10
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 ;-) – Funk Forty Niner Jul 30 '15 at 23:00
@Fred-ii-: Well I'll be damned. – AbraCadaver Jul 31 '15 at 00:35
@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. – Funk Forty Niner Jul 31 '15 at 00:40

score 1 · Accepted Answer · edited May 23 '17 at 11:58

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! — Gijs, Jul 31 '15 at 00:08

PDO fetch multiple records from database

2 Answers2