Using javascript to query a database 1

[deeciple]

Hi All,

I have a form field that populates a column in my database with a unique value. What I am trying to do is write a function that will be called on the form field's onblur event to initiate a query of the database column for the form field's value, pop-up an alert box if a match is found and clear the form field's value. I have done a bit of research and it seems this is possible with AJAX or Jquery but none of the solutions seem to fit my needs. I am using PHP for my server side script and javascript for the client side.

Can anyone help me figure out how to do this? Any help is always appreciated.

Thanks in advance.

Ken

 

[vacunita]

3 things you need to set up.

[ol]
[li]Server Side Script: This would be the script that will respond to the Ajax Call, run the query and return the results.[/li]
[li]Ajax Call to server side Script: This will contact the server side script pass the parameter you need to the script and wait for the results.[/li]
[li]The JS function that will take the returned value and display the alert.[/li]
[/ol]


The Server side script should eb pretty basic. Just looks for a value in the query string $_GET variable, or the $_POST variable to use to query the DB. Once it does that you'll want to have it echo the result so it can be read by the AJAX call. This needs to completely work on its own and echo out something. no need for HTML or anything like that. Based on your query a single value should be all it takes.


The AJAX call is pretty straight forward.

function ajaxCall(fieldObj)
{
  xmlhttp=new XMLHttpRequest();

  xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
      alert('The returned value was: ' + xmlhttp.responseText);

    }
  }
var str = fieldObj.value;
xmlhttp.open("GET","nameofscript.php?q="+[red]str[/red],true);
xmlhttp.send();
}

In this case I included the alert in the AJAX call, but you can have it call another function if you need to.





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[deeciple]

Hi Vacunita,


Thanks for your help. I think I got it but something isn't quite right because I am not getting the alert when I tab away from the form field after entering a known existing value. The script you sent me is pretty much unchanged and here is my PHP query script as well as the HTML from my form field. Can you help me see what I am doing wrong?

Thanks,

Ken

function ajaxCall(fieldObj)
{
  xmlhttp=new XMLHttpRequest();

  xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
      alert('The material ID ' + xmlhttp.responseText) + ' already exists.';

    }
  }
var str = fieldObj.value;
xmlhttp.open("GET","query-materialid.php?q="+str,true);
xmlhttp.send();
}

<?php require_once("../includes/connection.php"); ?>
<?php require_once("../includes/functions.php"); ?>
<?php error_reporting (E_ALL ^ E_NOTICE); ?>
<?php
	if(isset($_GET['str'])) {$MaterialID = $_GET['MaterialID'];}

?>
<?php
	$query = "SELECT `MaterialID` FROM `tblprograminfo` WHERE `MaterialID` LIKE $MaterialID";
	$result=mysql_query($query);

	if ($result) {
		echo $result;
	}
?>
<?php mysql_close($dbConnection); ?>

<input type="text" name="MaterialID" id="MaterialID" tabindex="3" size="17" onblur="ajaxCall()"/>

 

[deeciple]

I noticed some errors in my PHP script. The corrected version is below. BTW is there an "update" button to revise posts (instead of having to re-post)?

<?php require_once("../includes/connection.php"); ?>
<?php require_once("../includes/functions.php"); ?>
<?php error_reporting (E_ALL ^ E_NOTICE); ?>
<?php
	if(isset($_GET['q'])) {$MaterialID = $_GET['q'];}

?>
<?php
	$query = "SELECT `MaterialID` FROM `tblprograminfo` WHERE `MaterialID` LIKE $MaterialID";
	$result=mysql_query($query);

	if ($result) {
		echo $result;
	}
?>
<?php mysql_close($dbConnection); ?>

Ken

 

[vacunita]

O.k, let's start from the HTML and work back.

Your Input calls the ajaxCall function on blur. That's fine. However the function expects a parameter, as per the definition: fieldObj. But you pass none. It needs it to be able to get the value of the input. The "this" keyword references the object where the function is running, so send that over.

<input type="text" name="MaterialID" id="MaterialID" tabindex="3" size="17" onblur="ajaxCall([red]this[/red])"/>

The Ajax call: The Alert ends when the parenthesis closes, so everything after it should be issuing a syntax error most likely. If you needed to add more text to the alert, add it inside the parenthesis.

      alert('The material ID ' + xmlhttp.responseText + ' already exists.'[b][red])[/red][/b];

From the PHP side, you caught the mistake, the Ajax call sends q as the variable name, so that's what needs to be used in the GET array.

The only other thing I see, is that your query is going to send you back the same thing you passed, that being the MaterialID.

Also mysql_query returns a handle to a result set, not the actuall results. You'll want to use the mysql_fetch_* functions to get the result set data out.

For instance, if only be record is being returned,

$row=mysql_fetch_array($result);
$matID=$row['MaterialID'];

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[deeciple]

The only other thing I see, is that your query is going to send you back the same thing you passed, that being the MaterialID.

I'm not really sure I understand what you mean. What I am trying to do is query the table column (MaterialID) to see if the value in my form field exists there. Wouldn't the following query do this?

SELECT `MaterialID` FROM `tblprograminfo` WHERE `MaterialID` LIKE $MaterialID

I am also still having trouble as the mysql_fetch_array() function is returning the following error (which is probably related to my SQL lol.

"Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given..."

Kind regards,

Ken

 

[deeciple]

Ah! I see what you mean now. I needed to encase the variable in quotes like so:

SELECT `MaterialID` FROM `tblprograminfo` WHERE `MaterialID` LIKE '$MaterialID'

OK now I am getting an alert with the form field echoed back properly when there is a duplicate record and the mysql_fetch_array() function is happy. The problem is that I am getting the alert whether the query returns a match or not.

function ajaxCall(fieldObj) {
  xmlhttp=new XMLHttpRequest();

  xmlhttp.onreadystatechange=function() {

    if (xmlhttp.readyState==4 && xmlhttp.status==200) {
        alert('The material ID ' + xmlhttp.responseText + ' already exists.');
    }
  }  
var str = fieldObj.value;
xmlhttp.open("GET","query-materialid.php?q="+str,true);
xmlhttp.send();
}

<?php require_once("../includes/connection.php"); ?>
<?php require_once("../includes/functions.php"); ?>
<?php error_reporting (E_ALL ^ E_NOTICE); ?>
<?php
	if(isset($_GET['q'])) {
		$MaterialID = $_GET['q'];
	}

?>
<?php
	$query = "SELECT `MaterialID` FROM `tblprograminfo` WHERE `MaterialID` LIKE '$MaterialID'";
	$result=mysql_query($query);
	$row=mysql_fetch_array($result);
	$matID=$row['MaterialID'];

	if($matID) {
		echo $matID;
	}
?>
<?php mysql_close($dbConnection); ?>

 

[deeciple]

Hi Vacunita,

I got it working. Here is the working code:

function ajaxCall(fieldObj) {
var str = fieldObj.value;

  xmlhttp=new XMLHttpRequest();

  xmlhttp.onreadystatechange=function() {

    if (xmlhttp.readyState==4 && xmlhttp.status==200) {
      if (str==xmlhttp.responseText) {
        alert('The material ID ' + xmlhttp.responseText + ' already exists.');
        fieldObj.value="";
      }
    }
  }  
xmlhttp.open("GET","query-materialid.php?q="+str,true);
xmlhttp.send();
}

Thank you sooo much for your help.

Kind regards,

Ken

 

[vacunita]

Glad you worked it out.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech