PHP/mySQL query skip repeated results in column header

nullpoint81 · January 13, 2012

Hey all, first post to this site. I'm somewhat new to PHP, so bear with me - this might be an easy question, it might not be. Just looking for a little help.

Basically, I have a query which takes commands from a form through AJAX (day, time, time1). The query executes a wildcard on a table named sip_data, searches for linked $id (so if $id=3, for example in multiple tables, it spits out the $name, $zip in a div).

Here's the code:

<?php
$dbhost = "localhost";
$dbuser = "";
$dbpass = "";
$dbname = "";

//Connect to MySQL Server
$link = mysql_connect($dbhost, $dbuser, $dbpass);

//Select Database
mysql_select_db($dbname) or die(mysql_error());

// Retrieve data from Query String
$id = $_GET['id'];
$name = $_GET['name'];
$zip = $_GET['zip'];
$server_url = $_GET['server_url'];
$day = $_GET['day'];
$time = $_GET['time'];
$time1 = $_GET['time1'];

// Escape User Input to help prevent SQL Injection
$id = mysql_real_escape_string($id);
$name = mysql_real_escape_string($name);
$zip = mysql_real_escape_string($zip);
$server_url = mysql_real_escape_string($server_url);
$day = mysql_real_escape_string($day);
$time = mysql_real_escape_string($time);
$time1 = mysql_real_escape_string($time1);
//build query

$query = "SELECT * FROM $day,sip_data WHERE $day.id=sip_data.id AND $day$time<=>$day$time1 ORDER BY zip ASC";

//Execute query
$qry_result = mysql_query($query) or die(('No Results'));

//Build Result String
while($row = mysql_fetch_array($qry_result))

{
echo "<table id=query_result align=left>";
echo "<tr>";
    if($zip = $row[zip]); 
echo "<th><b>$row[zip]</b></th>";
echo "</tr>";
echo "<tr>";
echo "<td><a href=$row[server_url] rel=ajaxDiv>$row[name]</a></td>";
echo "</tr>";
echo "</table>";
}
echo $display_string;




?>

Basically, my issue with this is that some of the results within the sip_data DB will have zip codes that repeat. The current code prints out each results with the zip code and name - here's an image of a sample result:

What I would like to do is avoid repeating the zip code and just group the results under each zip code, kinda like this:

55408 55412 55423

--------- ------------ ----------

example example example

example example

example

Any help would be greatly appreciated. I feel like I'm so close to the answer, but just need a little guidance. Who knows, I might be way off.

Thanks in advance!

blacknight · January 13, 2012

try this code...

$places = array();
while($row = mysql_fetch_array($qry_result))
{
$places[$row[zip]]['url'] = $row[server_url];
$places[$row[zip]]['name'] = $row[name];
}

echo "<table id=query_result align=left>";
foreach ($places as $place => $d)
{
	echo "<tr>";
	echo "<th><b>$place</b></th>";
	echo "</tr>";
	foreach ($d as $n => $r)
	{
		echo "<tr>";
		echo "<td><a href=$r[url] rel=ajaxDiv>$r[name]</a></td>";
		echo "</tr>";
	}
}

should have the result your looking for

jcbones · January 13, 2012

Blacknight's code will error out. The advice is sound though.

Using the same theory, it should be closer to:

$places = array();
while($row = mysql_fetch_array($qry_result))
{
$places[$row['zip']][$row['server_url']] = $row['name'];
}

echo "<table id=query_result align=left>";
foreach ($places as $place => $d)
{
	echo "<tr>";
	echo "<th><b>$place</b></th>";
	echo "</tr>";
	foreach ($d as $n => $r)
	{
		echo "<tr>";
		echo "<td><a href=$n rel=ajaxDiv>$r</a></td>";
		echo "</tr>";
	}
}

The reason the original will not work is,

 //$n is both url and name, $r is both the value of url and the value of name indexes.
//This will leave you two rows for every value you wanted to show,
//and will error out because $r is NOT an array, so it doesn't have indexes.
foreach ($d as $n => $r)   
	{
		echo "<tr>";
		echo "<td><a href=$r[url] rel=ajaxDiv>$r[name]</a></td>"; 
		echo "</tr>";
	}

blacknight · January 13, 2012

really? i basicly modified a code i already use that works...

in theory it should have prodiced an array like

