ZXDB mysql query (newbie)
Moderator: druellan
ZXDB mysql query (newbie)
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
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
Re: ZXDB mysql query (newbie)
Hi [mention]xx3774[/mention]
Try this for the SQL query:
It displays the games which were originally published by Firebird.
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%'
- Einar Saukas
- Bugaboo
- Posts: 3144
- Joined: Wed Nov 15, 2017 2:48 pm
Re: ZXDB mysql query (newbie)
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:
However it's more accurate to extract the list of co-publishers (or single publisher) from each title's original release:
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;
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;
- Einar Saukas
- Bugaboo
- Posts: 3144
- Joined: Wed Nov 15, 2017 2:48 pm
Re: ZXDB mysql query (newbie)
You will also need "and publishers.publisher_seq = 1" otherwise you will get 2 rows for titles like "Quadrax".PeterJ wrote: ↑Mon Dec 03, 2018 1:19 pm Hi @xx3774
Try this for the SQL query:
It displays the games which were originally published by Firebird.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%'
Re: ZXDB mysql query (newbie)
Good point [mention]Einar Saukas[/mention]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".
[mention]xx3774[/mention]
[mention]Einar Saukas[/mention] designed ZXDB so he is the expert!
Re: ZXDB mysql query (newbie)
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
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.
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.
Re: ZXDB mysql query (newbie)
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
https://drive.google.com/file/d/1x-72vh ... sp=sharing
Re: ZXDB mysql query (newbie)
Magic Peter, many thanks
Re: ZXDB mysql query (newbie)
May we ask what project you are working on?
Re: ZXDB mysql query (newbie)
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.
Bit of an incentive to keep learning.
Re: ZXDB mysql query (newbie)
Now I just have to figure out why I'm not seeing the publishers table.
- Einar Saukas
- Bugaboo
- Posts: 3144
- Joined: Wed Nov 15, 2017 2:48 pm
Re: ZXDB mysql query (newbie)
Did you delete it by accident?
You better reload everything. Delete zxdb, then execute "ZXDB_mysql.sql" again.
Re: ZXDB mysql query (newbie)
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
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
- Einar Saukas
- Bugaboo
- Posts: 3144
- Joined: Wed Nov 15, 2017 2:48 pm
Re: ZXDB mysql query (newbie)
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.
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.
- Einar Saukas
- Bugaboo
- Posts: 3144
- Joined: Wed Nov 15, 2017 2:48 pm
Re: ZXDB mysql query (newbie)
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".
Re: ZXDB mysql query (newbie)
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 )
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
zxinfo-file-browser - Cross platform app to manage your files
https://zxinfo.dk - another ZXDB frontend
Re: ZXDB mysql query (newbie)
Magic - Thank you kolbeck
works perfectly
works perfectly
Re: ZXDB mysql query (newbie)
[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
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
Re: ZXDB mysql query (newbie)
[mention]PeterJ[/mention] good to know - thanks Peter
Re: ZXDB mysql query (newbie)
[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"
/Thomas
/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
zxinfo-file-browser - Cross platform app to manage your files
https://zxinfo.dk - another ZXDB frontend
Re: ZXDB mysql query (newbie)
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.
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 )
Re: ZXDB mysql query (newbie)
[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
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
zxinfo-file-browser - Cross platform app to manage your files
https://zxinfo.dk - another ZXDB frontend
Re: ZXDB mysql query (newbie)
Wow, thank you [mention]kolbeck[/mention] you're a star!