Freelancer

Description

I want to modify my website. It is now built in very simple .html and .php code
1- I want to add a mysql database that will contain links to different .html pages that will be included in .php pages
2- I will need 4 scripts that will query the database and return the results in .html form.
3- 4 tables will be setup for metrics, I will need 4 queries to analyze those tables
4- You will also code the std.php page that will define how the tables are setup, what goes in each cell and what style is used
If below is not clear, we will clarify as we go along

The mysql database
You must build the database so that it is compatible with excel. I must be able to upload my excel sheets to the server.
The folders containing the images will be sent to server through FTP

Table 1 is the main table from which the index.php, search.php, alphabet.php and the galleries are built

column 1 "date" Every day, there is a new article added to the homepage, this column has the date that the articles are added to the homepage. Below today's article, I will display yesterday's article and below that. the article of the day before etc ... date will be formatted as aug19.html, aug20.html etc ...
column 2 "main keyword" serves as the title for the gallery (the ads will be generated from the link with this keyword
column 3 "long description" includes about 60 words with 1 thumbnail (written in .html)
column 4 "short description1" this is about 20 words and will be returned as the search result, it will link to the gallery
column 5 "short description2" this is about 20 words and goes on top of gallery, this links to my sponsor and is one of the ads that will be compiled
column 6 "secondary keyword"
column 7 "message" that will be displayed at bottom of generated gallery (written in .html)
column 8 this column will have the code for the style of gallery to generate (different gallery style will be generated )
column 9 will link to imageX.jpg that will be used on today.html, it will resize at 120 dpi vertical with proportions maintained
column 10 to column 20 "images-1.jpg to images-12.jpg" links to the main images from which the thumbnails and full size
images will be generated. Format will be www.mysite.com/gallery1/image01.jpg
column 21 will contain "title" that will be included in generated .php file
column 22 will contain "third keyword" these will be included in .php file
column 23 will contain "short description1" but without the link, this will be included in .php file

Table 2 is the thumbnails ads table
column 1 "ad itself" this is a link to one sponsor including a link to display the actual thumbnail that serves as the ad
column 2 "main keyword"
column 3 "secondary keyword"
column 4 "third keyword"
column 5 "fourth keyword"

Table 3 is for metrics, I want to compile all clicks on the thumbnail ads
column 1 what ad is clicked on
column 2 the date on which the ad is clicked
column 3 the page on which the ad is clicked

Table 4 is for metrics, I want to compile all clicks on short description2
column 1 the date on which the ad is clicked
column 2 the page on which the ad is clicked
column 3 the link itself

Table 5 is for metrics, I want to compile all clicks on the main index.php
column 1 what links to other pages are clicked on the homepage
column 2 the date on which the ad is clicked

Table 6 is for metrics
column 1 all the search words that are inputted in the search box
column 2 the date on which the search word is inputted



The queries



These are the queries that will query the database, there are 4 main queries to program.

Query 1
Everyday, the script will post a new article that is about 60 words
1- I want the script to add the article of the day on top of the previous day's article which is itself on top of the previous day's article etc ... (please see the table below to get a better idea of what I want) The homepage goes back 30 days, so if today is the aug 8, it goes back to jul 9.
2- below the articles of the last 30 days, I want to post "short description1" of the previous 60 articles of the day, these will link to the actual galleries
3- The style is formatted so that each article alternates between a black and a charcoal background.

Query 2
The articles will be searchable, the return page will show "short description1" which will link to the galleries.
The search box will be located on top of the right column above the thumbnail ads

Query 3
1- Another query will list the galleries according to the first letter of "short description1".
Pages will be labeled A-C, D-G etc
Setup of the page will be the same as the setup for Query 2

Query 4 will generate the galleries
1- There will be about 4 different types of galleries according to what type of setup is associated with this gallery
2- The right column will generate ads related to the keyword associated with the gallery
3- The thumbnails in the gallery will link to image.jpg

The ads


How the thumbnail ads will display

The ads on main index.php will be formatted by me
The ads on the alphabetical query page will be formatted by me

The ads on the main query page (the ones called by the search box) will be relative to the main keywords of "short description1" they will display in a random manner,
They will appear in the right column



How I want to query the metrics database

Thumbnail ads queried will be imported in excel to analyze.

The query will return
1- date
2- what ad
3- what page the ad was clicked on
-----------------------------------
Short description2 (the ones linking to my sponsors) will be imported in excel to analyze.

The query will return
1- date
2- what ad
3- what page the ad was clicked on
--------------------------------------
I want to analyze the links that my surfers click on my homepage.
The queries generated (according to date) will be imported in excel to analyze.

The query will return
1- date
2- what link
------------------------------------------------
I want to analyze the search terms that will be inputted in the search box
The queries generated (according to date) will be imported in excel to analyze.

The query will return
1- date
2- what words


The std.php page

There will be 4 std.php pages that will define the 4 main query pages.

The std.php page will define
1- how the tables are setup
2- what to include in each cell
3- the style of each cell


-----------------------------------------------------------
The tables

The tables are formatted to fill the full screen of all sizes of monitors and to be viewable in all popular browsers. The main style for the body font is trebuchet MS size 12, color ffffff. The links are ffcc66 when static and ff0000 when rolled over.
------------------------------------------------------------

Table setup for the index.php

<div align="center">
<table border="1" width="800" cellspacing="0" cellpadding="0">
<tr>
<td>back1.gif</td>
<td>
<p align="center">back2.gif</td>
<td>back3.gif</td>
</tr>
<tr>
<td>back4.gif</td>
<td>
<div align="center">
<table border="1" width="750" cellspacing="0" cellpadding="0">
<tr>
<td>
<p align="center">header1.html</td>
<td>
<p align="center">header2.html</td>
</tr>
</table>
</div>
<div align="center">
<table border="1" width="750" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2">
<p align="center">header3.html</td>
</tr>
<tr>
<td colspan="2">
<p align="center">header4.html</td>
</tr>
<tr>
<td width="518">
<p align="center">today.html<br>
this cell will return long description with a
thumbnail to the left, <br>
dimension of this cell is about 80 x 600</td>
<td rowspan="6" width="226">
<p align="center">search.html<br>
this cell will have one box for search term and
one button for GO<br>
-----------------------<br>
this cell will display a series of ads of my
choosing<br>
<br>
ad1.html<br>
ad2.html<br>
ad3.html<br>
ad4.html<br>
ad5.html<br>
ad6.html<br>
<br>
these ads will be randomly displayed from the
ads database, similar to the script already used
for the days of the week<br>
Background for this column will be one color</td>
</tr>
<tr>
<td width="518">
<p align="center">today-1.html</td>
</tr>
<tr>
<td width="518">
<p align="center">today-2.html</td>
</tr>
<tr>
<td width="518">
<p align="center">today-3.html</td>
</tr>
<tr>
<td width="518">
<p align="center">etc ... until today-30</td>
</tr>
<tr>
<td width="518">
<p align="center">These cells are formatted to alternate
background between black and charcoal.</td>
</tr>
<tr>
<td colspan="2">
<p align="center">short description of today -31
days.html</td>
</tr>
<tr>
<td colspan="2">
<p align="center">short description of today -32
days.html</td>
</tr>
<tr>
<td colspan="2">
<p align="center">etc ... until short
description of today -60 days.html</td>
</tr>
<tr>
<td colspan="2">
<p align="center">footer1.html</td>
</tr>
<tr>
<td colspan="2">
<p align="center">footer2.html</td>
</tr>
</table>
</div>
</td>
<td>back5.gif</td>
</tr>
<tr>
<td>back6.gif</td>
<td>
<p align="center">back7.gif</td>
<td>back8.gif</td>
</tr>
</table>
</div>
</div>
</div>
<p align="center">Table setup for search.php and alphabet.php</p>
<div align="center">
<table border="1" width="800" cellspacing="0" cellpadding="0">
<tr>
<td>back1.gif</td>
<td>
<p align="center">back2.gif</td>
<td>back3.gif</td>
</tr>
<tr>
<td>back4.gif</td>
<td>
<div align="center">
<table border="1" width="750" cellspacing="0" cellpadding="0">
<tr>
<td>
<p align="center">header1.html</td>
<td>
<p align="center">header2.html</td>
</tr>
</table>
</div>
<div align="center">
<table border="1" width="750" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2">
<p align="center">header3.html</td>
</tr>
<tr>
<td colspan="2">
<p align="center">header4.html</td>
</tr>
<tr>
<td width="506">
<p align="center">result.html</td>
<td rowspan="8" width="238">
<p align="center">search.html<br>
this cell will have one box for search term and one
button for GO<br>
-----------------------<br>
ad1.html<br>
ad2.html<br>
ad3.html<br>
ad4.html<br>
ad5.html<br>
ad6.html<br>
<br>
the ads will be pulled out of a database, they will be
relevant to the keywords attached to &quot;short
description 1&quot;<br>
Background will be one color</td>
</tr>
<tr>
<td width="506">
<p align="center">result+1.html</td>
</tr>
<tr>
<td width="506">
<p align="center">result+2.html</td>
</tr>
<tr>
<td width="506">
<p align="center">result+3.html</td>
</tr>
<tr>
<td width="506">
<p align="center">result+4.html</td>
</tr>
<tr>
<td width="506">
<p align="center">result+5.html</td>
</tr>
<tr>
<td width="506">
<p align="center">result+6.html</td>
</tr>
<tr>
<td width="506">
<p align="center">The center column will display&nbsp;
the descriptions of up to 30 results of the search. Each
resulting line will alternate background between black
and charcoal.</td>
</tr>
<tr>
<td colspan="2">
<p align="center">footer3.html</td>
</tr>
<tr>
<td colspan="2">
<p align="center">footer2.html</td>
</tr>
</table>
</div>
</td>
<td>back5.gif</td>
</tr>
<tr>
<td>back6.gif</td>
<td>
<p align="center">back7.gif</td>
<td>back8.gif</td>
</tr>
</table>
</div>
<p align="center">The gallery page</p>

<div align="center">
<table border="1" width="800" cellspacing="0" cellpadding="0">
<tr>
<td>back1.gif</td>
<td>
<p align="center">back2.gif</td>
<td>back3.gif</td>
</tr>
<tr>
<td>back4.gif</td>
<td>
<div align="center">
<table border="1" width="750" cellspacing="0" cellpadding="0">
<tr>
<td>
<p align="center">header1.html</td>
<td>
<p align="center">header2.html</td>
</tr>
</table>
</div>
<div align="center">
<table border="1" width="750" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2">
<p align="center">header3.html</td>
</tr>
<tr>
<td colspan="2">
<p align="center">header4.html</td>
</tr>
<tr>
<td width="506">
<table border="1" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td align="center" colspan="3">This cell will
return short description2 that links to my
sponsor, <br>
clicks on this link will be compiled</td>
</tr>
<tr>
<td align="center">image1.jpg</td>
<td align="center">image2.jpg</td>
<td align="center">image3.jpg</td>
</tr>
<tr>
<td align="center">image4.jpg</td>
<td align="center">image5.jpg</td>
<td align="center">image6.jpg</td>
</tr>
<tr>
<td align="center">image7.jpg</td>
<td align="center">image8.jpg</td>
<td align="center">image9.jpg</td>
</tr>
<tr>
<td align="center">image10.jpg</td>
<td align="center">image11.jpg</td>
<td align="center">image12.jpg</td>
</tr>
</table>
</td>
<td width="238">
<p align="center">search.html<br>
this cell will have one box for search term and one
button for GO<br>
-----------------------<br>
ad1.html<br>
ad2.html<br>
ad3.html<br>
ad4.html<br>
ad5.html<br>
ad6.html<br>
<br>
the ads will be pulled out of a database, they will be
relevant to the keywords attached to &quot;short
description 1&quot;<br>
Background will be one color</td>
</tr>
<tr>
<td colspan="2">
<p align="center">footer3.html</td>
</tr>
<tr>
<td colspan="2">
<p align="center">footer2.html</td>
</tr>
</table>
</div>
</td>
<td>back5.gif</td>
</tr>
<tr>
<td>back6.gif</td>
<td>
<p align="center">back7.gif</td>
<td>back8.gif</td>
</tr>
</table>
</div>