Making a Linktracker

[SDP2006]

What I am going to do is pass information through the HTTP Addess. I am using variables $des,$cat, and $link

$des = name of site
$link = address of site
$cat = equals category of site

I am wanting to see if field des has something that matches $des and if it does, add one to field hits

If field des has nothing like $des, then enter the info and set it to one, where if clicked again, it will add one.

Here is the code I have so far

PHP Code:

<?php

$cat = $_GET['cat'];
$des = $_GET['des'];
$link = $_GET['link'];

$conn = mysql_connect("localhost","sdpeele","8590481");

$db = mysql_select_db("db");

$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE des='$des'");

$sql = mysql_fetch_array($result);
$des = $sql['des'];

if($des){
    $result = MYSQL_QUERY("UPDATE linktracker SET `hits`=`hits`+1 WHERE `des`='$des'");
}
elseif(!des){
    $result = MYSQL_QUERY("INSERT INTO `linktracker`(`id`,`des`,`cat`,`href`,`hits`) VALUES('NULL','$cat','$des','$link','1')");
    
}
?>

Thanks for any help....

[coffeedemon]

PHP Code:

<?php

$cat = $_GET['cat'];
$des = $_GET['des'];
$link = $_GET['link'];

$conn = mysql_connect("localhost","sdpeele","8590481");

$db = mysql_select_db("db");

$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE des='$des'");

$sql = mysql_fetch_array($result);
$resultsNum = mysql_numrows($result) # returns number of results

# if resultsNum is greater or equal to 1
if($resultsNum >= 1){
    $result = MYSQL_QUERY("UPDATE linktracker SET `hits`=`hits`+1 WHERE `des`='$des'")or die(mysql_error)); # if query fails return error
}
else
{
# if resultsNum is not greater then or equal to 1 then it must be 0
    MYSQL_QUERY("INSERT INTO `linktracker`(`id`,`des`,`cat`,`href`,`hits`) VALUES('NULL','$cat','$des','$link','1')")or die(mysql_error)); # if query fails return error
    
}
?>

[fimi]

i don't have your answer but i would make another field called link_id and assign an id to the link so that
when you do:
$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE des='$des'");

it would be:
$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE link_id='$l_id'");


matching numbers are the easiest and fastest, and using your method if and only if one char is diff it will be added into mysql...

its just a sugesstion,
fimi

[SDP2006]

Here is my code, thanks to coffeedemon

PHP Code:

<?php

$cat = $_GET['cat'];
$des = $_GET['des'];
$link = $_GET['link'];

$conn = mysql_connect("localhost","sdpeele","8590481");

$db = mysql_select_db("db",$conn);

$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE des='$des'");

$sql = mysql_fetch_array($result);
$resultsNum = mysql_num_rows($result);

if($resultsNum >= 1){
    $result = MYSQL_QUERY("UPDATE linktracker SET `hits`=`hits`+1 WHERE `des`='$des'") or die(mysql_error());
}
else
{
    MYSQL_QUERY("INSERT INTO `linktracker`(`id`,`des`,`cat`,`href`,`hits`) VALUES('NULL','$cat','$des','$link','1')") or die(mysql_error());
    
}
?>

Now, it is inserting the proper data, but its not adding one to hits. Its creating the row over and over again not adding 1 to 'hits' if it is more than one. If you dont understand, see this - http://www.net-riches.com/pics/db.JPG -

Thanks for all help...

[raf]

You could just as well replace all this

PHP Code:

$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE des='$des'");

$sql = mysql_fetch_array($result);
$resultsNum = mysql_num_rows($result);

if($resultsNum >= 1){
    $result = MYSQL_QUERY("UPDATE linktracker SET `hits`=`hits`+1 WHERE `des`='$des'") or die(mysql_error());
}
else
{
    MYSQL_QUERY("INSERT INTO `linktracker`(`id`,`des`,`cat`,`href`,`hits`) VALUES('NULL','$cat','$des','$link','1')") or die(mysql_error());
    
} 


with

PHP Code:

$result = mysql_query("REPLACE linktracker SET `hits`=(`hits`+1) WHERE `des`='" . $des . "'") or die(mysql_error()); 


Replace will check if the record exists and update it or insert a new record. 'des' should then have a unique index or should be the primary key
http://www.mysql.com/doc/en/REPLACE.html

