database.transaction(function(tx) < // select the record by username case insensetive. var selectSql = 'select * from ' + tableNameUserData + ' where name = \'' + userName +'\' COLLATE NOCASE'; console.log('selectSql = ' + selectSql); tx.executeSql(selectSql, [], function(tx, result)< console.log('result.rows.length = ' + result.rows.length); if(result.rows.length >0)< var message = 'The user name exist, please input another one.' alert(message); console.log(message); >else < // if not exist then insert the user data. insertFuncName(userName, userEmail, userNote); >>, function(tx, error)< alert(error); >); >);
2. How To Manage SQLite Database In Javascript Client-Side Example.
This example is similar to the example in the article How To Implement A Database Using Html5 Web Storage, the difference is that this example uses an SQLite database to save the user data in a database table.
This example contains 2 source files, html5-sqlite-database-operation-example.html, html5-sqlite-database-operation-example.js.
var outputListElement = null; var outputListID = 'outputList'; var dbName = 'UserDB'; var dbVersion = '1.0'; var dbDesc = 'User Info Database.'; var dbSize = 2 * 1024 * 1024; var database = null; var tableNameUserData = 'UserData'; function initApp() < outputListElement = document.getElementById(outputListID); database = openDatabase(dbName, dbVersion, dbDesc, dbSize); createUserDataTable(); searchAll(tableNameUserData); >function saveUserData(userNameId, userEmailId, userNoteId)< var userNameValue = ''; var userEmailValue = ''; var userNoteValue = ''; var userNameObject = document.getElementById(userNameId); if(userNameObject==null)< alert('The user name input text box does not exist.'); return; >else < userNameValue = userNameObject.value; if(userNameValue.trim().length == 0)< alert('The user name can not be empty.'); return; >> var userEmailObject = document.getElementById(userEmailId); if(userEmailObject==null)< alert('The user email input text box does not exist.'); return; >else < userEmailValue = userEmailObject.value; if(userEmailValue.trim().length == 0)< alert('The user email can not be empty.'); return; >> var userNoteObject = document.getElementById(userNoteId); if(userNoteObject==null)< alert('The user note input text area does not exist.'); return; >else < userNoteValue = userNoteObject.value; if(userNoteValue.trim().length == 0)< alert('The user note input text area can not be empty.'); return; >> isUserExist(userNameValue, userEmailValue, userNoteValue, insertUserData); > function insertUserData(userNameValue, userEmailValue, userNoteValue)< // execute insert sql to insert the user data to the UserData table. database.transaction(function(tx)< var insertSql = 'insert into UserData(id, name, email, note, time) values(?, ?, ?, ?, ?)'; * 10000); pubTime = Date.now(); valueArray = [id, userNameValue, userEmailValue, userNoteValue, pubTime]; console.log('insertSql = ' + insertSql); tx.executeSql(insertSql, valueArray, function(tx, result)< var message = 'Save user data to local SQLite database table successfully.'; alert(message); console.log('message = ' + message); searchAll(tableNameUserData) >, function(tx, error)< var message = 'Save user data to local SQLite database table fail, the error message is ' + error alert(message); console.log('message = ' + message); >); >); > // need to implement the below function later. function isUserExist(userName, userEmail, userNote, insertFuncName) < database.transaction(function(tx)< // select the record by username case insensetive. var selectSql = 'select * from ' + tableNameUserData + ' where name = \'' + userName +'\' COLLATE NOCASE'; console.log('selectSql = ' + selectSql); tx.executeSql(selectSql, [], function(tx, result)< console.log('result.rows.length = ' + result.rows.length); if(result.rows.length >0)< var message = 'The user name exist, please input another one.' alert(message); console.log(message); >else < // if not exist then insert the user data. insertFuncName(userName, userEmail, userNote); >>, function(tx, error)< alert(error); >); >); > function searchByName(searchByNameKeywordId) < var searchByNameKeywordObject = document.getElementById(searchByNameKeywordId); if(searchByNameKeywordObject==null)< alert('The search user by name keyword input text box does not exist.'); return; >var searchName = searchByNameKeywordObject.value; if(searchName.trim().length == 0)< searchAll(tableNameUserData); >else < database.transaction(function(tx)< // select all the data from the database table by the name condition. var selectByNameSql = 'select * from ' + tableNameUserData + ' where name like "%' + searchName + '%"'; tx.executeSql(selectByNameSql, [], function(tx, results)< var rowsNumber = results.rows.length; console.log('selectByNameSql = ' + selectByNameSql); console.log('rowsNumber = ' + rowsNumber); if(rowsNumber >0) < // remove all the child notes in the web page message list. outputListElement.innerHTML = ''; for(var i=0; i> >, function(tx, error)< >); >); > > function createUserDataTable()< database.transaction(function(tx)< // create the UserData table if not exist. var createTableSql = 'create table if not exists UserData(id unique, name TEXT, email TEXT, note TEXT, time INTEGER)'; tx.executeSql(createTableSql); >); > function searchAll(tableName) < database.transaction(function(tx)< // select all the data from the database table. var selectAllSql = 'select * from ' + tableName; tx.executeSql(selectAllSql, [], function(tx, results)< var rowsNumber = results.rows.length; console.log('selectAllSql = ' + selectAllSql); console.log('rowsNumber = ' + rowsNumber); // first empty all the user data list on the web page. outputListElement.innerHTML = ''; if(rowsNumber >0) < for(var i=0; i> >, function(tx, error)< >); >); > function addUserDataOnWebPage(rowData) < var labelUserId = document.createElement('label'); labelUserId.style.display = 'block'; var labelUserIdText = document.createTextNode('Id:' + rowData.id); labelUserId.append(labelUserIdText); outputListElement.append(labelUserId); var labelUserName = document.createElement('label'); labelUserName.style.display = 'block'; var labelUserNameText = document.createTextNode('Name:' + rowData.name); labelUserName.append(labelUserNameText); outputListElement.append(labelUserName); var labelUserEmail = document.createElement('label'); labelUserEmail.style.display = 'block'; var labelUserEmailText = document.createTextNode('Email:' + rowData.email); labelUserEmail.append(labelUserEmailText); outputListElement.append(labelUserEmail); var labelUserNote = document.createElement('label'); labelUserNote.style.display = 'block'; var labelUserNoteText = document.createTextNode('Note:' + rowData.note); labelUserNote.append(labelUserNoteText); outputListElement.append(labelUserNote); var labelPubTime = document.createElement('label'); labelPubTime.style.display = 'block'; var date = new Date(); date.setTime(rowData.time); var labelPubTimeText = document.createTextNode('Publish Time:' + date.toLocaleDateString() + ' ' + date.toLocaleTimeString()); labelPubTime.append(labelPubTimeText); outputListElement.append(labelPubTime); var hr = document.createElement('hr'); outputListElement.append(hr); >function clearUserDataTable()< database.transaction(function(tx)< var delSql = 'delete from ' + tableNameUserData; tx.executeSql(delSql, [], function(tx, result)< var message = 'Delete all data from UserData table successfully.'; alert(message); console.log(message); console.log(delSql); searchAll(tableNameUserData); >, function(tx, error)< >); >); >
Using HTML5, web pages can store data locally within the user’s browser.
WebSQL database defines an API for storing data in databases that can be queried using a variant of SQL.
There are three core methods:
Creating and Opening Databases(openDatabase)
If you try to open a database that doesn’t exist, the API will create it on the fly for you using openDatabase method.
To create and open a database, use the following code:
var db = openDatabase(‘mydb’, ‘1.0’, ‘my first database’, 2 * 1024 * 1024);
I’ve passed four arguments to the openDatabase method. These are:
Database name
Version number
Text description
Estimated size of database
Transactions(transaction)
Once we have opened our database, we can create transactions. The transactions give us the ability to rollback the query transactions.
i.e., if a transaction — which could contain one or more SQL statements — fails (either the SQL or the code in the transaction), the updates to the database are never committed — i.e. it’s as if the transaction never happened.
in single word we can say, transaction obeys, ATOMICITY property of DBMS.
There are also error and success callbacks on the transaction, so you can manage errors, but it’s important to understand that transactions have the ability to rollback changes.
db . transaction ( function ( query ) < // write SQL statements here using the «query» object >);
executeSql
executeSql is used for both read and write statements, includes SQL injection projection, and provides a callback method to process the results of any queries you may have written.
db . transaction ( function ( query ) < query . executeSql ( ‘CREATE TABLE foo (id unique, text)’ ); >);
Sample HTML Code:-
var db =openDatabase(‘mydb’,’1.0′,’testdb’,1024);
//Create table under the above DB
query.executeSql(‘create table if not exists user(id unique, usesr, passwd)’);
//Insert values to the table
query.executeSql(‘insert into user values (1,”jai”,”pass”)’);
//Get stored values from the table
query.executeSql(‘select * from user’,[],function(u,results)
How to connect HTML to database with MySQL using PHP? An example
How to connect HTML to database with MySQL using PHP? An example – This article helps to become a custom PHP developer. You will get complete steps for storing HTML form input field in MySQL database connection in a db table using the PHP programming with example . This article provide you HTML form, DB + Table SQL code, Bootstrap 5 with CSS, Form Validation and database connection + submission code . In the conclusion step, you will be GIT download link so no need to copy-paste the code.
Tools Required to connect HTML Form with MySQL Database using PHP
First of all, you must be install any XAMPP or WAMP or MAMP (for Mac OS) kind of software on your laptop or computer. With this software, you will get a local webserver i.e. Apache, PHP language, and MySQL database. The complete code is on Github and the download link is the last of this article.
In this article, my PHP, MySQL example is with database connection in xampp code.
After installation you need to on the Xampp see the image below:
After installation of any of these laptop or desktop software you need to check your localhost is working or not. Open your browser and check this URL http://127.0.0.1 or http://localhost/ . If this is working it means you have the local webserver activated with PHP/MySQL.
Also, GUI PHPmyAdmin coming for handling CRUD operations i.e. insert(create), update, delete, and select(read) records from tables. This interface is browser-based and very helpful, easy to use for creating and managing phpmyadmin database in table(column, row).
If you have the above installation you can go ahead to start your coding.
If you have not a LAMP stack-based web server then you can do this directly in your hosting space.
If you have any more query then you can comment on this post. We will reply to your query.
Suppose you have a web page to insert contact form field data in your DB. For this you need to follow the following steps:
Step 1: Filter your HTML form requirements for your contact us web page
Suppose you selected the form field Name (text input), Email(email input), Phone (number input), and message (multi-line text). The form submit button also necessary for submitting the form. You will get the complete form in HTML coding in step 3.
Step 2: Create a database and a table in MySQL
Open a web browser (chrome, firefox, edge, etc., ) and type this http://localhost/phpmyadmin/ or http://127.0.0.1/phpmyadmin/ for open GUI for managing DB on your computer. See the xampp screen below how it is coming.
Click on the databases link and create your db by the name “db_contact”. See the image below:
After creating your DB you need to create a table by any name I choose “tbl_contact” with the number of field 5. We choose 4 fields on top Name, Email, Phone, and Message. The first column we will keep for maintaining the serial number and in technical terms primary key(unique number of each recor). See the image below
When you will click to go button you will get this screen. Now we need to feed every field information.
See the below image in which I added field information. So for field Name used field Name – fldName, Email – fldEmail, Phone – fldPhone, Message – fldMessage.
Now click on the save button that is on the bottom right of your screen. After saving your table it is created in your database.
You can create your DB and table using the SQL below. You have to copy the following code and paste it into your MySQL GUI phpmyadmin database or any other GUI or command prompt. At the bottom of the blog, you will get a git download link to download the SQL file.
-- -- Database: `mydb` -- CREATE DATABASE IF NOT EXISTS `db_contact` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `db_contact`; -- -------------------------------------------------------- -- -- Table structure for table `tbl_contact` -- DROP TABLE IF EXISTS `tbl_contact`; CREATE TABLE IF NOT EXISTS `tbl_contact` ( `id` int(11) NOT NULL, `fldName` varchar(50) NOT NULL, `fldEmail` varchar(150) NOT NULL, `fldPhone` varchar(15) NOT NULL, `fldMessage` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Indexes for dumped tables -- -- -- Indexes for table `tbl_contact` -- ALTER TABLE `tbl_contact` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `tbl_contact` -- ALTER TABLE `tbl_contact` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
Step 3: Create HTML form for connecting to database
Now you have to create an HTML form. For this, you need to create a working folder first and then create a web page with the name “contact.html”. If you install xampp your working folder is in folder this “E:\xampp\htdocs”. You can create a new folder “contact” on your localhost working folder. Create a “contact.html” file and paste the following code.
Now your form is ready. You may test it in your localhost link http://localhost/contact/contact.html In the next step, I will go with creating PHP / MySQL code.
Step 4: Create a PHP page to save data from HTML form to your MySQL database
The contact HTML form action is on “contact.php” page. On this page, we will write code for inserting records into the database.
For storing data in MySQL as records, you have to first connect with the DB. Connecting the code is very simple. The mysql_connect in PHP is deprecated for the latest version therefore I used it here mysqli_connect.
You need to place value for your localhost username and password. Normally localhost MySQL database username is root and password blank or root. For example, the code is as below
$con = mysqli_connect('localhost', 'root', '',’db_contact’); The “db_contact” is our database name that we created before. After connection database you need to take post variable from the form. See the below code $txtName = $_POST['txtName']; $txtEmail = $_POST['txtEmail']; $txtPhone = $_POST['txtPhone']; $txtMessage = $_POST['txtMessage'];
When you will get the post variable then you need to write the following SQL command.
For fire query over the database, you need to write the following line
Here is PHP code for inserting data into your database from a form.
Step 5: All done!
Now the coding part is done. Download code from github
If you would like to check then you can fill the form http://localhost/contact/contact.html and see the result in the database. You may check via phpmyadmin your inserted record.
Why skills as a custom PHP developer?
Php is the most popular server-side programming language. It is used more than 70% in comparison to other website development languages. As a lot of CMS and custom PHP applications developed already on PHP, therefore, it will be a demanding language for the next 5 years.
The worldwide PHP development company is looking for cheap PHP developers in India. Many companies also like to freelance PHP developers in Delhi, London, Bangalore, Mumbai (locally). If you would like to hire a dedicated developer then you need to skills yourself.
See more answer about PHP script connect to Mysql on Facebook Group
Please join Facebook group for discussion click here Post your question here with the HASH tag #connectphpmysql #connecthtmlmysql . We will approve and answer your question.
Please view more answer on this hashtag on Facebook Group #connectphpmysql #connecthtmlmysql
