We help IT Professionals succeed at work.

How to populate advanceddatagrid with hierarchical data using php and MySQL?

salamay
salamay asked
on
Medium Priority
783 Views
Last Modified: 2013-11-05
Following is the hierarchy supposed to go in the grid

Project 1
         Well 1
         Well 2
         Well 3
Project 2
         Well 4
         Well 5
Project 3
         Well 6
         Well 7
         Well 8


I have two tables Projects and Wells in my db with a fk in wells table relating to the project_id

Projects table
project_id       project_name
1                     Project 1
2                     Project 2
3                     Project 3

Wells table
well_id           well_name        projects_project_id
1                     Well 1               1
2                     Well 2               1
3                     Well 3               1
4                     Well 4               2
5                     Well 5               2
6                     Well 6               3
7                     Well 7               3
8                     Well 8               3

I want to modify the following function to get hierarchical data
 
public function getAllProjectsAndWells() {

		$stmt = mysqli_prepare(
					$this->connection, "SELECT project_id, project_name, well_name 
					FROM projects, wells 
					WHERE projects.project_id = wells.projects_project_id");		
		$this->throwExceptionOnError();
		
		mysqli_stmt_execute($stmt);
		$this->throwExceptionOnError();
		
		$rows = array();
		
		mysqli_stmt_bind_result($stmt, $row->project_id, $row->project_name, $row->well_name);
		
	    while (mysqli_stmt_fetch($stmt)) {
	      $rows[] = $row;
	      $row = new stdClass();
	      mysqli_stmt_bind_result($stmt, $row->project_id, $row->project_name, $row->well_name);
	    }
		
		mysqli_stmt_free_result($stmt);
	    mysqli_close($this->connection);
	
	    return $rows;
	}

Open in new window


Also How can I populate the grid with this array?

Regards,
Salamay
Comment
Watch Question

CERTIFIED EXPERT
Commented:
I think this should do what you need:
<?php

$dbh=mysql_connect("db_host","db_user","db_pass");
mysql_select_db("db_name"); 

$sql="SELECT project_id,project_name FROM projects";
$result=mysql_query($sql,$dbh);
$count=mysql_numrows($result);
for($i=0; $i<$count; $i++) {
        $project_id=mysql_result($result,$i,"project_id");
        $project_name=mysql_result($result,$i,"project_name");
        echo "$project_name<br>\n";
        $wsql="SELECT well_name FROM wells WHERE projects_project_id='$project_id'";
        $wresult=mysql_query($wsql,$dbh);
        $wcount=mysql_numrows($wresult);
        for($w=0; $w<$wcount; $w++) {
                $well_name=mysql_result($wresult,$w,"well_name");
                echo "&nbsp; $well_name<br>\n";
        }
}

mysql_close($dbh);

?>

Open in new window

Author

Commented:
xterm,

I need to store the result in array with the hierarchy and use that to populate the advanceddatagrid in flex. So using your technique how would it be possible to form such an array?

Regards,
Faraz Mir
CERTIFIED EXPERT
Commented:
I'm not entirely sure what the advanceddatagrid is, but I rewrote the code to put the output into a linear array, so now that you have the array, hopefully that will steer you in the right direction.  I also wrapped it up in a function as you have it above.
<?php

ini_set('display_errors','1');
ini_set('display_startup_errors','1');
error_reporting(E_ALL | E_STRICT);
date_default_timezone_set("America/Chicago");

function getAllProjectsAndWells() {
        $dbh=mysql_connect("db_host","db_username","db_password");
        $wells=array();
        $icount=0;
        mysql_select_db("mydatabase");

        $sql="SELECT project_id,project_name FROM projects";
        $result=mysql_query($sql,$dbh);
        $count=mysql_numrows($result);
        for($i=0; $i<$count; $i++) {
                $project_id=mysql_result($result,$i,"project_id");
                $project_name=mysql_result($result,$i,"project_name");
                #echo "$project_name<br>\n";
                $wsql="SELECT well_name FROM wells WHERE projects_project_id='$project_id'";
                $wresult=mysql_query($wsql,$dbh);
                $wcount=mysql_numrows($wresult);
                for($w=0; $w<$wcount; $w++) {
                        $well_name=mysql_result($wresult,$w,"well_name");
                        $wells[$icount]["project_name"]="$project_name";
                        $wells[$icount]["well_name"]="$well_name";
                        $wells[$icount]["project_id"]="$project_id";
                        $icount++;
                }
        }
        mysql_close($dbh);
        return($wells);
}

$wells=getAllProjectsAndWells();
echo "<pre>\n";
print_r($wells);


?>

Open in new window

Author

Commented:
Thanks Xterm.

These are very nice implementations and easy to follow. However flex advanced data grid has its own hierarchy creation using actionscript and i believe that's the only way it can be done.

Regards,
Salamay

Explore More ContentExplore courses, solutions, and other research materials related to this topic.