Select query might be a big inefficient

Databases
1

I have the following query that I’m using to fetch a user’s last IP address from the table “user” and I’m trying to compare it against a session IP. Is the following okay code or should this be condensed a bit more?


$query = "SELECT u_last_ip 
              FROM user 
              WHERE u_last_ip = 
              '$visitor[1]'";
$match = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($match)){
   $last_known_ip = $row[0];
}

echo $last_known_ip;

I guess the main reason I’m concerned here is because I’ve used an entire while control structure to extract only 1 value from that resource.

What are the more efficient alternatives for minor retrievals like this?

2

what about just


[COLOR=#000000][COLOR=#000000][COLOR=#0000bb]$query [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#dd0000]"SELECT u_last_ip  [/COLOR]
[COLOR=#dd0000]            FROM user  [/COLOR]
[COLOR=#dd0000]            WHERE u_last_ip =  [/COLOR]
[COLOR=#dd0000]            '$visitor[1]'"[/COLOR][COLOR=#007700]; [/COLOR]
[COLOR=#0000bb]$match [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000bb]mysql_query[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000bb]$query[/COLOR][COLOR=#007700]) or die([/COLOR][COLOR=#0000bb]mysql_error[/COLOR][COLOR=#007700]()); [/COLOR]
 
[COLOR=#0000bb]$row [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000bb]mysql_fetch_array[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000bb]$match[/COLOR][COLOR=#007700]) ;[/COLOR]
 
[COLOR=#007700]echo [/COLOR][COLOR=#0000bb]$row[0][/COLOR][COLOR=#007700];[/COLOR][/COLOR]
[/COLOR]

since the query retrieves only 1 row

3

I guess that will work, Kalon. I’m not really sure what I was hoping for. I kept looking at that while statement and seeing overkill in my mind.

Anyway, thanks. That should do it. :slight_smile:

4

yep, since you’re only retrieving 1 row you don’t need any kind of loop.

if you want to store the last ip in a variable you could do just

 
$query = "SELECT u_last_ip  
            FROM user  
            WHERE u_last_ip =  
            '$visitor[1]'"; 
$match = mysql_query($query) or die(mysql_error()); 

$row = mysql_fetch_array($match) ;
 
$last_ip = $row[0];

generally, if you know the number of iterations you would use a FOR loop. If the number of iterations is unknown you would use a WHILE.

5

this topic sounds a lot like a php topic, not a mysql topic

as far as pulling out users based on their IP, it’s conceivable that you’ll find IPs with more than one user on them – corporate offices, university campuses, coffee shop wifi networks, etc.

internet service providers also routinely re-assign IPs, so a different user from the same town could come in with the same IP that someone else used five minutes ago

also, regarding your query (since this is the mysql forum), if you have an IP value, then when you run this –

SELECT u_last_ip FROM user WHERE u_last_ip = '$visitor[1]'

you’re going to get back zero, or one, or multiple rows, but each row is going to contain what? just the same value you fed in to the query, but you won’t know which user(s) it was!!!

:slight_smile:

6

There’s other modifiers in the bigger condition, though. I’m also using a registration hash obtained from a verification e-mail as well as username(s) created during pre-registration. But you’re right about the PHP stuff of this thread. Sorry about that.