ZXDB mysql query (newbie)

This is the place for general discussion and updates about the ZXDB Database. This forum is not specific to Spectrum Computing.

Moderator: druellan

Post Reply
xx3774
Drutt
Posts: 11
Joined: Sun Nov 25, 2018 11:11 am

ZXDB mysql query (newbie)

Post by xx3774 »

Hi,

I've downloaded the zxdb sql file from github.
Installed mysql / heidi on my window 7 pc.

I'm trying to create a titles.csv file with the following fields.
id, title, publisher.

I can't seem find the publisher field?

Could some clever person modify my code to do this please?.

use zxdb;
select id,title from entries
into outfile 'c:\\zxdb\\titles.csv'
fields terminated by ','
enclosed by '"'
lines terminated by '\n';


many, many thanks
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: ZXDB mysql query (newbie)

Post by PeterJ »

Hi [mention]xx3774[/mention]

Try this for the SQL query:

Code: Select all

select entries.id,entries.title,labels.name
from entries,publishers,labels
where entries.id = publishers.entry_id
and publishers.release_seq = 0
and publishers.label_id = labels.id
and labels.name like 'Firebird%'
It displays the games which were originally published by Firebird.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB mysql query (newbie)

Post by Einar Saukas »

It's complicated. Each title can be published several times. And each time it's published, it can have multiple co-publishers.

If you only want to extract the first publisher from each title's original release:

Code: Select all

SELECT e.id, e.title, b.name as publisher
FROM entries e
INNER JOIN publishers p ON p.entry_id = e.id AND p.release_seq = 0 AND p.publisher_seq = 1
INNER JOIN labels b ON p.label_id = b.id;
However it's more accurate to extract the list of co-publishers (or single publisher) from each title's original release:

Code: Select all

SELECT e.id, e.title, GROUP_CONCAT(b.name ORDER BY p.publisher_seq SEPARATOR ', ') as publisher
FROM entries e
INNER JOIN publishers p ON p.entry_id = e.id AND p.release_seq = 0
INNER JOIN labels b ON p.label_id = b.id
GROUP BY e.id;
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB mysql query (newbie)

Post by Einar Saukas »

PeterJ wrote: Mon Dec 03, 2018 1:19 pm Hi @xx3774

Try this for the SQL query:

Code: Select all

select entries.id,entries.title,labels.name
from entries,publishers,labels
where entries.id = publishers.entry_id
and publishers.release_seq = 0
and publishers.label_id = labels.id
and labels.name like 'Firebird%'
It displays the games which were originally published by Firebird.
You will also need "and publishers.publisher_seq = 1" otherwise you will get 2 rows for titles like "Quadrax".
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: ZXDB mysql query (newbie)

Post by PeterJ »

Einar Saukas wrote: Mon Dec 03, 2018 1:47 pm You will also need "and publishers.publisher_seq = 1" otherwise you will get 2 rows for titles like "Quadrax".
Good point [mention]Einar Saukas[/mention]

[mention]xx3774[/mention]

[mention]Einar Saukas[/mention] designed ZXDB so he is the expert!
xx3774
Drutt
Posts: 11
Joined: Sun Nov 25, 2018 11:11 am

Re: ZXDB mysql query (newbie)

Post by xx3774 »

Thank you Einar & Peter :)

I'm getting an error message, I can't seem to find the publishers table?.
maybe I downloaded an old file?.


Image
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: ZXDB mysql query (newbie)

Post by PeterJ »

Both of the queries from Einar work find for me. Try this at the top of your code to make sure you are in the right database

Code: Select all

use zxdb;

I have not used HeidiSQL before, but as you say you don't seem to have all the tables listed on the left hand panel.
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: ZXDB mysql query (newbie)

Post by PeterJ »

I have run the last query from [mention]Einar Saukas[/mention] and exported the CSV for you:

https://drive.google.com/file/d/1x-72vh ... sp=sharing
xx3774
Drutt
Posts: 11
Joined: Sun Nov 25, 2018 11:11 am

Re: ZXDB mysql query (newbie)

Post by xx3774 »

Magic Peter, many thanks :)
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: ZXDB mysql query (newbie)

Post by PeterJ »

May we ask what project you are working on?
xx3774
Drutt
Posts: 11
Joined: Sun Nov 25, 2018 11:11 am

Re: ZXDB mysql query (newbie)

Post by xx3774 »

No project, just starting to learn mysql, I thought learning mysql + reliving my speccy youth would make it a bit more interesting.
Bit of an incentive to keep learning.
xx3774
Drutt
Posts: 11
Joined: Sun Nov 25, 2018 11:11 am

Re: ZXDB mysql query (newbie)

Post by xx3774 »

Now I just have to figure out why I'm not seeing the publishers table.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB mysql query (newbie)

Post by Einar Saukas »

xx3774 wrote: Mon Dec 03, 2018 2:42 pm Now I just have to figure out why I'm not seeing the publishers table.
Did you delete it by accident?

You better reload everything. Delete zxdb, then execute "ZXDB_mysql.sql" again.
xx3774
Drutt
Posts: 11
Joined: Sun Nov 25, 2018 11:11 am

Re: ZXDB mysql query (newbie)

Post by xx3774 »

Hi Einar,

I will delete and re-download from githib.
I'll also install MariaDB just in case its an error with HeidiSql.
Thanks for the reply :)
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB mysql query (newbie)

