How do you retrieve data from a db with PHP and MySQL and indent returned results?

Just to put this program in perspective, Im trying to create a mini Bulletin.

This is my table:

CREATE TABLE Postings ( `PostID` INT( 11 ) NOT NULL AUTO_INCREMENT , `PostDate` DATETIME NOT NULL , `PostedBy` VARCHAR( 255 ) NOT NULL , `PostSubject` VARCHAR( 255 ) NOT NULL , `Content` VARCHAR( 255 ) NOT NULL , `ParentPost` INT( 11 ) NULL , PRIMARY KEY ( `PostID` ) )

And here is the data in that table:

insert into postings values ('1','11/01/03 10:15','[email protected]','Welcome','Welcome to the bulletin board',NULL); insert into postings values ('2','12/01/03 08:00','[email protected]','Welcome 2','This is posting 2','1'); insert into postings values ('3','13/01/03 09:00','[email protected]','Welcome 3','This is posting 3','1'); insert into postings values ('4','14/01/03 10:15','[email protected]','New Topic','This is posting 4',NULL); insert into postings values ('5','15/01/03 10:15','[email protected]','New Topic 2','This is posting 5','4'); insert into postings values ('6','16/01/03 10:15','[email protected]','New Topic 3','This is posting 6','4'); insert into postings values ('7','17/01/03 10:15','[email protected]','Welcome 4','This is posting 7','2'); insert into postings values ('8','18/01/03 10:15','[email protected]','Welcome 5','This is posting 8','2'); insert into postings values ('9','19/01/03 10:15','[email protected]','Welcome 6','This is posting 9','8'); insert into postings values ('10','20/01/03 10:15','[email protected]','New Topic 4','This is posting 10','6');

PostID is auto increment and is Primary Key, ParentPost is also INT, and will have values assigned to it.

Now what I want to do is write a SQL statement and PHP code that will allow me to retrive all of the values from the DB, but indent every row that has a ParentPost value thats equal to a PostID value beneath the PostID row.

I have a pic at the bottom that could better explain what Im after.

This is what I have come up with so far with no luck:

<?php $db_host = "localhost"; $db_username = "root"; $db_pass = ""; $db_name = "OnlineBulletinBoardDB"; @mysql_connect("$db_host","$db_username","$db_pass") or die ("Could no connect to MySQL"); @mysql_select_db("$db_name") or die ("No database"); $query="SELECT PostSubject FROM postings GROUP BY PostID = ParentPost;"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); ?> <?php $i=0; while ($i < $num) { $dno=mysql_result($result,$i,"PostSubject"); ?> <?php echo $dno; ?><br/> <?php $i++; } ?>

The end result im looking for is this shown in the pic below:How do you retrieve data from a db with PHP and MySQL and indent returned results?

Cheers guys.

-------------Problems Reply------------

Recursion is the key. To avoid doing lots of queries like "select * from Postings where ParentID = 0", "select * from Postings where ParentID = 1" etc do one query "select * from Postings" which brings back everything then populate a two dimension array with the parent id the key for the first array. The example below is untested but it should work with a bit of tidying up:

<?php

$db_host = "localhost";
$db_username = "root";
$db_pass = "";
$db_name = "OnlineBulletinBoardDB";

@mysql_connect("$db_host","$db_username","$db_pass") or die ("Could no connect to MySQL");
@mysql_select_db("$db_name") or die ("No database");

$query="SELECT PostSubject, PostID, ParentID FROM postings";

$res = mysql_query($query);

