-5

I have trouble figuring out how to join multiple rows from a second table. I have tried using GROUP_CONCAT but I want to display them as an array (see green box in the picture below)

The main issue here is the 1,2,4 from the airways-column. I really want to avoid having a third table just to connect the two tables together. I understand arrays are difficult to store in MySQL, but is there some magical way of getting the desired info from the tables below?

I wish I had some code, but I am lost here. Very thankful for pointers!

enter image description here

Tompina
  • 726
  • 1
  • 9
  • 25
  • 2
    @sagi I think the object-notation is on accident. She mentioned that she wants an array. –  Oct 10 '18 at 13:47
  • It's just pseudo code to be honest. I would like it to have that result in PHP, but obviously that shouldn't matter too much – Tompina Oct 10 '18 at 13:48
  • 1
    it's not particularly helpful to store the airway ids in a comma-separated string like that. Much better to have a separate "join" table which links an airway id with a city id – Robin Zigmond Oct 10 '18 at 13:48
  • og_city is a mysql table? – Strawberry Oct 10 '18 at 13:49
  • @Strawberry Yup! – Tompina Oct 10 '18 at 13:50
  • @RobinZigmond I see, that's what I tried to avoid. I hava a lot of tables like airways, and it's going to be a lot of work having third tables to connect the two. – Tompina Oct 10 '18 at 13:50
  • well sure, you can code this in PHP - pulling back the "airways" string, splitting it into an array and then making it a separate query to pull back the airway rows with those IDs. It's just a fair bit of extra work and extra queries, when if you had the proper table structure you'd just need a single query with a couple of joins. – Robin Zigmond Oct 10 '18 at 13:53
  • If you really don't want to have third table, you should have a unique identifier for each city apart from id and have one row per airway with that unique city identifier. With that, you can get all city rows with the identifier and make a second call to get all the airways. To avoid multiple rows and multiple calls we have to use third table :) – Sunil Dabburi Oct 10 '18 at 13:53
  • @SunilChakravarthy That makes sense! I didn't think this would be possible to be honest, but it would save me a ton of time. Can you put that as an answer so I can accept it? :) – Tompina Oct 10 '18 at 13:55
  • @RobinZigmond proper table structure as in 3 tables? (One table just to join the two together) – Tompina Oct 10 '18 at 13:56
  • @Tompina - yes. (I should caution that I'm not a database/SQL expert by any means - but this structure is what I am used to and it certainly makes it much easier to write queries!) – Robin Zigmond Oct 10 '18 at 13:57
  • @Tompina if you cannot normalize the tables. Look for `FIND_IN_SET()` function – Madhur Bhaiya Oct 10 '18 at 13:58
  • It's going to be a lot less work than the mess you've currently landed yourself with. – Strawberry Oct 10 '18 at 14:01
  • 1
    @Tompina please check the answer given below https://stackoverflow.com/a/52741981/2520628 – Tejashwi Kalp Taru Oct 10 '18 at 14:04

1 Answers1

2

What you can actually do is, use FIND_IN_SET. A simple query is given below:

SELECT c.name AS city_name, a.name AS airway_name, a.rating AS airway_rating
FROM og_city AS c JOIN og_airway AS a ON FIND_IN_SET(a.id, c.airways) WHERE c.id = 2

Here you pass the id as 2 which will help to select the flight for Bangkok (og_city, Bangkok id is 2). This query will output:

enter image description here

Which can be then converted into JSON (as your desired output) using json_encode

As a side note, you should really look forward to normalizing the database, if possible. It is violating the first normal form and there are a lot more side effects too, you can check here

Tejashwi Kalp Taru
  • 2,994
  • 2
  • 20
  • 35
  • This works perfectly! One question, currently it's just returning the city name (Bangkok). My real example has about 20 columns. Will this slow down performance a lot, or should I go with have a third table to link the two others? Thanks a ton! – Tompina Oct 10 '18 at 14:08
  • @Tompina I think it will not slow down the query a lot, but if possible you should normalize the table. Or if you can provide some real example, I can test and optimize the query – Tejashwi Kalp Taru Oct 10 '18 at 14:11
  • Got it! That's all I needed to know :) Thanks for the quick explanation!! – Tompina Oct 10 '18 at 14:12