Jump to content


Photo

AJAX PHP MYSQL


  • Please log in to reply
3 replies to this topic

#1 arandlett

arandlett

    Young Padawan

  • Members
  • Pip
  • 2 posts

Posted 16 May 2006 - 11:33 AM

I found some code on this wonderful thing called the internet to call mysql using Ajax through php. I am trying to setup a multiple field search. The search works except all fields have to have something for it to find items in the table. Any ideas on how i can change my code to allow for all or some of the fields to be populated to find items associated with those search items.

// If the user is using Mozilla/Firefox/Safari/etc
if (window.XMLHttpRequest) {
		//Intiate the object
		xmlhttp = new XMLHttpRequest();
		//Set the mime type
		xmlhttp.overrideMimeType('text/xml');
} else if (window.ActiveXObject) {
		//Intiate the object
		xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
}

function preSearch() {
	//Put the form data into a variable
	
	
	var theQuery = document.getElementById('query').value;
	var theQuery_a = document.getElementById('query2').value;
	var theQuery_b = document.getElementById('query3').value;
	
	
	
	//If the form data is *not* blank, query the DB and return the results
	if(theQuery!== "") {
		//Change the content of the "result" DIV to "Searching..."
		//This gives our user confidence that the script is working if it takes a moment for the result to be returned. However the user will likely never see this...
		document.getElementById('result').innerHTML = "Searching...";
		
		//This sets a variable with the URL (and query strings) to our PHP script
		var url = 'test.php?type=' + theQuery + '&Keyword=' + theQuery_a + '&church=' + theQuery_b;
		//Open the URL above "asynchronously" (that's what the "true" is for) using the GET method
		xmlhttp.open('GET', url, true);
		//Check that the PHP script has finished sending us the result
		xmlhttp.onreadystatechange = function() {
			if(xmlhttp.readyState == 4 && xmlhttp.status == 200) {
				//Replace the content of the "result" DIV with the result returned by the PHP script
				document.getElementById('result').innerHTML = xmlhttp.responseText + ' ';
			} else {
				//If the PHP script fails to send a response, or sends back an error, display a simple user-friendly notification
				document.getElementById('result').innerHTML = 'Error: preSearch Failed!';
			}
		};
		xmlhttp.send(null);  
	}
}
//-->

Edited by arandlett, 16 May 2006 - 11:34 AM.


#2 rc69

rc69

    PHP Master PD

  • P2L Staff
  • PipPipPipPip
  • 3,827 posts
  • Gender:Male
  • Location:Here
  • Interests:Web Development

Posted 16 May 2006 - 05:48 PM

Since the JS doesn't actually associate with the database, and it doesn't preformat the query that the PHP will use, could you show us the PHP that is used? We should only need the mysql_query, but everything above it could help incase i'm wrong.

But if you already know a little about what you're doing, i'm assuming your query uses the keyword AND in the query, using OR would probably achieve the desired effect.

#3 arandlett

arandlett

    Young Padawan

  • Members
  • Pip
  • 2 posts

Posted 16 May 2006 - 09:31 PM

Most of the php was writen by Interak Dreamweaver components. However, i wrote the sql query using dreamweaver tools.
I have included the code in the php file. Ideally I would like to have 3 or 4 form variables for choice to search from.


<?php require_once('../Connections/ABA.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
	case "text":
	  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
	  break;	
	case "long":
	case "int":
	  $theValue = ($theValue != "") ? intval($theValue) : "NULL";
	  break;
	case "double":
	  $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
	  break;
	case "date":
	  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
	  break;
	case "defined":
	  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
	  break;
  }
  return $theValue;
}
}

$varT_list = "-1";
if (isset($_GET['type'])) {
  $varT_list = (get_magic_quotes_gpc()) ? $_GET['type'] : addslashes($_GET['type']);
}
$varD_list = "-1";
if (isset($_GET['Keyword'])) {
  $varD_list = (get_magic_quotes_gpc()) ? $_GET['Keyword'] : addslashes($_GET['Keyword']);
}
mysql_select_db($database_ABA, $ABA);
$query_list = sprintf("SELECT Assets.id, Assets.item, Assets.type, Assets.`description`, Assets.owner, Assets.serial_number, Assets.condition, Assets.accessories, Assets.status, Assets.picture FROM Assets WHERE Assets.description like CONCAT('%%', %s, '%%') OR Assets.type like CONCAT('%%', %s, '%%') ORDER BY Assets.type", GetSQLValueString($varD_list, "text"),GetSQLValueString($varT_list, "text"));
$list = mysql_query($query_list, $ABA) or die(mysql_error());
$row_list = mysql_fetch_assoc($list);
$totalRows_list = mysql_num_rows($list);
?>
<?php


