Hello again.
I have insert title, author and content data into table with admin-are page and now want to create a user-view-page.php that everybody enter into that page and see all posts one after an other, something like this model:

first title by first author
first content

seconr title by second author
second content

.
.
last title by last author
last content

This is my code but it doesn't work:

<?php
$servername = "localhost";
$dbname = "mydbname";
$dbusername = "mydbusername";
$dbpassword = "mydbpassword";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT * FROM Posts WHERE";
    $result = $conn->query($sql);

    foreach($result as $row) {
        echo $result;
    }

} catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
}
?>
<html>
<head>
<title>user view page</title>
</head>
<body>
<?php
echo $result;
?>
</body>
</html>

What changes this code needs? Where is the problem? With sql syntax or with php code?

Recommended Answers

All 20 Replies

review your sql statement: where what?
and your echo $result wonder if it will work.

So i shoud chang it to this?
$sql = "SELECT * FROM Posts";

And haow should i echo? It this model correct?!

foreach($result as $row) {
    echo result['Title'];
    echo "by: " . result['Author'];
    echo "<br>";
    echo result['Content'];
    echo "<br><br>";
}

Remember you need an ORDER BY to get them in date order, and a LIMIT to get the specified amount as well.

Nearly there on the foreach, change it to this:

foreach($result as $row) {
    echo $row['Title'];
    echo "by: " . $row['Author'];
    echo "<br>";
    echo $row['Content'];
    echo "<br><br>";
}

<!-- // Or Even Better // -->
foreach($result as $row) {
    echo "<div class='post'>";
    echo "<h2>" . $row['Title'] . "</h2>";
    echo "<p>by: " . $row['Author'] . "</p>";
    echo "<p>" . $row['Content'] . "</p>";
    echo "</div>";
}

All I've done in the second one is neatened up your HTML. Instead of <br> tags, use CSS and put a bottom margin on div.post.

To get the last post (like in your little model), you would need a second query.

Member Avatar for diafol

LIMIT clause too. There's no way you want to show ALL posts in the table is there?

SELECT field1, field2... FROM table 
WHERE field1 = '' AND field2 = '' 
ORDER BY field1, field2,... 
LIMIT integer1, integer2

Using LIMIT like this allows you to paginate easily too. Seeing how you're producing your 'views', I'd strongly recommend a templating engine. But I think we've been here before.

You can try this:

<?php
$servername = "localhost";
$dbname = "mydbname";
$dbusername = "mydbusername";
$dbpassword = "mydbpassword";

if(isset($_GET['pg'])){
    $pg = $_GET['pg'];
}else{
    $pg = 1;
}
$post_count_per_page = 5;
$start_post = $pg * $post_count_per_page;
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "SELECT * FROM Post ORDER BY post_date, post_time";
    $result = $conn->query($sql);
    $all_data = $result->fetchAll();
    for($row_count = $start_post; $row_count < ($start_post + $post_count_per_page); $row_count++){
        $row = $all_data[$row_count];
        echo "<div class='post'>";
        echo "<h2>" . $row['Title'] . "</h2>";
        echo "<small>by: " . $row['Author'] . "</small>";
        echo "<p>" . $row['Content'] . "</p>";
        echo "</div>";
    }
} catch(PDOException $e) {
    echo "<br>" . $e->getMessage();
}
?>
<html>
<head>
<title>user view page</title>
</head>
<body>
</body>
</html>

using the $all_data = $result->fetchAll(); to fetch all data into an array instead of PDO Object, then loop for the data for what you want.

In addition, remember to add in checking if count of posts greater than 0 and display the error pages for invalid page or else viewer may alter the link to pg=2000 and will saw blank pages.

But, for better performance, I recommended the use of caches to store the data instead of querying all data each time the page load as the problem maybe occurs when you loading 10k rows of data and planning to display the 20th-30th data for page 3....

Hello again, couldn't check here for almost 3 weeks but now i'm here again to continue. Stil need help to solve the user-view-page.php problem, i can't get and echo data from Posts table.

@mattster, i used the code you gave me but i faced with a blanck page.

@lps, i used your code but look at this:
$sql = "SELECT * FROM Post ORDER BY post_date, post_time";
There is no post_data and post_time column in my table.

@diafol, SELECT field1, field2... FROM table what do you mean by "field" from table? Sorry i didn't understand where exactly you pointed with the "field".

