Loop Throught Sql Results And Don't Print Repeated Data

[alapimba]

Hello

 

I have a mysql database that looks like this:

 

id_name - phone number - email

1 - 927567348 - lalala@gmail.com

1 - 965437823 - lalala@gmail.com

1 - 963423495 - lalala@gmail.com

2 - 4357986345 - bubu@hotmail.com

2 - 4395874355 - bubu@hotmail.com

3 - 3459875234 - ghjfgh@mail.com

 

and now i want to print it like this:

 

1

927567348

965437823

963423495

lalala@gmail.com

 

2

4357986345

4395874355

bubu@hotmail.com

 

3

3459875234

ghjfgh@mail.com

 

 

I don't know how do this.

if i didn't needed to print the email in the end it would be "simple"

 

like this:

<?php
$id_name = $row_rs_livros['id_name'];
while ($row_rs_livros = mysql_fetch_assoc($rs_livros)) {
 if ($iddolivro != $row_rs_livros['id_name']) {
  echo $row_rs_livros['id_name'];
  }
 echo $row_rs_livros['phonenumber'];
 $iddolivro = $row_rs_livros['id_li'];
 }
?>

 

But in this case i'm lost.

 

Anyone can help me please?

 

Thanks

[mrMarcus]

These kinds of issues are avoidable with proper table setup.

 

`id_name` and `email` should not be repeated as you have shown above. You need to normalize your database.

 

TABLE `users`

 

`id_name`, `email`

 

TABLE `phone`

 

`phone_number`

 

This is just pseudo setup as I don't know your overall schema. But, for example, if you're using `email` as the username for a user, then that value would have to be unique.

 

Your best to separate the data that can have multiple variances into a separate table and create a relationship table and/or a simple JOIN.

 

Sorry if that comes off as confusing, but table setup is really the best solution to your problem from what I can see.

[alapimba]

These kinds of issues are avoidable with proper table setup.

 

`id_name` and `email` should not be repeated as you have shown above. You need to normalize your database.

 

TABLE `users`

 

`id_name`, `email`

 

TABLE `phone`

 

`phone_number`

 

This is just pseudo setup as I don't know your overall schema. But, for example, if you're using `email` as the username for a user, then that value would have to be unique.

 

Your best to separate the data that can have multiple variances into a separate table and create a relationship table and/or a simple JOIN.

 

Sorry if that comes off as confusing, but table setup is really the best solution to your problem from what I can see.

 

 

