Empty results when using $row[$variable] with LEFT JOIN in PHP

Question

I'd like to begin by saying that this is my first question here at stack and I apologize in advance if this question has been answered before, however I have been so far unable to find an answer or fix it myself.

I am trying to use the SELECT function in a php file to run a basic report. I wrote the SQL in PHPMyAdmin and used the convert-to-php button to do just that. What I get is the following:

SELECT
l.id AS 'ID',
l.type AS 'Type',
l.state AS 'State',
l.won_at AS 'Won',
l.lost_at AS 'Lost',
l.cancelled_at AS 'Cancelled',
l.created_at AS 'Created',
l.source AS 'Source',
u.first_name AS 'Owner First Name',
u.last_name AS 'Owner Last Name'
FROM `leads` AS l
LEFT JOIN `users` AS u ON
(u.`id` = l.`owner_id`)
LEFT JOIN `regions` AS rg ON
(rg.`id` = l.`region`)
WHERE l.`state` IS NOT NULL
[...]";

When I put this into a PHP document it looks like this:

    <?php
// Create connection
$con=mysqli_connect("localhost","root","pass","database");

// Check connection
if (mysqli_connect_errno($con))
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// And now for the good stuff

$result = mysqli_query($con,"SELECT
l.id AS 'LeadID',
l.type AS 'Type',
l.state AS 'State',
l.won_at AS 'Won',
l.lost_at AS 'Lost',
l.cancelled_at AS 'Cancelled',
l.created_at AS 'Created',
l.source AS 'Source',
u.first_name AS 'First Name',
u.last_name AS 'Last Name'
FROM `leads` AS l
LEFT JOIN `users` AS u ON
(u.`id` = l.`owner_id`)
WHERE l.`state` IS NOT NULL
");

echo "<table border='1'>
<tr>
<th>Test1</th>
<th>Test2</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row["$LeadID"] . "</td>";
  echo "<td>" . $row["$l.type"] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>

What seems to be happening is that I am able to create the table with no issue only when there is only one database being selected, and when I use LEFT JOIN, I have so far been unable to find a way to change the $row["$variable"] input to something that will work.

I know that the data is there and I know that the connection works, it's just the LEFT JOIN that is giving me a bit of trouble.

Any help on this would be greatly appreciated!

Worked, thanks for the help. Now to go try to make myself feel less dumb... — Tanner Paaltomo
– Tanner Paaltomo, Commented Sep 29, 2013 at 21:18
Probably because I had overlooked such a small detail and someone thought it wasn't an appropriate question. At the end of the day, regardless of what I had asked, I got fast assistance from people willing to do so simply because they like to help others. From a first question asked point of view, it's been a great experience. — Tanner Paaltomo
– Tanner Paaltomo, Commented Sep 29, 2013 at 23:22

Barmar · Accepted Answer · 2013-09-29 21:18:27Z

1

use:

echo "<td>" . $row["LeadID"] . "</td>";
echo "<td>" . $row["Type"] . "</td>";

You don't need $, these are just literal strings not the values of variables. And the keys are case-sensitive, so you have to use Type, not type.

answered Sep 29, 2013 at 21:18

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tanner Paaltomo Over a year ago

Awesome, worked perfectly. I had looked around for another answer before but found only quasi-related things, probably because I overlooked this admittedly pretty simple part. Thanks for the tip!

Akinak · Accepted Answer · 2013-09-29 21:22:13Z

0

I think its better to use foreach instead of while like this:

$rows = mysqli_fetch_array($result);
foreach ($rows as $value)
{
  echo "<tr>";
  echo "<td>" . $value["LeadID"] . "</td>";
  echo "<td>" . $value["Type"] . "</td>";
  echo "</tr>";
}
echo "</table>";

answered Sep 29, 2013 at 21:22

Akinak

1764 silver badges23 bronze badges

1 Comment

Barmar Over a year ago

You're thinking of mysqli_fetch_all(). mysqli_fetch_array() only returns one row, and your foreach will loop over the columns. But why do you think foreach is better? It means you have to fill up memory with all the results, instead of just one row at a time.

Collectives™ on Stack Overflow

Empty results when using $row[$variable] with LEFT JOIN in PHP

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related