Can't figure out where is the mistake in my inset code to mysql table

Beginners - Learning PHP
#1

Hi all,
This is my first testing app in phpmyadmin with php code. But I am familiar with HTML ,CSS and Javascript. What I am facing now is the code of inserting is not working . However the connect to db code is working.

Thanks in advace
Here My Codes
1-connectToDB.php
Snippet

<?php $db_connect=mysqli_connect('localhost:3306 ','applesch','***','applesChatdb') or die ('Not Connected'); echo '<h2>DataBase Is Connected</h1>' ?>

2-TableCommands.php

<?php
   $dbhost = 'localhost:3306';
   $dbuser = 'applesch';
   $dbpass = '';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   $value1 = $_POST['fname'];
   $value1 = $_POST['lname'];
   $value1 = $_POST['email'];
   $sql = "INSERT INTO users(fname, lname,email,reg_date)
      values(' $value1', '$value1',' $value1','$date')";

   mysql_select_db('applesChatdb');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not enter data: ' . mysql_error());
   }
   
   echo "Entered data successfully\n";
   
   mysql_close($conn);
?>

3-Index.php

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title></title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/                      bootstrap/4.4.1/css/bootstrap.min.css">

    </head>
    <body>
<?php
   include "connectToDB.php";
?>
     <div style="background-color: lightblue;padding: 20px;  font-size: large; display: inline-block; border: 2px solid navy; ">
     <h1>User's Information</h1>

         <form action="TableCommands.php" method="post">
    <table>
    <tr>
    <td><label >First Name:</label></td>
    <td><input type="text" name="fname" /></td>

    </tr>
    <tr>
    <td><label >Last Name:</label></td> 
     <td><input type="text" name="lname" /></td>

    </tr>
         <tr>
    <td><label >Email:</label></td>
    <td><input type="text" name="email" /></td>

    </tr>
</table>
  <input type="submit" value="Submit">
</form>   
</div>
    </body>
</html>
#2

All of those mysql_* functions have been removed. You need to upgrade what you are using to access the database. PDO would be the best to look into.

1 Like
#3

Toss what you have and start here…
https://phpdelusions.net/pdo

1 Like
#4

Thanks very much for helpful reply :heart_eyes:.
I used mysqli instead as follow

<?php
$servername = "localhost:3306";
$username = "applesch";
$password = "**********";
$dbname = "applesChatdb";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
   $value1 = $_POST['fname'];
   $value2 = $_POST['lname'];
   $value3 = $_POST['email'];
   $date = date('Y-m-d');
$sql = "INSERT INTO users (firstname, lastname, email,reg_date)
values(' $value1', '$value2',' $value3','$date')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

One last thing
How to separate config script from this code?

Thanks again :blue_heart::blue_heart:

Admin Edit: Added Code TAgs

#5

You can go a few different ways. One is a Config class that you call for the values, another is to use a JSON, XML, or YAML file and just have it outside of the public root that you read in.

1 Like
#6

Nice, but how to separate the config code from this code to and put it into config.php?

#7

At a basic level, you create a file and require_once to bring it into the file that is needed.

1 Like
#8

The posted code has one serious problem and several other issues.

  1. You are adding an extra leading space to two of the field values (firstname and email.) This will result in problems searching for values and using these values later.
  2. By outputting the connection and query error onto a web page, you are only giving hackers useful information when they intentionally trigger errors (the connection error alone contains the database server hostname/ip, the connection username, and web server path information.) You should instead use exceptions for database statement errors and in most cases let php catch and handle the exception where it will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.)
  3. You are not detecting if a post method form has been submitted before running any of the form processing code. This will cause a bunch of php errors if the page is requested without there being any form data.
  4. The form processing code and the form are on separate pages. The creates a poor user-experience since you cannot easily display any error messages and re-populate the form fields when there is an error. Put these on the same page, with the form processing code above the start of the html document.
  5. There is no validation logic in the form processing code. For things like ‘required’ fields or fields that must have a specific format (email), this code will allow empty and wrong values to be inserted. (BTW - the html labels in the 1st posted code are not being used correctly.) You should validate all input data, storing validation error messages in an array, using the field name as the array index. You can then output the contents of this array at the appropriate point in the html document.
  6. You are putting external values directly into the sql query statement. This will allow sql special characters in the data to break the sql query syntax, which is how sql injection is accomplished. You should use a prepared query when supplying external values to an sql query.
  7. You should switch to the much simpler and more consistent PDO extension. This will make using prepared queries easy compared to the mysqli extension.
  8. Don’t copy variables to other variable without any reason. This is just a waste of typing. And in fact, you have an error in the 1st posted code, using the same variable name three times.
  9. You should use the same name for any particular value throughout your code. Your form field names and database column names should match. By using different names for the same meaning value, you are creating more work for yourself keeping track of the different names for the same thing.
  10. Php destroys all resources when the script ends, so, you don’t need to close the database connection, php will automatically do that for you.
2 Likes
Sponsor our Newsletter | Privacy Policy | Terms of Service