Trouble with fetch(PDO::FETCH_ASSOC)

PHP
21

I’ve taken the advice given to move to PDO and have used pdo to connect to my database which is working. I have used FETCH_UNIQUE and other parts to display the text from the database. For testing purposes I have four entries in the pageheader / pagecontent of the database. I see the four ‘menu’ headers (home, news, timeline, prehistoric Egypt). When I click the home page, its contents are show, this is the same for prehistoric Egypt. However, news and timeline do not behave as a link, that is there is no change in pointer, no localhost/dynamicsite/index1.php?id=2 or 3 when I hover over news and timeline. As I mentioned the others links work.

I used the var_dump($data); and got the following:

array(4) { [1]=> array(4) { [“pageheader”]=> string(4) “Home” [0]=> string(4) “Home” [“pagecontent”]=> string(21) “This is the home page” [1]=> string(21) “This is the home page” } [2]=> array(4) { [“pageheader”]=> string(4) “News” [0]=> string(4) “News” [“pagecontent”]=> string(15) “Some news stuff” [1]=> string(15) “Some news stuff” } [3]=> array(4) { [“pageheader”]=> string(8) “Timeline” [0]=> string(8) “Timeline” [“pagecontent”]=> string(10) “Some links” [1]=> string(10) “Some links” } [4]=> array(4) { [“pageheader”]=> string(17) “Predynastic Egypt” [0]=> string(17) “Predynastic Egypt” [“pagecontent”]=> string(22) “Predynastic Egypt test” [1]=> string(22) “Predynastic Egypt test” } }

In case it matters I will include the database connection and the code for the index1.php page.

The connection:

$servername = "localhost";
$username = "root";
$password = "";

