Help with INNER JOIN

Databases
1

Hi,

I am trying to display data from two different tables in a single grid, but when I use INNER JOIN to grab an array and display it in the grid it only shows data from one of the table.

For context: the program I am building is a simple sleep and dream log tracker. One table is to track dates and times of a sleep session and the other table is to store dream text.

table 1: sleepsession fields: id, startofsleep, endofsleep and awakenings

table2: dreams fields: id, dreamtext, sleepsessionid

I would like the end result to display startofsleep, endofsleep, awakenings and dreamtext.

Here is the code I wrote to pull the array:

$result = mysqli_query($link,
‘SELECT sleepsession.id, startofsleep, endofsleep, awakenings
FROM sleepsession INNER JOIN dreamtext
ON sleepsessionid = sleepsession.id’);
if (!$result)
{
$error = 'Error fetching sleepsessions: ’ . mysqli_error($link);
include ‘error.html.php’;
exit();
}
while ($row = mysqli_fetch_array($result))
{
$sleepsessions = array(‘id’ => $row[‘id’], ‘startofsleep’ => $row[‘startofsleep’],
‘endofsleep’ => $row[‘endofsleep’],‘dreamtext’ => $row[‘dreamtext’]);
}
include ‘sleeplog.html.php’;

2

if you want to show the dreamtext column, you must include it in your SELECT clause :slight_smile:

SELECT sleepsession.id
     , sleepsession.startofsleep
     , sleepsession.endofsleep
     , sleepsession.awakenings
     , [COLOR="#0000FF"]dreams.dreamtext[/COLOR]
  FROM sleepsession
INNER
  JOIN dreams
    ON dreams.sleepsessionid = sleepsession.id
3

also, if there are any sessions which do not have an accompanying dreamtext, then you’ll want LEFT OUTER JOIN instead of INNER JOIN

4

Awesome, thanks! I will give it a shot.