Need a SQL query and proper php logic to construct a table of results for the following:
the table in mysql contains search engine placement data, namely the name of the search engine, the keywords (up to 5, separated by pipe | in a string), the positions (numeric, also separated by pipe | in a string which match each keyword) and the week the data was gathered (numeric, say 49 as an example).
Here's what the report needs to do:
For each search engine, it needs to print out the heading (eg <h3>google</h3>), then print out a table with the keyword9s) in the left-most col, the week in the top row, and the positions in the row of each keyword. For example:
| 45 | 46 | 47 | 48 | 49 |
-------------------------------------------------------------
keyword1 | 36 | 40 | 30 | 25 | 30 |
keyword2 | 1 | 4 | 6 | 4 | 10 |
It needs to be repeated for each search engine (there are three of them who's data is in the table.) the report needs to look at the current week - 10 weeks, so basically it is historical to 10 weeks.
The way the database table is constructed is each search engine has its own row for a given week, so google, msn, and yahoo each have a row of data for a given week (49 for example.)
Here's the last interesting part - week to week, the keywords may change. if the keyword string changed since the last week, the report needs to add row(s) with the new keywords (see below - keyword 3 is new...added during week 47...and keyword 2 was removed during week 49.)
| 45 | 46 | 47 | 48 | 49 |
-------------------------------------------------------------
keyword1 | 36 | 40 | 30 | 25 | 30 |
keyword2 | 1 | 4 | 6 | 4 | |
keyword3 | | | 23 | 25 | 20 |
Any questions, please PM me...should be simple for someone who knows php and mysql. Will provide mysql table schema and test data to the winning bidder, of course.