hyster Posted February 19, 2016 Share Posted February 19, 2016 I have 5 tables with identical layouts, the point being I have a snap shot of a players game stats for the previous 4 weeks. the tables will be used to work out how many games a player has had week by week. the search will pull "select * where clan = '123456' " use the ac_id to tie the tables but I need cw and sh from the 4 other tables the end result I'm after is name - past 7 - past 14 - past 21 - past 28 hyster - 10 - 15 - 23 - 30 play1 - 3 - 6 - 10 - 17 the problem I have is that I do not know what kinda join to use as none seem to work in phpmyadmin TABLES monitor acc_id - name -cw - sh - date - clan monitor_7 acc_id - name -cw - sh - date - clan monitor_14 acc_id - name -cw - sh - date - clan monitor_21 acc_id - name -cw - sh - date - clan monitor_28 acc_id - name -cw - sh - date - clan this returns null for the 4 tables so I don't know where I'm going wrong :/ pointers please if u would be so kind :$ SELECT * FROM 'monitor' where 'clan' = '123456' left JOIN monitor_7 ON monitor.acc_id=monitor_7.acc_id left JOIN monitor_14 ON monitor.acc_id=monitor_14.acc_id left JOIN monitor_21 ON monitor.acc_id=monitor_21.acc_id left JOIN monitor_28 ON monitor.acc_id=monitor_28.acc_id Quote Link to comment Share on other sites More sharing options...
Barand Posted February 19, 2016 Share Posted February 19, 2016 Table and column names should not be in single quotes. I would have expected you get an error message when you ran it in phpmyadmin. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted February 19, 2016 Share Posted February 19, 2016 JOIN's are used when there is a relationship between different meaning data in tables. an example would be a clan table, that defines the clan name and assigns an id to each clan and a table holding the data you currently have. if you want to retrieve the clan name for display or search for data using the clan name, you would join the two tables using the clan_id columns. for what you are doing, you should have ONE table holding all the results. the date column you have will let you retrieve the data you want. If you want to retrieve the n most recent weeks worth of data, you would add a term in the WHERE clause to match rows having a date value greater then or equal to the starting date you are interested in. Quote Link to comment Share on other sites More sharing options...
hyster Posted February 19, 2016 Author Share Posted February 19, 2016 the 'clan' is number (500035013, 500017963), the name will be hard coded as only 7 clans will be used as its for an internal web page. date is for the last game played not when the data was updated currently the update script moves the data from 1 table to the next. so id be better doing it like this ?? acc_id - name - clan - date - sh_cur - sh_7 - sh_14 sh_21 sh_28 cw_cur - cw_7 - cw_21 - cw_28 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted February 19, 2016 Share Posted February 19, 2016 no, you wouldn't have columns like that. i reviewed your last database related thread and a member mentioned Normalization. this is the same thing. short answer - there's one row in a table for each item of data and all the same meaning data is in the same table. by storing the data correctly, you can write simple queries that find any data that you want. storing the data correctly will also eliminate all the code you have to move data between tables. something tells me that the data you are showing us in this thread is actually derived/accumulated data. you should be calculating this when needed, not storing it in a table. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.