echoing out in a neatly formatted table?

claire

Hi there!

I have the following code. The data is all there, but it beats me how to get the echoing formatted nicely in a table.
I can't get the different loops to display like this:

surname | portkod
surname | portkod
surname | portkod
....


//search db1 for surnames and apartmentno (Andel)
initdb($db_1);

$sql = mysql_query("SELECT Andel, substring_index(Namn,' ',1) AS surname FROM Uppgifter WHERE Andel>=24 AND Andel <=48 GROUP BY Andel, surname ORDER BY surname ");

while ($row = mysql_fetch_assoc ($sql)) {
	$people[$row['Andel']][] =$row['surname'];
	$andel=$row['Andel'];

//search db2 for 'portkod'variable using aptno from previous search.
		initdb($db_2);

	$result = mysql_query("SELECT Portkod FROM Uppgifter WHERE  Andel=$andel");

		while ($rowc = mysql_fetch_assoc($result)) {
		$portkod[] = $rowc['Portkod'];
		}
}
//echo out surnames, grouped together according to aptno
	foreach($people as $k => $v) {
	$people[$k] = join(', ', $v);
	echo $people[$k];
	}

//echo out the 'portkod'variable
	foreach($portkod as $k=>$v) {
	echo $portkod[$k];
	}

winterbeef

Hello!

I think you can simplify your query by adding the Portkod into the column list, taking out the GROUP BY, and adding a DISTINCT...

Try this:

initdb($db_1);
$buffer = '';
$rs = mysql_query("SELECT DISTINCT Andel, substring_index(Namn,' ',1) AS surname, Portkod FROM Uppgifter WHERE Andel>=24 AND Andel <=48 ORDER BY surname ");

while ($row = mysql_fetch_assoc($rs)) {
	$buffer .= '<tr>';
	$buffer .= '<td>'.$row['Andel'].'</td>';
	$buffer .= '<td>'.$row['surname'].'</td>';
	$buffer .= '<td>'.$row['Portkod'].'</td>';
	$buffer .= '</tr>'."\n";	
}

echo '<table border="1">';
echo $buffer;
echo '</table>';

Hope that Helps!

winterbeef

By the way, I've heard that for performance reasons it's not great to 'accumulate' a big string into a variable like i did with $buffer. Apparently, the memory is re-allocated each time a concatentation - assignment operator is used...

On the other hand, it is easy to read & you have the printable data in a single variable....

Comments, anyone?

claire

The 'portkod-column' is in a different database than the surnames. The only information the two databases have in common is the aptno (andel) That's why I have to run two different queries.

winterbeef

Okay, try this:

initdb($db_1);
$buffer = '';
$rs1 = mysql_query("SELECT DISTINCT Andel, substring_index(Namn,' ',1) AS surname FROM Uppgifter WHERE Andel>=24 AND Andel <=48 ORDER BY surname ");
while ($row1 = mysql_fetch_assoc($rs1)) {

initdb($db_2);
$rs2 = mysql_query("SELECT Portkod FROM Uppgifter WHERE Andel='".$row1['Andel']."'");
while ($row2 = mysql_fetch_assoc($rs2)) {
    $buffer .= '<tr>';
    $buffer .= '<td>'.$row1['Andel'].'</td>';
    $buffer .= '<td>'.$row1['surname'].'</td>';
    $buffer .= '<td>'.$row2['Portkod'].'</td>';
    $buffer .= '</tr>'."\n";
}
}

echo '<table border="1">';
echo $buffer;
echo '</table>';

winterbeef

I wonder...

If the login info is the same, could one perform a JOIN accross databases? If so, the first version of code would work with only a modification to the SQL.

Something like:

select
t1.f1, t1.f2, t2.f1 as t2f2
from
db1.t1
inner join
db2.t2
on db1.t1.col=db2.t2.col

MrAlaska

Originally posted by winterbeef
By the way, I've heard that for performance reasons it's not great to 'accumulate' a big string into a variable like i did with $buffer. Apparently, the memory is re-allocated each time a concatentation - assignment operator is used...

On the other hand, it is easy to read & you have the printable data in a single variable....

Comments, anyone?

I had not considered that aspect, but it makes sense. I suppose you could assign by reference and avoid that problem.

Personally, I like to read the data into a normal array when I am parsing the PHP part of the page, then build my table with a foreach() loop calling on the array when I build the HTML. It makes me feel like I am segregating my code, anyway, and it seems just as easy to read to me.

claire

Originally posted by MrAlaska

Personally, I like to read the data into a normal array when I am parsing the PHP part of the page, then build my table with a foreach() loop calling on the array when I build the HTML. It makes me feel like I am segregating my code, anyway, and it seems just as easy to read to me.

