Ranking System

naero · #1 (**permalink**) 30-04-2005, 03:11 AM

mintak tolong skitt.....aku ade table mysql

tablename = mytable

|xxxxxx|xxxxxx|xxxxxxx|xxxxxxxx|xxxxxxxxxxxxx|xxxx xxxxxx|
|--ID--|--in--|--out--|--view--|--category--|--ranking--|
|xxxxxx|xxxxxx|xxxxxxx|xxxxxxxx|xxxxxxxxxxxxx|xxxx xxxxxx|
|--1---|-100-|-100---|--200---|---makan----|----?-----|
|--2---|-200-|-200---|--100---|---tido------|----?-----|
|--3---|-100-|-100---|--200---|---lepak-----|----?-----|
|--4---|-100-|-100---|--100---|---makan----|----?-----|
|--5---|-100-|-200---|--200---|---makan----|----?-----|
---------------------------------------------------------
ok sekarang ni aku tak tau macam mana nak isi column ranking mengikut category makan
ID = 1
(100 + 100 + 200) / 3 = 133.33

ID = 4
(100 + 100 + 100) / 3 = 100

ID = 5
(100 + 200 + 200) / 3 = 166.66 (highest)

jadi sekarang ni ID = 5 adalah tertinggi, ID = 1 adalah kedua tertinggi ID = 4 adalah yang ketiga tertinggi...
jadi macam mane nak isi column ranking mengikut (in + out + view) / 3

|xxxxxx|xxxxxx|xxxxxxx|xxxxxxxx|xxxxxxxxxxxxx|xxxx xxxxxx|
|--ID--|--in--|--out--|--view--|--category--|--ranking--|
|xxxxxx|xxxxxx|xxxxxxx|xxxxxxxx|xxxxxxxxxxxxx|xxxx xxxxxx|
|--1---|-100-|-100---|--200---|---makan----|----2-----|
|--2---|-200-|-200---|--100---|---tido------|----------|
|--3---|-100-|-100---|--200---|---lepak-----|----------|
|--4---|-100-|-100---|--100---|---makan----|----3-----|
|--5---|-100-|-200---|--200---|---makan----|----1-----|
---------------------------------------------------------
tolong la aku pening dah ni....
tolong la ek...kalau x takble pakai sistem ni..sistem ape yang boleh pakai untuk masuk column `ranking` mengikut (in + out + view) / 3 dan juga mengikut category

lagi satu ape fungsi count(*) atau count(1)
$result = "SELECT count(*) FROM (in + out + view) / 3 AND category = '$category' AND ID = '$ID'";
$result = "SELECT (in + out + view) / 3 FROM mytable WHERE ID = '$ID' AND category = '$category'";
sekian terima kasihh

zaimlah · #2 (**permalink**) 30-04-2005, 09:48 PM

untuk isi column ranking, cuba:

Code:

UPDATE mytable SET ranking=(mytable.in + mytable.out + mytable.view)/3

guna function count(*) untuk kira berapa row ada dalam result set, contoh:

Code:

SELECT count(*) FROM mytable WHERE ranking > 200

akan tunjuk berapa row dalam mytable yg ranking dia lebih dari 200.

naero · #3 (**permalink**) 01-05-2005, 05:00 PM

tq zaimlah...

ok sekarang camni...kalau boleh aku nak buat
dalam mytable ID 5 paling tertinggi so ranking die no 1 ID 1 no 2 tertinggi...
camne arrr..pening aku...aku dah try buatt pakai ID no 1..tapi ranking die tetap 1..takde 2 takde 3 (always no 1)

camne arr...kat bawah ni code die yang aku buat

PHP Code:


			
$ID = $_GET['ID'];

$result = mysql_query("SELECT (in + out + view) / 3 

                                FROM mytable 

                                WHERE ID = '$ID'");

  list($hits) = mysql_fetch_array($result, MYSQL_NUM); 

  if ($hits) 

  {

    $result = mysql_query("SELECT count(*) 

                                   FROM mytable 

                                   WHERE ((in + out + view) / 3) >= $hits 

                                   AND ID = '$ID'");

    list($rank) = mysql_fetch_array($result, MYSQL_NUM);

    if ($rank <= 5) 

    {

      $location = images."/".$rank.".".png;

      // testing begin

      print "hits = $hits<br>";

      print "rank = $rank<br>";

      print "id = $ID<BR>";

      // testing end

    }

header("Location: ".$location);

zaimlah · #4 (**permalink**) 01-05-2005, 08:17 PM

oh, ok. aku rasa sekarang baru aku faham, sorry tak baca betul2. column ranking simpan ranking bagi id kan? contoh: 1, 2, 3, 4, 5, etc. bukan value (in + out + view) / 3 (e.g: 230, 200, 500, etc.), kan? aku rasa lebih senang kalau column ranking simpan value formula, bukan ranking 1, 2, 3. cara ni tak ada guna php banyak sangat, only sql statements.

so, column ranking sekarang sepatunya di-rename-kan kepada "points" atau "average" atau (dalam contoh yg kau kasi) "hits". utk isi column ni, guna sql yg aku kasi:

Code:

UPDATE mytable SET hits=(mytable.in + mytable.out + mytable.view)/3

so, table mytable sekarang jadi macam ni:

Code:

| id | in  | out | view | category | hits |
+----+-----+-----+------+----------+------+
| 1  | 100 | 100 | 200  | makan    | 133  |
| 2  | 200 | 200 | 100  | tido     | 167  |
| 3  | 150 | 100 | 200  | lepak    | 150  |

so sekarang, untuk dapatkan ranking dia, just guna sql statement:

Code:

SELECT * FROM mytable ORDER BY hits DESC

statement ni akan return semua row dalam database ordered by average descending, macam ni:

Code:

| id | in  | out | view | category | hits |
+----+-----+-----+------+----------+------+
| 2  | 200 | 200 | 100  | tido     | 167  |
| 3  | 150 | 100 | 200  | lepak    | 150  |
| 1  | 100 | 100 | 200  | makan    | 133  |

contoh code php:

PHP Code:


			
// guna code ni untuk list ranking SELEPAS value utk 'average' dah calculate

$result = mysql_query("SELECT * FROM mytable ORDER BY average DESC");

if ($result) {

  $i = 1;

  while ($row = mysql_fetch_object($result)) {

    echo "rank: $i, hits: {$row->hits}, id: {$row->id}<br>\n";

    $i++;

  }

}

output:

Quote:

rank: 1, hits: 167, id: 2
rank: 2, hits: 150, id: 3
rank: 3, hits: 133, id: 1

good luck

naero · #5 (**permalink**) 04-05-2005, 12:06 AM

Tima kasih banyak2 Zaimlah... Idea ko suh masuk column average tu memang baguss la...apsall la aku tak terpk...tenkiu banyak2 zaimlah
memang berkesan gak idea ko nihh....

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Search Engine Ranking Factors	foress	Search Engine Marketing	4	07-02-2006 09:36 PM
1st - 3rd Page Ranking	Casey	Search Engine Marketing	16	22-09-2005 06:56 AM
will a redirect page hurt SEO ranking?	mrbadak	Search Engine Marketing	3	17-09-2005 08:50 PM
Increase your ranking in Google!!!	digidogstudios	Other Internet Marketing Methods	8	18-08-2005 10:18 PM
Get ranking & traffic boost using 1 link.	bzman	Other Webmaster-related Services and Promotion	0	12-07-2005 04:51 PM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)