Post by Einar Saukas »

You are welcome!
xx3774
Drutt
Posts: 11
Joined: Sun Nov 25, 2018 11:11 am

Re: ZXDB mysql query (newbie)

Post by xx3774 »

Hi All,

What would the MySQL code be if I wanted to see All the Magazine Reviews + All the Magazine Adverts for a particular title :?.
For example, entries.id = 1, Licence to Kill.

Many thanks for any replys.
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: ZXDB mysql query (newbie)

Post by Einar Saukas »

I'm traveling right now, without computer access. So I cannot check to be sure... But I think you just need to select from table "magrefs".
User avatar
kolbeck
Manic Miner
Posts: 309
Joined: Mon Nov 13, 2017 9:04 pm

Re: ZXDB mysql query (newbie)

Post by kolbeck »

Try this for ads - also check table referencetypes, reviews are type 10 i think

SELECT m.name AS magazine,i.date_year AS issueyear,i.date_month AS issueno,
ref.page AS pageno,reft.text AS magazine_type,f.name AS magazine_text,
m.link_mask
FROM entries e
INNER JOIN magrefs ref
ON ref.entry_id = e.id
INNER JOIN features f
ON ref.feature_id = f.id
INNER JOIN referencetypes reft
ON ref.referencetype_id = reft.id
INNER JOIN issues i
ON ref.issue_id = i.id
INNER JOIN magazines m
ON i.magazine_id = m.id
WHERE e.id = 4010
AND ref.referencetype_id IN ( 1, 2, 3, 15 )
https://api.zxinfo.dk/v3/ - ZXDB API for developers
zxinfo-file-browser - Cross platform app to manage your files
https://zxinfo.dk - another ZXDB frontend
xx3774
Drutt
Posts: 11
Joined: Sun Nov 25, 2018 11:11 am

Re: ZXDB mysql query (newbie)

Post by xx3774 »

Magic - Thank you kolbeck
works perfectly :P
User avatar
PeterJ
Site Admin
Posts: 6858
Joined: Thu Nov 09, 2017 7:19 pm
Location: Surrey, UK

Re: ZXDB mysql query (newbie)

Post by PeterJ »

[mention]xx3774[/mention]

Just so you know. If you use the at symbol '@' then start typing the user name, it should appear automatically. This also send the user a note that you have 'mentioned' them.

Peter
xx3774
Drutt
Posts: 11
Joined: Sun Nov 25, 2018 11:11 am

Re: ZXDB mysql query (newbie)

Post by xx3774 »

[mention]PeterJ[/mention] good to know - thanks Peter :D
User avatar
kolbeck
Manic Miner
Posts: 309
Joined: Mon Nov 13, 2017 9:04 pm

Re: ZXDB mysql query (newbie)

Post by kolbeck »

[mention]xx3774[/mention] - you're welcome, let me know if you need anything else. I'll try my best to support, while [mention]Einar Saukas[/mention] is on "vacation" :lol:

/Thomas
https://api.zxinfo.dk/v3/ - ZXDB API for developers
zxinfo-file-browser - Cross platform app to manage your files
https://zxinfo.dk - another ZXDB frontend
xx3774
Drutt
Posts: 11
Joined: Sun Nov 25, 2018 11:11 am

Re: ZXDB mysql query (newbie)

Post by xx3774 »

Hi [mention]kolbeck[/mention],
your code selects a single title, e.id = 4010, how would I go about showing all titles?.

WHERE e.id = * doesn't seem to work, I'm guessing it's not that simple.
kolbeck wrote: Wed Dec 12, 2018 12:24 am Try this for ads - also check table referencetypes, reviews are type 10 i think

SELECT m.name AS magazine,i.date_year AS issueyear,i.date_month AS issueno,
ref.page AS pageno,reft.text AS magazine_type,f.name AS magazine_text,
m.link_mask
FROM entries e
INNER JOIN magrefs ref
ON ref.entry_id = e.id
INNER JOIN features f
ON ref.feature_id = f.id
INNER JOIN referencetypes reft
ON ref.referencetype_id = reft.id
INNER JOIN issues i
ON ref.issue_id = i.id
INNER JOIN magazines m
ON i.magazine_id = m.id
WHERE e.id = 4010
AND ref.referencetype_id IN ( 1, 2, 3, 15 )
User avatar
kolbeck
Manic Miner
Posts: 309
Joined: Mon Nov 13, 2017 9:04 pm

Re: ZXDB mysql query (newbie)

Post by kolbeck »

[mention]xx3774[/mention] - Just remove the e.id= XXXX (and also the AND)

So the WHERE clause looks like:
WHERE ref.referencetype_id IN ( 1, 2, 3, 15 )

/Thomas
Last edited by kolbeck on Sat Dec 15, 2018 2:38 pm, edited 1 time in total.
https://api.zxinfo.dk/v3/ - ZXDB API for developers
zxinfo-file-browser - Cross platform app to manage your files
https://zxinfo.dk - another ZXDB frontend
xx3774
Drutt
Posts: 11
Joined: Sun Nov 25, 2018 11:11 am

Re: ZXDB mysql query (newbie)

Post by xx3774 »

Wow, thank you [mention]kolbeck[/mention] you're a star! :lol:
Post Reply