too long

I have a few tables joined together, but several of the columns have similar names. I'm using php to fetch those values, but I am unable to use the sql identifier in php to pick the correct column... i.e. in SQL I have two tables Register and Jurisdict. both of them have a column called "name". in SQL I can reference these columns as such

r.name
j.name

and these would give me values from the two different tables. However in I PHP am not able to use the r. or j. and anytime I use name it returns which ever table is selected first in the SQL statement.

Here is my current code

<?php


    $sql = "
SELECT j.rpt_name, r.name 
FROM [SMARTCM] . [dbo] . [REGISTER] r

join [SMARTCM] . [dbo] . [jurisdict] j
on j.UniqueKey = r.FK_JURSDICT_KEY

$stmt = sqlsrv_query( $conn, $sql );

?>

I'm using a while loop to generate the PHP, with this tag to reference the column

<?php echo $row['name']; ?>

However it breaks if I try and use the table reference (r.name) like i would i sql

you need to use aliasing feature of SQL query..

<?php


    $sql = "
SELECT j.name as j_name, r.name as r_name 
FROM [SMARTCM] . [dbo] . [REGISTER] r

join [SMARTCM] . [dbo] . [jurisdict] j
on j.UniqueKey = r.FK_JURSDICT_KEY";

$stmt = sqlsrv_query( $conn, $sql );


echo $row['j_name'];

echo $row['r_name'];
?>

You haven't shared the code involved in row retrieval but I presume it's using sqlsrv_fetch_array(). If you look at the documentation you'll see it has a parameter called $fetchType:

array sqlsrv_fetch_array ( resource $stmt [, int $fetchType [, int $row [, int $offset ]]] )

fetchType

A predefined constant specifying the type of array to return. Possible values are SQLSRV_FETCH_ASSOC, SQLSRV_FETCH_NUMERIC, and SQLSRV_FETCH_BOTH (the default).

... and then the important bit:

A fetch type of SQLSRV_FETCH_ASSOC should not be used when consuming a result set with multiple columns of the same name.

So you have to choose:

  • Have dupe names and retrieve stuff from numeric arrays (SQLSRV_FETCH_NUMERIC)

  • Get unique names so you can use associative arrays

    SELECT j.name AS j_name, r.name AS r_name

Last but not least, array contents (or any other PHP variable) should never be a mystery to you. Please have a look at var_dump().