Display results from any query.

jacksinn

I wan to add a feature (for ease of use) that will allow me to type in any query on a webpage and display the results. I thought it would be simple enough but I have only found examples on displaying results from hard-coded queries and I am already able to do that.

What I'm basically looking to do is have a textbox that captures the query. So if i type:

SHOW Tables;
and then hit submit it will show all my tables, but it should also handle results for

SELECT * FROM <table> WHERE ...;

I have done this which kind of works - but not quite.

        $theQuery = $_POST['textfield'];
        $result = mysql_query($theQuery);

//$row = mysql_fetch_array($result);
while( $queryContent = mysql_fetch_row( $result ) )
{

	// Display.
	$i = 0;
	while($i<sizeof($queryContent))
	{
		echo $queryContent[$i];
		echo " ***** ";
		$i++;
	}
	echo "<br>";
   }

Also, The first row seems messed up and it does not display column names which are a big help. I have $i set up to go through each column because each query can have different numbers of column. Doing a select on a 'logins' table may return 2 columns whereas a select on 'items' may return 5 columns.

I appreciate any feedback and thank you in advance for your time helping me to solve this.

leatherback

Out of curiosity.. Why would you want to do that!? You are basically opening your full database for the world to see, and manipulate!?

jacksinn

It would be available to admin logins, not users.

Piranha

As leatherback I can't see any need to do that. But if you really need it make sure that there are very high security to make sure that noone else would be able to change things.

Maybe you can use a database administration program like PHPMyAdmin for this?

Edit: Maybe you would like to make sure that it is only possible to do SELECT queries, then at least noone would be able to mess the database up.

jacksinn

This ultimately comes down to an issue of the person hosting the server not willing to allow SSH or access other than via PHP to the DB. PHPMyAdmin has been suggested and noted. I believe my partner on the back end is using it but I would like a browser page for myself. In the end, I'm curious about the subject and would like to implement it - if only for my own amusement. It has practical uses and I could possibly limit queries to only select statements using drop boxes or other security measures.

All in all, I would just like to know if it is feasible and if so, how. I'm not one to take code from others and pass it off as my own so if you offer a coded solution then I would either learn from it or implement it in its entirety but leave everything intact and citing the author as the source.

Alternatively, you could just point me in the right direction.

Piranha

jacksinn wrote:
This ultimately comes down to an issue of the person hosting the server not willing to allow SSH or access other than via PHP to the DB.

Change host. If they won't allow you access to the database then you are better off with another host.

I can understand that you want to do it just to see how to do it. Unfortunatley I can't help you with that.

jacksinn

I would love to switch hosts but unfortunately I'm working on a project with a client who is basically unwilling to allow and breathing room. Thanks for your concerns, though. I guess I'll continue to scour the bowels of the internet and rack my brain for a solution. What I have works decently, but I'd like better results. It'll only take more time on my behalf, I suppose. Or use PHPMyAdmin.

But I'm still curious.

jacksinn

Ok. I've tested it using a var_dump($queryContent) inside the loop and commented out the other stuff and it was getting the proper results, there were just null fields and it made it look not so nice.

So if anyone wants a quick hack for it, use the previous code and tweak it for better visual identification.

Still need to output column names though.

leatherback

go from here:

foreach($row as $key=>$value)
   {
   echo $key;
   }

// row = from while($row = mysql_fetch_array($result))

jacksinn

Thanks. I ended up working it out myself and have something that kind of resembles output from doing queries through an SSH connection. I'll post the code at some point if anyone is interested.

Roger_Ramjet

Just a quick 2 cents worth: if you used mysql_fetch_array you get both an indexed and an associative array returned so you can use any method of iterating through an array that you want, the number of columns in the results matters not one bit.