PHP and MySQL -- Adding data

Setting up a Structured MySQL Database for Dynamic Sites

When a site moves beyond a static brochure, the underlying data layer must be organized. A relational database lets you store, retrieve, and manipulate content without repeating code for each product or article. In this step we’ll build a simple database named visitors and a table called visinfo that will hold visitor details. The table will have three columns: name, email, and city. Those fields are sufficient for a basic contact‑form example, but you can expand the schema to include timestamps, status flags, or foreign keys to other tables.

First, open your MySQL command‑line client or a tool like phpMyAdmin. Create the database with a single command:

The character set utf8mb4 supports a wide range of Unicode characters, which is important if you expect visitors from different locales. Once the database exists, switch to it:

Now create the visinfo table. The SQL statement below defines each column’s data type and constraints:

The id column serves as a unique identifier for each row. It is automatically generated by MySQL thanks to AUTO_INCREMENT. The created_at column captures when the record was inserted, which is handy for later reporting or cleanup scripts.

After running the statements, verify that the table was created correctly by querying its structure:

You should see a list of columns, their types, and any default values. If everything looks good, the database layer is ready. The next section focuses on capturing visitor data with a form that matches the table’s columns.

Crafting a Clean, User‑Friendly HTML Form

Once the database is set up, the next step is to create an HTML form that will gather visitor data. The form must match the columns defined in the visinfo table so that data can be inserted without type mismatches. A simple, responsive design makes the form approachable on any device.

Place the following code in a file called form.html. The form uses the POST method and points to save_it.php, the PHP script that will handle the insertion.

<label for="city">City:</label>

<input type="text" id="city" name="city">

<button type="submit">Submit</button>

</form>

<img src="https://www.murdok.org/images/64cb5044c3e7egif" alt="Form preview">

<img src="https://www.murdok.org/images/64cb5044c3e86gif" alt="Form submission example">

<img src="https://www.murdok.org/images/64cb5044c3e88gif" alt="Resulting data display">

</body>

</html>

The CSS keeps the layout minimal yet clean. Notice the required attribute on the name and email fields; this triggers browser‑side validation, reducing the chance of bad data reaching the server. The image tags at the bottom are optional; they can help illustrate how the form looks and how data appears after submission. Adjust the paths or remove the images if they are not needed.

When a visitor fills in the form and clicks Submit, the browser sends a POST request to save_it.php. That script will be responsible for validating the input, sanitizing it, and inserting a new row into visinfo. The next section walks through the PHP code that makes this happen.

Transmitting Form Data to MySQL via PHP

With the form and database ready, it’s time to write save_it.php. The script’s job is to read the POST variables, clean them up, and add a new record to the visinfo table. A straightforward, procedural approach keeps the example easy to follow, but you can extend it with object‑oriented practices or frameworks later.

Begin the file by establishing a connection to MySQL using mysqli. Store your credentials in variables for clarity:

$conn = new mysqli($host, $user, $pass, $dbname);

if ($conn->connect_error) {

die('Connection failed: ' . $conn->connect_error);

}

$conn->set_charset('utf8mb4');

// Retrieve and trim incoming data

$name = trim($_POST['name'] ?? '');

$email = trim($_POST['email'] ?? '');

$city = trim($_POST['city'] ?? '');

// Basic server‑side validation

if (empty($name) || empty($email)) {

echo 'Name and email are required.';

exit;

}

if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {

echo 'Invalid email format.';

exit;

}

// Use a prepared statement to guard against SQL injection

$stmt = $conn->prepare('INSERT INTO visinfo (name, email, city) VALUES (?, ?, ?)');

$stmt->bind_param('sss', $name, $email, $city);

if ($stmt->execute()) {

echo 'Thank you for submitting your details.';

} else {

echo 'Error: ' . $stmt->error;

}

$stmt->close();

$conn->close();

?>

Each step serves a clear purpose:

• Connection setup – the mysqli object connects to the database and ensures the character set matches the table.
• Data retrieval – the $_POST superglobal pulls the form values. The trim() function removes stray whitespace.
• Validation – before hitting the database, the script checks that required fields are present and that the email looks valid. Simple checks keep the example lightweight.
• Prepared statement – prepare() and bind_param() make the INSERT safe from injection. Even with simple data, this habit protects the database.
• Feedback – the script prints a friendly message. In a real application you might redirect the user or render a separate success page.

  After you place save_it.php on your server, reload form.html in a browser, fill in the fields, and hit Submit. If everything is configured correctly, the new row will appear in the visinfo table, and you’ll see the confirmation text on the screen. From here you can build more sophisticated features - displaying a list of all visitors, adding pagination, or connecting to a front‑end framework.

  Amrit Hallan is a freelance copywriter and website content writer. He also dabbles with PHP and HTML. For more tips and tricks in PHP, JavaScripting, XML, CSS designing and HTML, visit his blog at aboutwebdesigning.com.