serverside "relay" script (was: RealBasic pulls MySQL support)

[Josh Mellicker]

FYI:

We just did a (simple) test of the serverside PHP middleware "relay"  
concept, and it worked great!

First, made a stack with a button with this script:

on mouseUp
     put "SELECT * FROM users" into tSQL
     put libUrlFormData("jjmysqlquery", tSQL) into tFormData
     post tFormData to url "http://www.revcoders.org/mysqlrelay.php"
     answer tAnswer
end mouseUp

------------------------------------------------

Then, on the server, a PHP script:

<?php

# Type="MYSQL"
# HTTP="true"
$hostname = "localhost";
$database = "test_db";
$username = "test_user";
$password = "icecream";
$connect = mysql_pconnect($hostname, $username, $password) or  
trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database, $connect);

$jjmysqlquery = $_POST['jjmysqlquery'];

$result = mysql_query($jjmysqlquery) or die("Error #" . mysql_errno 
() . ":" . mysql_error() );

while ($row = mysql_fetch_array($result)) {
	 echo $row[1]."|";
     }

?>

------------------------------------------------

The "it" variable had all the right data (from the first column of  
all records)! Yay!!!

Obviously, this is a crude test, we still need to implement security,  
and explode the array in PHP to deliver all the columns, etc.

We will need to use a different PHP command to execute commands like  
UPDATE, DELETE, etc. And we may go with a Rev CGI instead of PHP.

But I think this is a much superior idea to what I was doing before,  
which required updating the remote script with every different SQL  
query or command needed, which was madness!!! :-)



On Sep 12, 2007, at 10:59 AM, Josh Mellicker wrote:
>
>
> Chris,
>
> Did you settle on a simple PHP script that receives SQL statements  
> from Rev and simply "relays" them on to the localhost MySQL? And  
> then relays raw output back?
> (Along with appropriate security measures of course)
>
> ---
>
> The reason I ask is when I first started down the "Rev <-> PHP <->  
> remote db" path I wrote the SQL, and much of the parsing of the  
> returned data in PHP.
>
> I was trying to make it easy in Rev, just sending requests (similar  
> to function calls, like "getCustomers"), then having PHP nicely  
> format data before returning. It turned into a real headache having  
> to switch languages and dev environments, and deciding what part of  
> the data processing belonged where.
>
> Then, I realized you could just write a simple PHP script that took  
> completely formatted SQL and simply "bounced" them to MySQL... and  
> bounced the raw data back... but have not tried it yet.
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your  
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution