Trouble with fetch(PDO::FETCH_ASSOC)

PHP
1

I have been given advice on this previously and was given code to try. I have been a bit busy to try it, but when I did, there was an error message: Fatal error : Uncaught Error: Call to undefined method mysqli_result::fetch() in C:\xampp\htdocs\dynamicsite\index1.php:7 Stack trace: #0 {main} thrown in C:\xampp\htdocs\dynamicsite\index1.php on line 7

I have no idea what to do about this error:

This is the code - with thanks to those who provided it on sitepoint.

<?php
require_once("db1.php");

	$data = array();
	$sql = "SELECT `id`, `pageheader`, `pagecontent` FROM pages ORDER BY id ASC";
	$query = $conn->query($sql);
	while($row = $query->fetch(PDO::FETCH_ASSOC)){	
		$data[$row['id']] = $row;
	}
	
	$id = (isset($_GET['id']) && array_key_exists($_GET['id'],$data) ? $_GET['id'] : 1);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Dynamic Site</title>
</head>
<body>

<table class="topmenu">
		<tr>
			<td>
				<h1 class="siteName">Site Name here</h1>
			</td>	
		</tr>
		<?php
		foreach($data as $rid => $row):
			echo '<tr>
				<td><a href="index.php?id='.$rid.'">'.$row['pageheader'].'</a></td>
			</tr>'."\r";
		endforeach;
		?>
    </table>

    <hr>

    <?php echo $data[$id]['pagecontent']; ?>

</body>
</html>

I ask for help as a total beginner.

2

It looks like you may be mixing PDO and mysqli here.

3

this should be

$query->fetch_assoc()

https://www.php.net/manual/en/mysqli-result.fetch-assoc.php

1 Like
4

In your previous thread for this, you were using the much simpler, more consistent, better designed, universal PDO extension. Why have you now switched your connection code to use the limited mysqli extension, which won’t work with the rest of code?

1 Like
5

Thanks for the help. I had seen it on another pdo site and thought it might help. I now know differently.

I changed the code and I get a list of the menus (links), however, when i select link I am sent to a new page with the home page information. This occurs with all links.

What I am aiming to do is to have different information on the same page (index1.php). So home, will be home stuff, news will show news stuff etc.

I am still using the same code (with the change added). I must be missing something.

I am grateful for the help so for and am asking for help to achieve my aim.

6

One more reason to stick to PDO. There is no need to loop through the rows to make the ID become the index. Using the right PDO fetch mode will do that for you. See PDO::FETCH_UNIQUE
Of course it will only work with PDO, not mysql.

7

Your links are inside index1.php but they point to index.php, is that ok?

C:\xampp\htdocs\dynamicsite\index1.php on line 7

<a href="index.php?id=
8

Well to be fair, you also don’t need to do that for mysqli as well. All you really need to do is use ->fetch() which should give you a list of data. I haven’t used mysqli in a while, but you don’t necessarily have to go through a bunch of hoops to get the same result. The OP seems to be doing more work than really necessary. If you just throw it in an array, the array will handle all the indexing.

EDIT: What I would do is create 2 arrays. First array would be all id, pageheader, and pagecontent. You’ll then want to loop and throw those data into another array which should create a multi-dimensional array. Something like

$pageArray = [];

while($prepare->fetch()) {

	$pages = [];

	$pages['id'] = $id;
	$pages['pageheader'] = $pageheader;
	$pages['pagecontent'] = $pagecontent;

	$pageArray[] = $pages;

}

print_r($pageArray);

Also, @benpeters648, you should be using prepared statements. That’s why you’re getting that error. ->fetch() is part of stmt which is prepared statements.

1 Like
9

This can be shortened to 1 line

while($page = $prepare->fetch_assoc()) {
        $pageArray[] = $page;
}
1 Like
10

No it shouldn’t unless you want to start specifying the result using their index keys. What you’re proposing would require you to do something like

$pageArray[0][0]; // Gives id
$pageArray[0][1]; // Gives page header
$pageArray[0][2]; // Gives page content

What I wrote gives you something like

$pageArray[0]['id']; // Gives id
$pageArray[0]['pageheader']; // Gives page header
$pageArray[0]['pagecontent']; // Gives page content

