Query Database with Drupal Form

The Problem

  • By default, there is no way to allow your users to query one of your databases (either internal or external) in Drupal. Additionally, there are no modules that really allow you to do this either.

The Solution

  • With a little pasting and editing, this guide will enable you to let users do a simple search of one of your databases. This information can easily be extended to allow for all kinds of user-database interaction in Drupal.
  • Use Ajax! This is by far the easiest way to get the job done.

Preparation

  • Make sure that you have the PHP code Input Format enabled. To do this simply go to Administer->Site Building->Modules. Scroll Down to PHP Filter, make sure it’s enabled, click ‘Save Configuration’
  • If you have a rich text editor enabled (such as YUI) you should disable it temporarily while we create the form

The Drupal Page/Form

  • Go to Create Content and create a new page (it doesn’t have to be a page, but we’ll use a page for the sake of this guide.)
  • Call the page Database Query
  • Select PHP code from the Input Format dropdown.
  • Paste the following code into your page:

<html>
<body>

<script language=”javascript” type=”text/javascript”>
<!–
//Browser Support Code
function ajaxFunction(){
var ajaxRequest; // The variable that makes Ajax possible!

try{
// Opera 8.0+, Firefox, Safari
ajaxRequest = new XMLHttpRequest();
} catch (e){
// Internet Explorer Browsers
try{
ajaxRequest = new ActiveXObject(“Msxml2.XMLHTTP”);
} catch (e) {
try{
ajaxRequest = new ActiveXObject(“Microsoft.XMLHTTP”);
} catch (e){
// Something went wrong
alert(“Your browser broke!”);
return false;
}
}
}
// Create a function that will receive data sent from the server
ajaxRequest.onreadystatechange = function(){
if(ajaxRequest.readyState == 4){
var ajaxDisplay = document.getElementById(‘ajaxDiv’);
ajaxDisplay.innerHTML = ajaxRequest.responseText;
}
}
var lastphp = document.getElementById(‘lastphp’).value;
var queryString = “?lastphp=” + lastphp;
ajaxRequest.open(“GET”, “/php/check.php” + queryString, true);
ajaxRequest.send(null);
}
//–>
</script>

