如何连接3个表并为3个不同表中的多个字段创建变量？

Question

下面是完整的代码更新和建议。还添加了新的列名。如果是对的，请告诉我。

<?php
    //Initialize your first couple variables
    $encodedString = ""; //This is the string that will hold all your location data
    $x = 0; //This is a trigger to keep the string tidy

    //Now we query to the database      
    $result = mysql_query("SELECT visitors_pb_list.first, visitors_pb_list.last, visitors_pb_list.ip, visitors_pb_list.landing_page as visitors_pb_list_landing_page, pb_list.address, pb_list.landing_page as pb_list_landing_page, ziplatlang.longitude, ziplatlang.latitude FROM visitors_pb_list LEFT JOIN pb_list ON visitors_pb_list.landing_page = pb_list.landing_page LEFT JOIN ziplatlang ON pb_list.zip = ziplatlang.zip_code WHERE landing_page='" . $pb_list_id . "' order by id desc");

    //Multiple rows are returned
    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
        //This is to keep an empty first or last line from forming, when the string is split
        if ( $x == 0 )
        {
             $separator = "";
        }
        else
        {
             //Each row in the database is separated in the string by four *'s
             $separator = "****";
        }
        //Saving to the String, each variable is separated by three &'s
        $encodedString = $encodedString.$separator.
        "<p class='content'><b>Lat:</b> ".$row['latitude'].
        "<br><b>Long:</b> ".$row['longitude'].
        "<br><b>Name: </b>".$row['first'].$row['last'].
        "<br><b>Address: </b>".$row['address'].
        "<br><b>IP: </b>".$row['ip'].
        "</p>&&&".$row['latitude']."&&&".$row['longitude'];
        $x = $x + 1;
    }        
?>
<input type="hidden" id="encodedString" name="encodedString" value="<?php echo $encodedString; ?>" />

如果你想知道我在哪里得到的基本代码，它在这里：

http://www.macrostash.com/2011/09/17/demo-use-a-php-mysql-database-to-load-markers-on-a-google-map/#codesyntax_4

但是，我正在尝试使其适用于多个表。只有一个是不行的。需要使用visitors_pb_list进行循环。需要使用ziplatlang的纬度和经度。需要使用pb_list通过zip列连接其他两个表。到目前为止还没有成功。

Answer 1

您必须重命名具有相同名称的所有列，例如，如果所有三个表都有一个名称列：

SELECT 
visitors_pb_list.name as visitors_pb_list_name, 
pb_list.name as pb_list_name, 
ziplatlang.name as ziplatlong_name
FROM visitors_pb_list 
LEFT JOIN pb_list ON visitors_pb_list.landing_page = pb_list.landing_page 
LEFT JOIN ziplatlang ON pb_list.zip = ziplatlang.zip_code 
WHERE landing_page='" . $pb_list_id . "' order by id desc

在php中，我建议您按名称访问结果列，这是PDO的默认设置。

或者使用mysql函数：

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    //This is to keep an empty first or last line from forming, when the string is split
    if ( $x == 0 )
    {
         $separator = "";
    }
    else
    {
         //Each row in the database is separated in the string by four *'s
         $separator = "****";
    }
    //Saving to the String, each variable is separated by three &'s
    $encodedString = $encodedString.$separator.
    "<p class='content'><b>Lat:</b> ".$row['latitude'].
    "<br><b>Long:</b> ".$row['longitude'].
    "<br><b>Name: </b>".$row['name'];

    $x = $x + 1;
}