Jump to content


Photo

Top links - past 7 days


  • Please log in to reply
15 replies to this topic

#1 tomcat_fo

tomcat_fo
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 04 September 2006 - 04:45 PM

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


This is the script where i select the top-10 links past 7 days:
<?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>';
       
}
}
?>


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.

#2 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 04 September 2006 - 07:10 PM

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

#3 tomcat_fo

tomcat_fo
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 04 September 2006 - 07:20 PM

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



Are these things attributs ?:

t.links_id
t.hits
total_hits
l.navn
l.url
t

#4 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 04 September 2006 - 07:44 PM

sorry i dont set GROUP BY part
it must be
$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";
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

#5 tomcat_fo

tomcat_fo
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 04 September 2006 - 08:21 PM

sorry i dont set GROUP BY part
it must be

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


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

#6 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 04 September 2006 - 08:46 PM

you dont query your database
try to make test.php
<?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"
}
?>
and run it

#7 tomcat_fo

tomcat_fo
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 04 September 2006 - 10:22 PM

you dont query your database
try to make test.php

<?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"
}
?>
and run it



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

#8 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 05 September 2006 - 04:19 AM

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

#9 tomcat_fo

tomcat_fo
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 05 September 2006 - 11:30 AM

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


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

#10 tomcat_fo

tomcat_fo
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 05 September 2006 - 06:38 PM

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.

#11 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 05 September 2006 - 09:17 PM

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


#12 tomcat_fo

tomcat_fo
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 05 September 2006 - 10:46 PM

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

#13 tomcat_fo

tomcat_fo
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 05 September 2006 - 10:48 PM

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.

#14 tomcat_fo

tomcat_fo
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 05 September 2006 - 11:16 PM

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

?>

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

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

it will not work.... hope you can place something like that in the right place for me so it works.

#15 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 06 September 2006 - 12:16 AM

$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}


#16 tomcat_fo

tomcat_fo
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 06 September 2006 - 01:19 AM

Thanks so much !

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users