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