Storing IP into MySQL DB?

[CitizenSmif]

Hi,

I have an array that has an IP address in index 2 and a port in index 3 which I'm trying to insert into a DB, I'm using the following code:

$sqlcon = mysql_connect("localhost", "root", "password");
mysql_select_db("mydb", $sqlcon);

foreach ($result as $val) {
    echo "$val[2]:$val[3]<br />";

$sql = "INSERT INTO mytable (IP, PORT) VALUES($val[2], $val[3])";
mysql_query($sql, $sqlcon) or die(mysql_error());
}
mysql_close($sqlcon)

This will echo the first IP address and port and then returns an error with MySQL saying

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 '.185.112, 8080)' at line 1

The numbers being the second half of the IP and the port.. When I try entering the [3] data into the port column only it goes through OK, but when trying the IP it gives the same error so I'm assuming this has something to do with the periods. The datatype for the IP column is char(15) and the port is an integer.

Does anyone know where I'm going wrong?

 

[sleepylee]

You need quotes around $val[2] when you try to insert it.

 

[jryan21]

If you get new errors after you add the quotes, you may also need to put backticks around the table and field names.

 

[CitizenSmif]

Yeah adding the quotation marks worked, thanks.

Heres a proper question, is there any way to easily remove duplicate entries from a MySQL table?

 

[sleepylee]

remove duplicates from mysql table - Google Search

 

[jryan21]

Heres a proper question, is there any way to easily remove duplicate entries from a MySQL table?

What I usually do is create a new empty table based on the old one, except make an index UNIQUE on the column in question, then use INSERT IGNORE statements to insert the data.

 

[Grant29]

If you never want duplicates the UNIQUE index is the way to go. Be sure to use the INSERT IGNORE to prevent any errors especially if you are doing multi-row inserts.

 

[CitizenSmif]

Thanks. Jryan, I tried your way but I couldn't seem to work out when to make the new index, the code I was using is

$query1 = "CREATE TABLE iptemp AS
SELECT * FROM ipinfo WHERE 1";
$query2 = "DROP TABLE ipinfo";
$query3 = "RENAME TABLE iptemp TO ipinfo";

and then obviously executing them, but in $query1 I've created the table and copied everything across so when I would try to create a unique index it would complain about duplicate entrys. Also, is there a better way to execute all queries at once rather than individually? These are my first efforts with doing MySQL for myself instead of working with pre-written scripts, it looks easy when looking at the syntax but its actually rather daunting..

What I did was completely dropped and recreated the table, adding a unique index from the beginning and using Grants advice to INSERT IGNORE. All seems to be OK so far. Thanks

 

[kblessinggr]

Or you could simply do this and avoid the whole escape character headache...

$sql = "INSERT INTO mytable (IP, PORT) VALUES('".$val[2]."', ".$val[3].")";

By the way I did not see where you mentioned the type of field IP and PORT is in the database...