I’m sure you didn’t mean it and I’m 100% certain the OP is looking for the 2nd option. Simplifying is good, but to an extent.

$page doesn’t just come out of no where. Since this is mysqli, you have to literally create each individual variable to bind. Once you’ve done that, you can then use that variable to add to the $page array. So the variable $page in itself will not contain a full set of data arrays. It will only contain 1 data set. Meaning you have to construct the array yourself and properly do it.

On the side note, you are using the results object which your example works using ->fetch_assoc(), but you should really be using prepared statements. Especially if you are doing copying and pasting and end up dealing with user inputs.

1 Like
11

fetch_assoc() does exactly the same

In that case you don’t even need the loop

$pageArray = $prepare->fetchAll(PDO::FETCH_UNIQUE)
2 Likes
12

That was my point, using PDO with the FETCH_UNIQUE mode will create an array where the first column specified (in this case ID) becomes the array key for the row.
Fetch assoc will give you something like:-

    $fetch = array(
        0 => array(   // Numeric key defined by fectch order
            'id' => '5',
            'pageheader' => 'home',
            'pagecontent' => 'some content...'
            ),
        1 => array(
            'id' => '8',
            'pageheader' => 'about',
            'pagecontent' => 'some content...'
        ),
        2 => array(
            'id' => '11',
            'pageheader' => 'contact',
            'pagecontent' => 'some content...'
        )
    );

Where fetch unique will give you something like:-

    $fetchUnique = array(
        '5' => array(   // Key taken from ID column
            'pageheader' => 'home',
            'pagecontent' => 'some content...'
            ),
        '8' => array(
            'pageheader' => 'about',
            'pagecontent' => 'some content...'
        ),
        '11' => array(
            'pageheader' => 'contact',
            'pagecontent' => 'some content...'
        )
    );

…Straight from the query without any further processing with loops etc. I don’t see any reason to prepare in this case, as there are no variables and the query is not repeated.

2 Likes
13

Sure, take my apologies for copyright violation :grin:

2 Likes
14

Correct, but the OP is using mysqli. Again, you can’t mix and match APIs. If the OP doesn’t want to use PDO, that’s their choice, but they or anyone should realistically be using prepared statements. Which in that case will result in the version I wrote. You’re going to be using prepared statements in PDO anyways, why is it any different when using mysqli? So like I said, if someone uses prepared statements for PDO and not for mysqli, that makes absolutely no sense.

1 Like
15

In case of mysqli - there is fetch_assoc()
In case of PDO - there is fetchAll(PDO::FETCH_UNIQUE)

I still don’t get why do you want to manually set array keys inside a loop, if there are builtin methods for that

1 Like
16

I only realised I had made that mistake - index.php, instead of index1.php. Having changed it, the site works as expected.

17

I am using mysqli as that was part of the online help I got.

18

And now you’ve got help in using PDO :biggrin:

2 Likes
19

Thanks for all the help. :grin:

2 Likes
20

I think we can all agree on this, and it is the cause of the initial problem, trying to use a PDO fetch mode on a mysqli connection.

I have not been following the OP’s other topics, but it seems like the OP is flip-floping between the two.
Clearly the answer is to pick one, then stick with it.

Absolutely, it is always ultimately their choice what they do. But I don’t think they made a conscious decision that they don’t want to use PDO, but…

And is as I say, torn between the two and ended up getting in a muddle by mixing them.

So again, the OP must choose between mysqli or PDO. Unless someone is going to pop up and make a compelling case for mysqli, it really should be PDO.
Being able to use the correct fetch mode (in this instance FETCH_UNIQUE) is just one reason.

If anyone is still unsure, try this on any table they have with a PDO connection, where the first column is unique. See how it differs to FETCH_ASSOC which could be used with either API.

$sql = $pdo->query("SELECT * FROM table LIMIT 10");
$data = $sql->fetchAll(PDO::FETCH_UNIQUE);
var_dump($data);
exit;

Using the right mode means doing more with less code. No need for extra arrays, extra loops, and no need for prepared statements (in this instance).
And when you do need prepared statements, who wants to go though all that parameter binding palaver you get with mysqli?

2 Likes