manipulating database result set

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Mehnaz
Forum Newbie
Posts: 20
Joined: Mon Jun 02, 2008 7:49 pm

manipulating database result set

Post 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
Cryophallion
Forum Newbie
Posts: 10
Joined: Fri Apr 24, 2009 9:05 am

Re: manipulating database result set

Post 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.
Post Reply