So I'm trying to make a barebones blog in which it prints everything from a mySQL table but it seems to only print the first row. I have two blocks of PHP code and it's the 2nd.
As you can see in the code, I'm printing the latest row in a separate block first so if you also know how to skip the newest row in the table for the 2nd block that would also be nice.
Thanks in advance!
<?php
include_once"connect.php"; //Inkluderar connect.php filen
$sql = "SELECT * FROM posts";
$result = $conn->query($sql);
?>
<html>
<head>
<link rel="stylesheet" type="text/css" href="css/style.css">
<link rel="stylesheet" type="text/css" href="css/post-style.css">
<link href='https://fonts.googleapis.com/css?family=Source+Sans+Pro:600,400,300|PT+Sans:400,700|PT+Serif|PT+Sans+Narrow' rel='stylesheet' type='text/css'>
<meta name="viewport" content="width=device-width">
</head>
<body>
<?php
include_once"header.php";
//Väljer all data från angivna tabellen
$sql = "SELECT * FROM posts ORDER BY id DESC LIMIT 1";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
//Skriver ut data från varje rad
while($row = $result->fetch_assoc()){
echo '<div id="newest" style="background-image: url(img/' .$row['img']. ');">';
echo '<div id="newest-text">';
echo '<h1 class="title">' . $row['title'] . '</h1>';
echo '<h3 class="subtitle">' . $row['subtitle'] . '</h3>';
echo '<a class="fullPost" href="post.php?id=' . $row['id'] . '"><i>Read More</i></a>';
echo '</div></div>';
}
};
?>
<div id="content">
<?php
//Väljer all data från angivna tabellen
$sql = "SELECT * FROM posts ORDER BY id";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
//Skriver ut data från varje rad
while($row = $result->fetch_assoc())
{
echo '<div class="post">';
echo "<div class='img'><img src=".'img/'.$row['img']." /></div>";
echo '<h1 class="title">' . $row['title'] . '</h1>';
echo '<h3 class="subtitle">' . $row['subtitle'] . '</h3>';
$words = split(" ", $row['content']);
for ($i = 0; ($i < 50 && $i < count($words)); $i++) {
echo $words[$i] . " ";
}
echo '<br><a class="fullPost" href="post.php?id=' . $row['id'] . '"><i>Read More</i></a>';
echo '<div class="line-separator"></div>';
echo '<p class="tag"> Topic - ' . $row['tag'] . '</p>';
echo '<p class="date">' . $row['date'] . '</p>';
echo '</div>';
}
};
?>
</div>
but it seems to only print the first row
Take a look at your query:
SELECT * FROM posts ORDER BY id DESC LIMIT 1
LIMIT 1
The LIMIT clause is used to specify the number of records to return, remove it to get all records.
Your SQL has a LIMIT clause, meaning that only 1 row is ever returned (the latest row in this case).
If you increase the limit or remove it, your code should display all of those rows.
Since you query the same table 3 times it would be better to query once only:
<?php
include_once"connect.php"; //Inkluderar connect.php filen
$sql = "SELECT * FROM posts ORDER BY id DESC";
$result = $conn->query($sql);
#just for debug
echo 'result->num_rows = '.$result->num_rows;
?>
<html>
<head>
<link rel="stylesheet" type="text/css" href="css/style.css">
<link rel="stylesheet" type="text/css" href="css/post-style.css">
<link href='https://fonts.googleapis.com/css?family=Source+Sans+Pro:600,400,300|PT+Sans:400,700|PT+Serif|PT+Sans+Narrow' rel='stylesheet' type='text/css'>
<meta name="viewport" content="width=device-width">
</head>
<body>
<?php
if ($result->num_rows > 0) {
//Skriver ut data från varje rad
if($row = $result->fetch_assoc()){
echo '<div id="newest" style="background-image: url(img/' .$row['img']. ');">';
echo '<div id="newest-text">';
echo '<h1 class="title">' . $row['title'] . '</h1>';
echo '<h3 class="subtitle">' . $row['subtitle'] . '</h3>';
echo '<a class="fullPost" href="post.php?id=' . $row['id'] . '"><i>Read More</i></a>';
echo '</div></div>';
}
};
?>
<div id="content">
<?php
if ($result->num_rows > 0) {
//Skriver ut data från varje rad
while($row = $result->fetch_assoc())
{
echo '<div class="post">';
echo "<div class='img'><img src=".'img/'.$row['img']." /></div>";
echo '<h1 class="title">' . $row['title'] . '</h1>';
echo '<h3 class="subtitle">' . $row['subtitle'] . '</h3>';
$words = split(" ", $row['content']);
for ($i = 0; ($i < 50 && $i < count($words)); $i++) {
echo $words[$i] . " ";
}
echo '<br><a class="fullPost" href="post.php?id=' . $row['id'] . '"><i>Read More</i></a>';
echo '<div class="line-separator"></div>';
echo '<p class="tag"> Topic - ' . $row['tag'] . '</p>';
echo '<p class="date">' . $row['date'] . '</p>';
echo '</div>';
}
}
?>
</div>
At each call of fetch_assoc() the db cursor would move 1 record forward until there are no more rows