MYSQL, PHP & JQuery AutoComplete. Can you use it with a JOIN on multiple tables?

Question

Okay so I'm getting to the next stage with JQuery and I've used AutoComplete a few times with success but only using 1 table from MYSQL. Now I'm using it and trying to do a JOIN query with it. The MYSQL query works fine when I change LIKE :term to = 1 (A customer number for example.) in MYSQL but when I try the Query below I dont get anything, not errors just nothing. Any suggestions?

::: THIS IS THE SEARCH PAGE :::

<?php require '../_includes/jq.inc.php';?>
<link href="../_stylesheets/UPDATE.Customer.css" rel="stylesheet" type="text/css">
<html>
<head>
<script type="text/javascript">
$(function() {
    $('#CustomersCustomer_ID').val("");
    $('#CustomersCompanyName').val("");
    $('#CustomerDetailsDiscount').val("");
    $('#CustomerNotesCustNotes').val("");
           $("#searchterm").autocomplete({
                source: "UPDATE.Customer.SEARCH.QUERYTEST.php",
                minLength: 1,
                select: function(event, ui) {
                        $('#CustomersCustomer_ID').val(ui.item.CustomersCustomer_ID);
                        $('#CustomersCompanyName').val(ui.item.CustomersCompanyName);
                        $('#CustomerDetailsDiscount').val(ui.item.CustomerDetailsDiscount);
                        $('#CustomerNotesCustNotes').val(ui.item.CustomerNotesCustNotes);
                }
            });
     });
</script>
</head>
<body>
<form action="<?php echo $PHP_SELF;?>"  method="post">
SEARCH:<p class="ui-widget">
<input type="text" id="searchterm" size="60" onClick="this.form.reset()"/>
</form>
<form>
  CustomersCustomer_ID<br>
  <input name="CustomersCustomer_ID" type="text" id="CustomersCustomer_ID" size="10">
  <br>
  CustomersCompanyName<br>
  <input name="CustomersCompanyName" type="text" id="CustomersCompanyName" size="20">
  <br>
  CustomerNotesCustNotes<br>
  <textarea name="CustomerNotesCustNotes" id="CustomerNotesCustNotes" cols="20" rows="3"></textarea>
 <br>
  CustomerDetailsDiscount<br>
  <input name="CustomerDetailsDiscount" type="text" id="CustomerDetailsDiscount" size="5">
</form>
</body>
</html>

THIS IS THE BACKEND QUERY PAGE // LEFT OUT THE CONNECTION INFO

if ($conn)
{
$ac_term = "%".$_GET['term']."%";
$query = "SELECT
    Customers.Customer_ID,
    Customers.CompanyName,
    CustomerDetails.Discount,
    CustomerNotes.CustNotes,
    CONCAT_WS(' ', Customers.Customer_ID, Customers.CompanyName, CustomerDetails.Discount, CustomerNotes.CustNotes) AS DisplayName
FROM Customers
JOIN CustomerDetails USING (`Customer_ID`)
JOIN CustomerNotes USING (`Customer_ID`)
WHERE `Customer_ID` LIKE :term
    OR Customers.CompanyName LIKE :term
    OR CustomerDetails.Discount LIKE :term
    OR CustomerNotes.CustNotes LIKE :term
    ";

    $result = $conn->prepare($query);
    $result->bindValue(":term",$ac_term);
    $result->execute(); 

    while ($row = $result->fetch(PDO::FETCH_ASSOC)) {

        $row_array['value'] = $row['DisplayName'];

        $row_array['CustomersCustomer_ID'] = $row['Customers.Customer_ID'];
        $row_array['CustomersCompanyName'] = $row['Customers.CompanyName'];
        $row_array['CustomerDetailsDiscount'] = $row['CustomerDetails.Discount'];       
        $row_array['CustomerNotesCustNotes'] = $row['CustomerNotes.CustNotes'];

        array_push($return_arr,$row_array);
    }

}
$conn = null;
echo json_encode($return_arr);
?>

possible duplicate of [PHP PDO prepared statement -- mysql LIKE query](http://stackoverflow.com/questions/1786436/php-pdo-prepared-statement-mysql-like-query) — Marc B, Oct 21 '11 at 21:44

score 0 · Accepted Answer · answered Oct 21 '11 at 22:36

0

i think that the reason you don't get any results is because that you have an error in your query (duplicate 'Customer_ID' field), try this one:

$query = "SELECT
    Customers.Customer_ID,
    Customers.CompanyName,
    CustomerDetails.Discount,
    CustomerNotes.CustNotes,
    CONCAT_WS(' ', Customers.Customer_ID, Customers.CompanyName, CustomerDetails.Discount, CustomerNotes.CustNotes) AS DisplayName
FROM Customers
JOIN CustomerDetails USING (`Customer_ID`)
JOIN CustomerNotes USING (`Customer_ID`)
WHERE Customers.Customer_ID LIKE :term
    OR Customers.CompanyName LIKE :term
    OR CustomerDetails.Discount LIKE :term
    OR CustomerNotes.CustNotes LIKE :term
    ";

answered Oct 21 '11 at 22:36

Alon Eitan

11,997
8
49
58

No, this did not help. I still get nothing. Thank you for your suggestion. – Mont Oct 22 '11 at 14:53
so maybe change this line `$ac_term = "%".$_GET['term']."%";` to `$ac_term = "'%".$_GET['term']."%'";` – Alon Eitan Oct 22 '11 at 15:16
1

The solution turned out to be autocomplete array did not like the tablename.columName name. I changed it to just the columname and it works fine now. Thanks for you help. – Mont Oct 24 '11 at 20:56

MYSQL, PHP & JQuery AutoComplete. Can you use it with a JOIN on multiple tables?

1 Answers1