PHP query formatting

General PHP Help
#1

Hey guys,

I’m new to the forum and somewhat new to PHP/SQL programming. I have a question and code snippet I’d like to get some help with.

Here’s the code:

[code]<?php
$dbhost = “localhost”;
$dbuser = “username”;
$dbpass = “pass”;
$dbname = “db_table”;

//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’];
$phone = $_GET[‘phone’];
$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);
$phone = mysql_real_escape_string($phone);
$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”;

//Execute query

$qry_result = mysql_query($query) or die((‘No Results’));

if ( mysql_query( $query ) > 0 )
{
}
else
{
echo ‘No Result’;
}

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

$display_string .= “

”;
$display_string .= “”;
$display_string .= “”;
$display_string .= “”;
$display_string .= “”;
$display_string .= “”;
$display_string .= “”;
$display_string .= “
$row[zip]
$row[name]
”;
}
echo $display_string;
?>
[/code]

Basically, this code will produce a table that repeats cells horizontally with data from the DB.

What I would like to do is set it so: if the $zip variable is repeated or referenced more than once in the $qry_result, the results will all appear in the same column and not repeat the same zip code twice.

Gotta head to work, but thanks in advance! Will check in again after work.

#2

I took a look at this and I am wondering a few things, is there more than 1 result that the query brings? like does your select statement yield multiple results? if so, it looks like you’re recreating the table with every iteration of your while loop, a workaround would be to move the table start and end tags outside the loop like this:
[php] //Build Result String
$display_string = “

”; // remove the . from .= to avoid a $display_string does not exist error
while($row = mysql_fetch_array($qry_result)){
$display_string .= “”;
$display_string .= “”;
$display_string .= “”;
$display_string .= “”;
$display_string .= “”;
$display_string .= “”;
}
$display_string .= “
$row[zip]
$row[name]
”;
echo $display_string;[/php]
alternatively if you’re not sure how many results are being output you can do this
[php]
print $query;
[/php]
and see how that outputs…
if you need anymore info or clarification let me know :slight_smile:
#3

I think he’s trying to prevent a number of repetitive queries.
Which in case you have to save the db rows, and format it to a table structure for each column.

[code]<?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);”);
}
$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];
}
$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;
}

$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’]}";
else
$row=’ ';
$Rows[$i][]=$row;
}
}

echo ‘

’;
foreach($headers as $header)
{
echo “”;
}
echo ‘’;
foreach($Rows as $row)
{
echo ‘’;
foreach($row as $cell)
{
echo “”;
}
echo ‘’;
}
echo ‘
$header
$cell
’;[/code]
#4

Sorry for the delay in my reply, but thanks for the responses.

Yeah, basically the SELECT query will yield multiple results - eventually hundreds, maybe more. Each result will be sorted/displayed based on zip code (hence the $zip variable). The way it is right now is sort of stupid though, here’s an example:

I would like results with the same zip code to fall under…well, the same zip code. I already tried changing around the location of where the loop executes, and it hasn’t been successful thus far.

Hope this helps.

#5

Sorry, for some reason the site wouldn’t let me link the url…

#6

Just an additional followup - Thanks Laffin for the code snippet, it works awesome! I still have to go through and reformat things, but you nailed it on the first try. Thank you!

#7

Glad to here it. Doing something like this can be a bit tricky, as you go from a database tabular structure to a columnar format. I think I went with 3 ideas before this one, even tho they worked, the code was very complex.
This final version was simple, so made it ideal to learn how it all worked. The SQL queries were key in its implementation.

#8

Upon further review, this is not exactly what I’m looking for. It’s close, but I noticed that all the variables have been declared that are in use, while a query will be used to create the variables.

Close, but not quite there. Any suggestions?

#9

The database is built on the fly using sqlite, after the database is built, the variables are no longer used. and relies only on the database and queries. take a closer look at the code.

#10

I guess what I’m saying is that two parts confuse me.

The first:

$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);

Let’s say I have a row in my sip_data table that is $zip. Each $id within the sip_data table might have a different zip code ($zip), but some of them might be the same. To me, this code appears to declare all of those zip codes within the code, where as the way my code is currently built just returns the $zip row from sip_data.

The other thing:

$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);");
}
$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))

The names are also declared ($fnames, $snames), and when the query is executed the names are randomized into each $zips column. The cool part is that the $zips are sorted from lowest-to-highest, so that’s fine.

I guess I’m having problems visualizing a way to take my current data and plot it into this code without declaring everything to be plotted.

Again, thanks for all the help. Any suggestions are welcome.

#11

I forgot to add, I feel like my current code is pretty close to what would work (but I’ll admit, I may be totally off).

It seems like an if…else or foreach statement for repeated $zip, or some way to skip the re-echoing of them in the results while retaining the name and link would be ideal.

#12

You should really take time to examine the code and how it works. The two excerpts you posted belonged to building the sample database, which is entirely irrelevant to the code that builds a columnar layout.

Sponsor our Newsletter | Privacy Policy | Terms of Service