Jragon Posted November 15, 2010 Share Posted November 15, 2010 hey guys, i need some help with my php/mysql iplogger. My code: <?php //finds out ip $ip = $_SERVER['REMOTE_ADDR']; //conects to the mysql server $connection = mysql_connect('localhost', 'root', ''); //sellects the database mysql_select_db('iplog', $connection); //looks for duplacute ips $dup = mysql_query("SELECT COUNT(number) FROM logged_ips WHERE ip_address = '$ip'",$connection); $count = mysql_result($dup, 0); //checks to see if there is a duplecate name if ($count == 0){ //inserts the ip in to the database $string = 'INSERT INTO `logged_ips` (`aid`, `ip_address`, `ip_visits`) VALUES (\'' . null . '\', \'' . $ip . '\', \'0\')'; mysql_query($string, $connection); }else{ //adds a visit to the database $string2 = "UPDATE `logged_ips` SET `ip_visits` = '++1' WHERE `ip_address` = $ip LIMIT 0,1"; mysql_query($string2, $connection); } //outputs the ip echo $ip; ?> error: Warning: mysql_result() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\randoms\logger\test.php on line 11 127.0.0.1 It also doesnt put anything in to the mysql database. Please help. Thanks jragon Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/ Share on other sites More sharing options...
AbraCadaver Posted November 15, 2010 Share Posted November 15, 2010 $dup = mysql_query("SELECT COUNT(number) FROM logged_ips WHERE ip_address = '$ip'",$connection) or die(mysql_error()); Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134461 Share on other sites More sharing options...
Pikachu2000 Posted November 15, 2010 Share Posted November 15, 2010 Your query is failing and returning a boolean FALSE to mysql_result(). Echo the query string, and echo mysql_error(). Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134462 Share on other sites More sharing options...
Jragon Posted November 15, 2010 Author Share Posted November 15, 2010 Thanks, i should of put some error checking stuff my new error: Unknown column 'number' in 'field list' Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134463 Share on other sites More sharing options...
PFMaBiSmAd Posted November 15, 2010 Share Posted November 15, 2010 You can replace all three of those queries with a simple - INSERT ... ON DUPLICATE KEY UPDATE ... query. Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134468 Share on other sites More sharing options...
Jragon Posted November 15, 2010 Author Share Posted November 15, 2010 Could you put that in to a bit of code please =P Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134470 Share on other sites More sharing options...
Pikachu2000 Posted November 15, 2010 Share Posted November 15, 2010 Then obviously you have no column named `number` in that table. My best guess is that the query should be: "SELECT COUNT(ip_address) FROM logged_ips WHERE ip_address = '$ip'" Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134472 Share on other sites More sharing options...
Jragon Posted November 15, 2010 Author Share Posted November 15, 2010 Here is the dumps: -- phpMyAdmin SQL Dump -- version 3.2.4 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Nov 15, 2010 at 03:53 PM -- Server version: 5.1.41 -- PHP Version: 5.3.1 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `iplog` -- -- -------------------------------------------------------- -- -- Table structure for table `logged_ips` -- CREATE TABLE IF NOT EXISTS `logged_ips` ( `aid` int(11) NOT NULL AUTO_INCREMENT, `ip_address` varchar(68) NOT NULL, `ip_visits` int(11) NOT NULL, PRIMARY KEY (`aid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=38 ; -- -- Dumping data for table `logged_ips` -- /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134484 Share on other sites More sharing options...
Pikachu2000 Posted November 15, 2010 Share Posted November 15, 2010 And just as I said, there's no column named `number` in that table. Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134488 Share on other sites More sharing options...
Jragon Posted November 15, 2010 Author Share Posted November 15, 2010 Good point, but still with the edit it just shows the ip and doent do anything Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134491 Share on other sites More sharing options...
PFMaBiSmAd Posted November 15, 2010 Share Posted November 15, 2010 Seriously Jragon, if you got an error message telling you there was no column named 'number', a person posted that you did not have a column named 'number', and your own table definition shows that there no column named 'number', just what level of help are you expecting from a forum? As to the question of what would an INSERT... ON DUPLICATE KEY UPDATE ... query look like - $query = "INSERT INTO logged_ips (ip_address, ip_visits) VALUES ('$ip',0) ON DUPLICATE KEY UPDATE SET ip_visits = ip_visits + 1"; mysql_query($query, $connection); Edit: The reason your queries don't do anything is because you don't add a value in sql using ++1 the way you are trying to do. Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134492 Share on other sites More sharing options...
Jragon Posted November 15, 2010 Author Share Posted November 15, 2010 I have updated the code It sort of works, but on the seccond try it breaks errror: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 and then in the database it say the id is 38 and the number of visits is allready 2???? code: <?php //finds out ip $ip = $_SERVER['REMOTE_ADDR']; //conects to the mysql server $connection = mysql_connect('localhost', 'root', ''); //sellects the database mysql_select_db('iplog', $connection); //looks for duplacute ips $dup = mysql_query("SELECT COUNT(ip_address) FROM logged_ips WHERE ip_address = '$ip'",$connection) or die(mysql_error()); $count = mysql_result($dup, 0); //checks to see if there is a duplecate name if ($count == 0){ //inserts the ip in to the database $string = "INSERT INTO `logged_ips` (`aid`, `ip_address`, `ip_visits`) VALUES ('', '$ip', '2')"; mysql_query($string, $connection) or die(mysql_error()); }else{ //adds a visit to the database $string2 = "UPDATE `logged_ips` SET `ip_visits` = '++1' WHERE `ip_address` = '$ip' LIMIT 0,1"; mysql_query($string2, $connection) or die(mysql_error()); } //outputs the ip echo $ip; ?> Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134509 Share on other sites More sharing options...
Pikachu2000 Posted November 15, 2010 Share Posted November 15, 2010 SQL syntax is + 1 to increment, not ++1 Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134516 Share on other sites More sharing options...
Jragon Posted November 15, 2010 Author Share Posted November 15, 2010 Smae error with updated code. Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134521 Share on other sites More sharing options...
Pikachu2000 Posted November 15, 2010 Share Posted November 15, 2010 Go ahead and post the updated code, but why are you not using the consolidated code provided by PFMaBiSmAd? Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134524 Share on other sites More sharing options...
Jragon Posted November 15, 2010 Author Share Posted November 15, 2010 <?php //finds out ip $ip = $_SERVER['REMOTE_ADDR']; //conects to the mysql server $connection = mysql_connect('localhost', 'root', ''); //sellects the database mysql_select_db('iplog', $connection); //looks for duplacute ips $query = "INSERT INTO logged_ips (ip_address, ip_visits) VALUES ('$ip',0) ON DUPLICATE KEY UPDATE SET ip_visits = ip_visits + 1"; mysql_query($query, $connection); echo $ip; ?> doesnt work D= Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134530 Share on other sites More sharing options...
PFMaBiSmAd Posted November 15, 2010 Share Posted November 15, 2010 Is ip_address defined as a unique key so that the ON DUPLICATE KEY logic has something to test? You also forget to write in your post what "doesn't work" means. Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134536 Share on other sites More sharing options...
Jragon Posted November 15, 2010 Author Share Posted November 15, 2010 It still wont work, all it does is out put the ip. Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134539 Share on other sites More sharing options...
Pikachu2000 Posted November 15, 2010 Share Posted November 15, 2010 Define "doesn't work". Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134540 Share on other sites More sharing options...
Jragon Posted November 15, 2010 Author Share Posted November 15, 2010 It does not put anything in to the database, but outputs the ip adress Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134541 Share on other sites More sharing options...
PFMaBiSmAd Posted November 15, 2010 Share Posted November 15, 2010 If you troubleshoot what it is doing, you will find that the 'SET' keyword is not used in that syntax. Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134555 Share on other sites More sharing options...
Jragon Posted November 15, 2010 Author Share Posted November 15, 2010 here is my new code: <?php /** * @author Jragon * @copyright 2010 */ include ("connect.php"); $referer = $_SERVER['HTTP_REFERER']; $user_agent = $_SERVER['HTTP_USER_AGENT']; $ip_adress = $_SERVER['REMOTE_ADDR']; $dup = mysql_query("SELECT `ip_address` FROM `logged_ips` WHERE `ip_address` = '$ip'", $connection) or die(mysql_error()); $count = mysql_num_rows($dup); if ($count == 0) { $string = "INSERT INTO `logged_ips` (`ip_address`, `ip_visits`, `user_agent`, `referer`) VALUES ('$ip_adress', '1', '$user_agent', '$referer')"; mysql_query($string, $connection) or die(mysql_error()); } else { $string2 = "UPDATE `logged_ips` SET `ip_visits` = `ip_visits` + '1' WHERE `ip_address` = '$ip_adress'"; mysql_query($string2, $connection) or die(mysql_error()); } echo $ip_adress ?> dumps: -- phpMyAdmin SQL Dump -- version 3.2.4 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Nov 15, 2010 at 05:15 PM -- Server version: 5.1.41 -- PHP Version: 5.3.1 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `iplog` -- -- -------------------------------------------------------- -- -- Table structure for table `logged_ips` -- CREATE TABLE IF NOT EXISTS `logged_ips` ( `aid` int(11) NOT NULL AUTO_INCREMENT, `ip_address` int(11) NOT NULL, `user_agent` varchar(244) NOT NULL, `referer` varchar(244) NOT NULL, `ip_visits` int(11) NOT NULL, PRIMARY KEY (`aid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=54 ; -- -- Dumping data for table `logged_ips` -- INSERT INTO `logged_ips` (`aid`, `ip_address`, `user_agent`, `referer`, `ip_visits`) VALUES (53, 1270, 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12 ( .NET CLR 3.5.30729)', '', 1), (52, 1270, 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12 ( .NET CLR 3.5.30729)', '', 1), (51, 1270, 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12 ( .NET CLR 3.5.30729)', '', 1); /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; It doesnt compleatly work, it outputs the ip fine, but doesnt puts something strange in the ip box, and it doesnt only put the ip up once =S Pelase help Jragon Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134573 Share on other sites More sharing options...
Jragon Posted November 15, 2010 Author Share Posted November 15, 2010 Anyone got an idea Link to comment https://forums.phpfreaks.com/topic/218734-checking-to-see-if-there-is-a-allready-that-result-in-mysql/#findComment-1134626 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.