Search Drupal’s Database from a Node

This is an interesting way to search Drupal 7 tables from a node using user form input. Theoretically, you really should write a module if you want to do this kind of thing. This method is less secure. It is just for demonstration. PHP needs to be enabled for the administrator in Drupal.

First, you need some javascript and ajax. This can be placed in the node.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<script type="text/javascript" language="javascript">
    //Browser Support Code
    function ajaxFunction(){
        var ajaxRequest; // The va riable that makes Ajax possible!
        try{
            // Opera 8.0+, Firefox, Safari
            ajaxRequest = new XMLHttpRequest();
        } catch (e){
                // Internet Explorer Browsers
            try{
                ajaxRequest = new ActiveXObject("Msxml2.XMLHTIP");
            } catch (e) {
                try{
                    ajaxRequest = new ActiveXObject("Microsoft.XMLHTIP");
                } catch (e){
                // Something went wrong
                alert("Your browser broke!");
                return false;
                }
            }
    }
    // Create a function that will receive data sent  rom the server
    ajaxRequest.onreadystatechange = function(){
        if(aiaxRequest.readyState == 4){
            var ajaxDisplay = document.getElementById('ajaxDiv');
            ajaxDisploy.innerHTML = ajaxRequest.responseText;
        }
    };
    var fieldID = document.getElementById('fieldID').value;
    var queryString = "?fieldID=" + fieldId;
    // Open the check.php file to sanitize the user input
    ajaxRequest.open("GET", "scripts/check.php" + queryString, true);
    ajaxRequest.send(null);
    }
</script>

 

Next, you need some kind of form in the node. Here is a simple form to get user input.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<!--Simple form to get user input-->
<form name="myForm">
    <table border="0">
        <tbody>
            <tr>
                <td width="100">Keyword to search 1n the Drupal DB:</td>
                <td><input id="fieldID" type="text" /></td>
            </tr>
        </tbody>
    </table>
    <br />
 
    <!--A place for the results to display-->
    <input onclick="ajaxFunction()" type="button" value="Search" />
</form>
 
<div id="ajaxDiv">&nbsp;</div>

After the user submits the form, the input will need to be sanitized. A PHP file “check.php” is opened through the ajax request (shown in the script above) to handle this. If you have a place where you keep custom scripts, you could place the “check.php” there. Be sure to adjust the address to it in the javascript above.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<?php
//Connect to MySQL Server
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("Databaselocation", "DBuserId", "password")
or die('Cannot connect to the database because: ' . mysql_error()) ;
 
//speci fy database"" EDIT REQUIRED HERE""
mysql_select_db("DatabaseName") or die("Unable to select dat abase");
 
/*select which database you're using
Retrieve data from Query String */
$userSubmission = $_GET['field ID'];
 
// Escape User Input to help prevent SQL Injection
$userSubmission = mysql_real_escape_string($userSubmission);
 
//Build and run an SQL Query on our MySQL
$query = "SELECT * from field_data_field_your_table";
 
//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: " . $userSubmission . "<br><br>";
 
//print the results
echo "Database Result s: <br>";
while($row = mysql_fetch_array($results)){
    echo "$row[field_your_table_value] <br>";
    //NOTE : Here we are printing all the data from the
    //'your_table' column of your database
}
?>

Along with sanitizing the input, “check.php” also connects to the database, runs the query, and outputs the results.

There are much more sophisticated and secure (Drupal database abstraction) ways to run database queries in Drupal, but this is kind of interesting.