2

Is this even possible, does my title of this question makes sense?

I am struggling and trying to figure out the way to get all from database table, but I need to run a function wether SQL or PHP.

So real short example would be:

SELECT * FROM `table` WHERE `ip` = '$this->ip'

However I am using INET_ATON() to store the IP and INET_NTOA() to retrieve it back. I could've also use PHP's function ip2long() and long2ip(), but still I don't know how would I accomplish such a thing using the same query?

SELECT `id`, INET_NTOA(`ip`) as `ip`, `points`
FROM `table` WHERE `ip` = INET_ATON('$this->ip')

Those are all table columns defined manually to get to the point of what I need to do exactly. But... For this simple project it seems alright to do it this way, but what if I had more columns and only some of them or one of them require some converting?

So how can I accomplish something like... (I know that this is invalid)

SELECT * FROM `table` WHERE `ip` = '$this->ip` BUT INET_NTOA(`ip`) as `ip`

Beside this question, I also wonder is INET_ATON() & INET_NTOA() only MySQL function or SQL function. Because I am planning to rewrite my project to use PDO instead of MySQLi and I am unsure wether those functions will work or I should rely on PHP's built-in same functionality.

dvlden
  • 2,402
  • 8
  • 38
  • 61
  • Why do you want to do apply the function every time you do a select query? Can't you store the calculated thing in DB in the same column instead of IP or in a different column? – Sony Mathew Oct 20 '15 at 14:13
  • @ynos1234 – Can you describe it to me? I am using that query only once for each `visitor`. I read on one of Stackoverflow's questions – that it is much better to store `IP` as `INT UNSIGNED` using this `INET_ATON` function, than without converting IP and storing it within `VARCHAR(15)`. So I am trying to follow best practises right? – dvlden Oct 20 '15 at 14:19
  • I think `INET_ATON() & INET_NTOA()` is only in mysql. And if you want that in SQL you should be using SQL functions : http://stackoverflow.com/questions/1270855/what-is-the-sql-server-equivalent-of-inet-aton . By storing the IP as `UNSIGNED INT` rather than `VARCHAR`, you are doing the right thing. For converting back the IPs already stored using `INET_ATON`, you can either use `INET_NTOA` or PHP's `long2ip()` method. Now only I understood your usecase, so I think storing the same thing in different forms in 2 columns is not a good idea unless otherwise it is performance critical. – Sony Mathew Oct 20 '15 at 14:35
  • @ynos1234 I googled some more about those `MySQL` functions and they mostlikely are not going to work elsewhere (e.g. SQLite), so I'll need to use PHP's functions. About the storing the IP, thanks for confirming that to me... Although I don't understand why you mention two columns and two different forms. I never mention storing IP in two different forms. You just confused me... – dvlden Oct 20 '15 at 14:44
  • You could try to use [Views](https://dev.mysql.com/doc/refman/5.0/en/views.html), see as well [performance issues with views](http://stackoverflow.com/questions/10302615/mysql-views-performance). Views are supported in [SQLite](https://www.sqlite.org/lang_createview.html) as well and you would be able to somewhat "hide" the functions from the actual code/query. – Jan Oct 20 '15 at 15:22

1 Answers1

0

INET_ATON() & INET_NTOA() // ALL THESE ARE DEPRECATED! Use inet_pton() or inet_ntop() instead!! The reason for that is they do not work for IPv6 addresses and with more of the internet using IPv6 and not IPv4.

They is both a PHP and an SQL function with the same name convert an IP address into a string and back again.

kayleighsdaddy
  • 670
  • 5
  • 15