[fimi]

Originally posted by raf
You could just as well replace all this

PHP Code:

$result = MYSQL_QUERY("SELECT * FROM linktracker WHERE des='$des'");

$sql = mysql_fetch_array($result);
$resultsNum = mysql_num_rows($result);

if($resultsNum >= 1){
    $result = MYSQL_QUERY("UPDATE linktracker SET `hits`=`hits`+1 WHERE `des`='$des'") or die(mysql_error());
}
else
{
    MYSQL_QUERY("INSERT INTO `linktracker`(`id`,`des`,`cat`,`href`,`hits`) VALUES('NULL','$cat','$des','$link','1')") or die(mysql_error());
    
} 


with

PHP Code:

$result = mysql_query("REPLACE linktracker SET `hits`=(`hits`+1) WHERE `des`='" . $des . "'") or die(mysql_error()); 


Replace will check if the record exists and update it or insert a new record. 'des' should then have a unique index or should be the primary key
http://www.mysql.com/doc/en/REPLACE.html

LOL, i really like this.... i didn't know about REPLACE. 6 lines turned into 1
thx for the tip

[SDP2006]

In my table 'id' is my primary key.

I'm kinda confused raf, can you show me the complete code and not just what to replace?

Thanks

[raf]

PHP Code:

$cat = $_GET['cat'];
$des = $_GET['des']; // no need for this intermediate variable
$link = $_GET['link'];

/*these should go inside an include. from here */
$conn = mysql_connect("localhost","sdpeele","8590481");

$db = mysql_select_db("db",$conn);
/*to here */
$result = mysql_query("REPLACE linktracker SET `hits`=(`hits`+1) WHERE `des`='" . $des . "'") or die(mysql_error()); 


You see, it could actualy be just 2 lines of code (one for the include and one for the query.)

Now, replace need to know which record to look for to decide if it exists.
If all values from 'des' are unique, then you can create a unique index on it. Or else you need to use the id (so the querystring should contain the id value then)
Since each link can only point to one site, there shoukd be at least one 'no duplicate values' column inside your table. No ?

[SDP2006]

Now, I'm really confuses.

The replace function throws me for a loop.

All I want to do is -> Check for matching value ($des), if matching value exists add one to 'hits' if matching value does not exist, then create the field.

Errr, sorry if I am being a pain, buts thats kinda confusing.

Thanks for the help...

[bcarl314]

Not that I want to alarm anyone, but shouldn't we NOT give out our username and password???

[SDP2006]

Yes, but my host is pretty secure. You have to login into the control panel, then login to database management, and then login to phpmyadmin, (all three have different usernames and passwords)

[raf]

Originally posted by SDP2006
Now, I'm really confuses.

The replace function throws me for a loop.

How could that result in a loop? It will just execute the query once. If you get a loop, then it must be some othr code around the one you posted

Originally posted by SDP2006
All I want to do is -> Check for matching value ($des), if matching value exists add one to 'hits' if matching value does not exist, then create the field.

Check the link in my previous post and you'll see it does exactly that.

And bcarl314 is right about the user and pwd: All we need to do is take an account on the same host and we can manipulate your db. I don't see why we would need phpmyadmin.

[fimi]

Originally posted by bcarl314
Not that I want to alarm anyone, but shouldn't we NOT give out our username and password???

test it in your server, see if you can use it :
/*these should go inside an include. from here */
$conn = mysql_connect("localhost","sdpeele","8590481");

lol, that point is that it dosent matter, they do it on their own risk

[SDP2006]

So....

PHP Code:

$cat = $_GET['cat'];
$des = $_GET['des']; // no need for this intermediate variable
$link = $_GET['link'];

/*these should go inside an include. from here */
$conn = mysql_connect("localhost","sdpeele","8590481");

$db = mysql_select_db("db",$conn);
/*to here */
$result = mysql_query("REPLACE linktracker SET `hits`=(`hits`+1) WHERE `des`='" . $des . "'") or die(mysql_error()); 


will do all i need it to do?

[fimi]

AND...

Originally posted by raf
Now, replace need to know which record to look for to decide if it exists.
If all values from 'des' are unique, then you can create a unique index on it. Or else you need to use the id (so the querystring should contain the id value then)
Since each link can only point to one site, there shoukd be at least one 'no duplicate values' column inside your table. No ?