while($row = mysql_fetch_assoc($res){
$parentID = intval($row['ParentID']);
$posts[$parentID][] = $row;
}

function displayPosts($parentId, $posts){
echo "<ul>";
foreach($posts[$parentId] as $post){
echo "<li>";
echo $post['PostSubject'];
if(isset($posts[$post['PostID']])){
displayPosts($post['PostID'],$posts);
}
echo "</li>";
}
echo "</ul>";
}

displayPosts($posts['PostID'],$posts);

Hmm, I can't edit ejrowley's answer (I'm new to StackOverflow), so I post tidier and working example with recursion:

<?php

$db_host = "localhost";
$db_username = "root";
$db_pass = "";
$db_name = "OnlineBulletinBoardDB";

@mysql_connect("$db_host","$db_username","$db_pass") or die ("Could no connect to MySQL");
@mysql_select_db("$db_name") or die ("No database");

$posts = array();
$q = mysql_query("SELECT * FROM postings;");
while ($row = mysql_fetch_assoc($q)) {
$parentId = $row['ParentPost'];
$posts[$parentId ? $parentId : 0][] = $row; // because you use NULL and array can't have NULL key
}

function displayPosts($parentId, $posts) {
echo "<ul>";
foreach ($posts[$parentId] as $post) {
echo "<li>";
echo $post['PostSubject'];
$postId = $post['PostID'];
if (isset($posts[$postId])) {
displayPosts($postId, $posts);
}
echo "</li>";
}
echo "</ul>";
}

displayPosts(0, $posts); // where 0 means 'no parent'

?>

Category:php Views:0 Time:2011-05-26
Tags: php mysql sql

Related post

  • Can XML be used to store, modify and retrieve data in PHP like it can with MySQL? Managing data for general-purpose storage (eg. CRUD, CMS) 2011-09-20

    This is a long and old question that doesn't get to the point. I basically wanted to know practices involving flat files and the extent they could be used, as a replacement for SQL, mostly in terms of multi-user capability. At the time, I was wanting

  • How to retrieve data from cursor class 2010-05-11

    I need to know how to retrieve data from cursor. I need this because the ringtonemanager returns all the audio files in form of cursor object, I need to know how to retrieve the values. Anbudan. --------------Solutions------------- Once you have the

  • iOS: TouchJSON & retrieved data size 2010-11-19

    I want to display a UIProgressView indicating the amount of data received as I request JSON data using touchJSON. I was wondering if there was a way to listen to the size of the data being received. I request data using the following: - (NSDictionary

  • best practice for retrieving data which meet selected conditions 2008-11-01

    I have a database table named call with columns call_time, location, emergency_type and there are three types of emergency: paramedics, police and firefighters. In the windows form I created CheckBoxes 'paramedics', 'police', 'firefighters' and I wan

  • Inheritance question - retrieving data from Access database file and SQL Express 2008-11-01

    I am developing an application which will be connected to Access database at the beginning and the plan is to switch to MS SQL or SQL Express in the near future. The datatables structures are same for both types of databases and I am trying to avoid

  • How do you connect/retrieve data from a MYSQL database using objects in PHP? 2008-11-09

    Generally I connect and retrieve data using the standard way (error checking removed for simplicity): $db = mysql_select_db("dbname", mysql_connect("host","username","passord")); $items = mysql_query("SELECT * FROM $db"); while($item = mysql_fetch_ar

  • How can I handle non-ASCII characters when retrieving data from SQL Server using Perl? 2008-12-30

    I have a Perl script running on UNIX that uses DBI to connect to and retrieve data from a SQL Server database. The script looks like the following: $dbh = DBI->connect("dbi:Sybase:server=$connect;charset=UTF-8", $login, $password) or die("Couldn't

  • .Net: Retrieving data from threads 2009-04-01

    This is an Easy Q, but great help: Under title" Retrieving data from threads MSDN (Here) introduces a way to get data from a child thread by using callback method which is encapsulated by a delegate passed from main thread to the child thread - who h

  • C# WinForms - How Do i Retrieve Data From A Textbox On One Form Via Another Form? 2009-05-06

    I have a method that executes inside one form, but I need to retrieve data from another form to pass into the method. Whats the best way of doing this? --------------Solutions------------- Assuming that formB is initialized in formA I would recommend

  • How to retrieve data from 10g in java servlet & JSP 2009-06-06

    Retrieve data from database 10g in Servlet & JSP --------------Solutions------------- There are countless ways of skinning this particular cat. For one it depends on what Web framework (if any) you use. Personally I'm a huge fan of using Spring r

  • Retrieve data from WCF to store on SQL Server CE (TableAdapter) 2009-06-16

    I have a Compact Framework 2.0 app that consume a WCF service. The data that retrieve from the service will be store on a SQL Server 2005 CE. The SQL Server CE database has his own tables. I use a TableAdapter to access SQL Server CE and Insert and S

  • How to retrieve data with LINQ to SQL into a weakly-typed object that can be moved around 2009-07-01

    In classic ASP we had the record set object. With ADO.Net we had the datatable. Both were simple .Net objects that could be moved around easily. What is the equivalent for using LINQ To SQL? Most examples show "var" being used, however, this seems co

  • Android: Retrieving data from the database issue 2009-07-10

    I created the following method for retrieving stored settings from the database: public String getEntry(long rowIndex){ String value = ""; Cursor c = db.query(DATABASE_TABLE, new String[] {KEY_NAME, VALUE}, KEY_NAME + "=" + rowIndex, null, null, null

  • how to connect and insert and retrieve data from database? 2009-07-14

    $con = mysql_connect("localhost","music123_sri","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("music123_telugu", $con); $sql="INSERT INTO xml (File) VALUES (" . mysql_escape_string($xmlString) . ")"; $data =

  • Retrieve data in IMS from J2EE application 2009-07-21

    Currently, i have a J2EE application that retrieves data from IMS database through MQ. We would like to fetch data directly instead - without using MQ. Is there any option? Any connectors that we could deploy in our server (WAS 6) that would help us

  • Need generic function(s) for connecting to DB and retrieve data 2009-07-22

    I'm making a DAL class which I can use to connect to DB and retrieve data. I'm using SQL Server 2005 Express (and Visual Web Developer 2008 Express Edition). I found several examples on the web for connecting an retrieving data. But none where made i

  • Retrieve data from remote server in SQL Server! 2009-08-14

    Is there any other way to retrieve data from a remote server in SQL Server 2005 instead of using linked server? --------------Solutions------------- You can use OPENDATASOURCE. Example from linked page: SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data S

  • Retrieve data from mysql by php to create flot graph 2009-08-21

    Hi i am trying to retrieve data from mysql database to create flot graph can anyone walk me through this procedure or give me an idea of what to do thanks --------------Solutions------------- You probably want something like this. I haven't used flot

  • How to retrieve data from a attached zip file in Blackberry application? 2009-08-31

    I am using eclipse to build application for Blackberry. I attached a zip file with my application. Please help me, I don't know how to retrieve data form the zip file in application development. --------------Solutions------------- In BlackBerry we c

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.115 (s). 11 q(s)