Page 1 of 1
displaying random mysql query and data
Posted: Tue Jul 08, 2003 3:03 pm
by staypufinpc
I've used php to display mysql data in tables without a problem, but for the most part have done it using $info=mysql_fetch_array($result) and then printing the desired $info[field] when and where I want it.
I now have a textbox that I'd like the user to be able to type in any query on a certain table (stats) and print a table much like the one produced by mysql itself (the one you get when using mysql in the terminal). That is, I'm not going to know the name of each field because the random nature of the query and therefore can't really use the method I've been using to display information. Is there a simple way to do this and to print the field names at the top of each table (just like what's done in the mysql terminal)? I supposed I could use some form of mysql_fetch_row instead of mysql_fetch_array, but I'm not quite sure how to get the field names out of it? Would mysql_fetch_field do it?
clarification
Posted: Tue Jul 08, 2003 3:12 pm
by staypufinpc
what I'm trying to do is like what is done in phpMyAdmin when you click on SQL and are allowed to type in your own sql statement.
showing mysql data and tables without knowing the content
Posted: Wed Jul 09, 2003 9:05 am
by staypufinpc
I know no one's answered but I've figured it out and thought I'd include the code for those who're curious about this in the future. The following code is used to generate a table full of mysql data from any query. For those of you who may copy or mimic this code, bear in mind that I didn't inlcude a mysql_connect in this code because it was already added elsewhere. Just make sure you connect to a database before executing this code. The html is mixed in with the code so you can see how I put the info into a table:
Code: Select all
//strip the query of unwanted slashes and echo it so the user can see
//what query was run
$query=stripslashes($query);
echo $query;
$result=mysql_query($query) or die($query. "<p>". mysql_error());
//get the number of fields and the number of rows returned per query
$fnum=mysql_num_fields($result);
$rnum=mysql_num_rows($result);
//start the table
echo "<table class=main><thead><tr>";
//generate a list of headings
for ($x=0; $x<$fnum; $x++)
{
echo "<th>".mysql_field_name($result, $x)."</td>";
}
echo "</thead>";
//this is the first part of something to alternate row color
$color=ffffff;
//generate each row of info
for ($n=0; $n<$rnum; $n++)
{
$info=mysql_fetch_row($result);
echo "<tbody bgcolor=$color><tr>";
//generate each individual field of information
for ($i=0; $i<$fnum; $i++)
{
$field_name=mysql_field_name($result, $i);
//if the field is a date field, format it so it's readable
//(it's a UNIX timestamp initially)
if ($field_name=='date')
{
$date=date("m/d/y", $infoї$i]);
echo "<td width=10%>$date</td>";
}
else
echo "<td>".$infoї$i]."</td>";
}
//this is the rest of the row alternating color thing
if ($color==ffffff)
$color=dddddd;
else
$color=ffffff;
echo "</tr></tbody>";
}
echo "</table>";
I hope this helps.
Posted: Thu Jul 10, 2003 6:19 am
by ik
I am using a little trick to do not care about open/close tags and cr/lf :
//Open tag wuth attributes
function tag_open($tag,$arg="")
{
$res="\n<".$tag;
if ($arg) $res.=" ".$arg;
$res.=">";
return $res;
}
//Close tag
function tag_close($tag)
{
return "</".$tag.">";
}
//Get full tag string with attributes
function tag_echo($tag,$var,$arg="")
{
return tag_open($tag,$arg).$var.tag_close($tag);
}
With this stuff drawing table from DB looks like this (I assumed that rowsare already fetched to assosiateve array) :
$arg='class="mytab" cellspacing="0";
$data=[i.e. pg_fetch_all($rs)];
$keys=array_keys($data);
$s="";
$res="";
//Draw header
foreach ($keys as $val)
$s.=tag_echo("th",$val);
$res=tag_echo("tr",$s);
//Draw rows
for ($i=0;$i<count($data))
{
$s="";
foreach($data as $key=>$val)
$s.=tag_echo("td",$val);
$res.=tag_echo("tr",$s)
}
//Finish table
$res=tag_echo("table",$res,$arg);
//Out it
echo $res;
Igor
interesting
Posted: Thu Jul 10, 2003 1:51 pm
by staypufinpc
I haven't tried it, but that looks like it could be a great way of outputting the list without mixing in a bunch of html.