Array
(
    [55408] => Array
        (
            [0] => Array
                (
                    [url] => a
                    [name] => b
                )

            [1] => Array
                (
                    [url] => c
                    [name] => d
                )

        )

    [55412] => Array
        (
            [0] => Array
                (
                    [url] => e
                    [name] => f
                )

            [1] => Array
                (
                    [url] => g
                    [name] => h
                )

        )

nullpoint81 · January 13, 2012

Blacknight's code will error out. The advice is sound though.

Using the same theory, it should be closer to:

$places = array();
while($row = mysql_fetch_array($qry_result))
{
$places[$row['zip']][$row['server_url']] = $row['name'];
}

echo "<table id=query_result align=left>";
foreach ($places as $place => $d)
{
	echo "<tr>";
	echo "<th><b>$place</b></th>";
	echo "</tr>";
	foreach ($d as $n => $r)
	{
		echo "<tr>";
		echo "<td><a href=$n rel=ajaxDiv>$r</a></td>";
		echo "</tr>";
	}
}

The reason the original will not work is,

 //$n is both url and name, $r is both the value of url and the value of name indexes.
//This will leave you two rows for every value you wanted to show,
//and will error out because $r is NOT an array, so it doesn't have indexes.
foreach ($d as $n => $r)   
	{
		echo "<tr>";
		echo "<td><a href=$r[url] rel=ajaxDiv>$r[name]</a></td>"; 
		echo "</tr>";
	}

Yeah, I just tried both snippets and the last one is closest, only bad part is that it's placing all results into one column. Blacknight's code was truncating the names and giving incorrect urls. But hey, way better than what I had before.

Lemme see if I can figure out a way to start a new column with each $zip.

nullpoint81 · January 13, 2012

Ok, one other thing I noticed is that the <table> doesn't close, so I threw an

echo "</table">;

In at the end and it's pushing everything horizontal now, which is great except it isn't including everything within the html table.

I'm thinking there must be some simple code that will allow the script to dump all of it's results, then add the

echo "</table">;

in at the end after everything is finished.

Thanks for all the help guys, you've really taught me something today.

laffin · January 14, 2012

I did this code to demonstrate for someone else

<?php

//header('Content-Type: text/plain');

$fnames='Norman,Bree,Savannah,Tatyana,Victor,Haley,Shea,Ivana,Frances,Hanae';
$snames='Walter,Floyd,Becker,Sears,Robertson,Barnes,Hurst,Barr,Gross,Cotton';
$zips='91752,10798,56244,68925,04025,48726,57696,20589,84348,19588';
$fnames=explode(',',$fnames);
$snames=explode(',',$snames);
$zips=explode(',',$zips);

// Build sample DB
$sdb=sqlite_open(':memory:');
$count=rand(21,35);
sqlite_exec($sdb,'CREATE TABLE sample ( id INTEGER PRIMARY KEY, name VARCHAR( 40 ), surname VARCHAR( 40 ), zip  NUMERIC );');
for($i=0;$i<$count;$i++)
{
  $f=$fnames[rand(0,count($fnames)-1)];
  $s=$snames[rand(0,count($snames)-1)];
  $z=$zips[rand(0,count($zips)-1)];
  sqlite_exec($sdb,"INSERT INTO sample (name,surname,zip) VALUES ('$f','$s',$z);");
}

// Code of interest starts here
// Get the max rows and set up our headers
$res=sqlite_query($sdb,'SELECT zip,count(*) FROM sample GROUP BY zip ORDER BY zip;');
$max=0;
$headers=array();
while($row=sqlite_fetch_array($res,SQLITE_NUM))
{
  $max=max($max,$row[1]);
  $headers[]=$row[0];
}
// Retrieve the records, and place them in Column arrays
$res=sqlite_query($sdb,'SELECT * FROM sample ORDER BY zip');
$crow=0;
while($row=sqlite_fetch_array($res,SQLITE_ASSOC))
{
  if($row['zip']!=$headers[$crow])
  {
    $crow++;
  }
  $Cols[$crow][]=$row;
}

// Format data
$Rows=array();
for($i=0;$i<$max;$i++)
{
  foreach($Cols as $col)
  {
    if(isset($col[$i]))
      $row="<a href=\"{$col[$i]['id']}\">{$col[$i]['name']} {$col[$i]['surname']}</a>";
    else
       $row=' ';
    $Rows[$i][]=$row;
  }
}

// Display Table
echo '<table><tr>';
foreach($headers as $header)
{
  echo "<td>$header</td>";
}
echo '</tr>';
foreach($Rows as $row)
{
  echo '<tr>';
  foreach($row as $cell)
  {
    echo "<td>$cell</td>";
  }
  echo '</tr>';
}
echo '</table>';

nullpoint81 · January 14, 2012

Haha, that was actually me on a different forum:

http://www.phphelp.com/forum/index.php/topic,14978.0.html

That's pretty funny!

jcbones actually provided a snippet closest to what I am looking for, but I'm still trying to figure out a way to keep the loop going until all the information has been inserted, THEN insert the echo "</table>"; to end the table.

blacknight · January 14, 2012

place your </table at the end of the first foreach loop

jcbones · January 14, 2012

really? i basicly modified a code i already use that works...

in theory it should have prodiced an array like

Array
(
    [55408] => Array
        (
            [0] => Array
                (
                    [url] => a
                    [name] => b
                )

            [1] => Array
                (
                    [url] => c
                    [name] => d
                )

        )

    [55412] => Array
        (
            [0] => Array
                (
                    [url] => e
                    [name] => f
                )

            [1] => Array
                (
                    [url] => g
                    [name] => h
                )

        )

No, it will produce an array that looks like:

]Array
(
    [55408] => Array
        (
           
                    [url] => a
                    [name] => b
               

    [55412] => Array
        (
                    [url] => e
                    [name] => f
        )

Because the key of zipcode can only hold one index name url, and one index name name. In order to build the array like you expected it to, you would have to build it as.

<?php
for($i = 0; $i < 10; $i++) {
$places[$zip['code']][$i]['url'] = $row['url'];
$places[$zip['code']][$i]['name'] = $row['name'];
}

That way you create the additional keys needed.

nullpoint81 · January 15, 2012

place your </table at the end of the first foreach loop

I've tried that, and it basically just shortens the loop to one iteration, then dumps everything else outside of the table.

PFMaBiSmAd · January 15, 2012

The closing </table> tag goes after the end of the 'outside' loop. The point of using php to produce and output HTML to the browser, is that the HTML must be valid and be what you want it to be, the same as if you had written it by hand.

If you do a 'view source' of the page in your browser, is the HTML what you expect?

nullpoint81 · January 15, 2012

The closing </table> tag goes after the end of the 'outside' loop. The point of using php to produce and output HTML to the browser, is that the HTML must be valid and be what you want it to be, the same as if you had written it by hand.

If you do a 'view source' of the page in your browser, is the HTML what you expect?

Because of the way I have the site built, I'm not able to view source on that particular div. But yeah, I've tried moving the </table> around all over the place, tried adding a <br> in here or there - it's just baffling me.

nullpoint81 · January 15, 2012

Ok, I just figured it out...such a stupid thing.

The <table> began outside of the "outer" loop - changing the starting position of that tag actually gives me the result I was looking for.

Just for future reference for other newbs, here's the code change:

$places = array();
while($row = mysql_fetch_array($qry_result))
{
$places[$row['zip']][$row['server_url']] = $row['name'];
}

echo "<table id=query_result align=left>";
foreach ($places as $place => $d)
{
	echo "<tr>";
	echo "<th>$place</th>";
	echo "</tr>";
	foreach ($d as $n => $r)
	{
	echo "<tr>";
	echo "<td><a href=$n rel=ajaxDiv>$r</a></td>";
	echo "</tr>";

	}

}

echo "</table>";	

?>

Produced this result:

$places = array();
while($row = mysql_fetch_array($qry_result))
{
$places[$row['zip']][$row['server_url']] = $row['name'];
}


foreach ($places as $place => $d)
{
	echo "<table id=query_result align=left>";
	echo "<tr>";
	echo "<th>$place</th>";
	echo "</tr>";
	foreach ($d as $n => $r)
	{
	echo "<tr>";
	echo "<td><a href=$n rel=ajaxDiv>$r</a></td>";
	echo "</tr>";

	}

}

echo "</table>";

Produced this result:

And yes, technically it is producing several tables instead of one with different columns, but that's totally fine actually. I'm cool with that.

Thanks for all the help everyone, will mark this as "solved".

Sign In

PHP/mySQL query skip repeated results in column header

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information