Arrays, 1 level tree

timpea

Hi,

I am wanting to create an array that is similar to the following but from a database query.

$test = array(
"Print"=>array("Print_Brochure"=>"Brochure",
             "Print_Leaflets"=>"Leaflets"), 
"Web"=>array("Web_Design"=>"Design",
             "Web_Develop"=>"Develop",
             "Web_Amend"=>"Amend"));

The table that stores this information in the database is of the following structure:

CREATE TABLE jobtypes (
  jobtype varchar(32) NOT NULL default '',
  jobsubtype varchar(32) NOT NULL default '',
  title varchar(64) NOT NULL default '',
  datatype varchar(32) NOT NULL default '',
  PRIMARY KEY  (jobtype,jobsubtype,title)
) TYPE=InnoDB;

A Jobtype for example is Web, and its Subtypes are Design, Develop and Amend

I can then query this table using the following to retrieve just unique jobtypes and their subtypes

SELECT jobtype, jobsubtype FROM jobtypes GROUP BY jobtype, jobsubtype ORDER BY jobtype, jobsubtype

But i am being really slow turing this query into the array formatted as above.

And if it helps in anyway, i am wanting to create this array for a Smarty html-options, and i am using Pear DB for my database connection.

Any help would be great.

Thanks
Tim

emptyfish

so your RecordSet would return

+++++++++++++++
print brochure
print leaflets
web design
web develop
web amend
+++++++++++++++

to get this into smarty which i love, you could do this

+++++++++++++++
// Performing SQL query
$query = 'SELECT jobtype, jobsubtype FROM jobtypes GROUP BY jobtype, jobsubtype ORDER BY jobtype, jobsubtype';

$recordSetData = mysql_query($query) or die('Query failed: ' . mysql_error());

// what your doing here is transposing the data into goups by a common RS field
// which is what smarty loves
foreach( $recordSetData as $v ) {

$test[ $v["jobtype"] ][ $v["jobtype"] . "_" . $v["jobsubtype"] ] = $v["jobsubtype "];

}

NICE

timpea

Wonderfull.

It seems so obvious now, thankyou. your a star.

Tim.

emptyfish

No probs

In fact when i first started using smarty, i had the same sort of problem, so i asked on a forum and i got a simalar answer.

with any sort of recordset in smarty always use {foerah }} loops, i never use sections.

master arrays and the mainpulation of your RS and you wont go far wrong