mysqli_fetch_array with table.column

I’m trying to use mysqli_fetch_array to retrieve the results of a SELECT query. It works just fine except for when I specify the table from which to fetch the data. For example,

$sql = "SELECT datasheet.id, authors.name, technique,
        description, approvers.name FROM datasheet
        INNER JOIN authors ON authorid = authors.id
        INNER JOIN approvers ON approvedby = approvers.id
        WHERE datasheet.id = 1";

$result = mysqli_query($link, $sql);

while ($row = mysqli_fetch_array($result))
{
    $datasheetdata[] = array ('id' => $row['datasheet.id'], 
                                        'name' => $row['authors.name'], 
                                        'technique' => $row['technique'], 
                                        'description' => $row['description'], 
                                        'approvedby' => $row['approvers.name']); 
}

If I then do:

<?php foreach ($datasheetdata as $datasheet): ?>
	<h1><p><?php htmlout($datasheet['title']); ?></p></h1>
         <p>Approved By: <?php htmlout($datasheet['approvedby']); ?></p>
<?php endforeach; ?>

Only the title shows, the approvers.name data is not picked up.

Would appreciate any insights. Thanks!

Normally, the columns have the same name in the result set as they have in the tables. Only when you select two columns from different tables that have the same name, MySQL renames one to keep the names unique. If you don’t want this to happen, you can rename them yourself in your query using aliasses:


SELECT 
    datasheet.id
  , authors.name AS authorname
  , technique
  , description
  , approvers.name AS approvername
FROM datasheet
INNER JOIN authors ON authorid = authors.id
INNER JOIN approvers ON approvedby = approvers.id
WHERE datasheet.id = 1

I never refer to columns by number. It makes the code so much less understandable.

Brilliant! That’s a much better solution. Thanks Guido!

Good point about the title. I simplified the code I posted above to make it easier to explain the problem and forgot that I had removed the title from the code. :slight_smile:

Thanks for the print_r tip. The column names do not contain the table names indeed.

I have two columns called ‘name’, one in the authors table and the other in the approvers table.

According to the print_r output,

authors.name has been turned to [2]
and approvers.name has been turned to [name]

I can now get to the data using:
‘name’ => $row[2]
‘approvedby’ => $row[‘name’]

Thanks Guido!

I’d like to know more about how this works though. Are there any rules for the naming of the columns when using mysqli_fetch_array or do the names always have to be identified on a case by case basis?

Or is it considered best practice to simply refer to the columns by numbers, i.e. $row[0], $row[1], etc?

It’s strange that the title shows, since you didn’t put a title in that array, at least not in the code you posted :wink:

And about the table names, if you do a print_r of $row, you’ll see that the column names do not contain the table name.