SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Select query might be a big inefficient...

    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?
    PHP Code:
    $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. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what about just

    Code:
    $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) ;
     
    echo $row[0];
    
    since the query retrieves only 1 row

  3. #3
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
     
    $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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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 --
    Code:
    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!!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard Wolf_22's Avatar
    Join Date
    Jul 2005
    Posts
    1,711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •