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
manipulating database result set
Moderator: General Moderators
-
Cryophallion
- Forum Newbie
- Posts: 10
- Joined: Fri Apr 24, 2009 9:05 am
Re: manipulating database result set
Pseudo code, but hopefully you get the idea:
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.
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 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.