Top links - past 7 days

[tomcat_fo]

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.

[sasa]

$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";

[tomcat_fo]

[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

[sasa]

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

[tomcat_fo]

[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>';
       
}
}
?>

[sasa]

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

[tomcat_fo]

[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

[sasa]

add ; in line echo "</pre><hr />\n"

[tomcat_fo]

[quote author=sasa link=topic=106861.msg428190#msg428190 date=1157429984]
add ; in line echo "</pre><hr />\n"
[/quote]

Now i get:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /usr/home/web/web128130/test.php on line 7

[tomcat_fo]

Does anyone know what could cause the error...... and most of all how i make the script work so that it show top-10 links past 7 days.

[sasa]

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]

[tomcat_fo]

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 ;)

[tomcat_fo]

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.

[tomcat_fo]

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.

[sasa]

[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]

[tomcat_fo]

Thanks so much !

Have been trying to get that thing to work for several days now, but with no luck.