PHP Array Select with inner join

Question

I have got an inner join working how I want it to when I do an sql query in my database.

SELECT debate.date, school1.name, school2.name
FROM debate
INNER JOIN schools as school1
ON debate.homeid=school1.id
INNER JOIN schools as school2
ON debate.awayid=school2.id

Returns

date        name        name
19-01-2013  st Peters   St Simons

When I try doing this with php. I use the same select statement and add in the below.

foreach ($result as $row)
{
  $debates[] = array(
    'date' => $row['date'],
    'awayschool' => $row['name'],
    'homeschool' => $row['name'],
  );
}


<table>
<tr>
<td><?php echo $debate['date']; ?></td>
<td><?php echo $debate['name']; ?></td>
<td><?php echo $debate['name']; ?></td>
</tr>
</table>

Which returns

date        name        name
19-01-2013  st Peters   st Peters

However I want it to return.

date        name        name
19-01-2013  st Peters   St Simons

In my array I have tried several different things such as replacing

'awayschool' => $row['name'],

with

'awayschool' => $row['school1.name'],

but I get an error saying undefined index when I do this.

What am I doing wrong here?

Dale · Accepted Answer · 2012-11-19 11:16:03Z

2

The issue here is that both school1.name and school2.name are (in php) both named $row['name'], you can fetch one using an AS to rename it

SELECT debate.date, school1.name, school2.name AS name2
FROM debate
INNER JOIN schools as school1
ON debate.homeid=school1.id
INNER JOIN schools as school2
ON debate.awayid=school2.id

Then change your foreach loop to this

foreach ($result as $row)
{
  $debates[] = array(
    'date' => $row['date'],
    'awayschool' => $row['name'],
    'homeschool' => $row['name2'],
  );
}

answered Nov 19, 2012 at 11:16

Dale

10.5k23 silver badges34 bronze badges

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

Comments

Mahmoud Gamal · Accepted Answer · 2012-11-19 11:16:18Z

1

Modify your query like so

SELECT debate.date, school1.name "School1Name", school2.name "School2Name"
FROM debate
INNER JOIN schools as school1
ON debate.homeid=school1.id
INNER JOIN schools as school2
ON debate.awayid=school2.id;

Then:

foreach ($result as $row)
{
  $debates[] = array(
    'date' => $row['date'],
    'awayschool' => $row['School1Name'],
    'homeschool' => $row['School2Name'],
  );
}

answered Nov 19, 2012 at 11:16

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Comments

techie_28 · Accepted Answer · 2012-11-19 11:17:02Z

0

Just use an alias for them.Like school1.name AS SCHLNAME1, school2.name AS SCHLNAME2 then these Indexes will be available in your array.Then it will be

'awayschool' => $row['SCHLNAME1'],
'awayschool' => $row['SCHLNAME2']

answered Nov 19, 2012 at 11:17

techie_28

2,1454 gold badges44 silver badges65 bronze badges

Comments

Avinash · Accepted Answer · 2012-11-19 11:19:54Z

0

Try changing the following

<td><?php echo $debate['date']; ?></td>
<td><?php echo $debate['name']; ?></td>
<td><?php echo $debate['name']; ?></td>

to this

 <td><?php echo $debate['date']; ?></td>
 <td><?php echo $debate['awayschool']; ?></td>
 <td><?php echo $debate['homeschool']; ?></td>

answered Nov 19, 2012 at 11:19

Avinash

2,0157 gold badges31 silver badges57 bronze badges

Collectives™ on Stack Overflow

PHP Array Select with inner join

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related