Jump to content

Top links - past 7 days


tomcat_fo

Recommended Posts

I have a problem with a scipt that is suppose to show top-10 links with most hits past 7 days...... the problem is, that the same link can apear several times on the list, and i don't want that - hope someone can help me.


THis is the script that update/insert the toplinks table:
[color=blue]<?php 
  // Check if link exists
  $link = false;
  if (isset($_GET['link']) && (int)$_GET['link'] > 0) {
   
    $sql_check = mysql_query('SELECT id, url, bolka_id  FROM links WHERE id = "' . (int)$_GET['link'] . '"') or die('Ikki funni&eth;2');
    if (mysql_num_rows($sql_check) > 0) $link = mysql_fetch_array($sql_check);
  }
 
  if ($link) {
   
    $cur_date = date('Y-m-d');
    // Check link hits for current day
    $sql_count = mysql_query('SELECT toplinks_id FROM toplinks WHERE links_id = "' . $link['id'] . '" AND date = "' . $cur_date . '"') or die('Ikki funni&eth;2');
    if (mysql_num_rows($sql_count) > 0) {
     
      // Link is already counted for current date
      $result = mysql_fetch_row($sql_count);
      @mysql_query('UPDATE toplinks SET hits = hits + 1 WHERE toplinks_id = "' . $result[0] . '"');
     
    } else {
     
      // Link is not counted for current date
      @mysql_query('INSERT INTO toplinks (links_id, date, hits, bolka_id) VALUES("' . $link['id'] . '", "' . $cur_date . '", "1", "' . $link['bolka_id'] . '")');
    }
   
    // Redirect user
    header('Location: ' . $link['url']);
    exit();
   
  } else {
   
    echo 'Unable to find link - <a href="javascript:window.close();">Close Window</a>';
  }
 
?> [/color]

This is the script where i select the top-10 links past 7 days:
[color=red]<?PHP
//Server connect -------------------------------------

$placering=1;

$toplinks = mysql_db_query("$datab", 'select * FROM toplinks where date BETWEEN "' . date('Y-m-d') . '"" AND "' . date('Y-m-d', time()-604800) . '"');
while ($row = mysql_fetch_array ($toplinks)) {
$toplinks_id = "".$row["toplinks_id"];
$links_id = "".$row["links_id"];
$date = "".$row["date"];
$hits = "".$row["hits"];


$toplinks1 = mysql_db_query("$datab", "SELECT * FROM links WHERE id='".$links_id."'");
while ($row = mysql_fetch_array ($toplinks1)) {
$navn = "".$row["navn"];
$url = "".$row["url"];

$nummar=$placering++;

echo '<a href="javascript:void(0);" onclick="window.open(\''
        . 'kikarin/redirect.php?link=' . $row['id']
        . '\', \'linkname\', \'height=600,width=780,resizable=1,scrollbars=1\');">'
        . $nummar
. $row['navn']
        . '</a> <br>';
       
}
}
?>[/color]

Would be very thankful if someone can get it to work.... BTW as the scipt is now, nothing is showing in my website but an error-message, but even when there was something, on the list, links where dublicated.
Link to comment
Share on other sites

$date1 = date('Y-m-d');
$date2 = date('Y-m-d', time()-604800);
$sql="SELECT t.links_id , SUM(t.hits) as total_hits, l.navn , l.url FROM toplinks t, links l WHERE (t.links_id = l.id) AND t.date BETWEEN '$date1' AND '$date2' ORDER BY total_hits DESC LIMIT 10";
Link to comment
Share on other sites

