[mysql]自动完成地址表格跨两个表

标签: jQuery MySQL PHP
发布时间: 2017/4/8 23:08:33
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

在这里,新手需要的帮助。

我使用的代码从http://www.jensbits.com/2010/03/29/jquery-ui-autocomplete-widget-with-php-and-mysql/

和一些领域做得不错。我的问题是,我已有国家和存储在另一个表中的国家,"试图获取属性的非对象"错误传开"如果 ($address_query 网站-> num_rows) {"。

Autocomplete.php

$dbhost = 'SERVER';
$dbuser = 'USERNAME';
$dbpass = 'PASSWORD';
$dbname = 'DATABASE_NAME';

try {
  $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}
catch(PDOException $e) {
    echo $e->getMessage();
}

$return_arr = array();
if ($conn)
{
$ac_term = "%".$_GET['term']."%";
$address_query = "SELECT * FROM address where customer_id = '" . $customer_id . "' AND (firstname like :term OR lastname like :term) LIMIT 10";
if ($address_query->num_rows) { // got "Trying to get property of non-object" error
        $country_query = "SELECT * FROM country WHERE country_id = '" . (int)$address_query->row['country_id'] . "'";

if ($country_query->num_rows) {
            $country = $country_query->row['name'];
            $iso_code_2 = $country_query->row['iso_code_2'];
        } else {
            $country = '';
            $iso_code_2 = '';
        }

        $zone_query = "SELECT * FROM zone WHERE zone_id = '" . (int)$address_query->row['zone_id'] . "'";

        if ($zone_query->num_rows) {
            $zone = $zone_query->row['name'];
            $zone_code = $zone_query->row['code'];
        } else {
            $zone = '';
            $zone_code = '';
        }   
$result = $conn->prepare($address_query);
$result->bindValue(":term",$ac_term);
$result->execute();

/* Retrieve and store in array the results of the query.*/
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $return_arr [] = array(

    'label' => $row['firstname'] .' '. $row['lastname'] .' '. $row['company'] .' '.         $row['city'] .' '. $row['postcode'],
  'value' => $row['firstname'] .' '. $row['lastname'],
  'Company' => $row['company'],
  'Country' => $row['iso_code_2'],
  'postCode' => $row['postcode'],

   );
}


}

echo json_encode($return_arr);
flush();
?>

如果删除了代码

if ($query->num_rows) { // got "Trying to get property of non-object" error
        $country_query = "SELECT * FROM country WHERE country_id = '" . (int)$address_query->row['country_id'] . "'";

if ($country_query->num_rows) {
            $country = $country_query->row['name'];
            $iso_code_2 = $country_query->row['iso_code_2'];
        } else {
            $country = '';
            $iso_code_2 = '';
        }

        $zone_query = "SELECT * FROM zone WHERE zone_id = '" . (int)$address_query->row['zone_id'] . "'";

        if ($zone_query->num_rows) {
            $zone = $zone_query->row['name'];
            $zone_code = $zone_query->row['code'];
        } else {
            $zone = '';
            $zone_code = '';
        }   

它适用于姓名、 公司和邮编。顺便说一句,国家和状态字段是下拉列表中选择窗体。

任何帮助将不胜感激 !

解决方法 1:

你的 $address_query 不是对象,而一个字符串所以你得到错误"试图获取非对象的属性"

若要访问您需要访问它通过 $conn 创建 pdo 对象

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);

$address_query = "SELECT * FROM address where customer_id = '" . $customer_id . "' AND (firstname like :term OR lastname like :term) LIMIT 10";

$result = $conn->prepare($address_query);
$result->execute(array(':term'-> $term)); 

if ($result->rowCount()){

对象是 $conn 和 $address_query 是字符串,字符串中你也持有地方︰ 你需要一词在执行函数中设置该值为您的 sql 语句,这找到所有︰ 长期在字符串和替换的值

您的 sql 也出格既然你已经知道国家表中的 id 你应联接表

您的代码应类似于︰

$dbhost = 'SERVER';
$dbuser = 'USERNAME';
$dbpass = 'PASSWORD';
$dbname = 'DATABASE_NAME';

try {
  $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
}
catch(PDOException $e) {
    echo $e->getMessage();
}

$return_arr = array();
if ($conn)
{
$ac_term = "%".$_GET['term']."%";
$address_query = "SELECT * FROM address LEFT JOIN country address.country_id = country. country_id ON where address.customer_id = '" . $customer_id . "' AND (address.firstname like :term OR address.lastname like :term) LIMIT 10";

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

/* Retrieve and store in array the results of the query.*/
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    $return_arr [] = array(

    'label' => $row['firstname'] .' '. $row['lastname'] .' '. $row['company'] .' '.         $row['city'] .' '. $row['postcode'],
  'value' => $row['firstname'] .' '. $row['lastname'],
  'Company' => $row['company'],
  'Country' => $row['iso_code_2'],
  'postCode' => $row['postcode'],

   );
}}
赞助商