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

Question

here is entire code updated with suggestions. also added new column names. let me know if it's right.

<?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; ?>" />

if you want to know where i got the base code, it's here:

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

however i'm trying to adapt it to multiple tables. can't do it with just one. need to use visitors_pb_list for the loop. need to use ziplatlang for the latitude & longitude. need to use pb_list to bridge the two other tables via zip columns. so far not finding success.

Answer 1

You have to rename all columns that have the same name, for example if all three tables have a name column:

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

In php, I suggest you access the resulting columns by name, which is the default for PDO

or with mysql functions:

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;
}