How to store users IP Address in database using mysqli and php when button is clicked

Question

I have created a login form where a user must enter a username and password, once they click the submit button I want to store their IP Address into a mysql database that I have created.

I've tried the following, but I haven't had any luck, could someone please help? Thanks.

Login Form:

<!DOCTYPE html>
<html>
<style>


/* Header/Logo Title */
.header {
 padding: 2px;
 text-align: center;
 background: rgb(49, 48, 48);
 color: white;
 font-size: 15px;

}

form {

    border: 5px solid #f1f1f1;
    background-color: white;
    font-family: Verdana, Geneva, Tahoma, sans-serif;
    text-align: center;
   
}

input[type=text],
input[type=password] {
    width: 40%;
  
    padding: 12px 20px;
    margin: 8px 0;
    display:block;
    border: 1px solid #ccc;
    box-sizing: border-box;       
}
 /*style buttons*/

body{
background-color: rgb(180, 46, 46);
}

button {
    background-color: #265ec5;
    color: white;
    padding: 14px 20px;
    margin: 8px 0;
    border: none;
    cursor: pointer;
    width: 10%;
 }
 /* set a hover effect for the button*/

button:hover {
    opacity: 0.8;
}

.imgcontainer {
    padding: 20px;
    text-align: center;
    
}



/*set padding to the container*/

.container {
    padding: 16px;

}
/*set the forgot password text*/

span.psw {
    float: center;
    padding-top: 16px;
}

</style>

<body>


<!HTML-->

<div class="header">
    <h2>
        <img src="vs/banner.png"> 
    </h2>

  </div>

  <form action="test.php" method="POST">
    <div class="imgcontainer">
        <img src=
         "data:image/png;base64,"base64code" width="250" height = "70"
            alt="Avatar" class="avatar">
    </div>
    <h2 style="text-align:center;" > Sign In  </h2>
   
    <div class="container">
     
        <label><b> Username</b></label>
        <input type="text" name="user_name" required placeholder="username@email.co.uk"
        oninvalid="this.setCustomValidity('Enter a valid email address, phone number or Skype name')"
        oninput="this.setCustomValidity('')"
        
        />
     

        <label><b>Enter password</b></label>
        <input type="password" placeholder="Enter Password" name="user_password" required place>
     
        <input type="submit" value = "Login">
        <label>
        <input type="checkbox" checked="checked"> Remember me
    </label>

    </div>

    <div class="container" style="background-color:#f1f1f1">
        
        <span class="psw">Forgot <a href="#">password?</a></span>
        <p></p>
        <span class="usn_info">You must login using the correct email</span>
    </div>

</form>

php file named test.php:

$mysqli = new mysqli( 'localhost', 'admin', 'pass', 'ip_add' );

// Check  connection
if ( $mysqli->connect_error ) {
die( 'Kan niet verbinden met database. Probeer het later opnieuw. ' . 
$mysqli->connect_errno . ': ' . $mysqli->connect_error );
}

// Insert data

$sql = "INSERT INTO details (ip_add) VALUES ('{$mysqli- 
>real_escape_string($_SERVER['REMOTE_ADDR'])}'";

 $insert = $mysqli->query($sql);

if ( $insert ) {
echo "U bent succesvol ingeschreven! U heeft bootnummer: {$mysqli- 
>insert_id}. Op de naam: . U krijgt hiervan nog een bevesteging op uw 
mail, uw bootnummer kan nog veranderen";
} else {
die("Error: {$mysqli->errno} : {$mysqli->error}");
}


$mysqli->close();
}

?>

My database is called details and has a table column call ip_add which uses VARCHAR(255). Whenever I submit details on the login form, nothing appears in my table for my database.

If you'd enabled PHP error reporting I'm pretty sure you'd have a load of errors (about the `mysql_` functions I expect) which would give you a clue about why it's not working as you expected. Although, maybe you did and just didn't tell us the error? `I haven't had any luck` isn't a useful description of a problem, it conveys no information about what you're actually experiencing. — ADyson, May 18 '22 at 18:01
BTW you can't convert from mysql_ to mysqli_ simply by adding `i` everywhere...there are some differences in the API. In your case, for example, `mysqli_error()` expects you to pass `$conn` into it as an argument. Consult a proper guide and/or the PHP manual! — ADyson, May 18 '22 at 18:03
Although having said that, you really need to read [mysqli or die, does it have to die?](https://stackoverflow.com/questions/15318368/mysqli-or-die-does-it-have-to-die) (tldr: no, because you can achieve the same error handling functionality with one line of code to enable mysqli to thow exceptions, instead of adding repetitive error handling code after every single command). — ADyson, May 18 '22 at 18:04
Next you'll no doubt find there's a SQL syntax error because you forgot to single-quote the IP address string within the query. BUT, having said that, you should be using prepared statements and parameters for all queries where you combine SQL text with PHP variables. If you learnt your current technique from a tutorial or book or something, please don't use it again. It can also leave you vulnerable to SQL injection attacks in many cases, which is a serious risk. — ADyson, May 18 '22 at 18:14
https://phpdelusions.net/mysqli contains good, simple examples of writing safe, reliable SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . — ADyson, May 18 '22 at 18:14
I've edited the script and it's now giving me an error of Error: 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1, but I can't work out what it is — McCloud54, May 18 '22 at 21:34
Ok. Unless you share the changes with us, we're going to struggle too... :-). You can [edit] your question, rather than dumping code in the comments area. — ADyson, May 18 '22 at 21:36
Ok thanks. I can see you didn't really read the links I provided, then.... `$stmt = $mysqli->prepare("INSERT INTO details (ip_add) VALUES (?)"); $stmt->bind_param("s", $_SERVER['REMOTE_ADDR']); $stmt->execute();` is what you should have written. This is correctly using prepared statements so that you don't have to worry about quote marks, or use the obsolete mysqli_real_escape_string function. — ADyson, May 18 '22 at 21:40
(P.S. Your attempt was also broken primarily because you forgot the closing `)` at the end of the SQL.) — ADyson, May 18 '22 at 22:07
It looks like it must be actually your later `if ( $insert ) {` line which throws that, not my code. You can change my last line to `$insert = $stmt->execute();` if you really want. But I didn't put a return value from the execute() in the sample because that's really an anti-pattern...instead of checking the result of every single command, you can just read [mysqli or die, does it have to die?](https://stackoverflow.com/questions/15318368/mysqli-or-die-does-it-have-to-die) as I suggested earlier, and implement the far saner and less verbose way to handle sql errors shown there. — ADyson, May 19 '22 at 12:21

How to store users IP Address in database using mysqli and php when button is clicked

0 Answers0