[quote author=sasa link=topic=106861.msg427949#msg427949 date=1157397017]
$date1 = date('Y-m-d');
$date2 = date('Y-m-d', time()-604800);
$sql="SELECT t.links_id , SUM(t.hits) as total_hits, l.navn , l.url FROM toplinks t, links l WHERE (t.links_id = l.id) AND t.date BETWEEN '$date1' AND '$date2' ORDER BY total_hits DESC LIMIT 10";
[/quote]

[i][b]
Are these things attributs ?:[/b][/i]
t.links_id
t.hits
total_hits
l.navn
l.url
t
Link to comment
Share on other sites

sorry i dont set GROUP BY part
it must be[code]
$sql="SELECT t.links_id , SUM(t.hits) as total_hits, l.navn , l.url FROM toplinks t, links l WHERE (t.links_id = l.id) AND (t.date BETWEEN '$date1' AND '$date2') GROUP BY t.link_id ORDER BY total_hits DESC LIMIT 10";[/code]
t is short name from table toplinks
t.links_id is field from table toplink named link_id etc.
total_hits is name for sum of value of t.hits with same links_id
Link to comment
Share on other sites

[quote author=sasa link=topic=106861.msg427969#msg427969 date=1157399048]
sorry i dont set GROUP BY part
it must be[code]
$sql="SELECT t.links_id , SUM(t.hits) as total_hits, l.navn , l.url FROM toplinks t, links l WHERE (t.links_id = l.id) AND (t.date BETWEEN '$date1' AND '$date2') GROUP BY t.link_id ORDER BY total_hits DESC LIMIT 10";[/code]
t is short name from table toplinks
t.links_id is field from table toplink named link_id etc.
total_hits is name for sum of value of t.hits with same links_id
[/quote]

I have now tryed that.... now i get no error message, but i don't get it to print anything... maybe i got some of your notation wrong  ???

Here is how the script looks like now:
<?PHP
//Server connect -------------------------------------
include 'www/samband/kikarin/samband.php';
$placering=1;
$date1 = date('Y-m-d');
$date2 = date('Y-m-d', time()-604800);
$sql="SELECT links_id , SUM(hits) as total_hits, navn , url FROM toplinks, links WHERE (links_id = id) AND (date BETWEEN '$date1' AND '$date2') GROUP BY link_id ORDER BY total_hits DESC LIMIT 10";
while ($row = mysql_fetch_array ($toplinks)) {
$toplinks_id = "".$row["toplinks_id"];
$links_id = "".$row["links_id"];
$date = "".$row["date"];
$hits = "".$row["hits"];


$toplinks1 = mysql_db_query("$datab", "SELECT * FROM links WHERE id='".$links_id."'");
while ($row = mysql_fetch_array ($toplinks1)) {
$navn = "".$row["navn"];
$url = "".$row["url"];

$nummar=$placering++;

echo '<a href="javascript:void(0);" onclick="window.open(\''
        . 'kikarin/redirect.php?link=' . $row['id']
        . '\', \'linkname\', \'height=600,width=780,resizable=1,scrollbars=1\');">'
        . $nummar
. $row['navn']
        . '</a> <br>';
       
}
}
?>
Link to comment
Share on other sites

you dont query your database
try to make test.php[code]
<?php
// conect to database
$date1 = date('Y-m-d');
$date2 = date('Y-m-d', time()-604800);
$sql="SELECT links_id , SUM(hits) as total_hits, navn , url FROM toplinks, links WHERE (links_id = id) AND (date BETWEEN '$date1' AND '$date2') GROUP BY link_id ORDER BY total_hits DESC LIMIT 10";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
echo '<pre>';
print_r($row);
echo "</pre><hr />\n"
}
?>
[/code]and run it
Link to comment
Share on other sites

[quote author=sasa link=topic=106861.msg428006#msg428006 date=1157402815]
you dont query your database
try to make test.php[code]
<?php
// conect to database
$date1 = date('Y-m-d');
$date2 = date('Y-m-d', time()-604800);
$sql="SELECT links_id , SUM(hits) as total_hits, navn , url FROM toplinks, links WHERE (links_id = id) AND (date BETWEEN '$date1' AND '$date2') GROUP BY link_id ORDER BY total_hits DESC LIMIT 10";
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
echo '<pre>';
print_r($row);
echo "</pre><hr />\n"
}
?>
[/code]and run it
[/quote]


if i do that i get this message:
Parse error: syntax error, unexpected '}', expecting ',' or ';' in /usr/home/web/web128130/test.php on line 11
Link to comment
Share on other sites

at last i setup sam data for testing. it must works
the output is just table with some data but i believe thet you can adapt it[code]
<?php
mysql_connect("localhost", "", "");
mysql_select_db("test");
// conect to database
$date1 = date('Y-m-d');
$date2 = date('Y-m-d', time()-604800);
$sql="SELECT t.links_id , SUM(t.hits) as total_hits, l.navn , l.url FROM toplinks t, links l WHERE (t.links_id = l.id) AND (t.date BETWEEN '$date2' AND '$date1') GROUP BY t.links_id ORDER BY total_hits DESC LIMIT 10";
$result = mysql_query($sql);
$nummar = 0;
echo '<table border="3"><tr><td>Numar</td><td>Links ID</td><td>Total hits</td><td>Navn</td><td>URL</td></tr>',"\n";
while ($row = mysql_fetch_assoc($result)) {
echo '<tr>';
echo '<td>',++$nummar,'</td><td>',$row['links_id'],'</td><td>',$row['total_hits'],'</td><td>',$row['navn'],'</td><td>',$row['url'],'</td></tr>';
echo "</tr>\n";
}
echo '</table>';
?>
[/code]
Link to comment
Share on other sites

Thanks a lot, that seems to work, and i see no dublicates now...

The only thing is, that i don't want visible tables arround it, and i only want a number (place) in front of the title (navn) of the link, and that text is suppose to be a link, that opens in a new window of a preset size.... you can probably se what i mean if you look at my orginal script.


Thanks a lot, I will try and adjust your code so it works like i want to.... but if you have some spare time - i would be very thankefull ;)
Link to comment
Share on other sites

