I am using MySQL 8 and was trying to query SELECT DISTINCT with CONCAT on my Linux Mint system. If you have MySQL 5.x, please refer to the official documentations.
Here is the query that worked for me..
SELECT DISTINCT CONCAT( author_fname, ' ', author_lname) as Author FROM books;
A quick PHP, MySQL CRUD operation with PDO mysql driver with minimul HTML and almost no CSS. I am going to share the code for each file and operation separately.
I am using XAMPP server with MariaDB, PHP 8.1, and Apache 2.4 web server on my Windows 10 Pro.
I assume that you are already familiar with XAMPP htdocs and phpmyadmin. I have created a database using phpmyadmin and rest everything in php files.
/db.php
<?php
$connect = new PDO('mysql:host=localhost;dbname=phpecom', 'root', 'password');
$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>This is where is the create or insert HTML form to insert row data into the database table
/index.php
<?php include_once "db.php"; ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<h3>Create a New Profile....</h3>
<form action="crud.php" method="post">
<input type="text" name="name" id="" placeholder="Full Name">
<input type="email" name="email" id="" placeholder="[email protected]">
<input type="text" name="username" id="" placeholder="useranme">
<input type="password" name="password" id="" placeholder="**********">
<input type="submit" name="submit" value="Create Profile">
</form>
<br>
<br>
<br>
<a href="crud.php">Show all</a>
</body>
</html>The CREATE OR INSERT into table code is in a file called crud.php but you can also keep whatever file you want to in.
/crud.php
<h3>All Profiles...</h3>
<?php
include_once "db.php";
$profiles = "CREATE TABLE IF NOT EXISTS profiles (
u_id int not null auto_increment,
u_name varchar(120) not null,
u_email varchar(120) not null,
u_username varchar(120) not null,
u_password varchar(120) not null,
primary key(u_id))";
$stmt = $connect->query($profiles);
if (isset($_POST['submit'])) {
$createProfile = "insert into profiles values()";
$name = $_POST['name'];
$email = $_POST['email'];
$username = $_POST['username'];
$password = $_POST['password'];
$profileRow = "INSERT INTO profiles(u_name, u_email, u_username, u_password) VALUES('$name', '$email', '$username', '$password')";
$row = $connect->query($profileRow);
}
$showProfiles = $connect->query("select * from profiles");
$rows = $showProfiles->fetchAll();
foreach ($rows as $row) {
echo "<br><b>ID:</b> ", $row['u_id'], ", <b>Name:</b>", $row['u_name'], " <b>Email:</b> ", $row['u_email'], " <b>Password:</b> ", $row['u_password'], " ", "<a href=edit.php?id=". $row['u_id']. "> Edit</a>", " ", "<a href=view.php?id=". $row['u_id'] . "> View</a>", " ", "<a href=delete.php?id=". $row['u_id']. "> Delete</a>", "<br>";
}
?>
<br>
<br>
<br>
<a href="index.php">Crate Profile</a>
/edit.php
<?php include_once "db.php"; ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<h3>Edit Profile...</h3>
<?php
$id = $_GET['id'];
$profile = "select * from profiles where u_id = '$id' ";
$editRow = $connect->query($profile);
$updateRows = $editRow->fetchAll();
foreach ($updateRows as $updateRow) {
?>
<form method="post">
<input type="text" name="name" value="<?php echo $updateRow['u_name'] ?>" placeholder="Full Name">
<input type="hidden" name="id" value="<?php echo $updateRow['u_id'] ?>" >
<input type="email" name="email" value="<?php echo $updateRow['u_email'] ?>" placeholder="[email protected]">
<input type="text" name="username" value="<?php echo $updateRow['u_username'] ?>" placeholder="useranme">
<input type="password" name="password" value="<?php echo $updateRow['u_password'] ?>" placeholder="**********">
<input type="submit" name="updateProfile" value="Update Profile">
</form>
<?php } ?>
<!-- update query on update button click -->
<?php
if (isset($_POST['updateProfile'])) {
$uid = $_POST['id'];
$name = $_POST['name'];
$email = $_POST['email'];
$username = $_POST['username'];
$password = $_POST['password'];
$updateEdit = "UPDATE profiles SET u_name='$name', u_email='$email', u_username='$username', u_password='$password' where u_id = '$uid' ";
$row = $connect->query($updateEdit);
if ($row) {
header('location:crud.php');
}
}
?>
</body>
</html>/view.php
<?php
include_once('db.php');
$id = $_GET['id'];
$dataquery = $connect->query("select * from profiles where u_id = '$id'");
$viewdata = $dataquery->fetchAll();
foreach ($viewdata as $value) {
echo "<br><b>ID:</b> ", $value['u_id'], ", <b>Name:</b>", $value['u_name'], " <b>Email:</b> ", $value['u_email'], " <b>Password:</b> ", $value['u_password'];
}
?>
/delete.php
<?php
include_once "db.php";
$id = $_GET['id'];
$connect->query("DELETE FROM profiles WHERE u_id = '$id'");
header('Location: crud.php');
?>I assume that you already have installed MySQL on your Windows operating system and you are trying to access it through command prompt or PowerShell and you are getting this message MySQL is not recognized as an internal or external command, operable program or batch file.
I completely understand why you are getting this message. Basically, we need to add MySQL to the systems variable path.
To add MySQL to the system’s variable path, we need to locate the Bin folder in MySQL installation directory and then add it to the system’s variable path.
Alright let’s do it
I are going to install MySQL server on Ubuntu 19.04 using terminal, but you can follow the instructions and install MySQL server on Ubuntu 18.04 as well.
First thing first, we need to update the system core, so launch your terminal manually or press control+shift+t and type following command..
sudo apt update && upgradeWait for the updates and upgradables to be finished and then run MySQL installation command…
sudo apt install mysql-server -yWhen MySQL server is installed on your Ubuntu desktop, simply exit out of the terminal and relaunch the terminal and login to MySQL with sudo right.. type –
sudo mysqlNow you in the MySQL ternimal interface, here we are now going to alter root password, so that you can login to MySQL with root username and its password.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';Here you should choose a secure password for the root user. Now, let’s flush the privileges
FLUSH PRIVILEGES;Now, exit out of the MySQL interface and log back in with root user and the altered password.
exit
mysql -u root -pType root user password and create your very first SQL database with this simple command..
CREATE DATABASE db_name;Check all the databases, that exist on the SQL database.
SHOW DATABASES;If you still can’t install MySQL on Ubuntu, you can watch me install MySQL on my Ubuntu 19.04
Hello World!! In this tiny MySQL Tutorial video, I demonstrate how to create a database, select database, create a table, and insert values into the table, using the terminal, command line, prompt.
How to hash the password in MySQL Database.
How to create a database in MySQL
CREATE DATABASE database_name;
How to list databases in MySQL
SHOW DATABASES;
How to select a database in MySQL
USE database_name;
How to create a table in MySQL
CREATE TABLE table_name(
ID INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL,
useremail VARCHAR(80) NOT NULL,
userpassword CHAR(50) NOT NULL,
user_created_at TIMESTAMP);
How to insert values in MySQL table
INSERT INTO table_name(username, usermail, userpassword) VALUE('name1', '[email protected]', SHA('secret");
DROP DATABASE mydb;
This is the database drop command is.
Make sure you are logged in the MySQL server
MySQL -u -root -p
and then run the database drop command to delete MySQL database using the terminal.