database query

[nobertos]

hi,

i want to have a text box in which the user can enter any kind of query, like:

SELECT name, email from foobar

then they hit submit.

then i want to display the results neatly to the user.

what's a good way to do this?

i'd like the output to look kind of like it does when i do a query in phpmyadmin - neatly formatted table header text, etc. pagination isn't necessary though.

thanks!
cg

[AstroTeg]

What are you stuck on?

Setup a form with a text box.

Have the form go to a PHP page that executes the query. This is uber-dangerous unless you have some SERIOUS query checking (or you just desire that people have this much control). People literally could create/delete not only data, but tables and entire databases if they so choose.

You run their query

Output the results into something pretty. The column names are given in the query and some functions like mysql_fetch_array() will store the column name/value pairs in an associative array which you could use to your advantage.

Not difficult to do.

[nobertos]

i am concerned with how to format the result correctly. Sometimes the user will enter a SELECT statement that specifies field names, sometimes they won't.

say i had a query like "SELECT * FROM FOO" where the field names aren't given, how would i get at the name/value pairs correctly so i could output a table like:

Field One Name Field Two Name
data data for field two
more data more field two data

could i use generic code that would work for all different kinds of SELECT statements?

cg

[AstroTeg]

If you're using MySQL, you can use mysql_fetch_array() and then use foreach($array as $key => $value) where $key contains the column name and $value contains the value of that column. In theory, problem solved.

[nobertos]

actually, doing that doesn't get me what i want... i am doing something wrong - i only get one row of the db back, and it is formatted strangely:

A <- a 'value'
buyer_cd <- a 'column name'
A <- 'value' repeated
1
FOO, RAQUEL
buyer_name
FOO, RAQUEL
2
[e-mail]
buyer_email
[e-mail]
3
694-1111
buyer_phone
694-1111
4
raquel
user_id
raquel
5
testing
password
testing

here's the code i used for testing, my $db_query was "SELECT * from codes", which has many rows:

$db_query_result = mysql_query($db_query);

$db_query_result_row = mysql_fetch_array($db_query_result);

foreach($db_query_result_row as $key => $value) {
echo "$key<br>";
echo "$value<br>";
}

I'd like to have the column names shown nicely, then all values below them, in a simple table. How do I correctly present the information?