SqLite Database Query - Display Chart / Graph - PHP

Our goal is to display charts and graphs of our data on our website using “Open Flash Chart” charting code. This job requires creating a SQL query, output in JSON format and display of multiple “Open Flash Chart”. ** SqLite database Requirements: 1. Create a SqLite database query: We will be plotting 4 points (one for each search engine) on a graph to show the daily website search ranking over time (daily, weekly). One chart for each website + keyword. a. X axis = time b. Y axis = page ranking c. Plotted Points = different color for each search engine. i. Google = Blue, Bing = Orange, Yahoo = Purple, Ask = Red. d. Combine “PageNo” and “Rank” into a single number, we need to create this decimal for graphing display purposes. For example 3.4 for a website on search engine page 3 and result number 4. i. “PageNo” represents the page number in search engine results. ii. “Rank” represents the number within the page where the results are displayed. 1. Each search engine has a different number of results displayed per page – “Rank” should be rounded to a number from 1-10 for easy display. 2. Calculation b. Number or records c. time and date calculations d. Percentage of change 3. Prepare data in JASON format to be read by “Open Flash Chart” 4. Chart will display data for one website per chart (multiple websites will be present in SqLite database) e. X Axis (bottom): Time (per week with daily data) from “LoggedAt” column (date / time stamp) f. Y Axis (side): Page rank calculated from “PageNo” and “Rank” g. Plot 4 different lines, one for each search engine: Google, Bing, Yahoo and Ask h. Apply labels 2. Display Statistics a. Website URL b. From Dates (first date to last date) c. Google (repeat this for Bing, Yahoo and Ask) i. Total Google clicks (total number of records for website + keyword + google) ii. Average daily Google clicks (above divided by number of days) iii. Percentage Change (total change in “PageNo” and “Rank” from first record to last) 5. Install the query code on our server and display charts on a Wordpress page. SqLite databases contains a total of 7 columns of data: rowid, Link, Keyword, SearchEngine, PageNo, Rank, LoggedAt (date / time stamp) Other: Open Flash Chart allows you to add buttons to change the data / view. Could be used to represent multiple website / key word combinations. Also change the time period from daily to weekly to monthly