Inner Joined Tables with Search Engine in Javascript wont produce query to databse

[dmfgkdg]

 

 

I am having the search form query a inner joined table from multiple other tables in MySQL. I am using a local intranet with Xampp/Apache with When I run the SQL in PHPMyAdmin, it runs Perfect and produces the results exactly as I want them to appear on my website.

My database name is: loodt My tables are: ee, er, aa, adjuster and attorney. I have foreign keys on the ee table for the other tables (ee.er_id = er.id) etc...

However, when I try to run a search on my website, it pops up an error saying "Could not query database for search results, MYSQL ERROR Table 'loodt.ee a" doesn't exist." So I tried taking away the shortcut letters [referenced below in code] (A, B, C, D< E, & F) and typing in ee.name, er.name, etc... instead of assigning these letters and it didnt work either.

I was using the search engine already to query results from a single table before I started to modify it a few days ago, So I know it works and has to be an issue with the "temporary" table and my lack of knowledge of course.

Here is the search engine javascript:

$(function(){

//Hide the result list on load

$('#results-list').hide();

//Click event when search button is pressed

$('#search').click(function(){

doSearch();

});

//Keypress event to see if enter was pressed in text input

$('#text').keydown(function(e){

if(e.keyCode == 13){

doSearch();

}

});

});

>

function doSearch() {

var searchText = $('#text').val();

//Rehide the search results

$('#results-list').hide();

$.ajax({

url: './search.php',

type: 'POST',

data: {

'text': searchText

},

beforeSend: function(){

//Lets add a loading image

$('#results-holder').addClass('loading');

},

success: function(data) {

//Take the loading image away

$('#results-holder').removeClass('loading');

//Was everything successful, no errors in the PHP script

if (data.success) {

//Clear the results list

$('#results-list').empty();

//Display the results

//Check to see if there are any results to display

if(data.results.length > 0) {

//Loop through each result and add it to the list

$.each(data.results, function(){

//Give the list element a rel with the data results ID incase we want to act on this later, like selecting from the list

$('#results-list').append(

"<li rel='" + this.A_name + "'>" + this.D_name + " | " + this.A_file_no + " | " + this.A_claim_no + " | " + this.A_adj_no + " | " + this.F_acronym + " | " + this.A_doi + " | " + this.A_opened + " | " + this.A_status + " | " + this.A_redwells + " | " + this.E_firm_name + " | " + this.B_name + " | " + this.C_initials + " </li>");

});

} else {

//If there are no results, inform the user - add 'no-results' class so we can style it differently

$('#results-list').append("<li class='no-results'>Your search did not return any results</li>");

}

//Show the results list

$('#results-list').fadeIn();

} else {

//Display the error message

alert(data.error);

}

}

});

}

 The javascript file above is called from my index page in the header, not sure if that is important. And this is the search.php file that is called inside the javascript code above.   

> <?php

//Prepare an array to hold data we are going to send back to the jQuery

$data = array(

'results' => array(),

'success' => false,

'error' => '' 

);

//Has the text been posted?

if (isset($_POST['text'])) {

//Connect to the database



>

$dbhost = 'localhost'; //hostname

$dbuser = 'xxxx'; //database username

$dbpass = 'xxxxx'; //database password

$dbname = 'loodt'; //database name

>

//Create the connection to the mySQL database or catch the exception if there is an error

$db = new mysqli($dbhost, $dbuser, $dbpass);

>

$db->select_db($dbname);

>

if($db->connect_errno > 0){

die('ERROR! - COULD NOT CONNECT TO mySQL DATABASE: ' . $db->connect_error);

}

>


> //Escape the text to prevent SQL injection

$text = $db->real_escape_string($_POST['text']);

//Run a LIKE query to search for titles that are like the entered text

>

$q = "SELECT `A.name AS A_name`, `D.name AS D_name`, `A.file_no AS A_file_no`, `A.claim_no AS A_claim_no`,

`A.adj_no AS A_adj_no`, `F.acronym AS F_acronym`, `A.doi AS A_doi`, `A.opened AS A_opened`,

`A.status AS A_status`, `A.redwells AS A_redwells`, `C.initials AS C_initials`, `B.name AS B_name`, `E.firm_name AS E_firm_name`

FROM `ee A`

INNER JOIN `adjuster B` ON `A.adjuster_id` = `B.id`

INNER JOIN `attorney C` ON `A.attorney_id` = `C.id`

INNER JOIN `er D` ON `A.er_id` = `D.id`

INNER JOIN `aa E` ON `A.aa_id` = `E.id`

INNER JOIN `wcab F` ON `A.wcab_id` = `F.id`




>

WHERE


`A_name` LIKE '%{$text}%' OR `A_file_no` LIKE '%{$text}%' OR `A_claim_no` LIKE '%{$text}%' OR `A_adj_no` LIKE '%{$text}%' OR `A_doi` LIKE '%{$text}%'";;

$result = $db->query($q);

//Did the query complete successfully?

if (!$result) {

//If not add an error to the data array

$data['error'] = "Could not query database for search results, MYSQL ERROR: " . $db->error;

} else {

//Loop through the results and add to the results array

while ($row = $result->fetch_assoc()) {

$data['results'][] = array(

'A_name' => $row['A_name'],

'D_Name' => $row['D_name'],

'A_file_no' => $row['A_file_no'],

'A_claim_no' => $row['A_claim_no'],

'A_adj_no' => $row['A_adj_no'],

'F_acronym' => $row['F_acronym'],

'A_doi' => $row['A_doi'],

'A_opened' => $row['A_opened'],

'A_status' => $row['A_status'],

'A_redwells' => $row['A_redwells'],

'C_initials' => $row['C_initials'],

'B_name' => $row['B_name'],

'E_firmname' => $row['E_firmname']


);

}

//Everything went to plan so set success to true

$data['success'] = true;

}

}

