SQL result to multidimensional array (SQL to markdown file)

Question

I am trying to transfer data from a MySQL database (with relational columns) to a markdown file (Pico CMS). For that I created the following SQL query to get the data:

SELECT DISTINCT 
rqypj_mt_links.link_name,
rqypj_mt_links.link_desc,
rqypj_mt_links.address,
rqypj_mt_links.city,
rqypj_mt_links.state,
rqypj_mt_links.country,
rqypj_mt_links.postcode,
rqypj_mt_links.telephone,
rqypj_mt_links.fax,
rqypj_mt_links.email,
rqypj_mt_links.website,
rqypj_mt_links.price,
rqypj_mt_links.lat,
rqypj_mt_links.lng,
rqypj_mt_links.zoom,
rqypj_mt_cats.cat_name,
rqypj_mt_images.filename,
rqypj_mt_cfvalues.value,
rqypj_mt_customfields.caption
FROM rqypj_mt_links
LEFT JOIN rqypj_mt_cl
ON rqypj_mt_links.link_id = rqypj_mt_cl.link_id
LEFT JOIN rqypj_mt_cats
ON rqypj_mt_cl.cat_id = rqypj_mt_cats.cat_id
LEFT JOIN rqypj_mt_images
ON rqypj_mt_links.link_id = rqypj_mt_images.link_id
LEFT JOIN rqypj_mt_cfvalues
ON rqypj_mt_links.link_id = rqypj_mt_cfvalues.link_id
LEFT JOIN rqypj_mt_customfields
ON rqypj_mt_cfvalues.link_id = rqypj_mt_customfields.cf_id
ORDER BY rqypj_mt_links.link_id, rqypj_mt_cl.cat_id
LIMIT 100

This qives the following result

Title | desc | adress | cats | etc.

But when a title has multiple cats I get multiple rows with the same data only the cat row difference. Therefore I am looking for the best way to transfer the SQL data in to a PHP array. F.e.:

$result['title']
$result['desc']
$result['cats'][0]
$result['cats'][1]
Etc.

I guess that way it w'll be more easy to write the markdown file. Hopefully someone can get me some advise about the best approach and some PHP tips/scripts.

Thanks in advance! Jelte

"I guess that way it w'll be more easy to write the markdown file." Maybe, maybe not. What format does the Markdown file need to have? — ChrisGPT was on strike, Mar 04 '17 at 01:25
I understand but the database contains al the info (40000 rows). So I need to migrate. The markdown file is build up with one item per row. Title | desc | cat 1 | cat 2 — Jelte, Mar 04 '17 at 08:13
Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — ChrisGPT was on strike, Mar 04 '17 at 14:09

score 0 · Answer 1 · edited May 23 '17 at 12:17

I think you want to do something similar to this question and similarly you can use GROUP_CONCAT.

Look at this SQL Fiddle. Maybe it will help you :-)

Schema:

CREATE TABLE link
(
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE cat
(
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE has_cat
(
  `link_id` INT,
  `cat_id` INT,
  FOREIGN KEY (link_id) REFERENCES link(id),
  FOREIGN KEY (cat_id) REFERENCES cat(id)
);

Values:

INSERT INTO link (`name`)
VALUES
    ('John'),
    ('Calvin')
;

INSERT INTO cat (`name`)
VALUES
    ('Garfield'),
    ('Nermal'),
    ('Hobbes')
;

INSERT INTO has_cat (link_id, cat_id)
VALUES
  (1, 1),
  (1, 2),
  (2, 3)
;

Query:

SELECT link.name AS link, GROUP_CONCAT(cat.name) AS cats
FROM link, has_cat, cat
WHERE
  link.id = has_cat.link_id AND
  cat.id = has_cat.cat_id
GROUP BY link.name

Result:

|   link |            cats |
|--------|-----------------|
| Calvin |          Hobbes |
|   John | Garfield,Nermal |

Make a PHP array from it

From here, you can explode the comma separated string into an array:

var_dump( explode( ',', $result['cats'] ) );

That will give you:

array(2)
(
    [0] => string(8) "Garfield"
    [1] => string(6) "Nermal"
)

SQL result to multidimensional array (SQL to markdown file)

1 Answers1

Make a PHP array from it