Link to home
Start Free TrialLog in
Avatar of jbrashear72
jbrashear72

asked on

Take two. =) - I need to create a function that ads an IP to a table and.....

Here are the requirements:
grab the IP and add it to a table.
Basically I have a table that looks like this:

CREATE TABLE IF NOT EXISTS `set1` (
  `set1id` int(11) NOT NULL auto_increment,
  `ip` varchar(16) NOT NULL,
  `group` varchar(2) NOT NULL,
  PRIMARY KEY  (`set1id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

There is a 2nd table called set2
This table has ip's pre loaded in it.
First I need to check that the current IP of the user is not in table 1 "set1" or table 2 "set2".
If the IP is in table set1 I want to return the group value from its record.
If the IP is in the set2 table return the group value from its record.

Now if the IP does not exist I need to ad the IP to the set1 table and set the group value.
Here is the thing I need to alternate  A and B in this field. So if the last record in the table had a group value of B then the next group value is A.


I need to grab the IP and insert it into this table
Avatar of jbrashear72
jbrashear72

ASKER

Avatar of hielo

function addIp($ip)
{
	mysql_connect("serveraddress","username","password") or die(mysql_error());
	mysql_select_db("yourdbnamehere") or die(mysql_error());
	$sql = "	SELECT `group`,'set1` as `table_source` FROM `set1` WHERE `ip` = '$ip'
			UNION
			SELECT `group`,'set2` as `table_source` FROM `set2` WHERE `ip` = '$ip'";
	$res1 = mysql_query($sql) or die(mysql_error());
	$activeGroup="";
	if( mysql_num_rows($res1) == 0 )
	{
		$res2 = mysql_query( "SELECT `group`  FROM `set1` ORDER BY `set1id` DESC LIMIT 0,1") or die(mysql_error());
		$row=mysql_fetch_assoc($res2);
		if( 'A'==strtoupper($row['group']) )
		{
			$activeGroup="B";
			mysql_query("INSERT INTO `set1`(`group`) values('B')") or die(mysql_error());
		}
		else
		{
			$activeGroup="A";
			mysql_query("INSERT INTO `set1`(`group`) values('B')") or die(mysql_error());
		}
	}
	else
	{
		$row = mysql_fetch_assoc($res1);
		$activeGroup=$row['group'];
	}
return $activeGroup;
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of hielo
hielo
Flag of Wallis and Futuna image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Im' getting this error:

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 '127.0.0.1' UNION SELECT `group' at line 1
<?php require_once('Connections/babyearth_ab2.php'); ?>
<?php
// starting the session
session_start();
 
 
$ip = (isset($_SERVER["HTTP_CLIENT_IP"]) ? $_SERVER["HTTP_CLIENT_IP"] : (isset($_SERVER["REMOTE_ADDR"]) ? $_SERVER["REMOTE_ADDR"] : "EMPTY") );
 
// GET THE TABLE NUMBER
$_SESSION["table"] = addIp($ip);
 
function addIp($ip)
 
{
        //mysql_connect("serveraddress","username","password") or die(mysql_error());
        //mysql_select_db("yourdbnamehere") or die(mysql_error());
        $sql = "        SELECT `group`,'set1` as `table_source` FROM `set1` WHERE `ip` = '$ip'
                        UNION
                        SELECT `group`,'set2` as `table_source` FROM `set2` WHERE `ip` = '$ip'";
        $res1 = mysql_query($sql) or die(mysql_error());
        $activeGroup="";
        if( mysql_num_rows($res1) == 0 )
        {
                $res2 = mysql_query( "SELECT `group`  FROM `set1` ORDER BY `set1id` DESC LIMIT 0,1") or die(mysql_error());
                $row=mysql_fetch_assoc($res2);
                if( 'A'==strtoupper($row['group']) )
                {
                        $activeGroup="B";
                        mysql_query("INSERT INTO `set1`(`group`,`ip`) values('B','$ip')") or die(mysql_error());
                }
                else
                {
                        $activeGroup="A";
                        mysql_query("INSERT INTO `set1`(`group`,`ip`) values('A','$ip')") or die(mysql_error());
                }
        }
        else
        {
                $row = mysql_fetch_assoc($res1);
                $activeGroup=$row['group'];
        }
return $activeGroup;
}
?>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
 
<body>
  <?php
echo "Table ID: ";  
echo $_SESSION["table"];
?>
 
</body>
</html>

Open in new window

Here is the database:
--
-- Table structure for table `set1`
--
 
CREATE TABLE IF NOT EXISTS `set1` (
  `set1id` int(11) NOT NULL auto_increment,
  `ip` varchar(16) NOT NULL,
  `group` varchar(2) NOT NULL,
  PRIMARY KEY  (`set1id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 
--
-- Dumping data for table `set1`
--
 
 
-- --------------------------------------------------------
 
--
-- Table structure for table `set2`
--
 
CREATE TABLE IF NOT EXISTS `set2` (
  `set3id` int(23) NOT NULL auto_increment,
  `ip` varchar(255) NOT NULL,
  `engine` varchar(35) default NULL,
  `group` varchar(2) NOT NULL default 'A',
  PRIMARY KEY  (`set3id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1581 ;
 
Here is the Connection File:
<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_babyearth_ab = "localhost";
$database_babyearth_ab = "ab_testing2";
$username_babyearth_ab = "root";
$password_babyearth_ab = "yeshua2k";
$babyearth_ab = mysql_pconnect($hostname_babyearth_ab, $username_babyearth_ab, $password_babyearth_ab) or trigger_error(mysql_error(),E_USER_ERROR); 
?>

Open in new window

put parenthesis around the selects:

        $sql = "        (SELECT `group`,'set1` as `table_source` FROM `set1` WHERE `ip` = '$ip')
                        UNION
                        (SELECT `group`,'set2` as `table_source` FROM `set2` WHERE `ip` = '$ip')";
I copied that in and got this error:
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 '127.0.0.1') UNION (SELECT `gro' at line 1

<?php //require_once('Connections/babyearth_ab2.php'); ?>
<?php
// starting the session
session_start();
 
 
$ip = (isset($_SERVER["HTTP_CLIENT_IP"]) ? $_SERVER["HTTP_CLIENT_IP"] : (isset($_SERVER["REMOTE_ADDR"]) ? $_SERVER["REMOTE_ADDR"] : "EMPTY") );
 
// GET THE TABLE NUMBER
$_SESSION["table"] = addIp($ip);
 
function addIp($ip)
{
        mysql_connect("localhost","root","yeshua2k") or die(mysql_error());
        mysql_select_db("ab_testing2") or die(mysql_error());
       $sql = "        (SELECT `group`,'set1` as `table_source` FROM `set1` WHERE `ip` = '$ip')
                        UNION
                        (SELECT `group`,'set2` as `table_source` FROM `set2` WHERE `ip` = '$ip')";
        $res1 = mysql_query($sql) or die(mysql_error());
        $activeGroup="";
        if( mysql_num_rows($res1) == 0 )
        {
                $res2 = mysql_query( "SELECT `group`  FROM `set1` ORDER BY `set1id` DESC LIMIT 0,1") or die(mysql_error());
                $row=mysql_fetch_assoc($res2);
                if( 'A'==strtoupper($row['group']) )
                {
                        $activeGroup="B";
                        mysql_query("INSERT INTO `set1`(`group`,`ip`) values('B','$ip')") or die(mysql_error());
                }
                else
                {
                        $activeGroup="A";
                        mysql_query("INSERT INTO `set1`(`group`,`ip`) values('A','$ip')") or die(mysql_error());
                }
        }
        else
        {
                $row = mysql_fetch_assoc($res1);
                $activeGroup=$row['group'];
        }
return $activeGroup;
}
 
?>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
 
<body>
  <?php
echo "Table ID: ";  
// echo $_SESSION["table"];
?>
 
</body>
</html>

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That did it!