How to compare a value with ALL rows?

Anon

I wanted to write a php/mysql script which checks the visitor's ip and the time when he visited the site and then increased the counter by 1, but only if ip is not the same. counter is in one table and is updated, the ip and time in another.all ips and times in the table are reset after a certain amount of time specified by the user. I got, however a problem, because when I compare ip and time of the visitor with all ips and times already in the table, the script compares only the first (or the last if order by desc) row, but not all. here's the problematic potion of the script, just the ip part:
$db = mysql_connect("localhost", "root", "");
$sqlab10 = "select ipnumber"; //row
$sqlab10 .= " from ipsucker";//table

mysql_select_db("mysqlcounter");
$res10 = mysql_query($sqlab10);
$num10 = mysql_num_rows($res10);

if ($num10==0)

echo "no fitting rows found";

for ($i=0; $i<$num10; $i++)
{
$ip1 = mysql_result($res10, $i, "ipnumber"); //$ip1 = all ip numbers in the database

echo "$ip1<br>";

}

now the echo "$ip1 "; above returns all ips in the mysql database. but if later in the script I use the if statement:

if (ip==$ip1) {
echo "ip is the same as ip1, counter value remains the same";
}
the script compares the ip of the visitor only with one of the rows, so even if it is ip==ip1 in some of the rows but the one which the script recognises, the counter is still increased, because it then follows the statement
if (ip!==$ip1) {
not recognising that ip is already in the database.

Please tell me how to do that ALL ip (and time) values in the mysql table are compared to the new ip and time value of the visitor.

lightning

There is a much easier and effienct way to do what your doing. Review this sample code.

<?php
// connect to database
$connection = mysql_connect("localhost", "root", "");

// select database to work with
mysql_select_db("mysqlcounter");

/ =========================================
search table for IP Address using the WHERE clause
By using the WHERE, it will check all rows for $ipnumber
========================================= /

$sql = "SELECT ipnumber FROM ipsucker WHERE ipnumber='$ip'";
$data = mysql_query($sql);

/ =========================================
If there are no matches there will be no rows, I'll first find
out how many rows there are and then test to see if there are no rows.
========================================= /

$numrows = mysql_num_rows($data);

// if this is true, this is a new IP, a new visitor.
if ($numrows==0) {
/
Here you would add the code to increase the hit count by 1.
/
}
mysql_close($connection);
?>

Anon

Thank you very much, here's the script, it works without problems. I'll post it here, in case somebody wants to use it:

<?php
###################################################################

PHP MySQL Counter 1.0 (with IP & Time Checker plus Backup):

Description:

Every IP is counted only once for a specified amount of time; after

the time is up, all ips and times in the MySQL database are backed

up into a *.txt file with the exact date as the file name and then

the table is deleted and recreated.

Works in Frame, IFrame & (I)Layer, although I am not so sure about

the (i)layer thing; it's a dying species anyway. If you don't know

what it is, go and learn HTML before you play with PHP & MySQL.

Also can be included in an "include" statement:

include('CounterName.php');

The following code looks like @!#$, I know, but don't care as long

as it works perfectly (and it does).

ADMIN WRFan

Created by WRFan

Email: PhoenixWR@hotmail.com

Web: http://localhost:81 hehehe

#
#####################################################################
#

COPYRIGHT NOTICE:

NO Copyright 2002 by WRFan. NO Rights Reserved, hehe

Freely distributable.

Selling the code without prior written consent is forbidden; besides,

that would be kind of lame. In all cases copyright and header must

remain intact.

#
######################################################################

//////////////////////////////////////////////////////////////////////////////////////////// I) If first visitor...:

function firs(){

	////////////////////////////////////////////////////////////////////////////////////////a couple of needed variables

$timeout='240';
$heute = time();
$sucker=(date ("r"));

if (getenv ("HTTP_X_FORWARDED_FOR")) //IP includer for servers behind firewalls, like www.f2s.com
{
$ip = getenv ("HTTP_X_FORWARDED_FOR");

}
else
{
$ip = getenv ("REMOTE_ADDR");

}

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

$db = mysql_connect("localhost", "root", ""); //connecting to MySQL database, server, username, password

		$condatabase = mysql_select_db("mysqlcounter", $db); //which database?

////////////////////////////////////////////////////////////////////////////////////////

@mysql_create_db ("mysqlcounter", $db); //creating database

mysql_select_db("mysqlcounter", $db); //selecting database

$sql_query = "CREATE TABLE " . 'counter' . " (count CHAR(255))"; //creating table counter for counter number
$sql_query2 = "CREATE TABLE " .'ipsucker' . " (ipnumber CHAR(255), tim CHAR(255))"; //creating database for ip numbers & time

@($sql_query); //executing the above code
@($sql_query2); //executing the above code

$sqlab1 = "insert counter"; //creating counter value for the first time (first visitor)

$sqlab1 .= "(count) values ";

$sqlab1 .= "('0')";
mysql_select_db("mysqlcounter");
mysql_query($sqlab1);

$sqlab20 = "insert ipsucker"; //inserting ip of the first visitor

$sqlab20 .= "(ipnumber, tim) values ";

$sqlab20 .= "('$ip', '$heute')";
mysql_select_db("mysqlcounter");
mysql_query($sqlab20);

// update counter:

$sqlab = "update counter set count = count + 1"; //counter 0+1=1

mysql_select_db("mysqlcounter");
mysql_query($sqlab);
mysql_select_db("mysqlcounter");
$res = mysql_query("select * from counter");

 $zahl = mysql_result($res, "count");

$num = mysql_affected_rows(); //rows affected (altered)?
echo " Welcome, first visitor ever, the counter changed to $zahl";

}

////////////////////////////////////////////////////////

////////////////////////////////////////////////////////////////////////////////////if not first visitor, but 1+xth visitor:
function secon(){
////////////////////////////////////////////////////////////////////////////////////////a couple of needed variables

if (getenv ("HTTP_X_FORWARDED_FOR"))
{
$ip = getenv ("HTTP_X_FORWARDED_FOR");

}
else
{
$ip = getenv ("REMOTE_ADDR");

}
////////////////////////////////////////////////////////////////////////////////////////more needed variables
$heute = time();
$timeout='240';
$sucker=(date ("r")); // I really can't think of good names for my own variables, hehe
////////////////////////////////////////////////////////

/////////////////////////////////////////////////////////////////////////////////another dozen of needed variables:

////////////////////////////////////////////////////////////////////////////////ip checking variable

$db = mysql_connect("localhost", "root", "");
$sqlab = "select ipnumber from ipsucker";
$sqlab .= " where ipnumber like '$ip' order by ipnumber asc";

mysql_select_db("mysqlcounter");

$result = mysql_query($sqlab);
#if ($result) { // old code, remains here for sentimental reasons only. worth a look if you are a newbie (uncomment the following lines too)
$number = mysql_num_rows($result);

#echo "<table border=1>";

#while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { //this is an array. kill me if I know what it is, I just saw it in some php book. anyway, it works

if ($ip==$row["ipnumber"])

{

mysql_select_db("mysqlcounter");

#$resa18 = mysql_query("select count from counter");

$zahla18 = mysql_result($resa18, "count");

#}
#elseif ($ip!==$row["ipnumber"])

#{

#}
#echo "<tr><td>";

#echo implode("</td><td>",$row);
#echo "</td></tr>\n";

#}
#echo "</table>";
#}

////////////////////////////////////////////////////////////////////////////////time checking variable:

$sqlab77 = "select tim from ipsucker";
$sqlab77 .= " where tim+3600*$timeout < '$heute' order by tim asc"; //time to delete ip numbers, the specified number of hours is over. PHP counts in seconds (since the beginning of the Unix age in 1970 or something like that, I am not sure. so 3600 seconds (an hour) x the $timeout time specified in the script (default is 240).

mysql_select_db("mysqlcounter");

$result77 = mysql_query($sqlab77);
$number77 = mysql_num_rows($result77);

/////////////////////////////all needed variables specified, from here on using the variables specified above (hell of work, hm?!)

if (($number<1) && ($number77<1)) ////////////// 1st possibility: new visitor and still time to go before ips are deleted
{

$sqlaba9 = "update counter set count = count + 1"; //updating (counter value +1)

mysql_select_db("mysqlcounter");
mysql_query($sqlaba9);

$sqlab29 = "insert ipsucker"; //inserting ip of the new visitor

$sqlab29 .= "(ipnumber, tim) values ";

$sqlab29 .= "('$ip', '$heute')";
mysql_select_db("mysqlcounter");
mysql_query($sqlab29);

mysql_select_db("mysqlcounter");
$res78 = mysql_query("select * from counter");

 $zahl78 = mysql_result($res78, "count");

echo " Welcome, you made the counter change to $zahl78 ";

}

elseif (($number>=1) && ($number77<1)) // 2nd possibility: ip is already in the database, but there's still time to go till deletion
{

mysql_select_db("mysqlcounter");
$res99 = mysql_query("select * from counter");

 $zahl99 = mysql_result($res99, "count");

echo " Hi, dear visitor, you have been here before, therefore the counter value remains the same: $zahl99";
#echo "still time before deletion";

}

elseif (($number<1) && ($number77>=1)) // 3rd possibility: New visitor, but time is up, so deleting the whole table and recreating it
{

//////////////////////////////////////////////////////////////// backing up all old ips & times in file before deleting from database

#$time_in_years=$heute / 3600 /24 /30 /12;
$vlad=(date("l_dS_of_F_Y_h_i_s_A")); // time - name for the backup file

$db = mysql_connect();
mysql_select_db("mysqlcounter");
$res = mysql_query("select * from ipsucker");
$num = mysql_num_rows($res);

echo "$num Datensätze gefunden ";

for ($i=0; $i<$num; $i++)
{

    $ip1 = mysql_result($res, $i, "ipnumber"); 
   $timmy = mysql_result($res, $i, "tim"); 
#echo "<br>$ip1</br>";
$fpss=fopen("$vlad.txt", "a+");  // here we go: backing up all old ips and times from the table

fputs ($fpss,$ip1.","."$timmy"."\n");
fclose($fpss);
}
//////////////////////////////////////////////////////////////// backup in file end

$stra21 = "DROP TABLE ipsucker";
$resulta21 = mysql_query($stra21);

$sql_query4 = "CREATE TABLE " . 'ipsucker' . " (ipnumber CHAR(255), tim CHAR(255))"; //creating database for ip numbers

@($sql_query4);

$sqlaba11 = "insert ipsucker"; //inserting ip of the first visitor

$sqlaba11 .= "(ipnumber, tim) values ";

$sqlaba11 .= "('$ip', '$heute')";
mysql_select_db("mysqlcounter");
mysql_query($sqlaba11);

echo " Great, you are the first visitor to this website (for the next $timeout hours), on $sucker";

///////ip & time of the first visitor after deletion of the database:

$sqlaba9 = "update counter set count = count + 1"; //updating (counter value +1)

mysql_select_db("mysqlcounter");
mysql_query($sqlaba9);

mysql_select_db("mysqlcounter");
$resa10 = mysql_query("select * from counter");

 $zahla10 = mysql_result($resa10, "count");

echo " You made the counter change to $zahla10";

}

elseif (($number>=1) && ($number77>=1)) // 4th possibility: Old visitor, and time is up, so deleting the whole table and recreating it:

{

//////////////////////////////////////////////////////////////// backing up all old ips & times in file before deleting from database

#$zeit_in_jahren=$heute / 3600 /24 /30 /12;
$vlad=(date("l_dS_of_F_Y_h_i_s_A"));

$db = mysql_connect();
mysql_select_db("mysqlcounter");
$res = mysql_query("select * from ipsucker");
$num = mysql_num_rows($res);

echo "$num Datensätze gefunden ";

for ($i=0; $i<$num; $i++)
{

    $ip1 = mysql_result($res, $i, "ipnumber"); 
   $timmy = mysql_result($res, $i, "tim"); 
#echo "<br>$ip1</br>";
$fpss=fopen("$vlad.txt", "a+");

fputs ($fpss,$ip1.","."$timmy"."\n");
fclose($fpss);
}
//////////////////////////////////////////////////////////////// backup in file end

mysql_select_db("mysqlcounter");

$stra15 = "DROP TABLE ipsucker"; //deletion of old ips and times
$resulta15 = mysql_query($stra15);

$sql_query4a15 = "CREATE TABLE " . 'ipsucker' . " (ipnumber CHAR(255), tim CHAR(255))"; // re-creating database for ip numbers

@($sql_query4a15);

$sqlaba16 = "insert ipsucker"; //inserting ip of the first visitor

$sqlaba16 .= "(ipnumber, tim) values ";

$sqlaba16 .= "('$ip', '$heute')";
mysql_select_db("mysqlcounter");
mysql_query($sqlaba16);

$resa17 = mysql_query("select count from counter");
$zahla17 = mysql_result($resa17, "count");

echo " Hi, dear visitor, you have been here before, therefore the counter value remains the same: $zahla17";
echo " Great, buddy , you are the first visitor to this website (for the next $timeout hours), on $sucker";

}

//////////////////////////////////////////////////////////////////////////
} //end of function secon
////////////////////////////////////////////////////////////////////////// Executing the functions below:

$db = mysql_connect("localhost", "root", ""); //connecting to MySQL database, server, username, password

		$condatabase = mysql_select_db("mysqlcounter", $db); //which database?

////////////////////////////////////////////////////////////////////////////////////////
if (!$condatabase) { //if database not created yet:
firs();

}

else { // if database already exists:

secon();

}
mysql_close($db); //bye bye, MySQL