CODEWITHSUNDEEP
databasedrupal
jlanaux
jlanaux

Reputation: 1

Query a database with drupal 7

I have searched the internet for a way to use our history database on our drupal 7 site. I have a database built with names, marriage dates, etc. I would like to have a basic page to do a search. I found a site that helped with the code, but it is not working. I am not a programmer, but I can follow directions. Here is the code I have:

On a basic page using php code as my input format,

<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>

Then, I created a php file:

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

//specify database ** EDIT REQUIRED HERE **
mysql_select_db(“genealogy”) 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 columbus_ledger_enquirer_obituary_db”; //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
}
?>

I placed the php file in a folder called php under /sites/default.

The basic page has a nice box and search button, but when I type in a name, nothing happens.

Can anyone here tell me what's wrong?

Thanks

Upvotes: 0

Views: 2395

Answers (2)

Pierre.Vriens
Pierre.Vriens

Reputation: 2117

These days "there is a module for it" (to query external databases in formats such as Oracle, MS SQL, Postgress, SQLite, or just any PDO compliant format). It's the Forena module (disclosure: I'm a co-maintainer of it).

Head over to its demo site to see it as work, and refer to its community docu for way more details. Here is a quote from its docu:

... built of the idea of using SQL to get data out of a database and use XHTML and CSS to format it into web reports.

It is designed to leverage existing knowledge of HTML, CSS, SQL and JavaScript to help you create rich interactive web reports.

Upvotes: 1

Clive
Clive

Reputation: 36957

As @SpaceBeers mentions this is really not a good way to do this kind of thing in Drupal (Google "bootstrapping Drupal" or "access custom database in Drupal" for some ideas).

The reason your code probably isn't working, though, is because you're calling your AJAX in using the following code:

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

The problem is in the path...you mention you've put your "php" folder in '/sites/all', so surely the path from your AJAX call needs to be the same:

ajaxRequest.open("GET", "/sites/all/php/check.php" + queryString, true);

Upvotes: 3

Related Questions