Member Avatar for diafol

field1, field2 etc are just the fieldnames you want to retrieve - so could be 'title', 'year' etc.

'table' is the name of your table - I believe you've been using 'Posts'.

You should always list which specific fields you need in the query - never use *.

Well, still can't find the right way to solve it.

@mattster can you tell me why it doesn't work?

<?php
$servername = "localhost";
$dbname = "mydbname";
$dbusername = "mydbusername";
$dbpassword = "mydbpassword";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT * FROM Posts WHERE";
    $result = $conn->query($sql);

    foreach($result as $row) {
        echo $row['Title'];
        echo "by: " . $row['Author'];
        echo "<br>";
        echo $row['Content'];
        echo "<br><br>";
    }

} catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
}
?>

Can you guide me more clear please?
There is 4 fields in my Posts table: ID - Title - Author - Content
How should i change my code and get data from those column?

Member Avatar for diafol

SELECT id,title,author,content FROM Posts WHERE...

I changed my code to this:

<?php
$servername = "localhost";
$dbname = "mydbname";
$dbusername = "mydbusername";
$dbpassword = "mydbpassword";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT Title FROM Posts";
    $result = $conn->query($sql);

    foreach($result as $row) {
        echo $row['Title'];
        echo "<br>";
    }

} catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>

And the output was all Titles. Is it good or is better to write it in another way?!

And something more... in my Posts table there are some rows that are completely empty (they have been created when i clicked the Submit button on the editing post area without typing any title or author name or any content.)
Now whith the code above, those empty rows will be printed in the output too. I mean sth like this:

First post



my first post here...

Second post
a new post

You can see blank lines between titles that that are printed. How can i tell the script not to print those blank lines?

This is my last code changes:

<?php
$servername = "localhost";
$dbname = "mydbname";
$dbusername = "mydbusername";
$dbpassword = "mydbpassword";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT ID, Title, Author, Content FROM Posts";
    $result = $conn->query($sql);

    foreach($result as $row) {
        echo $row['ID'] . ") " .  $row['Title'];
        echo "<br>" . " by: " . $row['Author'];
        echo "<br>";
        echo $row['Content'];
        echo "<br><br><br>";
    }

} catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>

With this code, all posts in the Posts table will be printed into the page and users can see all post one after another.

Now how can i ask the script to echo only 3 posts in each page?
I think @lps mentioned this limitation in his code, but i didn't understand it clear.

And there must be a "NEXT" button to direct users into next pages to see next 3 posts, correct? So how should i do it? (My code above is in "user-view-page.php" file, so do i need more php pages to show next 3 postes with NEXT button?!)
For now, just need your guidance, thank you.

Member Avatar for diafol

THis is the whole point of the LIMIT clause.

This is what I meentioned previously:

Using LIMIT like this allows you to paginate easily too. Seeing how you're producing your 'views', I'd strongly recommend a templating engine. But I think we've been here before.

You're after pagination. The LIMIT x,y clause allows for an easy implementation of this.

If you only want non-empty titles, you can use the WHERE clause to filter them out...

SELECT title FROM Posts WHERE title <> '' LIMIT $start, $postsPerPage

Where $start would be the ($page_number - 1) * $postsPerPage

SO for $page = 1 and $postsPerPage = 3

$start would be (1-1)*3 = 0 (start on record zero)

etc.

What is <> and '' that you have used between title and Limit?
What do they mean?

Member Avatar for diafol

A little research on your part would not go amiss here nil. While I often respond to basic questions, this is where I put thee burden of work back onto the OP, look it up in the MySQL manual or sites from a search query. Come back if you're still stuck.

I couldn't find it, can you please explain it to me?

Member Avatar for diafol

Oh come on, seriously? You type "mysql manual" into a search engine, and there by magic a few links turn up. Click one of the links and you will be taken to your destination. If you can't do that by yourself, forget programming as it will be too difficult for you.

No i can!...wait i think maybe i have not understand the main point correctly! i searched for mysql manual and check some of the links, but didn't find any definition for <> sign there... it seems i was looking for wrong topic yes?!

Please tell me about that. I WANT TO LEARN IT SERIOUSLY.

Member Avatar for diafol

<> means not equal to.

Thank you @diafol.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.