checking to see if there is a allready that result in mysql

[Jragon]

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

[AbraCadaver]

$dup = mysql_query("SELECT COUNT(number) FROM logged_ips WHERE ip_address = '$ip'",$connection)
       or die(mysql_error());

[Pikachu2000]

Your query is failing and returning a boolean FALSE to mysql_result(). Echo the query string, and echo mysql_error().

[Jragon]

Thanks, i should of put some error checking stuff

 

my new error:

Unknown column 'number' in 'field list'

[PFMaBiSmAd]

You can replace all three of those queries with a simple - INSERT ... ON DUPLICATE KEY UPDATE ... query.

 

[Jragon]

Could you put that in to a bit of code please =P

[Pikachu2000]

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'"

[Jragon]

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 */;

[Pikachu2000]

And just as I said, there's no column named `number` in that table.

[Jragon]

Good point, but still with the edit it just shows the ip and doent do anything

 

[PFMaBiSmAd]

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.

[Jragon]

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;
?>

[Pikachu2000]

SQL syntax is + 1 to increment, not ++1

[Jragon]

Smae error with updated code.

[Pikachu2000]

Go ahead and post the updated code, but why are you not using the consolidated code provided by PFMaBiSmAd?

[Jragon]

<?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=

[PFMaBiSmAd]

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.

[Jragon]

It still wont work, all it does is out put the ip.

[Pikachu2000]

Define "doesn't work".

[Jragon]

It does not put anything in to the database, but outputs the ip adress

[PFMaBiSmAd]

If you troubleshoot what it is doing, you will find that the 'SET' keyword is not used in that syntax.

[Jragon]

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

[Jragon]

Anyone got an idea