require_once('../Connections/ABA.php'); 


require_once('../includes/common/KT_common.php');

// Load the tNG classes
require_once('../includes/tng/tNG.inc.php');

// Make a transaction dispatcher instance
$tNGs = new tNG_dispatcher("../");

// Make unified connection variable
$conn_ABA = new KT_connection($ABA, $database_ABA);



if($_GET['type'] || $_GET['Keyword'] !=""){

 do { 
 echo('<table width="654" border="0" cellspacing="3" cellpadding="2">');
	 echo(' <tr>');
	  echo('<td width="105" height="22" valign="top"><div align="right">Item</div></td>');
	  echo('<td width="348" valign="top">');
	   echo $row_list['item'];
		echo('</td>');
	  echo(' <td width="177" rowspan="8" valign="top"><img src="');
	   echo tNG_showDynamicImage("../", "../aba_resources/images/", "{list.picture}");
		echo('" /></td>');
	 echo('</tr>');
	 echo('<tr valign="top">');
	  echo(' <td><div align="right">Type</div></td>');
	  echo(' <td>'); 
	  echo $row_list['type']; 
	  echo( '</td>');
	echo( '</tr>');
	echo( '<tr valign="top">');
	 echo( ' <td><div align="right">Description</div></td>');
	   echo( '<td>'); 
	   echo $row_list['description']; 
	   echo( '</td>');
	echo( '</tr>');
	echo( '<tr valign="top">');
	  echo( '<td><div align="right">Owner</div></td>');
	  echo( '<td>'); 
	  echo $row_list['owner'];  
	  echo('</td>');
	echo('</tr>');
   echo(' <tr valign="top">');
	  echo('<td><div align="right">Serial Number </div></td>');
	  echo('<td>'); 
	  echo $row_list['serial_number']; 
	  echo('</td>');
   echo('</tr>');
	echo(' <tr valign="top">');
	   echo('<td><div align="right">Condition</div></td>');
	   echo('<td>');  
	   echo $row_list['condition']; 
	   echo('</td>');
   echo('</tr>');
   echo(' <tr valign="top">');
	  echo(' <td><div align="right">Accessories</div></td>');
	  echo('<td>'); 
	  echo $row_list['accessories'];  
	  echo('</td>');
   echo('</tr>');
   echo(' <tr valign="top">');
	  echo('<td><div align="right">Status</div></td>');
	  echo('<td>');
	   echo $row_list['status'];  
	  echo('</td>');
   echo('</tr>');
	 echo('<tr valign="top">');
	   echo('<td>&nbsp;</td>');
	   echo('<td>&nbsp;</td>');
	   echo('<td valign="top">&nbsp;</td>');
	 echo('</tr>');
	   echo('</table>');
   } while ($row_list = mysql_fetch_assoc($list)); 
   

}else{

echo("No Search Results");
};


?>

<?php
mysql_free_result($list);
?>

Edited by arandlett, 16 May 2006 - 09:35 PM.


#4 rc69

rc69

    PHP Master PD

  • P2L Staff
  • PipPipPipPip
  • 3,827 posts
  • Gender:Male
  • Location:Here
  • Interests:Web Development

Posted 16 May 2006 - 10:40 PM

I never thought i'd see WYSIWYG php/mysql... That has to be the dumbest idea i've ever heard of... But enough about me, lets talk about you.

I only see one real "problem" with the query. I've got no idea whether or not fixing it will fix the problem, but then again, i don't know why mysql isn't throwing an error every time you try to run that query. Use the following instead:
$query_list = sprintf("SELECT Assets.id, Assets.item, Assets.type, Assets.description, Assets.owner, Assets.serial_number, Assets.condition, Assets.accessories, Assets.status, Assets.picture FROM Assets WHERE Assets.description LIKE CONCAT('%%', %s, '%%') OR Assets.type LIKE CONCAT('%%', %s, '%%') ORDER BY Assets.type", GetSQLValueString($varD_list, "text"),GetSQLValueString($varT_list, "text"));
You had backticks around the first "description", to the best of my knowlege, that's invalid mysql. But i don't agree with a lot of stuff in a lot of that code, one of which happens to be using table.field to select fields when there is only one table (therefore, i don't know how you would properly backtick them).

If that doesn't fix the problem, i don't know what will. My only other suggestion would be to redo the entire script from the ground up, by hand.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users