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

Jragon · 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

AbraCadaver · November 15, 2010

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

Pikachu2000 · November 15, 2010

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

Jragon · November 15, 2010

Thanks, i should of put some error checking stuff

my new error:

Unknown column 'number' in 'field list'

PFMaBiSmAd · November 15, 2010

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

Jragon · November 15, 2010

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

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

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

Pikachu2000 · November 15, 2010

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

Jragon · November 15, 2010

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

PFMaBiSmAd · 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.

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

Pikachu2000 · November 15, 2010

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

Jragon · November 15, 2010

Smae error with updated code.

Pikachu2000 · November 15, 2010

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

Jragon · 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=

PFMaBiSmAd · 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.

Jragon · November 15, 2010

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

Pikachu2000 · November 15, 2010

Define "doesn't work".

Jragon · November 15, 2010

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

PFMaBiSmAd · November 15, 2010

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

Jragon · 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

Jragon · November 15, 2010

Anyone got an idea

Sign In

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

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information