Search the Community

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); ?>