Manipulating a MySQL Resultset

[Gropr]

I have a resultset obtained from a MySQL database which contains all the information I want. 

I now want to reformat this data, and need some help.

 

My resultset is a list of people's telephone numbers.  Most people have more than one number,

and at the moment, these appear as separate rows in my resultset, with the person's name repeating on every line.  I want to concatenate the telephone numbers so that they appear in a single row with the persons name.  So, somehow, I need to check the person_id of each

entry, compare it with the last one, etc., etc.

 

What is the best way of doing this ?

 

Thanks for all your help.

[litebearer]

show us your code thus far

[Gropr]

This is the section of code I am trying to develop.  It works as far as the 'for' statement, then, obviously, I have incomplete code following that.

 

Not sure how to address the records in the resultset, and the array values.  I'm trying to concatenate the multiple 'info' for a person, eg.,

Mobile: 07777 777 777 | Office: 0123 456 789 | Home: 0123 456 788

which at present appear as separate records in the resultset.

 

if($nbrows>0){
    while($rec = mysql_fetch_array($result)){
            // concatenate info_title and info fields, eg  Mobile: 07777 777 777
      $rec['info']=($rec['info_title'].': '.$rec['info']);
      $arr[] = $rec;	  
    }
for (i=2; i = $nbrows+1; $nbrows +1){
	if i[person_id] = (i-1)[person_id] {
		$rec['info'] = $rec['info'] . ' | ' . $rec['info']

	} // if person_id = person_id
}  // for row counter

    $jsonresult = JEncode($arr);
    echo '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
  } else {
    echo '({"total":"0", "results":""})';
  }
}                 // function getList

 

Thanks again.

[jcbones]

What is your query.  You should be able to use group by, or maybe distinct.

[sasa]

try

<?php
if($nbrows>0){
    while($rec = mysql_fetch_array($result)){
            // concatenate info_title and info fields, eg  Mobile: 07777 777 777
      $rec['info']=($rec['info_title'].': '.$rec['info']);
      $arr[$rec['person_id']][] = $rec['info'];	  
    }
foreach ($arr as $key => $value){
	$arr[$key] = implode(' | ', $value);
}

    $jsonresult = JEncode($arr);
    echo '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
  } else {
    echo '({"total":"0", "results":""})';
  }
             // function getList

?>

[Gropr]

Thanks sasa,

 

That did what you thought it would, but unfortunately, I lost my lastname and firstname.  I've tried moving the lines about a bit, but no luck.

 

My query is:-

$query = "SELECT dep_people.person_id, dep_people.lastname, dep_people.firstname, dep_info.info_title, dep_info.info

            FROM dep_people

            LEFT JOIN dep_info

            ON dep_info.person_id = dep_people.person_id";

 

I want to see lastname, firstname, then all the info titles and values, all on one line.

 

As jcbones suggested, I had thought of using the query, with GROUP_CONCAT, but the query became even more complicated than the PHP, and I was unsure about how to develop it.

 

Thanks again for any help.

[sasa]

<?php
if($nbrows>0){
    while($rec = mysql_fetch_array($result)){
            // concatenate info_title and info fields, eg  Mobile: 07777 777 777
      $rec['info']=($rec['info_title'].': '.$rec['info']);
      $arr[$rec['person_id']][] = $rec['info'];	
      $names[$rec['person_id']] = $rec['firstname'] . ' | ' . $rec['lastname'] ;
    }
foreach ($arr as $key => $value){
	$arr[$key] = $names[$key] . ' | ' . implode(' | ', $value);
}

    $jsonresult = JEncode($arr);
    echo '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
  } else {
    echo '({"total":"0", "results":""})';
  }
             // function getList

?>

[Gropr]

Sasa,

Thanks once again. Your code performed exactly as expected.  Although I wasn’t thinking of having the complete line (name and contact information) as a string, I can use this just as effectively.

 

I am really grateful for your help here, but I would like to be able to improve my own ability in this area.  I’m interested to know your thought process when writing a small piece of code to manipulate an array such as this.  I find arrays quite daunting, but I realise it is essential that I get to grips with them, and a brief explanation from you would help.

Thanks again for your help.

 

[sasa]

look comments

<?php
if($nbrows>0){
    while($rec = mysql_fetch_array($result)){
            // concatenate info_title and info fields, eg  Mobile: 07777 777 777
      $rec['info']=($rec['info_title'].': '.$rec['info']); //your code
      $arr[$rec['person_id']][] = $rec['info']; // make 2-dim array, 1st key is primary key for table
      $names[$rec['person_id']] = $rec['firstname'] . ' | ' . $rec['lastname'] ; //build 1-dim array key is same as in  $arr array
    }
foreach ($arr as $key => $value){ //combine two arrays, in $key is 'person_id' (1st key of array), and in $value is array of 'info'
                // convert array $value to string and concat it with $names with same key
	$arr[$key] = $names[$key] . ' | ' . implode(' | ', $value);
}
// your code	
    $jsonresult = JEncode($arr);
    echo '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
  } else {
    echo '({"total":"0", "results":""})';
  }
             // function getList

?>

[Gropr]

Sasa,

Thanks very much for your comments added to the code.  I'll now study this, and I'm sure it will help me.

Topic closed !!