//Set the content type for a json object and ensure charset is UTF-8. NOt utf8 otherwise it will not work in IE (Darn IE! >.<)

header("Content-Type: application/json; charset=UTF-8");

//json encode the data and cast to an object so we can reference items like this.id in the javascript instead of this['id'] etc.

echo json_encode((object)$data);

?>

 

[Barand]

That is because you are including the table alias inside the backticks with the table name

 

FROM `ee` A

 

not

 

FROM `ee A`

 

You only need backticks if your table name is a reserved word or contains spaces or other special characters

[dmfgkdg]

That is because you are including the table alias inside the backticks with the table name

 

FROM `ee` A

 

not

 

FROM `ee A`

 

You only need backticks if your table name is a reserved word or contains spaces or other special characters

Okay, so I changed the backticks and now it says the syntax is wrong at :

INNER JOIN `adjuster` B ON `A.adjuster_id` = `B.id`

INNER JOIN `attorney` C ON `A.attorney_id` = `C.id`

[Barand]

INNER JOIN `adjuster` B ON `A`.`adjuster_id` = `B`.`id`

Everything inside backticks is taken to be a single identifier. Just get rid of them, you don't need them.

[dmfgkdg]

INNER JOIN `adjuster` B ON `A`.`adjuster_id` = `B`.`id`

Everything inside backticks is taken to be a single identifier. Just get rid of them, you don't need them.

 

So, delete all the backticks in this SQL sequence? Just to confirm.

[dmfgkdg]

Okay, so I changed my search.php file to get rid of all the backticks. Now, when I search the form. Absolutely nothing happens. here is the new search.php file:

<?php
	//Prepare an array to hold data we are going to send back to the jQuery
	$data = array(
		'results' => array(),
		'success' => false,
		'error' => '' 
	);
	//Has the text been posted?
	if (isset($_POST['text'])) {
		//Connect to the database
		


$dbhost = 'localhost'; //hostname
$dbuser = 'root'; //database username
$dbpass = 'runrun'; //database password
$dbname = 'loodt'; //database name

//Create the connection to the mySQL database or catch the exception if there is an error
$db = new mysqli($dbhost, $dbuser, $dbpass);

$db->select_db($dbname);

if($db->connect_errno > 0){
    die('ERROR! - COULD NOT CONNECT TO mySQL DATABASE: ' . $db->connect_error);
}


		//Escape the text to prevent SQL injection
		$text = $db->real_escape_string($_POST['text']);
		//Run a LIKE query to search for titles that are like the entered text

		$q = "SELECT A.name, D.name, A.file_no, A.claim_no, 
A.adj_no, F.acronym, A.doi, A.opened, 
A.status, A.redwells, C.initials, B.name, E.firm_name
FROM ee A
INNER JOIN adjuster B ON A.adjuster_id = B.id
INNER JOIN attorney C ON A.attorney_id = C.id
INNER JOIN er D       ON A.er_id = D.id
INNER JOIN aa E       ON A.aa_id = E.id
INNER JOIN wcab F     ON A.wcab_id = F.id




WHERE 

A.name LIKE '%{$text}%' OR A.file_no  LIKE '%{$text}%' OR A.claim_no  LIKE '%{$text}%' OR A.adj_no  LIKE '%{$text}%' OR A.doi  LIKE '%{$text}%'";
		$result = $db->query($q);
		//Did the query complete successfully?
		if (!$result) {
			//If not add an error to the data array
			$data['error'] = "Could not query database for search results, MYSQL ERROR: " . $db->error;
		} else {
			//Loop through the results and add to the results array
			while ($row = $result->fetch_assoc()) {
				$data['results'][] = array(
					'A_name' => $row['A_name'],
					'D_Name' => $row['D_name'],
					'A_file_no' => $row['A_file_no'],
					'A_claim_no' => $row['A_claim_no'],
					'A_adj_no' => $row['A_adj_no'],
					'F_acronym' => $row['F_acronym'],
					'A_doi' => $row['A_doi'],
					'A_opened' => $row['A_opened'],
					'A_status' => $row['A_status'],
					'A_redwells' => $row['A_redwells'],
					'C_initials' => $row['C_initials'],
					'B_name' => $row['B_name'],
					'E_firmname' => $row['E_firmname']
					
				);
			}
			//Everything went to plan so set success to true
			$data['success'] = true;
		}
	}
	//Set the content type for a json object and ensure charset is UTF-8. NOt utf8 otherwise it will not work in IE (Darn IE! >.<)
	header("Content-Type: application/json; charset=UTF-8");
	//json encode the data and cast to an object so we can reference items like this.id in the javascript instead of this['id'] etc.
	echo json_encode((object)$data);
?>

[Barand]

What should happen?

[dmfgkdg]

What should happen?

Well, per the javascript, a nice little results list should pop up per the search criteria "text" typed into the search form.

 

I also updated the javascript parts where it has A_name, etc... to A.name (My current error probably resides in the javascript syntax) but I'm not sure.