Thread: MySql func.

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Kiev, Ukrain
    Posts
    88
    Rep Power
    2

    MySql func.


    Hello, i'm doing a function for checking the IP address :
    is it in base or not .
    here is the function for checking the address :
    PHP Code:
    function IPcheck ($ip){
            
    $mydb = @ new mysqli('localhost','root','','questiondb');
            
    //geting the ips from IPTable and puting them into result_set
            
    $result_set $mydb->query("SELECT ip FROM ipt");
            
    //comparing ip with ips from db
            
    while($row $result_set->fetch_assoc()){
                if (
    strcmp($row$ip)==0){
                    echo 
    "its TRUE</br>";
                    return 
    "TRUE";
                    break;
                } else {
                    echo 
    "its FALSE</br>";
                    return 
    "FALSE";
                    }
            }
            

    Here is the place where i'm inputing values in func. and checking the output:
    PHP Code:
    if(isset($_POST['n1']))
    {

        if( 
    IPcheck($userIP) == TRUE ){
        echo 
    "Hello baby";
        } else {
        echo 
    "Failed function";
        }
        

    That is how i'm getting the ip address :
    PHP Code:
    $userIP $_SERVER["REMOTE_ADDR"]; 
    I have 2 IP addresses in my data base : 127.0.0.1 and 176.181.193.125 . I'm inputing 127.0.0.1 to the function IPcheck (--IPcheck($userIP)--). So if my function is right (address from $userIP isequal to address from mydb) and it shpuld return TRUE and the probable output should be :
    it's TRUE
    Hello baby
    but actual output is :
    its FALSE
    Hello baby
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,986
    Rep Power
    9397
    "TRUE" and "FALSE" are both strings. They are not the same as true and false, which are both boolean values.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Kiev, Ukrain
    Posts
    88
    Rep Power
    2
    You are right . I put them in quotes and the output changed :
    Its False
    Failed function
    This means the function is wrong as i see ? maybe it's strcmp ?
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    you compare a string ($ip) with an array ($row). This cannot work.

    The approach in general is wrong. When you want to fetch a specific dataset (or check its existence), you make a query with a WHERE clause. Fetching the whole table in order to loop through it in PHP is a bad idea, because this is extremely inefficient. A database system can use indices to quickly find data, while PHP has to actually check each row one by one. And of course you'll end up sending around and storing large amounts of useless data.

    So always do things like that on database level. That's what a database system is made for:

    PHP Code:
    $ip_stmt $mydb->prepare('
        SELECT
            EXISTS(
                SELECT
                    1
                FROM
                    ipt
                WHERE
                    ip = ?
            )
    '
    );

    $ip_stmt->bind_param('s'$ip);
    $ip_stmt->execute();

    $ip_stmt->bind_result($ip_exists);
    $ip_stmt->fetch();

    if (
    $ip_exists)
        echo 
    'the IP exists';
    else
        echo 
    'the IP does not exists'
    requinix will tell you now that you don't need a prepared statement. Use it anyway. It's good practice to send all parameters through prepared statements, even if you consider them to be safe at the moment. Use the query() method only for constant queries without any input.

    When you distinguish between "safe" and "unsafe" values, there's a risk of getting it wrong and making a query vulnerable to SQL injections. Don't take that risk.

    Comments on this post

    • FAQer agrees
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,986
    Rep Power
    9397
    Originally Posted by Jacques1
    requinix will tell you now that you don't need a prepared statement. Use it anyway.
    When's the last time I told someone to not use prepared statements? I do believe that it's a waste to use them for one query but I understand that it's easier to tell the same story to everyone, especially when it makes their code easier and lets them focus on learning other aspects of PHP and databases.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Kiev, Ukrain
    Posts
    88
    Rep Power
    2
    Jacques1. Thank you very much . It works by your way. but could you comment each row please. I can't get a 100% of you thinking way. Especially this :
    PHP Code:
     $ip_stmt $mydb->prepare(
        SELECT 
            EXISTS( 
                SELECT 
                   1
                FROM 
                    ipt 
                WHERE 
                    ip = ? 
            ) 
        '
    ); 
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2009
    Location
    Nebraska, USA
    Posts
    867
    Rep Power
    275
    THIS should pretty much explain that.

    I had never seen that either, so, had to go research it..."google is your friend"
  14. #8
  15. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    I prefer using prepared statements for everything simply because it allows you to use bound parameters. When you never inject variables directly into a string, you can't forget to escape one.

    PHP Code:
      // This statement defines the query's SQL, but uses ? as a placeholder for the IP address instead of injecting the IP directly into the query SQL
      // Then the SQL query is passed to the database driver so it can be prepared (parsed)
      
    $ip_stmt $mydb->prepare('
        SELECT
            EXISTS(
                SELECT
                    1
                FROM
                    ipt
                WHERE
                    ip = ?
            )
    '
    );

    // This statement "binds" the variable $ip as a string - the 's' means as a string.
    // Because this is the first call to bind_param, it will bind to the first ? in the query
    $ip_stmt->bind_param('s'$ip);

    // This sends the query to the database and runs it
    $ip_stmt->execute();

    // This tells the database to "bind" the variable $ip_exists to the first column in the result
    // When you call fetch(), it will populate these variables will values from the database row
    // $ip_exists is bound to the first column because it's passed as the first argument to bind_result
    $ip_stmt->bind_result($ip_exists);

    // This retrieves a row from the database and populates any bound result variables
    $ip_stmt->fetch();

    // This checks to see whether the first column returned true or false
    if ($ip_exists)
        echo 
    'the IP exists';
    else
        echo 
    'the IP does not exists'
    However, I also don't like the way that Mysqli handles prepared statements. I think PDO's method is much clearer:
    PHP Code:
    // Define the query with a named parameter called visitors_ip
    // Note: the removal of SELECT EXISTS and the addition of LIMIT 1 are just a matter of style, it has nothing to do with the database driver
    $sql '
      SELECT
        1
      FROM ipt
      WHERE
        ip = :visitors_ip
      LIMIT 1
    '
    ;

    // Prepare the query
    $ip_stmt $pdo->prepare($sql);

    // Define an array of parameter values to use for replacing the parameters in the query
    // The array index lines up with the parameter name
    $params = array(
      
    'visitors_ip' => $ip// you have to define IP before this code
    );

    // Execute the query with the set of parameters defined in $params
    $ip_stmt->execute($params);

    // Retrieve the first result set
    $result_set $ip_stmt->fetch();

    // If a result was returned, then the IP exists
    if($result_set)
    {
      echo 
    'the IP exists';
    }
    else
    {
      echo 
    'the IP does not exists';
    }

    // Tell the database that you're done fetching results for this query
    $ip_stmt->closeCursor(); 
    Obviously you wouldn't be able to use my example though unless you change your database driver to PDO instead of Mysqli. I haven't actually used Mysqli before, so I'm not 100% sure it doesn't support this method of parameter binding, but a quick glance at the documentation made it look like it didn't.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Kiev, Ukrain
    Posts
    88
    Rep Power
    2
    Thank you very much . but i googled that already . and i can't get if 1 - is amount vars that taken at a time . so "$mydb->prepare" when this prepares my db it takes 1 row at a time or 1 vaule in a row .
    if 1 row : i need some more functions for splitting returned row into several values of columns.
    if 1 value : it's kinda scary at all .
    than here :
    WHERE ip = ?
    this means that i take the value from "1" row or it's a special setting for taking all the possible ip from ipt .
    But it's also possible that i'm asking an obvious things . I'm sorry for that . i'll read some more inf-on about php's syntax . thank you .
  18. #10
  19. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    Your questions are actually about SQL syntax, not PHP syntax. I recommend looking some some basic intro tutorials on MySQL.

    i can't get if 1 - is amount vars that taken at a time
    In the SQL query "SELECT 1", the "1" is literally the integer 1 - a constant value. It's telling MySQL that you want it to return the integer 1 in the result set for each. It has nothing to do with the number of fields. It is roughly equivalent to do this in PHP:
    PHP Code:
    $variable 1
    The reason it's used in this case is because you don't actually care what values are stored in the matching row, all you care about is whether or not there are matching rows. Telling MySQL to fetch "1" instead of an actual field value is far more efficient because MySQL doesn't need to look up the value of "1", obviously it already knows it because you gave it the value right in the query.

    When you write "SELECT 1", MySQL will return "1" for every matching row in the database. For example, if you have 500 rows that match the WHERE clause, then MySQL will return the integer "1" 500 times. If you have 0 matching rows, then MySQL will not return the integer "1" at all. This is why it works as a check of whether or not there are any present rows.

    The "1" in the "LIMIT 1" part of the query just tells MySQL that you want it to stop after returning 1 row. You only care if there are 0 rows or > 0 rows, you don't care about the difference between 1 row and 500 rows, so if MySQL finds any existing rows it can stop searching. Jacques1's use of EXISTS() does exactly the same thing.

    so "$mydb->prepare" when this prepares my db it takes 1 row at a time or 1 vaule in a row .
    prepare doesn't deal with rows or values at all. It just parses the text of the SQL query.

    if 1 row : i need some more functions for splitting returned row into several values of columns.
    Every time you call fetch() it advances to the next row of the result set (assuming there are more). If your result set contains more than one column, you need to pass more than one variable to bind_result. Alternatively, you can retrieve them as an array:
    PHP Code:
    $result $ip_stmt->get_result();
    while(
    $row $result->fetch_assoc()){} 
    WHERE ip = ?
    this means that i take the value from "1" row or it's a special setting for taking all the possible ip from ipt .
    The WHERE clause allows you to restrict the set of returned rows to only rows that match certain conditions (like the ip column being equal to a specific value).

    Doing filtering of rows in PHP (like you did in your initial attempt) is extremely slow and inefficient. You always want to return the smallest-possible dataset from the database when doing any queries. The database engine is extremely fast at filtering rows according to conditions.

    Comments on this post

    • FAQer agrees : This was very helpfull
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo