[SOLVED] How to check if something exists in mysql db

[RickyF]

Hello,

 

Is it possible to grab the data from the database, the var is $weburl, and $weburl will be a website, e.g. http://www.site.com

 

I need mysql to be checked to see if $weburl already exists in the mysql database, i have included the database structure. But it has to check in such a format, it has to check if *site.com* exists in the database, not www.site.com or http://www.site.com

 

Hope that makes sense 

 

Thanks for any help!

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE `plugboard` (
  `id` int(10) NOT NULL auto_increment,
  `imageurl` text,
  `weburl` text,
  `date` varchar(15) default NULL,
  `ip` varchar(15) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=154 ;

[bluebyyou]

Something like this..

<?php
$test = "http://www.site.com";


$connection = mysql_connect($host,$mysql_user,$password) or die ("Couldn't connect to server.");
$selectdb = mysql_select_db($db,$connection) or die ("Couldn't select Intern database.");

$query = "SELECT * FROM plugboard WHERE weburl = '$test'";

$result = mysql_query($query) or die (mysql_error());
$num = mysql_num_rows($result);

if ($num > 0) {$site_exists = "yes";}
?>

[chigley]

Try this:

 

<?php

$weburl = "http://www.site.com"; // Define the web url

$search = str_replace("www.", "", parse_url($weburl, PHP_URL_HOST)); // Split the web url into site.com

$query = mysql_query("SELECT * FROM plugboard WHERE weburl LIKE '%{$search}%'") or die(mysql_error()); // Select all the rows where the weburl field contains site.com

if(mysql_num_rows($query)) { // If the query returned any rows
echo "{$weburl} found in database"; // Found
} else {
echo "{$weburl} not found in database"; // Not found
}

?>

[RickyF]

Warning: parse_url() expects exactly 1 parameter, 2 given, on line 13

 

Line 13 is: $search = str_replace("www.", "", parse_url($weburl, PHP_URL_HOST)); // Split the web url into site.com

 

<?php
$host = 'removed';
$mysql_user = 'removed';
$password = 'removed';
$db = 'removed';

$weburl = "http://www.test.com"; // Define the web url

$connection = mysql_connect($host,$mysql_user,$password) or die ("Couldn't connect to server.");

$selectdb = mysql_select_db($db,$connection) or die ("Couldn't select Intern database.");

$search = str_replace("www.", "", parse_url($weburl, PHP_URL_HOST)); // Split the web url into site.com

$query = mysql_query("SELECT * FROM plugboard WHERE weburl LIKE '%{$search}%'") or die(mysql_error()); // Select all the rows where the weburl field contains site.com

if(mysql_num_rows($query)) { // If the query returned any rows
echo "{$weburl} found in database"; // Found
} else {
echo "{$weburl} not found in database"; // Not found
}

?>

[chigley]

Works on my version of PHP, try this:

 

<?php

$weburl = "http://www.site.com"; // Define the web url

$scheme = parse_url($weburl); // Parse the web url into separate parts
$host = $scheme["host"]; // Separate the host

$search = str_replace("www.", "", $host); // Remove the www. - leaving us with site.com

$query = mysql_query("SELECT * FROM plugboard WHERE weburl LIKE '%{$search}%'") or die(mysql_error()); // Select all the rows where the weburl field contains site.com

if(mysql_num_rows($query)) { // If the query returned any rows
echo "{$weburl} found in database"; // Found
} else {
echo "{$weburl} not found in database"; // Not found
}

?>

[RickyF]

Hey, thanks for that, but http:// needs to be stripped aswell as www., and also make it ignore anything typed after .com or whatever the extension is, e.g. if  http://www.site.com/folder/ was typed, i would only want *site.com* to be searched for.

 

 

Thanks!

[chigley]

It should work, http:// and following folders are stripped out by parse_url

[RickyF]

lol me being lazy, sorry mate, thanks for the help! 

[RickyF]

One last thing, my script is showing only the last 20 records as i want it, how ever i don't know how to make the databae not store any more then 20 records, how is this changed, by php or the table structure its self, could you help out with that?

[chigley]

No problemo, if you have PHP 5 I'd suggest using str_ireplace for the www. replacement

 

EDIT: I don't understand your second post, can you explain it better please?

[RickyF]

Still on php 4.4.7, heh.

 

Well, 20 rows are shown, but theres still rows in the table that exist but arent being shown, this will make the database huge over time, so theres no point in making the database hold more then 20 rows - or records whatever you want to call them.

[RickyF]

Heres part of my script that gets the mysql data:

 

      $db = mysql_connect($dbhost,$dbuser,$dbpass); 
      mysql_select_db($dbname) or die(mysql_error());
      mysql_query("INSERT INTO plugboard(imageurl,weburl,date,ip) VALUES('$imageurl','$weburl','$date','$ip')"); 
    }
}

  $db = mysql_connect($dbhost,$dbuser,$dbpass);
  mysql_select_db($dbname) or die(mysql_error());
  $query = "SELECT * FROM plugboard ORDER BY id DESC LIMIT 20"; 
  $result = mysql_query($query);

 

[keeB]

2 ways. (maybe 3)

 

1. Cron job which deletes all rows older than the last 20 seconds.

2. Every time the table is updated, you delete the rows.. this is a lot of overhead, though!

 

 

And the 3rd..

 

Write a SQL Trigger.. more information here: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

 

[RickyF]

Oh really, i thought you could "limit" the mysql table, so that only 20 records could be in the table at any one time, and when new data was inputted the 20th row would be removed then the new new data would be first etc.

[keeB]

Never heard of that.

[redarrow]

limit is to limit a selected database rows, if you use limit 20 then ur only see 20 rows and can be done in order asc asending desc desending, limit does nothink else.

 

use a cron to update / delete via a timestamp

[RickyF]

I dont really have crons in mind, how much would be involved in making this work through php/mysql?

 

What sortof script size etc?

[RickyF]

A script i have already used in my script already does this i just realised!

 

http://www.phpclasses.org/browse/file/10982.html

 

It creates a time stamp, and then checks each time the db is accessed if the time stamp is expired, if it is it deletes the rows that are expired.

 

Could some one help out with something that does this, the code is in the script above, just needs to be fiddled around with a bit.

[keeB]

I just gave you the ways to do it.

 

<?php

function deleteOlderThan20Seconds($20secago) {
   $q = "DELETE FROM myTable where TIMESTAMP < $20secago";
   mysql_query($q);
}

function updateTable($data) {
   deleteOlderThan20Seconds(strtotime("20 seconds ago"));
   $q = "INSERT INTO myTable ... bla bla bla";
}
?>

 

Or a cron job.