How to create a new database using php?

Submitted 5 months ago
Ticket #418
Views 87
Language Other
Priority Medium
Status Closed

I am new to php, and I am learning it so to store the information of the user in database ,we have to create it .....

How we have to create a database in php.....

How many ways are there?

Any kind of information regarding this will be of great help......

Submitted on Apr 19, 21
add a comment

1 Answer

Verified

I think a simple way is:

<?php
// this shouldn't be public no matter what in PRODUCTION
DB_NAME = "my_db";  // must be created from MySQL (MariaDB) using this command: CREATE DATABASE my_db
DB_USER = "root";  // or any user you have defined
DB_PASS = "";  // by default it's empty password for root
DB_HOST = "localhost";

$conn_dns = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME;

try {
  $dbc = new PDO($conn_dns, DB_USER, DB_PASS); 
} catch(PDOException $e) {
  echo $e->getMessage();
}

// CREATE TABLE users
$sql = "CREATE TABLE IF NOT EXISTS users(
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password CHAR(128) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4"; 

$dbc->execute($sql);

// INSERT DATA TO users TABLE
$username = "john_doe";
$password = "pass123";
$options = ['cost' => 12, 'salt' => 'some_string_here'];

// USE PREPARED STATEMENTS FOR SECURITY REASONS
$sql = "INSERT INTO users VALUES(:username, :password) LIMIT 1";

$stmt = $dbc->prepare($sql);

$stmt->bindParam(":username", $username, PDO::PARAM_STR);
$stmt->bindParam(":password", password_hash($password, PASSWORD_BCRYPT, $options), PDO::PARAM_STR);

$stmt->exec();

// READ DATA FROM users TABLE
$sql = "SELECT username, password FROM users";
$results = $dbc->exec();

while($row = $results->fetch(PDO::FETCH_OBJ)) {
  echo "Username:" . $row->username . " PASSWORD: " . $row->password;
}

// UPDATE DATA IN users TABLE
$sql = "UPDATE users SET username = :username WHERE id = :id";
$stmt = $dbc->prepare($sql);

$stmt->bindParam(":username", "New username", PDO::PARAM_STR);
$stmt->bindParam(":id", 1, PDO::PARAM_INT);

$stmt->exec();

if($stmt->rowCount()) { 
  echo "Update successfully"; 
}

// DELETE FROM users
$sql = "DELETE FROM users WHERE id=:id"

$stmt = $dbc->prepare($sql);
$stmt->bindParam(":id", 1, PDO::PARAM_INT);

$stmt->exec();

This should work.

UPDATE:

I think in PHP you create constants using the define() function like this:

<?php

define("DB_NAME", "my_db"); // instead of DB_NAME = "my_db"

Submitted 5 months ago


Wanna Post Ad?
Reach a large group of audience by posting an ad about your business here. For more details
Drop us a Message