displaying random mysql query and data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
staypufinpc
Forum Newbie
Posts: 12
Joined: Thu May 01, 2003 7:59 pm

displaying random mysql query and data

Post 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?
staypufinpc
Forum Newbie
Posts: 12
Joined: Thu May 01, 2003 7:59 pm

clarification

Post 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.
staypufinpc
Forum Newbie
Posts: 12
Joined: Thu May 01, 2003 7:59 pm

showing mysql data and tables without knowing the content

Post 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++)
	&#123;
		echo "<th>".mysql_field_name($result, $x)."</td>";
	&#125;
	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++)
	&#123;
		$info=mysql_fetch_row($result);
		echo "<tbody bgcolor=$color><tr>";

  //generate each individual field of information
		for ($i=0; $i<$fnum; $i++)
		&#123;
			$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')
			&#123;
				$date=date("m/d/y", $info&#1111;$i]);
				echo "<td width=10%>$date</td>";
			&#125;
			else
				echo "<td>".$info&#1111;$i]."</td>";
		&#125;

  //this is the rest of the row alternating color thing
		if ($color==ffffff)
			$color=dddddd;
		else
			$color=ffffff;
		echo "</tr></tbody>";
		
	&#125;
	
	echo "</table>";
I hope this helps.
ik
Forum Commoner
Posts: 34
Joined: Thu Jul 10, 2003 5:33 am
Location: Lancs Uni, UK
Contact:

Post 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
staypufinpc
Forum Newbie
Posts: 12
Joined: Thu May 01, 2003 7:59 pm

interesting

Post 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.
Post Reply