Im currently working on a gift based affiliate site (ya i know its been done approximately 840328402 times before <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> ) and i have a series of questions.

The typical category, master/detail setup of most of the site is easy to do but im having a problem with the front page. I want to have 4 small dynamic tables with the following

- Site News - that will show the latest site news in the news table based on the date. The most recent news
- Gift Of The Day - based on the number of times the "buy now" link has been clicked.
- Most Popular - based on the number of times a gift has been viewed
- Merchant Spotlight - sequentially cycling through the merchant table and display merchant details

Couple of big questions here. How do i calculate the number of times the "buy now" link has been clicked, obviously id like a field in the database such as clickcount too keep a permanent record for each individual gift. Secondly how to i calculate the number of times a gift has been viewed. Possibly id do that by counting the number of times the name of the gift has been clicked to go to the detail view.

I also need to know how i output news based on the date, and how to sequentially cycle through the merchant table displaying one record with the relevant details..say from merchant 1 to 100.

These are all sql questions i guess, but im hopeless with sql. Never have been able to get my head around it.

Any help is very much appreciated.

Im using PHP, MySQL and DWMX

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>Im currently working on a gift based affiliate site (ya i know its been done approximately 840328402 times before <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> )<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
How eerie, you are actually exactly <i>right</i> on that #, I counted haha.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>...Couple of big questions here. How do i calculate the number of times the "buy now" link has been clicked, obviously id like a field in the database such as clickcount too keep a permanent record for each individual gift...<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
You answered your own question, just need to make a function or link call the page, and in the head of the page can UPDATE the database to add one more to its clicks.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>...Secondly how to i calculate the number of times a gift has been viewed. Possibly id do that by counting the number of times the name of the gift has been clicked to go to the detail view...<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Again, you answered your own question, just need to make a function or link call the page, and in the head of the page can UPDATE the database to add one more to its viewings.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>...I also need to know how i output news based on the date, and how to sequentially cycle through the merchant table displaying one record with the relevant details..say from merchant 1 to 100.<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
SQL can filter, sort, and limit your recordsets before they get outputted to the page.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>These are all sql questions i guess, but im hopeless with sql. Never have been able to get my head around it... Im using PHP, MySQL and DWMX<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
You came to one of the right places, here's some <b>tutorials/articles</b> you should read since you are learning SQL:
<ul>
<li><b>Beginner's SQL: Cross Joins</b> - www.dmxzone.com/showDetail.asp?TypeId=28&NewsId=6554</li><li><b>Beginner's SQL: Many-to-many relationships</b> - www.dmxzone.com/showDetail.asp?TypeId=28&NewsId=6696</li><li><b>Beginner's SQL: Aggregates and Grouping</b> - www.dmxzone.com/showDetail.asp?TypeId=28&NewsId=6638</li><li><b>SQL: Speed LIMITs (Using LIMIT for Speed in Your Queries)</b> - www.dmxzone.com/showDetail.asp?TypeId=2&NewsId=7044</li><li><b>MySQL DATA TYPES</b> - www.dmxzone.com/ShowDetail.asp?NewsId=6653</li></ul>
... DMXzone's Premium Content area has SQL & PHP/MySQL articles galore: www.dmxzone.com/categories.asp?TypeId=28

... and here's some <b>Extensions</b> that will really let you rip a <b>SQL queries visually</b>:
<ul><li><b>Advanced Query Wizard</b> (AQW) - www.advancedextensions.com by Advanced Extensions; I use this currently.</li><li><b>QuB</b> (MX Query) - www.interaktonline.com/Products/Dreamweaver-Extensions/QuB/ by InterAKT</li><li><b>WA QueryBuilder</b> - www.webassist.com/Products/ProductDetails.asp?PID=11 by WebAssist</li></ul>
All work (integrated) in DW MX/MX2004, and work for all <i>Server Models</i> since it's just SQL code generated.

Hi Chris,

Ive temporarily moved away from trying to get the date to work, and ive almost completed the admin backend of my site (a really really simple admin lol)

Talking about the UPDATE query for adding +1 clickcount. It works and i have it set up on the detail page for each one of my gifts, but for some reason it updates the clickcounts for all the gifts...Any idea what might be going on there.?

Any help is appreciated, thx for replying to my queries

I imagine that you have not got a condition statement working correctly in the update command: i.e.

You have:

update yourtable set clickcount = clickount + 1

you need

update yourtable set clickcount = clickcount + 1 where detail_id = 44(the id of your item)

Otherwise, it will simply change every record in the table if you have not specified exactly which.

Regards
Vince

DMX Talkzone Manager

Visit my home: www.chez-vince.com
(Now completely CSS based and bye bye to all tables!)

VBScript | ASP | HTML | CSS | SQL | Oracle | Hosting

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I imagine that you have not got a condition statement working correctly in the update command: i.e.

You have:

update yourtable set clickcount = clickount + 1

you need

update yourtable set clickcount = clickcount + 1 where detail_id = 44(the id of your item)

Otherwise, it will simply change every record in the table if you have not specified exactly which.

Regards
Vince

DMX Talkzone Manager

Visit my home: www.chez-vince.com
(Now completely CSS based and bye bye to all tables!)

VBScript | ASP | HTML | CSS | SQL | Oracle | Hosting
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Vince, your a champion <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> Thx for that bud...all fixed. Dont spose you could check out my date problems in the SQL forum. I got serious date issues and its driving me MAD!!! lol