You can see my website at www.kikarin.com

The top-list is down to right of the website frontpage..... your is the one with frames  - i have other top-lists there, and they look like i want to, but don't work like i want to (they have dublicates links), but with your script i can now adjust them as well.
Link to comment
Share on other sites

Now i have adjusted the code that i use in TOP-10 LINKS

But i still have a very small problem i think.....

THe code looks like this:
[code]<?php

include 'www/samband/kikarin/samband.php';
$placering = 1;
// conect to database
$date1 = date('Y-m-d');
$date2 = date('Y-m-d', time()-604800);
$sql="SELECT * , SUM(hits) as total_hits, navn , url FROM toplinks, links WHERE (links_id = id) AND (date BETWEEN '$date2' AND '$date1') GROUP BY links_id ORDER BY total_hits DESC LIMIT 10";
$result = mysql_query($sql);


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

$nummar=$placering++;

echo '<a href="javascript:void(0);" onclick="window.open(\''
        . 'kikarin/redirect.php?link=' . $row['id']
        . '\', \'linkname\', \'height=600,width=780,resizable=1,scrollbars=1\');">'
        . $nummar
        . $row['navn']
        . '</a> <br>';
}

?>[/code]

I have links in diferent groups and i don't want to print all groups.... in the table [b]toplinks [/b]there is an atribut [b]bolka_id[/b] (gruop id) and i only want to print/echo links where bolka_id is under 30 ([b]bolka_id <30[/b]) ... but i get an error message if i do that.

Have tryed to insert this in your script (sql guerry):
[b]AND (bolka_id < 30)[/b]

it will not work.... hope you can place something like that in the right place for me so it works.
Link to comment
Share on other sites

[code]
$sql="SELECT * , SUM(hits) as total_hits, navn , url FROM toplinks, links WHERE (links_id = id) AND (date BETWEEN '$date2' AND '$date1') AND (toplinks.bolka_id < '30') GROUP BY links_id ORDER BY total_hits DESC LIMIT 10";
[/code}[/code]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.