Calling custom PHP inside Drupal

Question

Drupal version 6.12

I have a page whose input format is PHP.

I simply want to update a database table. The SQL code appears to be too complex for db_query. I can not make db_query work nor does including php nor does dropping custom php code into the “Body” seem to work either. Any advise on how I can make the following code work inside Drupal?

Here is the code we put in the body. I tried creating a PHP file and just including the PHP file with an INCLUDE statement too.

I know the PHP is error free. it was taken from a site that does not use Drupal!

<?php

 if( isset( $_GET['file'] ) )
 {
   $fileno    = $_GET['file'];
   $client = $_POST["Client"];

   $DBLink = pg_connect("host=XXXX dbname=XXXX user=XXX password=XXXX" );

   $sql = "update 
           webform_submitted_data sd set data = 'A' 
           where
           sd.nid = '27' and
           sd.cid = (select wc.cid from webform_component wc where wc.nid = sd.nid and wc.form_key = 'status') and
           sd.sid = (select wd.sid from webform_submitted_data wd, webform_component wc
                   where wc.nid = sd.nid and wc.form_key = 'your_file_' and wd.nid = wc.nid and
            wd.data = '$fileno');"

if( ! pg_query($DBLink, $sql) )
{
  print( "Database Connection Failure: " . pg_last_error($DBLink));
  exit;
}
else
{
  print "File: $fileno is now Assigned to $client";
}

pg_close($DBLink);
}

?>

I also tried calling the Drupal APIs for sending an update to the database with no luck either, see code that follows. I actually tried this method first before giving up and trying the code above.

I also tried two versions of the db_query. The one you see below and one where I replaced %s with $fileno in the $sql string and called db_query($sql).

<?php

 if( isset( $_GET['file'] ) )
 {
   $fileno    = $_GET['file'];
   $client = $_POST["Client"];

   $sql = "update 
             webform_submitted_data sd set data = 'A' 
           where
             sd.nid = '27' and
             sd.cid = (select wc.cid from webform_component wc where wc.nid = sd.nid and wc.form_key = 'status') and
             sd.sid = (select wd.sid from webform_submitted_data wd, webform_component wc
                        where wc.nid = sd.nid and wc.form_key = 'your_file_' and wd.nid = wc.nid and
                        wd.data = '%s');"

   db_query($sql, $fileno);
   print "File: $fileno is now Assigned to $client";
}

?>

I also put my database in full logging mode, logging connections and all statements and neither query hits the database. In the first case, if I INCLUDE the PHP I get just a white/blank screen -- it's like the PHP code is running but drupal is parsing the code before running it. I just want the code to run AS-IS.

Also, I'm really not interested in creating drupal modules. If it's possible to make this work without a lot of Drupal customizattion, that's what I'm after. This is a short-term tactical fix while we work on a more strategic goal...

Thanks all!

Answer 1

A couple of questions and thoughts:

1. Are there any database errors that appear on the screen? They usually appear in red 'warning' message boxes at the top of the content after an error has occurred.
2. Instead of webform_component you should be using {webform_component}. All table names should be in brackets.
3. The proper way to use data from a form input is to use $form_values[] for a form or $node for a node (if the data is part of a node). Additionally, if you are using the webform module, you can add steps to the submission of a webform. There are some tutorials here.
4. Last, does php actually reach the if if( isset( $_GET['file'] ) )? Drupal will complain heavily about database errors. So if you don't receive an error message, it means that the query was not executed (never got to that step) or it executed cleanly (but perhaps not with the intended effect).

In my experience, there haven't been queries that have been too complex for drupal because you are entering SQL (sanitized and parametrized) directly.

Answer 2

First of all creating a module can be done quite fast, atleast when you know Drupal. You should considder it, as code in nodes or templates like that generally is a bad idea.

There is a lot of drupal API flaws in your code but nothing major enough to break it though. You don't write what you are doing, but you try to get both get and post data, that's not possible. Most likely you don't have the get params you think you do as drupal so your if block isn't executed.

If you wrote what you do to "execute" the code, e.g. what URL you visit, module you use ect. If you simply want to run SQL and nothing more, you can do that in your db. So what exactly are you trying to do?