<form name=’myForm’>
<table border=”0″>
<tr>
<td width = 100>Last Name: <br /></td>
<td><input type=’text’ id=’lastphp’> </td>
</tr>
</table>
<br />
<input type=’button’ onclick=’ajaxFunction()’ value=’Search’ />
</form>
<div id=’ajaxDiv’></div>
</body>
</html>

  • There is only one thing we eventually will have to change, but we’ll leave it for now: “/ajax-example.php” (just before the tag. You’ll probably want to keep your php file somewhere else, and will have to update the file location accordingly.
  • For now, just hit the save button to save your page and move onto the next step.

The PHP File

  • We now have to create the PHP file that the above ajax script refers to.
  • Open up your favorite text editor and paste in the following code:

<?php
//Connect to MySQL Server
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","MySQLUser","password") or die('Cannot connect to the database because: ' . mysql_error());

//specify database ** EDIT REQUIRED HERE **
mysql_select_db(“databaseName”) or die(“Unable to select database”); //select which database we’re using

// Retrieve data from Query String
$last = $_GET[‘lastphp’];

// Escape User Input to help prevent SQL Injection
$last = mysql_real_escape_string($last);

//Build and run a SQL Query on our MySQL tutorial
$query = “SELECT * from dataTableName”; //just grab every row from our table
$results = mysql_query($query)or die(mysql_error());

//print what the user entered (eventually I’m sure you’ll want to use this data in your query)
echo “You Entered: ” . $last . “<br><br>”;

//print the results
echo “Database Results: <br>”;
while($row = mysql_fetch_array($results)){
echo “$row[lastname]<br>”; //NOTE: Here we are printing all the data from the ‘lastname’ column of our database, either change this name, or make sure you have a lastname column with some data in it
}
?>

  • You will have to make some modifications to this file! These include
    • Server: I used localhost, you might have to change this
    • MySQL User: Change this to your user
    • password: This is the user’s password
    • databaseName: This is the name of your database
    • lastname NOTE: This needs to be changed to the name of a column/field in your database (it is located at the bottom of the code)
  • Save this file as check.php
  • Upload this file to your server/hosting space in a php folder within your Drupal Installation.
    • For example, if Drupal is installed in the root of you web page (like on http://firstsearchblue.com) just create a folder called php and upload your check.php file to it.
  • That’s it! Navigate back to the form you created (we called it Database Query) and try hitting the search button.
  • I’m sure this guide is a little tough to work through PLEASE post any questions or issues below – they will be answered quickly!

drupal mysql query

  • deepesh

    Hi, great shortcut. I have one question, how can I check login status of drupal user in my custom php file.

  • Great information! I’ve been looking for something like this for a while now. Thanks!

  • Joe

    This does not work for me.I have been working on how to implement a search of this sort for over a 1 1/2 months using every possible way. But everything I have tried will not work.
    Please help!

    • acopetric

      This example is good (with some correction:-))

      function ajaxFunction(){
      var ajaxRequest; // The variable that makes Ajax possible!
      try{
      // Opera 8.0+, Firefox, Safari
      ajaxRequest = new XMLHttpRequest();
      } catch (e){
      // Internet Explorer Browsers
      try{
      ajaxRequest = new ActiveXObject(“Msxml2.XMLHTTP”);
      } catch (e) {
      try{
      ajaxRequest = new ActiveXObject(“Microsoft.XMLHTTP”);
      } catch (e){

      // Something went wrong
      alert(“Your browser broke!”);

      return false;
      }
      }
      }

      // Create a function that will receive data sent from the server
      ajaxRequest.onreadystatechange = function(){
      if(ajaxRequest.readyState == 4){

      var ajaxDisplay = document.getElementById(‘ajaxDiv’);
      ajaxDisplay.innerHTML = ajaxRequest.responseText;
      }
      }

      var lastphp = document.getElementById(‘lastphp’).value;
      var queryString = “?lastphp=” + lastphp;

      ajaxRequest.open(“GET”, “/sites/all/modules/modulename/check.php” + queryString, true);

      ajaxRequest.send(null);

      }

      Last Name:

      ///—————————————————————————–//

      check.php script

      <?php

      //Connect to MySQL Server

      //connect to your database ** EDIT REQUIRED HERE **

      mysql_connect("localhost","username","password") or die('Cannot connect to the database because: ' . mysql_error());

      //specify database ** EDIT REQUIRED HERE **

      mysql_select_db("database") or die("Unable to select database"); //select which database we’re using

      // Retrieve data from Query String
      $last = $_GET['lastphp'];
      // Escape User Input to help prevent SQL Injection
      $last = mysql_real_escape_string($last);
      //Build and run a SQL Query on our MySQL tutorial
      $query = "SELECT * from table where lastname LIKE '%" . $last . "%'"; //just grab every row from our table

      $results = mysql_query($query)or die(mysql_error());
      //print what the user entered (eventually I’m sure you’ll want to use this data in your query)

      echo "You Entered: " . $last . "”;

      //print the results

      echo “Database Results: “;

      while($row = mysql_fetch_array($results)){

      echo $row[‘lastname’].””; //NOTE: Here we are printing all the data from the ‘lastname’ column of our database, either change this name, or make sure you have a //lastname column with some data in it

      }

      ?>

      • acopetrc

        check.php script
        ———————————————
        <?php

        //Connect to MySQL Server
        //connect to your database ** EDIT REQUIRED HERE **

        mysql_connect("localhost","username","password") or die('Cannot connect to the database because: ' . mysql_error());

        //specify database ** EDIT REQUIRED HERE **

        mysql_select_db("database") or die("Unable to select database"); //select which database we’re using

        // Retrieve data from Query String

        $last = $_GET['lastphp'];

        // Escape User Input to help prevent SQL Injection

        $last = mysql_real_escape_string($last);

        //Build and run a SQL Query on our MySQL tutorial

        $query = "SELECT * from table where lastname LIKE '%" . $last . "%'"; //just grab every row from our table

        $results = mysql_query($query)or die(mysql_error());

        //print what the user entered (eventually I’m sure you’ll want to use this data in your query)

        echo "You Entered: " . $last . "”;

        //print the results

        echo “Database Results: “;

        while($row = mysql_fetch_array($results)){

        echo $row[‘lastname’].””; //NOTE: Here we are printing all the data from the ‘lastname’ column of our database, either change this name, or make sure you have a //lastname column with some data in it

        }

        ?>

  • It works well for us, as per my programmer. Thanks for sharing this excellent small tutorial with us.

  • Acampada

    I found that if copy the example it doesn't work…. I don't know why… maybe it's a format problem (I have deactivated CK editor(rich editor text) but the problem persisted)… when I wrote the example, all was OK.

  • Dtroy3

    Does this work with a the webform module in Drupal?

  • Hrreza5232

    Hello
    This is reza. I think you can make query with multiple query parameter using Ajax using the same concept……..

  • Dtroy3

    Can anyone send a link as to what this should look like in action, on a live page?

  • Jlanaux

    For some reason, my button does not show. What was created instead is a box with the word ‘Search’ and I can type in that box. What did I do wrong?

  • Rajark

    Search button is not enabled on this, can anyone see this problem ?

    • Jlanaux

      I fixed the button, but I can’t seem to get any results. Is this post being monitored anymore? I would love to see this work. 

      To fix the button, I replaced the line that makes the button and put in this line:  and as you see it is a little different.

      Let me know if you are able to make the query work.

  • jlanaux

    Has anyone figured out how to make this work? I was able to get a form on a Drupal basic page, but no query results. There has to be a solution.