PHP & MySQL Question

Status
Not open for further replies.
B

bam bam

New member
Oct 9, 2007
962
11
0
So i'm using this code (kinda taken from erect's post in the SEO Questions):

PHP: 
<?php

$ref = $_GET['url'];
$handle = fopen("/home/artofbam/public_html/trackthemes.html", "a");

fwrite($handle, $ref. '<br>');
fclose($handle);

?>
With my link above it in plain text (so it displays on the theme).

Speaking of which here's the code to display my link:

PHP: 
<?php
$url = $_SERVER['HTTP_HOST'];
echo file_get_contents('http://www.mysite.com/abovefile.php?url=' . $url);
All well and good but i want to take it to the next level by adding the $url to a database, but only if it isn't already in there.

So, how do i check for duplicates in MySQL and add the $url if there are none?
 


I'm pretty new to php / SQL, but this is how I would try to do it:

//try to match the url thet you have with one in the database
$query = ("SELECT url FROM table WHERE url = '$url'");
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$dupeurl = $row['url'];
//if it doesn't find a match, add it to the database
if ($dupeurl != $url)
{
("INSERT INTO table (url) VALUES ('$url')");
}

So you search the url field in your database for the value in $url, if it doesn't find it, it adds it to the databas, if it does find it it does nothing. I've not run the code, so it may have errors in it...
 
Create a unique constraint on the url field in the database and use the following sql.

Code: 
INSERT [B]IGNORE[/B] INTO table (url) VALUES ($url);
If it finds the value for $url in the url field of the database it will ignore the insert statement.
 
  • Like
Reactions: erect
ikonic said:
Create a unique constraint on the url field in the database and use the following sql.

Code: 
INSERT [B]IGNORE[/B] INTO table (url) VALUES ($url);
If it finds the value for $url in the url field of the database it will ignore the insert statement.
Click to expand...

You know entirely too much about sql. In years of programming I've never used the 'IGNORE' feature ... which makes me sound quite foolish. Great answer though as php would take a few lines to handle this.

One thing to point out to bam is that I'm not sure exactly how HTTP_HOST the www/non-www issue ... so just strip it out before inserting into db. You want your records to be as clean as possible when they get inserted.
 
Right, thanks for the help guys, but i can't get it working.

PHP: 
<?php

$url = $_GET['url'];

$con = mysql_connect("localhost","my_user","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$sql="INSERT IGNORE INTO table (url)
VALUES
('$_GET[url]')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}

?>

Only problem now is it just adds it to the db whether it's there or not.
So it's not checking for dupes.

Any ideas?
 
Have you set the field to be unique?

Code: 
ALTER  TABLE  `table`  ADD  UNIQUE ( `url` );
(where `table` is the table and `url` is the field with the urls)
 
This isn't tested but try this

Code: 
$url = $_GET['url'];

$con = @mysql_connect("localhost","my_user","pass") or die('Could not connect: ' . mysql_error());

@mysql_select_db("my_db", $con);

$sql = "INSERT IGNORE INTO table (url) VALUES ('".$_GET[url]."')";

@mysql_query($sql);

@mysql_close();
If that doesn't work try this

Code: 
$url = $_GET['url'];

$con = @mysql_connect("localhost","my_user","pass") or die('Could not connect: ' . mysql_error());

@mysql_select_db("my_db", $con);
$sql = "SELECT id FROM table WHERE url = '".$_GET['url']."'";
$result = @mysql_query($sql);
$rows = @mysql_num_rows($result);

@mysql_close();

if(!$rows) {
  $con = @mysql_connect("localhost","my_user","pass") or die('Could not connect: ' . mysql_error());

@mysql_select_db("my_db", $con);

$sql = "INSERT IGNORE INTO table (url) VALUES ('".$_GET[url]."')";

@mysql_query($sql);
@mysql_close();
You can also keep a persistant connection open by using the true value after your connection... for instance like this $con = @mysql_connect("localhost","my_user","pass",true) or die('Could not connect: ' . mysql_error());

It makes it so you don't have to continually open new connections.
 
Add a new integer field to your database called linkcount with a default value 0. Make sure URL is set up as a UNIQUE field.

Then use this SQL statement

Code: 
[FONT=Courier New][COLOR=white]$sql=[/COLOR][/FONT][FONT=Courier New][COLOR=#dd0000][COLOR=white]"INSERT INTO table (url) [/COLOR][/COLOR][/FONT]
[FONT=Courier New][COLOR=#dd0000][COLOR=white]VALUES('$_GET[url]')[/COLOR] [/COLOR][/FONT]On duplicate KEY UPDATE linkcount=linkcount+1 ";

When it tries to insert an already existing URL it will update the linkcount for that URL.

Now you're on your way to building your own PageRank algo! :)
 
Status
Not open for further replies.
Top Bottom