I understand a bit what you said but at the moment my tables are already so big that it will mess with other stuff on the site if i decided to change it now. (it's not a 3 column table as the example that i show...)

in reallity it's 3 tables with inner joins that gives that kind of output.

 

i have one table with id_username and e-mail

i have another with id_username_phone and phone number

and then i have a 3rd table with id_username and id_username_phone to connect everything.

 

the query that i use gives that kind of result.

 

Now i need to organize it with php... theres no way?

[Barand]

Your three tables I created as

username

id_username | email

 

username_phone

id_username_phone | phone

 

email_phone

id_username | id_username_phone

 

This query should make it easy to get the format you want

 

SELECT id_username, email,
GROUP_CONCAT(phone SEPARATOR '<br>') as phones
FROM username
INNER JOIN email_phone USING (id_username)
INNER JOIN username_phone USING (id_username_phone)
GROUP BY id_username;

 

RESULTS

+-------------+------------------+-------------------------------------+
| id_username | email		 | phones						 |
+-------------+------------------+-------------------------------------+
|		 1 | lalala@gmail.com | 875641239<br>689745211<br>987654321 |
|		 2 | buba@hotmail.com | 875641239<br>689745211<br>987654321 |
|		 3 | ghjfgh@mail.com | 987654321<br>875641239<br>689745211 |
+-------------+------------------+-------------------------------------+

Edited November 22, 2012 by Barand

[jazzman1]

Barand, if he has only one single table to get the same results, he has to use self-join, right or..... am I wrong?

[Barand]

Jazzman,

 

It's 3 tables - re-read reply #3

[alapimba]

Hi

 

I just realized that my example was too simple for my final needs :-\

 

i understand that example above.

 

but in my work i need it a bit more complex..

 

imagine that instead of phone numbers only i have area code and phone numbers (in separate columns).

 

How can i print it like:

name<br>

area code - phone number <br>

area code - phone number2 <br>

area code - phone number3 <br>

e-mail

 

In my real example this tables have books and authors.

 

some books have more then 1 author..

 

and i need to print it like this:

 

<div id="contentdiv">
<?php
$i = 0;
do {
?>
<?php				
$i = $i + 1;
?>
		 <div id="contentdiv<?php echo $row_rs_livros2['id_li']; ?>" <?php if ($i <= 1) { echo "style='display: block;'"; } else { echo "style='display: none;'"; }?>>
			 <a href="livros.php?id_li=<?php echo $row_rs_livros2['id_li']; ?>" class="titulos_vermelho_livros"><?php echo $row_rs_livros2['livro']; ?></a><br/><br/>


			 <?php //here i'll have more then 1 author sometimes ?>
			 <span class="texto"><a href="autores.php?id=<?php echo $row_rs_livros2['id']; ?>" class="titulos_livros"><?php echo $row_rs_livros2['nome']; ?><?php echo $row_rs_livros2['apelido']; ?></a><br>



 <?php	
 mb_internal_encoding("UTF-8");
 $str = $row_rs_livros2['texto'];
 $limit = 300; //Specify the length of the new substring
 if (substr($str, $limit, 1) != ' ' && ($l = mb_strrpos(mb_substr($str, 0, $limit), ' '))) {
	 echo mb_substr($str, 0, $l);
 } else {
	 echo mb_substr($str, 0, $limit);
 }
 ?>(...)<br />

			 <a href="livros.php?id_li=<?php echo $row_rs_livros2['id_li']; ?>" class="style3">ver mais »</a></span></div>
			 <?php } while ($row_rs_livros2 = mysql_fetch_assoc($rs_livros2)); ?>
</div>
</div>

 

at the moment my query is still this:

SELECT autores.id, autores.nome, autores.apelido, livros.id_li, livros.texto, livros.pdfs, livros.foto, livros.foto_grande, livros.livro, livros.ordem, ids.id_livro FROM autores INNER JOIN ids ON autores.id=ids.id_autores INNER JOIN livros ON ids.id_livro=livros.id_li WHERE estado = 'on' ORDER BY livros.ordem

[PFMaBiSmAd]

Your problem is actually simple. After you retrieve the rows you want in the order that you want them, you simply output the data the way you want it when you iterate over the rows in the result set. Example code showing how you can do this -

 

<?php
// sample data to simulate the rows a query produced
$data[] = array('id_name'=>1,'phone number'=>'927567348','email'=>'lalala@gmail.com');
$data[] = array('id_name'=>1,'phone number'=>'965437823','email'=>'lalala@gmail.com');
$data[] = array('id_name'=>1,'phone number'=>'963423495','email'=>'lalala@gmail.com');
$data[] = array('id_name'=>2,'phone number'=>'4357986345','email'=>'bubu@hotmail.com');
$data[] = array('id_name'=>2,'phone number'=>'4395874355','email'=>'bubu@hotmail.com');
$data[] = array('id_name'=>3,'phone number'=>'3459875234','email'=>'ghjfgh@mail.com');


// form and execute your query that gets the rows you want in the order that you want them here...


$last_heading = null; // remember the last heading, initialize to a value that will never appear in the data

//while($row = mysql_fetch_assoc($result)){ // loop over the actual rows from your query here...
foreach($data as $row){ // loop over the sample data, in place of the traditional while(){} loop on the line above
   // detect a change in the heading
   if($last_heading != $row['id_name']){
       // heading change, either a new one or the first one
       if($last_heading != null){
           // not the first section, close out the previous section here ...
           echo $last_email . '<br /><br />';
       }
       // start a new section here...
       echo $row['id_name'] . '<br />';

       // remember values from the current row
       $last_heading = $row['id_name']; // remember the heading
       $last_email = $row['email']; // remember the email since it will have changed to the next value at the time you need the last value
   }
   // output the data under each heading here...
   echo $row['phone number'] . '<br />';
}
// close out the last section here, if any...
if($last_heading != null){
   echo $last_email . '<br /><br />';
}

[alapimba]

nice seems simple.

i'll try to use on my real data that it's a bit more complex.

 

the array can be generated on a while($row = mysql_fetch_assoc($result)) loop?

[PFMaBiSmAd]

The array of data was just so the demonstration would work. See the comments in the code -

//while($row = mysql_fetch_assoc($result)){ // loop over the actual rows from your query here...

foreach($data as $row){ // loop over the sample data, in place of the traditional while(){} loop on the line above

[alapimba]

everything worked perfect for my needs.

Thanks a lot for your help