And how do you build your table when you have two parallell foreach() loops? (which is exactly the problem I'm facing)

I can't put one forech loop within the other one...

I can't use join as suggested by Winterbeef (even if that was a good idea) since I'm performing a search across two different databases, not two different tables. At least I haven't found a way to do that in a single query statement.

How do I save the results from the foreach loops so that the arrays $people[$k] and $people[$k] can be called from outside? If I can only reach them from outside the loops it is simple to create the HTML table.

MrAlaska

I haven't dug into your code, really, when I dropped in last time it looked like the nested SQL statement Winterbeef posted would work for you. I'd do it now but I am on my way out the door.

My approach in the case of parallel result sets might be to load the data sets into indexed[] arrays, then use a for() loop instead of a foreach() so you could access the members via the counter indice. For that matter you could put a counter inside the foreach() of one of them to address the other one through, but a for loop would probably be more intuitive for that type of application.

[EDIT=add a little] Another approach would be to make both queries, then inside the foreach of one result set you could call fetch_row from the other result set. Of course, that assumes there will always be a matching row.[/EDIT]

MrAlaska

I am still on the run, but while I braid my hair with one hand I'll
throw some psuedo code at you with the other and maybe what
I said might make more sense. Unless I have gone whacked
again, in which case nothing I say makes sense anyway. Of
course, if you have unequal numbers of rows getting returned,
you need to throw a few more lines at it. And there are other
approaches you can take, this is just the one that works for me
(I think)

mysql_select_database('firstDB');
$sql="select foo, bar from table";
$db1="";
$result=mysql_query($sql);
while($row=mysql_fetch_assoc($result)){
	$db1[]=$row;
}

mysql_select_database('secondDB');
$sql="select foobar from table";
$db2="";
$result=mysql_query($sql);
while($row=mysql_fetch_assoc($result)){
	$db2[]=$row;
}

// build the html
echo "<table>";
for($i=0 , $i< count($db1), $i++){
	echo "<tr>";
	echo "<td>";
	echo $db1[$i]['foo'] 
	echo $db1[$i]['bar']
	echo $db2[$i]['foobar']
	// something like that anyway :)
}

tomhath

Since the "foreign key" between the two selects here is the andel column, why not use that as the first index into the $db1 and $db2 arrays:

while($row=mysql_fetch_assoc($result)){
	$db1[$row['andel']=$row;
}
...

The the for loop at the bottom becomes a foreach on one of the associative arrays (which gives you the key into the other).

claire

I'm sorry, but I'm just not knowledgeable enough to get it right!

It seems as if I either mess up the name column or the 'portkod' column in the table I create.

Winterbeefs second buffer suggestion displays a nice table where the 'portkod'strings are correct but the names wrong (since the vital join syntax found in the foreach statement is left out).
(I don't know how to run the foreach statement within the while loop in order to get the $buffer .= '<td>'.$row['surname'].'</td>'; correct.)

MrAlaska suggestions displays a nice table where the names are correct but not the 'portkod' column.
I'm not quite sure what I've done! If I echo out $portkod within the while loop it only finds the second element!
See: http://www.brfskalen11.org/claire/distinct.php

Here's what the code looks like at the moment. Hopefully someone can do some minor changes to get it right?

initdb($medlemmardb);

$svar = mysql_query("SELECT Andel, substring_index(Namn,' ',1) AS surname FROM Uppgifter WHERE Andel>=24 AND Andel <=48 GROUP BY Andel, surname ORDER BY surname ");

$db1="";
	while($row=mysql_fetch_array($svar)){
		$db1[$row['Andel']]=$row;
		$people[$row['Andel']][] = $row['surname'];
		$andel=$row['Andel'];
	}

initdb($andelardb);

$resultat = mysql_query("SELECT Portkod FROM Uppgifter WHERE  Andel=$andel");
	$db2="";
	while($row=mysql_fetch_array($resultat)){
		$db1[$row['Andel']]=$row;
		$portkod=$row['Portkod'];
	}
echo "<table cellpadding=\"2\" border=\"1\">";
	foreach($people as $k => $v) {
		$people[$k] = join(', ', $v);
		echo "<tr>";
		echo "<td width=\"300\">";
		echo $people[$k];
		echo "</td>"."<td>";
		echo $portkod;
		echo "</td>"."</tr>";
	}
echo "</table>";

tomhath

I can't test this, but here's an attempt (part of the problem you had was $db1 in the second while loop, should've been $db2):

  initdb($medlemmardb);

  $svar = mysql_query("SELECT Andel, substring_index(Namn,' ',1) AS surname FROM Uppgifter WHERE Andel>=24 AND Andel <=48 GROUP BY Andel, surname ORDER BY surname ");

  $people=array();
  while($row=mysql_fetch_array($svar)){
    $people[$row['Andel']][] = $row['surname'];
  }

  initdb($andelardb);

  $resultat = mysql_query("SELECT Portkod FROM Uppgifter WHERE   Andel>=24 AND Andel <=48");

  $portkod=array();
  while($row=mysql_fetch_array($resultat)){
      $portkod[$row['Andel']]=$row['Portkod'];
   }

   echo "<table cellpadding=\"2\" border=\"1\">";

   foreach($people as $and => $nam) {
        echo "<tr>";
        echo "<td width=\"300\">";
        echo $join(', ', $nam);;
        echo "</td>"."<td>";
        echo $portkod[$and];
        echo "</td>"."</tr>";
   }
   echo "</table>";

claire

Hi, tried your code - no output! See same link as before. Could it be something simply overlooked?

tomhath

Change both of the queries to report errors like this:

$svar = mysql_query("...") or die "Query xxx failed " . mysql_error();

Inside the while loops, echo out the fields you are using from $row.

After the while loops, print out the values in the arrays like this:

echo "<PRE>";
print_r($people);
echo "</PRE>";
...
echo "<PRE>";
print_r($portkod);
echo "</PRE>";

claire

Put everything in there now. You can see the results on the same link as before.

Queries OK
Echoes within loops OK
Print_r($people); OK
print_r($portkod)Not OK

and the foreach loop still shows nothing.

MrAlaska

Keying the array on 'Andel' would be cleaner, but it would
require 'Andel' to be a unique key. If 'Andel' means 'primary key'
in your language then it would be the way to go. To get a foreign
key relationship going, I think you will need to do a sub-query
to load 'Portkod' into the array, but it could all be loaded into the
same array. I think so anyway. I just ate a bad bag of popcorn
with too much sugar on it so I coud be all twisted around in my
thinking.

<?php
initdb($medlemmardb);
$svar = mysql_query
	(
	" SELECT Andel, substring_index(Namn,' ',1) AS surname ".
	" FROM Uppgifter ".
	" WHERE Andel>=24 ".
	" AND Andel <=48 ".
	" GROUP BY Andel, surname ".
	" ORDER BY surname "
	) or die (mysql_error());

$db1="";
while($row=mysql_fetch_array($svar, MYSQL_ASSOC)){
	$people[]['Andel']=$row['Andel'];
	$people[]['surname']=$row['surname'];
	//$people[]=$row; // would be the same
}

$i=0;
initdb($andelardb);
foreach ($people as $person){
	$resultat = mysql_query
		(
		" SELECT Portkod ".
		" FROM Uppgifter ".
		" WHERE  Andel='".$person['Andel']."'"
		) or die (mysql_error());
	while($row=mysql_fetch_array($resultat, MYSQL_ASSOC)) {
		$people[$i]['Portkod']=$row['Portkod'];
	}
	$i++;
}

echo "<table cellpadding=\"2\" border=\"1\">";
    foreach($people as $k => $v) {
        $people[$k] = join(', ', $v);
        echo "<tr>";
        echo "<td width=\"300\">";
        echo $people[$k];
        echo "</td>"."</tr>";
    }
echo "</table>";
?>

claire

I'm getting an error msg
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/b/brfskale/www/claire/test3.php on line 32

It's the line
while($row=mysql_fetch_array($svar, MYSQL_ASSOC)){

Andel is not the primary/unique key. It is simply the apartment number.

It's getting very late over here and I have to get some sleep. Will try all good suggestions tomorrow!

Thanks for all help so far!

MrAlaska

I had at least one typo that would have broke the SQL statement. Blame it on the popcorn, this time, except it always happens....

tomhath

Missed a pair of brackets in the second while loop, try this revised revision (still not tested):

  initdb($medlemmardb);

  $svar = mysql_query("SELECT Andel, substring_index(Namn,' ',1) AS surname FROM Uppgifter WHERE Andel>=24 AND Andel <=48 GROUP BY Andel, surname ORDER BY surname ");

  $people=array();
  while($row=mysql_fetch_array($svar)){
    $people[$row['Andel']][] = $row['surname'];
  }

  initdb($andelardb);

  $resultat = mysql_query("SELECT Portkod FROM Uppgifter WHERE   Andel>=24 AND Andel <=48");

   echo "<table cellpadding=\"2\" border=\"1\">";

  while($row=mysql_fetch_array($resultat)){
        echo "<tr>";
        echo "<td width=\"300\">";
        echo $join(', ', $people[$row['Andel']]);
        echo "</td>"."<td>";
        echo $row['Portkod'];
        echo "</td>"."</tr>";
   }
   echo "</table>";