Grouping data from MySQL with PHP - Newbie question.

giobbi · #1 03-Feb-2004, 09:53

I have a working .php that displays data from fields company, name and phone like this:

Company name phone
Company name phone
Company name phone
Company name phone
Company name phone
Company name phone
...

How do i group them into this:

Company 1
name phone
name phone
name phone
name phone
name phone

Company 2
name phone
name phone
name phone
name phone

Company 3
name phone
...

The database contains a couple of companys with employees that would like to display in a nice fashion.

Thanks for helping out.

JdS · #2 03-Feb-2004, 14:10

giobbi, let's see some code... it's quite easy to do; even easier to explain if we have the code snippet to refer to.

giobbi · #3 03-Feb-2004, 15:40

Here comes my "hi-tech" code!

PHP Code:


<?php
$conn = mysql_connect("localhost", "root", "xxx");

mysql_select_db("receptionen",$conn);

$sql = "select * from emosms order by foretag ASC, namn ASC";

$result = mysql_query($sql, $conn) or die(mysql_error());

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

$namn = $newArray['namn'];

$mobil = $newArray['mobil'];

$foretag = $newArray['foretag'];

echo "
<table border=0 cellspacing=0 cellpadding=0> 
<tr> 
<td width=50><span class=style6><text=#333333><strong> $foretag </td>
<td width=170><span class=style6> $namn </td>
<td width=220><a href=\"mailto:$mobil\"><span class=style5> $mobil</a></td>
</tr>
</table>";

}
?>

The arrays are in swedish if you are wondering.. ; )

Thanks.

JdS · #4 04-Feb-2004, 04:42

About your PHP code, the HTML bits... it seems like it's displaying a TABLE for each row of data. I personally would not do it like that but it's your code and you probably have a good reason for doing it like that.

So my example code below (including suggested changes) is reproduced in a similar fashion i.e. each row in it's own TABLE.

Please also refer to the PHP comments inside the example code below.

PHP Code:


<?php
// .. other PHP code...

$result = mysql_query( $sql, $conn ) or die(mysql_error());

// initialise $foretag
$foretag = '';

while( $newArray = mysql_fetch_array($result) )
{
  $namn  = $newArray['namn'];
  $mobil = $newArray['mobil'];
  /* we don't really need this line anymore... -> $foretag = $newArray['foretag']; */

  // on each WHILE loop, check to see if $foretag and $newArray['foretag'] is NOT identical
  if( $foretag !== $newArray['foretag'] )
  {
    echo  "\t<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\r\n".
          "\t\t<tr>\r\n".
          // added a style attribute to control the display of the $foretag
          "\t\t\t<td width=\"390\"><span class=\"style6\" style=\"color:#333333;font-weight:bold;\">$foretag</span></td>\r\n".
          "\t\t</tr>\r\n".
          "\t</table>\r\n";
    // we add the COMPANY name to the $foretag so if it's in the next loop, the test will fail and none of this
    // will be echo'ed.
    $foretag  =  $newArray['foretag'];
  }

  echo  "\t<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\r\n".
        "\t\t<tr>\r\n".
        "\t\t\t<td width=\"170\"><span class=\"style6\">$namn</span></td>\r\n".
        // 'mailto:' for a phone number?
        "\t\t\t<td width=\"220\"><a href=\"mailto:$mobil\"><span class=\"style5\">$mobil</span></a></td>\r\n".
        "\t\t</tr>\r\n".
        "\t</table>\r\n";
}

// .. other PHP code...
?>

giobbi · #5 04-Feb-2004, 05:35

Many thanks for the reply, i will try it to night. Tables right, the reason is to have some control over the layout. I want straight edges in the colums and some space. Is there any other way to do this? Inserting tables in php-code is real pain i think.

Thx.

giobbi · #6 04-Feb-2004, 07:20

Work great except for foretag comes below at the end of the column like this:

name phone
name phone
Company

name phone
name phone
Company 2

...

Instead of reverse:
Company
name phone

Company 2
name phone
..

Tried some copy paste the code foretag above the namn and mobil but that wasnt any good idea.. ; )

Saw ur note on "mailto: phone number?" This is really smart (at least i think so

). I put the users mobile with an emailadress and make it klickable on theweb so it would adress itself in an email like this:

0733788998@tele2.sms.se

This will send the text i write in an email to our mobile service provider and convert to an sms (Short Message Service) and send it to the mobile phone with the phonenumber in front of the @. Free of charge.

Simple enought for my users to use..

JdS · #7 04-Feb-2004, 07:40

Yes, I thought as much... but since I didn't see the string "@some_example_domain.com" I was a bit confused.

About the company name appearing below; this is impossible, just looking at the flow of the script. So the first test must be failing...

Did you notice that I added a line here? :

PHP Code:


<?php
// .. other PHP code...

$result = mysql_query( $sql, $conn ) or die(mysql_error());

// initialise $foretag
$foretag = '';  // <--  ADDED LINE HERE
?>

giobbi · #8 04-Feb-2004, 08:04

I saw that and put that line in but should i put anything in the = ''? If so what?

JdS · #9 04-Feb-2004, 08:09

No nothing should be there... it's just that the variable $foretag must be created before the loop starts... so the comparing /test works as planned.

Finally, if all else fails, are you certain that the Company Name (appearing below) matches (or belongs to) the phone numbers above? It might just be that the FIRST Company Name is missing - do you see what I am saying?

giobbi · #10 04-Feb-2004, 09:04

Aah - ok. I'll check my database and see if everything is ok there.

Thx!

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Reliable Hosting $2-$8/month: PHP, MySQL, FrontPage, CGI, SSH, FTP, POP3, Web Mail	fcolor	Web Hosting Advertisements & Offers	0	04-Nov-2003 06:03
Automate a data change php form	mjfmn	MySQL / PHP Forum	4	20-Oct-2003 09:37
FREE 25 MB, No Ads, Control Panel, ASP, ColdFusion, PHP, MySQL, Access Hosting	rkmails	Free Web Hosting	0	08-Sep-2003 05:49
[script] Password Protect web pages/site using PHP and MySQL	JdS	PHP Code Library	0	23-Jul-2003 10:02
Windows: From only £20p/y,Linux: from $10p/m. ASP, ASP.NET, PHP, Free MySQL, +More	EyotaHosts	Web Hosting Advertisements & Offers	0	28-Jun-2003 13:54