ZXDB mysql query (newbie)
Moderator: druellan
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: 3093
- 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: 3093
- 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: 3093
- 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!