Thread: Ajax/PostgreSQL
I'm doing some massive (internal company) applications using PHP, which query extensive PostgreSQL tables. This is fine, but obviously it often requires multiple web pages to get something done. Supposedly, AJAX promises to make web pages more interactive. But from what I understand, I'd have to have bindings from Javascript into PostgreSQL to make this work. Here's an example: The user wants to enter a bill (accounts payable) into the system. He first has to pick a vendor. Normally, this would entail a PHP page that generates a PostgreSQL query. The user would then get a second page with various vendor information (like number of due days for that vendor), and various other payable info. But wouldn't it be nice to have vendor information filled in on the original page, directly after the user picks a vendor? Theoretically, AJAX might allow something like this. But from what I can see, it would require PostgreSQL bindings in Javascript, and some way to pass the data back so that PHP could use it. Is this even possible? Is it being worked on? Is there a different solution I don't know about? I can see where Javascript can alter the look of a page, but I can't work out how it would allow interactive use of a PostgreSQL table. -- Paul M. Foster
Paul M Foster <paulf@quillandmouse.com> writes: > Here's an example: The user wants to enter a bill (accounts payable) into the > system. He first has to pick a vendor. Normally, this would entail a PHP page > that generates a PostgreSQL query. The user would then get a second page with > various vendor information (like number of due days for that vendor), and > various other payable info. But wouldn't it be nice to have vendor information > filled in on the original page, directly after the user picks a vendor? > Theoretically, AJAX might allow something like this. But from what I can see, > it would require PostgreSQL bindings in Javascript, and some way to pass the > data back so that PHP could use it. I'd do it the reverse: Javascript would call a PHP-enabled URL, PHP would get the data, return to JS, JS would then populate the form. This way all your logic is contained within PG and PHP. JS would only be used to manipulate the interface. -- Jorge Godoy <jgodoy@gmail.com>
The way to handle this is to make your ajax call PHP scripts which handle your data and return your XML for the Javascript (or HTML). AJAX makes HTTP requests, it does not talk directly to the database server. In essence to do what you're asking you would need a JavaScript implementation of the pgsql protocol. This would at the *very* least make for the possibility of security issues with your database backend, where every client hitting your website would need to be able to access it. Hope this helps, Gavin On Aug 5, 2006, at 3:42 PM, Paul M Foster wrote: > I'm doing some massive (internal company) applications using PHP, > which query extensive PostgreSQL tables. This is fine, but > obviously it often requires multiple web pages to get something > done. Supposedly, AJAX promises to make web pages more interactive. > But from what I understand, I'd have to have bindings from > Javascript into PostgreSQL to make this work. > > Here's an example: The user wants to enter a bill (accounts > payable) into the system. He first has to pick a vendor. Normally, > this would entail a PHP page that generates a PostgreSQL query. The > user would then get a second page with various vendor information > (like number of due days for that vendor), and various other > payable info. But wouldn't it be nice to have vendor information > filled in on the original page, directly after the user picks a > vendor? Theoretically, AJAX might allow something like this. But > from what I can see, it would require PostgreSQL bindings in > Javascript, and some way to pass the data back so that PHP could > use it. > > Is this even possible? Is it being worked on? Is there a different > solution I don't know about? I can see where Javascript can alter > the look of a page, but I can't work out how it would allow > interactive use of a PostgreSQL table. > > -- > Paul M. Foster > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
Paul M Foster wrote: > I'm doing some massive (internal company) applications using PHP, which > query extensive PostgreSQL tables. This is fine, but obviously it often > requires multiple web pages to get something done. Supposedly, AJAX > promises to make web pages more interactive. But from what I understand, > I'd have to have bindings from Javascript into PostgreSQL to make this > work. > > Here's an example: The user wants to enter a bill (accounts payable) > into the system. He first has to pick a vendor. Normally, this would > entail a PHP page that generates a PostgreSQL query. The user would then > get a second page with various vendor information (like number of due > days for that vendor), and various other payable info. But wouldn't it > be nice to have vendor information filled in on the original page, > directly after the user picks a vendor? Theoretically, AJAX might allow > something like this. But from what I can see, it would require > PostgreSQL bindings in Javascript, and some way to pass the data back so > that PHP could use it. > > Is this even possible? Is it being worked on? Is there a different > solution I don't know about? I can see where Javascript can alter the > look of a page, but I can't work out how it would allow interactive use > of a PostgreSQL table. > No - AJAX is really talking about the ability to use client-side JavaScript to request XML documents from a server. In this case if you want to have a page show vendor information when a user selects a specific vendor, then your client-side JavaScript would request a resource on your server that will return that information in XML. If you're using PHP then the request you might make might be something like: http:/myserver.com/getVendorInfo.php The PHP request on the server goes to Postgres and retrieves the appropriate information, formats it as XML and writes it out like any other page. The client-side Javascript gets the XML response, uses client-side DOM to decode the response and changes the page HTML to include the appropriate information without having the page do a refresh. Obviously this is often not much faster than getting a different page, and is one of the fundamental problems with things like AJAX and SOA - each remote request is remote - it has to go over the internet and suffers not only the processing time at the database but also the network latency. But to answer your question - there is *not* coupling between JavaScript and Postgres. The JavaScript runs on the client, Postgres on the server and PHP as your intermediary. Best regards Pete -- Peter Wilson YellowHawk Ltd (http://www.yellowhawk.co.uk) Server Side XML and Javascript Web Application Server - http://www.whitebeam.org
On Aug 5, 2006, at 3:42 PM, Paul M Foster wrote: > But from what I can see, it would require PostgreSQL bindings in > Javascript, and some way to pass the data back so that PHP could > use it. No, you should be able to this all within PHP with a AJAX library. 99% of my ajax experience has been with Rails.. and there is no need to make javascript talk directly to PostgreSQL. Robby -- Robby Russell Founder & Executive Director PLANET ARGON, LLC Ruby on Rails Development, Consulting & Hosting www.planetargon.com www.robbyonrails.com +1 503 445 2457 +1 877 55 ARGON [toll free] +1 815 642 4068 [fax]
Paul M Foster <paulf@quillandmouse.com> wrote:
> I'm doing some massive (internal company) applications using PHP, which
> query extensive PostgreSQL tables. This is fine, but obviously it often
> requires multiple web pages to get something done. Supposedly, AJAX
> promises to make web pages more interactive. But from what I understand,
> I'd have to have bindings from Javascript into PostgreSQL to make this
> work.
NO. NO. ABSOLUTELY NOT. That is an incredible security risk.
Javascript code runs on the client's browser and should not be allowed to
just send raw queries into a database.
Your javascript code should politely ask your server-side code to do
something, and your server-side code should very, *VERY* carefully inspect
the request before sanitizing, escaping, and *maybe* passing it on to
postgresql.
> Here's an example: The user wants to enter a bill (accounts payable)
> into the system. He first has to pick a vendor. Normally, this would
> entail a PHP page that generates a PostgreSQL query. The user would then
> get a second page with various vendor information (like number of due
> days for that vendor), and various other payable info. But wouldn't it
> be nice to have vendor information filled in on the original page,
> directly after the user picks a vendor? Theoretically, AJAX might allow
> something like this. But from what I can see, it would require
> PostgreSQL bindings in Javascript, and some way to pass the data back so
> that PHP could use it.
Have PHP serve as an intemediary between the Javascript on the
client browser and the database on your backend. Make sure your PHP looks at
everything the client is sending across very carefully. Pay special
attention to symbols that are used for quoting and escaping in SQL. Don't
hae the javascript think in terms of tables, just result sets, specific ones
that the PHP feeds it.
- Tyler
If you take a peek at the CVS version of phpPgAdmin, you will see the "autocomplete" functionality for foreign keyed columns when doing "insert row" or "edit row". You can easily get started with that by using the pagila database and trying to insert some data into film_category. $ cvs -d:pserver:anonymous@phppgadmin.cvs.sourceforge.net:/cvsroot/phppgadmin login $ cvs -z3 -d:pserver:anonymous@phppgadmin.cvs.sourceforge.net:/cvsroot/phppgadmin co -P webdb Pagila: http://pgfoundry.org/frs/download.php/919/pagila-0.8.0.zip Files of interest is "autocomplete.php", this was part of my Google SoC project. Regards, John On 8/5/06, Paul M Foster <paulf@quillandmouse.com> wrote: > I'm doing some massive (internal company) applications using PHP, which > query extensive PostgreSQL tables. This is fine, but obviously it often > requires multiple web pages to get something done. Supposedly, AJAX > promises to make web pages more interactive. But from what I understand, > I'd have to have bindings from Javascript into PostgreSQL to make this work. > > Here's an example: The user wants to enter a bill (accounts payable) > into the system. He first has to pick a vendor. Normally, this would > entail a PHP page that generates a PostgreSQL query. The user would then > get a second page with various vendor information (like number of due > days for that vendor), and various other payable info. But wouldn't it > be nice to have vendor information filled in on the original page, > directly after the user picks a vendor? Theoretically, AJAX might allow > something like this. But from what I can see, it would require > PostgreSQL bindings in Javascript, and some way to pass the data back so > that PHP could use it. > > Is this even possible? Is it being worked on? Is there a different > solution I don't know about? I can see where Javascript can alter the > look of a page, but I can't work out how it would allow interactive use > of a PostgreSQL table. > > -- > Paul M. Foster > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
On Sat, Aug 05, 2006 at 08:27:25PM -0300, Jorge Godoy wrote: > Paul M Foster <paulf@quillandmouse.com> writes: > > Here's an example: The user wants to enter a bill (accounts payable) > > into the system. He first has to pick a vendor. Normally, this would > > entail a PHP page that generates a PostgreSQL query. The user would > > then get a second page with various vendor information (like number > > of due days for that vendor), and various other payable info. > > But wouldn't it be nice to have vendor information filled in > > on the original page, directly after the user picks a vendor? > > Theoretically, AJAX might allow something like this. But from what I > > can see, it would require PostgreSQL bindings in Javascript, and > > some way to pass the data back so that PHP could use it. > > I'd do it the reverse: Javascript would call a PHP-enabled URL, PHP > would get the data, return to JS, JS would then populate the form. > > This way all your logic is contained within PG and PHP. JS would only > be used to manipulate the interface. I'm not using php, but the idea behind it is similar. I use postgresql, zope and scriptaculous. The autocompleter calls a ZSQL method, which calls a stored procedure and returns data to the ajax part of the website. If you give a look in google for scriptaculous examples, you'll find a lot of them for php working in a similar way: they call a php page which returns the information for the autocompleter. In all the examples I've seen there's no input sanitysing (they are all toy examples) but it is not difficult to implement it.
Hi Paul :
You can use deferred load do this.
I am using tu active /deactive user without open a window and going to other
page and return.
No ajax in this code.
----------------------------------
// This is in the user form...
----------------------------------
<head>
<script type="text/javascript" src="../js/cargadiferida.js"></script>
</head>
<script type="text/javascript">
function AtivaDesativar( sDescricao, nIdUsuario )
{
if ( confirm(" Deseja Ativar / Desativar o Usuário " + sDescricao + " ? " )
)
{
// This make the call to a php file that work with pg.
RealizarPeticao( "usuarios_ativardesativar.php?idusuario=" + nIdUsuario
);
} else
return false;
}
}
</script>
-------------------------------
This is the cargadiferida.js:
-------------------------------
function RealizarPeticao( sPagina )
{
if ( sPagina.trim() == '' )
return '';
// Create a new element script...
var oScript = document.createElement( 'script' );
// Put the source page.
// Some like this "usuarios_ativardesativar.php?idusuario=10";
oScript.src = sPagina;
// Y add the new script tag to the body....
document.body.appendChild( oScript );
}
// This is to chage the image in the list.
function ProcessarRespostaAtivacao( sNomeTag, sClase )
{
if ( sNomeTag.trim() == '' )
return '';
oResultado = document.getElementById( sNomeTag.trim() );
oResultado.className = sClase;
}
----------------------------------------------
This is the php file, usuarios_ativardesativar.php :
----------------------------------------------
<?php
header("Content-type: text/javascript"); // Note that the output is
javascript...
require_once( '../comun/cabecalho.php' );
$sSql = " SELECT CASE WHEN ativo = 1 THEN 0 ELSE 1 END AS ativo ";
$sSql .= " FROM os_usuarios ";
$sSql .= " WHERE idusuairo = {$_REQUEST['idusuario]} ";
$sSql .= " AND idempresa = {$_SESSION['empresa_session']} ";
$rs = $Connection->Execute( $sSql );
if ( !$rs )
Error_Msg( ' Erro : ao Atualizar => ', $Connection->ErrorMsg(), $sSql );
if ( !$rs->EOF )
{
if ( $rs->fields['ativo'] == 0 )
$sClase = 'ativar';
else
$sClase = 'desativar';
$sSql = " UPDATE os_usuarios ";
$sSql .= " SET ativo = {$rs->fields['ativo']} ";
$sSql .= " WHERE idusuario = {$_REQUEST['idusuario']} ";
$sSql .= " AND idempresa = {$_SESSION['empresa_session']} ";
$rs = $Connection->Execute( $sSql );
if ( !$rs )
Error_Msg( ' Erro : ao Atualizar => ', $Connection->ErrorMsg(),
$sSql );
?>
// This is a call to ProcessarRespostaAtivacao, note this is a javascript
funcion.
ProcessarRespostaAtivacao( '<?php echo "cmd{$_REQUEST['idusuario']}"; ?>',
'<?php echo $sClase; ?>' );
<?php } ?>
I test this in Firefox and ie. I have no problem to execute this code.
Alejandro Michelin Salomon
Porto Alegre
Brasil.
-->-----Mensagem original-----
-->De: pgsql-general-owner@postgresql.org
-->[mailto:pgsql-general-owner@postgresql.org] Em nome de Paul M Foster
-->Enviada em: sábado, 5 de agosto de 2006 19:43
-->Para: pgsql-general@postgresql.org
-->Assunto: [GENERAL] Ajax/PostgreSQL
-->
-->
-->I'm doing some massive (internal company) applications using
-->PHP, which
-->query extensive PostgreSQL tables. This is fine, but
-->obviously it often
-->requires multiple web pages to get something done. Supposedly, AJAX
-->promises to make web pages more interactive. But from what I
-->understand,
-->I'd have to have bindings from Javascript into PostgreSQL to
-->make this work.
-->
-->Here's an example: The user wants to enter a bill (accounts payable)
-->into the system. He first has to pick a vendor. Normally, this would
-->entail a PHP page that generates a PostgreSQL query. The
-->user would then
-->get a second page with various vendor information (like
-->number of due
-->days for that vendor), and various other payable info. But
-->wouldn't it
-->be nice to have vendor information filled in on the original page,
-->directly after the user picks a vendor? Theoretically, AJAX
-->might allow
-->something like this. But from what I can see, it would require
-->PostgreSQL bindings in Javascript, and some way to pass the
-->data back so
-->that PHP could use it.
-->
-->Is this even possible? Is it being worked on? Is there a different
-->solution I don't know about? I can see where Javascript can
-->alter the
-->look of a page, but I can't work out how it would allow
-->interactive use
-->of a PostgreSQL table.
-->
-->--
-->Paul M. Foster
-->
-->---------------------------(end of
-->broadcast)---------------------------
-->TIP 9: In versions below 8.0, the planner will ignore your desire to
--> choose an index scan if your joining column's datatypes do not
--> match
-->