Retrieve certain row from array depending on a value

Question

Echo out the right row from an array compiled from a mysql database.

I have extracted information from a database (locations) containing three fields: id, name, city into an array called $array. I want to loop through another database (events) in which the id's from the first database (locations) are stored in a field. When looped I want to display the corresponding name and city from the locations database. Is this possible without having to fetch information every loop?

This is my first try

$query = "Select id, name, city FROM locations WHERE typ = '1'";
$result = mysqli_query($conn, $query);
$row = array();
while ($row = mysqli_fetch_assoc($result)) {
$array[] = $row;
}

And then I thought I could specify the key myself like this:

$query = "Select id, name, city FROM locations WHERE typ = '1'";
$result = mysqli_query($conn, $query);
$row = array();
while ($row = mysqli_fetch_assoc($result)) {

$array[$row['id']] = $row;
}

But I couldn't figure out how to echo the right row.

When you say "databases" do you actually mean tables?

SpacePhoenix
– SpacePhoenix

2019-02-02 20:44:09 +00:00
Commented Feb 2, 2019 at 20:44 — SpacePhoenix
– SpacePhoenix, Commented Feb 2, 2019 at 20:44
Yeah, sorry bout that.

Oskar Svensson
– Oskar Svensson

2019-02-03 21:22:49 +00:00
Commented Feb 3, 2019 at 21:22 — Oskar Svensson
– Oskar Svensson, Commented Feb 3, 2019 at 21:22

tshimkus · Accepted Answer · 2019-02-02 20:44:25Z

1

You can join both tables in a single query, using something like this:

Select locations.id, locations.name, locations.city, events.name
FROM locations 
JOIN events ON locations.id = events.id
WHERE locations.typ = '1'

The events.id on the JOIN statement is assuming that this is the column name of the id in the events table. I also made the assumption that these are the two fields that will match between the two tables. Adjust accordingly if your matching criteria is different.

The SELECT statement was modified to pull columns from both tables. Add whichever fields are relevant to your needs.

answered Feb 2, 2019 at 20:44

tshimkus

1,1692 gold badges18 silver badges25 bronze badges

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

1 Comment

Oskar Svensson Over a year ago

Worked like a charm, thank you for making me think outside my little box I've managed to trap myself in!

Collectives™ on Stack Overflow

Retrieve certain row from array depending on a value

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related