Basic help for a beginner

[phodgesZoho]

Hi

 

Essentially I need a php query that will combine two tables and return them as a structured array. The two tables are:

 

Table1: industries

 

id, type, colour

 

Table2: products

 

id, industryId, type

 

And the returned array would be like this:

 

industries.type

industries.colour

products_array

                products.type

 

The class below is as far as I have gotton. Complete newbie so appreciate a point in the right direction

 

// Get a list of Industries

 

function getIndustries () {

 

$result = mysql_query("SELECT * FROM industries");

 

return $result;

 

}

 

// Get a list of Products

 

function getProducts () {

 

$result = mysql_query("SELECT * FROM products");

 

return $result;

 

}

 

 

function getIndustryStructure () {

 

$industries = $this->getIndustries();

$products = $this->getProducts();

 

$industry_array = array();

 

while($industryRow = mysql_fetch_array($industries, MYSQL_ASSOC)) {

$product_array = array();

  while($productRow = mysql_fetch_array($products, MYSQL_ASSOC)) {

  if ($industryRow['id'] == $productRow['industryId'] ) {

$product = $productRow['type'];

array_push($product_array, $product);

}

  }

  $industry = array(

  mType => $industryRow['type'],

  colour => $industryRow['colour'],

                    products => $product_array

          )

array_push($industry_array, $industry);

}

return $industry_array;

}

 

 

Cheers in advance.

[jjmusicpro]

How do you want the array structured?

[lemmin]

I'm just guessing, but you probably want something like this:

$qry = "SELECT * FROM products p LEFT JOIN industries i ON i.type = p.type";

[phodgesZoho]

 

There is probably a much more effiecient way to do this, but it works and returns the information to flash in the structure I need so moving on to the next problem.

 

However if there is a better approach to doing this than would love it if someone would point me in the right direction.

 

<?php

 

class industries {

 

// Login information for database

 

 

// Constructer

 

function industries() {

 

// Create the method table for AMFPHP

 

$this->methodTable = array (

    "getIndustries" => array (

"description" => "Retrieve a list of Industries",

"access" => "remote",

"arguments" => array()

),

"getProducts" => array (

"description" => "Retrieve a list of products",

"access" => "remote",

"arguments" => array()

),

"getIndustryStructure" => array (

"description" => "Retrieve a strucutred array of Industries and their associated products",

"access" => "remote",

"arguments" => array()

)

);

 

//Create the connection to the database derver and select the database

 

$this->conn = mysql_pconnect($this->dbhost, $this->dbuser, $this->dbpass);

@mysql_select_db($this->dbname);

 

} 

 

// Get a list of Industries

 

function getIndustries () {

 

$result = mysql_query("SELECT * FROM industries");

 

return $result;

 

}

 

// Get a list of Products

 

function getProducts () {

 

$result = mysql_query("SELECT * FROM products");

 

return $result;

 

}

 

// Get a list of Industries and their associated products

 

function getIndustryStructure () {

 

$industries = $this->getIndustries();

$products = $this->getProducts();

 

$industry_array = array();

 

$industryCount = 0;

 

while($industryRow = mysql_fetch_array($industries, MYSQL_ASSOC)) {

 

$product_array = array();

$productCount = 0;

 

  while($productRow = mysql_fetch_array($products, MYSQL_ASSOC)) {

  if ($industryRow['id'] == $productRow['industryID'] ) {

$product_array[$productCount]['smName'] = $productRow['productName'];

$productCount++;

}

  }

 

  $industry_array[$industryCount]['mName'] = $industryRow['type'];

$industry_array[$industryCount]['dotColour'] = $industryRow['colour'];

$industry_array[$industryCount]['subMinerals'] = $product_array;

 

$industryCount++;

}

return $industry_array;

}

}?>