php/mysql - I have a question

[Danzig]

I am writing a links page and everything was going fine until I got the idea to have a hits counter for every link. Meaning that every time someone clicks a link the links counter should be updated with +1..... I have no idea of how I should do that. I am a begginer, have only been working on php for about 2 weeks and anoyingly as it is my newly purchased php book went missing in the spring cleaning last week...damn it!

Anyway, is it possible to do?

The code looks like this:


..... ...... .....
$RESULT = mysql_query("SELECT * FROM links ORDER BY id")
or die ("Felaktig fråga till databas");


while ($row=mysql_fetch_array ($RESULT)) {
?>
<tr>
<td width="50%"><div align="Justify"><h1><a href="<? echo $row["url"]; ?>"><? echo $row["name"]; ?></a></h1>
<h2> <? echo $row["descr"]; ?>
<br>
</h2>



<small><b><? echo $row["rate"]; ?></b></small><br>
</div></td>

</tr><?
}
?>

[coolblu]

Im a complete beginner at mySQL myself but I believe that the code is something like


$query = "update links set clicks=clicks+1 where id = $id";

I think that you have to have tables called links, clicks and id, so you would have an id for each link and then add 1 for each click on a link. This would need to be put into a mySQL statement of some sort but I dont know what.

Sorry for my very limited help

Coolblu

[Danzig]

Originally posted by coolblu
Sorry for my very limited help


Hey, it is really nice to have someone to discuss the problem with, thanks! :)

I got a table called links and I got 6 columns called id, name, url, hits, descr and rate.

The code you wrote didn't make much sense to me *blush* do you care to explain it?:)

I am not sure but I think it is that "while" code that makes it ugly/difficult....

[Danzig]

I have been thinking... I don't really know but it seem like I need something like: if onclick then execute ... inside the a href tag itself.

Man this is really tricky... I don't know what to do???:confused:

[Danzig]

Anyone....please. Atleast tell me if it is possible or not.

[megapuzik]

coolblu gave you exactly what you need, what is the problem ?

[Danzig]

Originally posted by megapuzik
coolblu gave you exactly what you need, what is the problem ?

I am sorry but I am no good at this:o , it wont work for me....:confused2

I have implemented what coolblu suggested, like this:



$RESULT = mysql_query("SELECT * FROM links ORDER BY id")
or die ("Felaktig fråga till databas");

$query = "UPDATE links set hits=hits+1 where id = $id";
while ($row=mysql_fetch_array ($RESULT)) {
?>
<tr>
<td width="50%"><div align="Justify"><h1><a href="<? echo $row["url"]; ?>">
<? mysql_query($query); ?>
<? echo $row["name"]; ?></a></h1>
<h2> <? echo $row["descr"]; ?>
<br>
<br>
Hits Counter: <? echo $row["hits"]; ?>


</h2>



<small><b><? echo $row["rate"]; ?></b></small><br>
</div></td>

</tr><?
}
?>


Well it isn't working.... and I can't figure out what is wrong. :cry2:

[coolblu]

Originally posted by Danzig


Hey, it is really nice to have someone to discuss the problem with, thanks! :)


Im glad I could be of help.

[megapuzik]

ok, the way you should do this is by using the vars with GET method....
look at this :
1) you should need to do a page that people will rate this, r, you can do a link in the same page :


$RESULT = mysql_query("SELECT * FROM links ORDER BY id")
or die ("Felaktig fråga till databas");
while ($row=mysql_fetch_array ($RESULT)) {
?>
<tr>
<td width="50%"><div align="Justify"><h1><a href="<? echo $row["url"]; ?>">
<? echo $row["name"]; ?></a></h1>
<h2> <? echo $row["descr"]; ?>
<br>
<br>
Hits Counter: <? echo $row["hits"]; ?>


</h2>



<small><b><? echo $row["rate"]; ?></b></small><br>
</div></td>

</tr><br>
click <a href=<? print "$PHP_SELF?rateid='$row[\"rate\"]'";?>>HERE</a> to rate this bla<?
}
?>

and in the head of the page do something like this :


if(isset($rateid))
{
mysql_query("UPDATE links set hits=hits+1 where id = $rateid");
}


thats it

[Danzig]

Ok problem solved... After consulting www.php.net for aprox. 8 hours I found the solution! I did it in another way then you suggested though, have a look:



$RESULT = mysql_query("SELECT * FROM links ORDER BY hits DESC")
or die ("Felaktig fråga till databas");

while ($row=mysql_fetch_array ($RESULT)) {
?>
<tr>
<td width="50%"><div align="Justify"><h1><a href="sendlinks.php?<? echo $row["id"]; ?>">
<? echo $row["name"]; ?></a></h1>


<br> <h2> <? echo $row["descr"]; ?>
<br>
<br>
Hits Counter: <? echo $row["hits"]; ?>

</h2>

<small><b><? echo $row["rate"]; ?></b></small><br>
</div></td>

</tr><?
}
?>


Then I created sendlinks.php and put the folloving code there:






mysql_query("UPDATE links set hits=hits+1 where id = $QUERY_STRING");

$apa = $QUERY_STRING;
echo "Redirecting...";

$result = mysql_query("SELECT * FROM links WHERE id = $apa")
or die ("Felaktig fråga till databas");
$data = mysql_fetch_object($result);
?>

<script>
location.href="<? echo $data->url; ?>"
</script>


:)
I don't know if this was the best solution to the problem but atleast it is now doing exactly what I want.;) Thanks for the tips and ideas!:)