php-mysql top 10 chart

kookyboy

hi all im trying to built a chart of books, the listing must be sorted according to the number of sales of each book, (the number of sales is an attribute in my database). The part that i need help is to show also the "past position" of a book. Is there anyone who can help me? thanx alot

Mordecai

What exactly do you mean? It's not very clear to me what the "past position" of a book is.

johnnyv

sounds like you need a rank column in the database table as well

if you added a int column and assigned a rank to each book depending on its number of sales .
eg rank 1 for top seller.

then you could view the previous rank, but only for one time period if you wanted more detailed rank tracking you would need to add more rank columns into the table or be able to query sales for each book over time.

it also depends on how you are ranking?
want to rank by total sales all time periods or total sales for a particular period like sales this week.

kookyboy

hi again

first, this chart is part of an online book shop, everytime a book is sold, the number of sales attribute in the database is increased according to the quantity. so what i need to do is populate the books in a table and be sorted by the book with the bigger munber of sales.

for exmple: 1. book14 2. book3 3.book8 4.book2
thats tha current position(according to number of sales)

now i want to show also in this table the past positions of the books

for example: 1. book3 (past pos 2) 2.book14 (past pos1)

and so on

kookyboy

johnnyv, i can create a new table "rank", store there the current rank of a book_id into it, but how can i show that this value is a past position after 1 week for example?

johnnyv

add 2 columns to the table to store rank eg book_rank & book_past_rank

for mysql you could
SELECT book_code, book_sales FROM books ORDER BY book_sales DESC;

which would return a list of book_codes and their associated sales ordered by sales.

if you created the columns in the mysql table(or what database you use)

you could populate the new columns like this

<?
$sql = "SELECT book_code FROM books ORDER BY book_sales DESC";
$results = mysql_query($sql, your connection stuff);

$counter = 1;

while ($row = mysql_fetch_assoc($result))
{
$book_code = $row['book_code'];
$sql = "UPDATE books SET book_rank = \"$counter\", book_past_rank = \"$counter\" WHERE book_code = \"$book_code\"";
$counter++;
}

that would populate the book_rank and book_past_rank columns initially

then to update book ranks

<?
$sql = "SELECT book_code FROM books ORDER BY book_sales DESC";
$results = mysql_query($sql, your connection stuff);

$counter = 1;

while ($row = mysql_fetch_assoc($result))
{
$book_code = $row['book_code'];
$sql = "UPDATE books SET book_past_rank = book_rank, book_rank=\"$counter\" WHERE book_code =\"$book_code\"";
}

get the idea?