Page 1 of 1

manipulating database result set

Posted: Thu Apr 23, 2009 8:44 pm
by Mehnaz
Hi

I have a table with three columns like this

id Instance Alias
1 Adam John
2 Adam Smith
3 Dave Black
4 Dave Peter

I want to join each 'Instance' with their 'Alias' as follows (Number of Alias for each Instance vary)
Adam John Smith
Dave Black Peter


I am getting databse reults as follows. But stuck to retrieve as per required format.

// Code
$result = mysql_query("SELECT * FROM chv_term_excel");

while ($row = mysql_fetch_array($result)) {


echo "ID: ".$row['id'].", Instance:".$row['Instance'] .", Alias:".$row['Alias']."<br/>";

}

Pls help

Thanks

Mehnaz

Re: manipulating database result set

Posted: Fri Apr 24, 2009 12:21 pm
by Cryophallion
Pseudo code, but hopefully you get the idea:

Code: Select all

 
$sql = 'SELECT DISTINCT Instance FROM table;';
$instances = mysql_query($sql, $conn);
while ($instance = mysql_fetch_array($instances){
  $sql = "SELECT name FROM table WHERE instance = {$instance};";
  $names = mysql_query($sql, $conn);
  echo $instance . ' ';
  while($name = $mysql_fetch_array($names)){
    echo $name . ' ';
  }
  echo " \n";
}
 
You could also use a Self join here, but I think this is likely an easier explanation.

You first get all the distinct values from one column, then find all the matching values from the other column.

This may be a really good place for using relational tables, so you don't have a ton of entries with the same name, since one mistype will make it more difficult for you. I would do some research on joins and relational tables, as the above is more database intensive, but I was trying to give you an idea of the thought process.