try {
  $conn = new PDO("mysql:host=$servername;dbname=scarabbeetle", $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Connected successfully";
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}

The index1,php:

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

	$data = array();
	$sql = $conn->query("SELECT * FROM pages");

    $data = $sql->fetchAll(PDO::FETCH_UNIQUE);
    var_dump($data);

	$id = (isset($_GET['id']) && array_key_exists($_GET['id'],$data) ? $_GET['id'] : 1);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>test</title>
    <link href="css/pagestyles.css" rel="stylesheet">
</head>
<body>
<?php include("includes/top.php") ?>

<nav class="nav">
   <span class="nav__list">
   <?php

	foreach($data as $rid => $row):
		echo '<a href="index1.php?id='.$rid.'">'.$row['pageheader'].'</a>';
	endforeach;

	?>
   </span>
</nav>

    <hr>

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

	<hr>

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


<div class="space"></div>

<!-- footer content -->
<?php include('includes/footer.php'); ?>
</body>
</html>

I don’t understand why the news / timeline links are not links. Any help will be appreciated.

22

What html does it produce for the navigation links, if you “view source” in your browser?

2 Likes
23

I was also going to ask what the ‘view source’ is in your browser (the symptom is that of some broken html.)

If you are going to show someone the var_dump/print_r output, please output <pre> </pre> tags around it in your php code so that it will be readable. Also, for data that someone here might need to use to test with (since we don’t have or want your database), use var_export instead. And when posting these type of data dumps, surround it with three markdown back-ticks ```, the same as for posted code, so that the forum software won’t beautify it, converting all the double-quotes to useless smart/curly quotes.

You need to set the default fetch mode to assoc when you make the database connection, so that you don’t get double the data. Also, when you make the database connection, set emulated prepared queries to false (you want to use real prepared queries.)

There’s no good reason to catch a database exception unless it is for something that the visitor to your site can correct, such as inserting/updating duplicate or out of range submitted data. For all other database errors, they are fatal problems (php code execution should halt), that the visitor (including hackers who are intentionally trying to trigger errors) don’t need to know anything about. For these cases, simply let php catch and handle the database exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) For these fatal cases, remove any database exception try/catch logic you may currently have, simplifying your code.

While the person who came up with this single-query method to display both navigation and content was well intentioned, this is too closely coupled and won’t work once you have a large number of links where you need to add a search feature and/or pagination to limit the number of links that are displayed at one time. If someone finds a useful page on a site they should be able to book-mark the link and return to that page at any time. With added search/pagination, if the link they use is not in the default search/pagination result, the requested page content won’t be displayed. At the earliest opportunity, please add a separate query that uses the input id value to get the data needed to display the page content.

24

Of course this was just an example query to test the fetch mode. Using SELECT * is lazy unless you need every column fetching. You were better before selecting only the columns you need:-

"SELECT `id`, `pageheader`, `pagecontent` FROM pages ORDER BY id ASC"

Again the var_dump was just to illustrate the result of the fetch, to see the row id has become the array key for the row automatically. Though it can be useful as a temporary debugging aid to examine the fetched data.

For the problem you still have, like they say, we need to see the HTML output.
But I agree, it’s probably best to have a query to fetch data to build the menu and another to fetch data to build the individual elements of the page: content, title, description, etc.

25

Thanks for the advice. Here are some things I’ve done so far: Removed the try / catch statement and changed fetch mode to assoc. Also used var_export($data).

The result of the export:

<p>array (
  0 => 
  array (
    'id' => '1',
    'pageheader' => 'Home',
    'pagecontent' => 'This is the home page',
  ),
  1 => 
  array (
    'id' => '2',
    'pageheader' => 'News',
    'pagecontent' => 'Some news stuff',
  ),
  2 => 
  array (
    'id' => '3',
    'pageheader' => 'Timeline',
    'pagecontent' => 'Some links',
  ),
  3 => 
  array (
    'id' => '4',
    'pageheader' => 'Predynastic Egypt',
    'pagecontent' => 'Predynastic Egypt test',
  ),
)</p>

The nav from the code view source page:

<nav class="nav">
   <span class="nav__list">
   <a href="index1.php?id=0">Home</a><a href="index1.php?id=1">News</a><a href="index1.php?id=2">Timeline</a><a href="index1.php?id=3">Predynastic Egypt</a>   </span>
</nav>

As for adding a separate query that uses the input id value to get the data needed to display the page content - well I’m not entirely sure how to do that. For now the code parts not edited, remain as sent previously.

For now, the issue remains.

26

Any CSS?

27

Based on the current data output, you changed it in the fetchAll() statement, not by -

The code to do that would be part of your connection code and would look like -

$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc

The PDO::FETCH_UNIQUE must still be used in the fetchAll() statement to produce the desired data output.

The view source output looks okay (I was expecting there to be some html entities in the values, that were breaking the html markup, that weren’t being shown in the forum’s display of the data.) When I tested with your previous $data output and the portion of your code producing the navigation links, all the links worked for me.

28

This has made the array keys sequential numbers, as opposed to the actual IDs that fetch unique gives you.
If you used $row['id'] it would work, but your array_key_exists() condition won’t work when the keys are not IDs.
Then if you do go down the route separate queries, this won’t be a problem. Though it should not be a problem (at this stage at least) using fetch unique.

Again, what is the HTML output when you use fetch unique?
Going by the var_dump the IDs should be 1 to 4.

29

It has been stated a few time to set the default fetch mode to fetch assoc by adding the line below. Not sure you saw that.

$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);

So all in all your connection is now…

$conn = new PDO("mysql:host=$servername;dbname=scarabbeetle", $username, $password);
$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Doing your fetch unique now returns the desired results with the id as the array key.

$sql = $conn->query("SELECT `id`, `pageheader`, `pagecontent` FROM pages ORDER BY id ASC");
$data = $sql->fetchAll(PDO::FETCH_UNIQUE);

echo "<pre>";
print_r($data); 
echo "</pre>";

Array
(
    [1] => Array
        (
            [pageheader] => Home
            [pagecontent] => This is the home page
        )

    [2] => Array
        (
            [pageheader] => News
            [pagecontent] => Some news stuff
        )

    [3] => Array
        (
            [pageheader] => Timeline
            [pagecontent] => Some links
        )

    [4] => Array
        (
            [pageheader] => Predynastic Egypt
            [pagecontent] => Predynastic Egypt test
        )

)

Now looping through $data, $rid holds the correct id value so links are built as intended.

foreach($data as $rid => $row):
	echo '<a href="index1.php?id='.$rid.'">'.$row['pageheader'].'</a>';
endforeach;

$id can then be correctly defined as array_key_exists finds the correct key.

$id = (isset($_GET['id']) && array_key_exists($_GET['id'],$data) ? $_GET['id'] : 1);

Resulting in intended output of page content.

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

links

1 Like
30

Thanks for all the advice and help. I now have a web page that does what it’s supposed to. I have learnt a lot through this process and will continue to learn. I appreciate the time and effort put in to helping me get to this point.

:+1:

3 Likes
31

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.