[PHP-DB] Entering a Query
Hi, I would be very interested to see an example of how it would be possible to enter a query into a text area and the results of the query displayed on the next page, similar to PHP admin I guess, are they any examples out there? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Entering a Query
Hi Shaun Comments inline ... On 01 Apr,2003 at 13:37 shaun shaun wrote: snip Hi, I would be very interested to see an example of how it would be possible to enter a query into a text area and the results of the query displayed on the next page, similar to PHP admin I guess, are they any examples out there? /snip Fairly simple At its most basic you have a form :- form method=post action=do_query.php textarea name=myquery/textarea input type=submit name=submit value=Submit /form which posts to your php page :- ?php $sql = $HTTP_POST_VARS[myquery]; // You'll need code here to sanitize the query and make sure there's // no nasty surprises in it. // then simply create your connection (assuming a function here) $conn = db_connect(); $result = mysql_query($sql); // Code to display the result here ? You would need to have some checking involved for the query. Also, as always, apply 'least privilege' to the user the query runs as. I don't know what you're using it for but be VERY careful - I would only allow selects here unless you're sure you know how it will be used. Hope this helps ... :-) -- Ronan e: [EMAIL PROTECTED] t: 01903 739 997 w: www.thelittledot.com The Little Dot is a partnership of Ronan Chilvers and Giles Webberley -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Entering a Query
thanks for your reply, What i was after is a way of representing the data, given that you dont know what columns will be displayed and how many rows etc? I would only be looking to do 'selects' so how can i ensure that this is the only type of query run? Thanks for your help Ronan Chilvers [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi Shaun Comments inline ... On 01 Apr,2003 at 13:37 shaun shaun wrote: snip Hi, I would be very interested to see an example of how it would be possible to enter a query into a text area and the results of the query displayed on the next page, similar to PHP admin I guess, are they any examples out there? /snip Fairly simple At its most basic you have a form :- form method=post action=do_query.php textarea name=myquery/textarea input type=submit name=submit value=Submit /form which posts to your php page :- ?php $sql = $HTTP_POST_VARS[myquery]; // You'll need code here to sanitize the query and make sure there's // no nasty surprises in it. // then simply create your connection (assuming a function here) $conn = db_connect(); $result = mysql_query($sql); // Code to display the result here ? You would need to have some checking involved for the query. Also, as always, apply 'least privilege' to the user the query runs as. I don't know what you're using it for but be VERY careful - I would only allow selects here unless you're sure you know how it will be used. Hope this helps ... :-) -- Ronan e: [EMAIL PROTECTED] t: 01903 739 997 w: www.thelittledot.com The Little Dot is a partnership of Ronan Chilvers and Giles Webberley -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Entering a Query
Hi Shaun
On 01 Apr,2003 at 14:28 shaun shaun wrote:
snip
thanks for your reply,
What i was after is a way of representing the data, given that you dont know
what columns will be displayed and how many rows etc?
/snip
One way to do it is to load the data into arrays. I generally use mysql_fetch_array()
to access recordsets. This delivers both an Indexed and associative array of values.
You can modify this behaviour with the last argument. Assuming you want to preserve
the keys (which will be the column headings in the sql result), in mysql_fetch_array()
you would use the constant MYSQL_ASSOC. You could do something like this:-
?php
// Assume you have a db connection
if (($result = mysql_query($sql_from_textarea))===0) {
// Drop out gracefully
}
// Check we have some rows
if (mysql_num_rows($result)) {
// We have some rows so load up an array with your recordset
// Start a counter
$counter=0;
// Step thru the recordset
while($data = mysql_fetch_array($result,MYSQL_ASSOC)) {
// loop through the current row, listing keys and values
while (list($k,$v)=each($data)) {
// create a new array member of the form
// $myarray[index][key]=value
$myarray[$counter][$k]=$v;
}
// increment the index counter
$counter++;
}
// Now we have an array which looks like the data so we can // display it
(you could do the keys bit up above as well
// actually)
// get the keys from the first element - safe cos we know
// we have at least one row
$keys = array_keys($myarray[0]);
// Now a standard loop through to first display the keys
// as headings then the data
$html = tabletr;
// Create a row of headings
for ($i=0;$icount($keys);$i++) {
$html .= tdb.$keys[$i]./b/td;
}
// Now loop through the rest of the data
while (list(,$v)=each($myarray)) {
$html .= tr;
// Send keys array pointer back to the start
reset($keys);
while(list(,$b)=each($keys)) {
$html.= td.$v[$b]./td;
}
$html .= /tr;
}
$html .= /table;
echo $html;
} else {
// Drop out gracefully
}
?
Does that all make sense ? Basically, you grab the recordset into an array whose
dimensions you can loop through. Then you grab the keys from an array element to give
you the columns. Then you loop through, using the keys as your guide for when to
change to a new row.
There may well be a more elegant way to do it but this would be where I would start !!
snip
I would only be looking to do 'selects' so how can i ensure that this is the
only type of query run?
/snip
Make sure the user you run the script as, only has select permissions on the db you
are using.
Once again, hope this helps ;-)
--
Ronan
e: [EMAIL PROTECTED]
t: 01903 739 997
w: www.thelittledot.com
The Little Dot is a partnership of
Ronan Chilvers and Giles Webberley
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Entering a Query
I would be very interested to see an example of how it would be possible to enter a query into a text area and the results of the query displayed on the next page, similar to PHP admin I guess, are they any examples out there? Issue the query with mysql_query() and check for an error. If there's an error, display mysql_error() or do your own error handling. From there, you'll count the number of fields returned by mysql_num_fields(). Loop through with mysql_field_name() to create your header row. The header text will match whatever the name of the column was. Then loop through mysql_fetch_row() to get all of the rows returned from the query. You already know how many fields there are, so just loop through the result of mysql_fetch_row() that many times and put each element in it's own td tag. That's pretty much it. ---John W. Holmes... PHP Architect - A monthly magazine for PHP Professionals. Get your copy today. http://www.phparch.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
