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!