Learn about php mysql

PHP mysqli class to connect with MySQL database

PHP mysqli class is one of the multiple ways to connect with MySQL database using PHP. In this post we will learn about PHP mysqli class, it’s useful methods and properties.

Following logical steps will show you how to use mysqli class to access MySQL database in PHP:

1. Connecting with MySQL database using PHP
2. Creating a Table in MySQL Database using PHP mysqli
3. Inserting data in MySQL database using mysqli class
4. Update data using mysqli class in PHP
5. Fetch Associated array from MySQL table
6. Fetch table row as object using mysqli class

Connecting with MySQL database using PHP mysqli

To establish connection, we need to create an object of PHP mysqli class. Once the object is created, we will check it’s connect_errno property. If connection is successful, connect_errno property will hold 0 otherwise it will hold the MySQL error code as described here.

$mysqli = new mysqli('127.0.0.1','root','','weblearningblog'); /*connecting with mysql*/ if(!$mysqli->connect_errno) < echo "Connection Successful"; >else < echo $mysqli->connect_error; > /*connecting end*/

Notice the order of the parameters of mysqli constructor, it is host,username,password and database name respectively.

Creating a Table in MySQL Database using PHP mysqli

To create a new table in database, we use query() method of mysqli class. This method returns FALSE if query fails. In queries, where a result set is expected, query() method will return result set and for other successful queries query() method returns TRUE.

$mysqli = new mysqli('127.0.0.1','root','','weblearningblog'); /*creating a table starts*/ if($mysqli->connect_errno)< echo $mysqli->connect_error; > else < $sql = 'CREATE TABLE students( id INT NOT NULL AUTO_INCREMENT , name varchar(30) NOT NULL, study_program varchar(20) NOT NULL, department varchar(20), date_added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) )'; $result = $mysqli->query($sql); if(!$result)< echo "Query Failed. ".$mysqli->error; > else < echo "Query Successfull"; >$mysqli->close();//close the connection >*/ /*creating a table ends*/

CREATE TABLE is SQL command therefore, discussing it is not in the scope of this article. We store SQL query in a string, later this string is passed as first and only required parameter for mysqli query() method.

Inserting data in MySQL database using mysqli class

Inserting records in MySQL database using mysqli class is similar to the previous section as the process is same but we will use a different SQL Command INSERT INTO for this purpose.

$mysqli = new mysqli('127.0.0.1','root','','weblearningblog'); if($mysqli->connect_errno)< echo $mysqli->connect_error; > else < $sql = 'INSERT INTO students (name,study_program,department) VALUES("John","BS","Computer Science")'; $result = $mysqli->query($sql); if(!$result)< echo "Query Failed. ".$mysqli->error; > else < echo "Successfully Inserted. ".$mysqli->affected_rows." Records"; > >

The mysqli class affected_rows property is useful in insert and update operations as it hold the number of rows, which query has affected .i.e. in this case, it will hold 1 as this query is inserting one row in table.

For inserting multiple records at once, we just need to change the SQL command INSERT INTO as follows:

$mysqli = new mysqli('127.0.0.1','root','','weblearningblog'); if($mysqli->connect_errno)< echo $mysqli->connect_errno; > else < $sql = 'INSERT INTO students (name,study_program,department) VALUES ("John","BS","Computer Science"), ("Kane","MS","Computer Science"), ("Bob","PhD.","Physics"), ("Stewart","MBA","Computer Science") '; $result = $mysqli->query($sql); if(!$result)< echo "Query Failed. ".$mysqli->error; > else < echo "Successfully Inserted. ".$mysqli->affected_rows." Records"; > >

Update data using mysqli class in PHP

Updating the record in MySQL table using mysqli class is pretty straight forward.

$mysqli = new mysqli('127.0.0.1','root','','weblearningblog'); if($mysqli->connect_errno)< echo $mysqli->connect_errno; > else < $sql = 'UPDATE students SET name="Kane" WHERE = $mysqli->query($sql); if(!$result)< echo "Query Failed. ".$mysqli->error; > else < echo "Successfully Updated. ".$mysqli->affected_rows." Records"; > >

Fetch associated array from MySQL table using mysqli

Reading data from MySQL table using PHP mysqli extension involves retrieving a result set from table and then iterate through it by fetching record as associative array one by one.

$mysqli = new mysqli('127.0.0.1','root','','weblearningblog'); if($mysqli->connect_errno)< echo $mysqli->connect_errno; > else < $sql = 'SELECT * FROM students'; $result = $mysqli->query($sql); if(!$result)< echo "Query Failed. ".$mysqli->error; > else < if($result->num_rows > 0)< $html ; $html . ;//header row while($current_row = $result->fetch_assoc())< $html .= ''; $html .= ''.$current_row['id'].''; $html .= ''.$current_row['name'].''; $html .= ''.$current_row['study_program'].''; $html .= ''.$current_row['department'].''; $html .= ''; > $html . ; $result->free();//free the resultset memory echo $html; > else < echo "No Record Exists"; >> >

As shown in code snippet above, the query() method is now returning a result set instead of TRUE or FALSE as in insert and update operations discussed in previous sections. Here we are getting the students records from MySQL table and creating a HTML table to view it in a browser.

num_rows property of result set object is having the number of rows or records, this result set is consisting of, therefore it is a good idea to evaluate num_rows property before using the result set to read data.

While reading data from database table, it is very important to learn how fetch_assoc() method of result set object works. Whenever we call fetch_assoc() method on result set object, it gives us one row and move internal data pointer to next row, which means if we call fetch_assoc() again, it will return next row and so on. Ultimately if we use fetch_assoc() in a while loop, it will execute on each iteration and returns a new row. If there is no record left fetch_assoc() will return NULL which will cause while loop to exit.

As fetch_assoc() returns associative array, The key of the array is column name of the table and value of the array is the column value for current row. Building up of HTML table is straight forward and don’t need any explanation.

Fetch table row as object using mysqli class

To fetch record as object instead of associative array, we just have to replace fetch_assoc() method with fetch_object().

while($current_row = $result->fetch_object())< $html .= ''; $html .= ''.$current_row->id.''; $html .= ''.$current_row->name.''; $html .= ''.$current_row->study_program.''; $html .= ''.$current_row->department.''; $html .= ''; >

Conclusion

In this tutorial, I tried to focus on practical and code oriented approach to teach this topic. Please share your valuable feedback by commenting.

Follow us

Источник

PHP MySQL Database

With PHP, you can connect to and manipulate databases.

MySQL is the most popular database system used with PHP.

What is MySQL?

• MySQL is a database system used on the web
• MySQL is a database system that runs on a server
• MySQL is ideal for both small and large applications
• MySQL is very fast, reliable, and easy to use
• MySQL uses standard SQL
• MySQL compiles on a number of platforms
• MySQL is free to download and use
• MySQL is developed, distributed, and supported by Oracle Corporation
• MySQL is named after co-founder Monty Widenius’s daughter: My

The data in a MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows.

Databases are useful for storing information categorically. A company may have a database with the following tables:

PHP + MySQL Database System

• PHP combined with MySQL are cross-platform (you can develop in Windows and serve on a Unix platform)

Database Queries

A query is a question or a request.

We can query a database for specific information and have a recordset returned.

Look at the following query (using standard SQL):

The query above selects all the data in the «LastName» column from the «Employees» table.

To learn more about SQL, please visit our SQL tutorial.

Download MySQL Database

If you don’t have a PHP server with a MySQL Database, you can download it for free here: http://www.mysql.com

Facts About MySQL Database

MySQL is the de-facto standard database system for web sites with HUGE volumes of both data and end-users (like Facebook, Twitter, and Wikipedia).

Another great thing about MySQL is that it can be scaled down to support embedded database applications.

Источник

PHP With MySQL: Ultimate Step-By-Step Guide

PHP is a language that gives you the flexibility to connect and work with different databases while developing your webpage. There are different databases, both commercial and free to use. Amongst them, MySQL is the most commonly used database alongside PHP.

MySQL is an open-source, free-to-use relational database management system (RDBMS). It is a fast, simple, and highly scalable program and hence can be used for small as well as huge businesses.

Basics to Advanced — Learn It All!

Prerequisites for PHP With MySQL

This PHP with MySQL tutorial will mainly focus on linking and managing a database with your webpage. Hence, the following prerequisites should be met before beginning the tutorial:

Software Requirements for the Tutorial

To set up the PHP development environment, you mainly need two software, a code editor and a web server package.

Now that you know the prerequisites for this tutorial as well and the software requirements, it’s time to move on to creating a database.

How to Create a Database?

A database is defined as an organized collection of data that makes the data easy to access, manage and modify. Generally, databases use SQL (Structured Query Language) for data manipulation.

1. Open XAMPP Control Panel and start the Apache server and MySQL service.

2. Now, go to your browser and type localhost in the address bar, and then on the XAMPP dashboard, click on the phpmyadmin tab.

3. Into the phpmyadmin section, click on new to create a new database. Here, name your database “tutorial”.

4. Now, create a table named “users” and create 6 columns as shown below, which you will use on the registration page.

Keep the “id” column as Auto Incremented by clicking the check box next to ‘AI’ since it will be the primary key.

Basics to Advanced — Learn It All!

How to Create a Registration Page?

To create a registration, first, create a folder named “demo” inside htdocs and then open it inside the code editor i.e, Visual Studio Code.

Now, create a file named “register.php” and write the following code.

$sql = «INSERT INTO `users`(`firstname`, `lastname`, `email`, `password`, `gender`)

echo «New record created successfully.»;

echo «Error:». $sql . «
«. $conn->error;

Registration Form

That’s it. Now you just need to connect this webpage with the database.

How to Connect Webpage to the Database Using PHP?

To connect the webpage to the database, create another file in the code editor named “config.php” and write the following code.

$conn = new mysqli($servername, $username, $password, $dbname);

die(«Connection Failed» . $conn->connect_error);

Now that you have the registration page ready and connected to the database, open the browser for the output.

• Open the browser and type “localhost/demo/register.php”. Fill in the required fields and click on “submit”.

As you can see, the records are visible in the table “users”.

Now you know how to create a database and link it to a webpage. You will now see how to edit or delete the fields from the phpmyadmin control panel.

Learn From The Best Mentors in the Industry!

How to Edit and Delete Data From the Database?

Once data has been entered into the database, you can open the phpmyadmin panel from the XAMPP dashboard and locate to the table “users” where every information entered into the table is visible.

Here, with every data, there exists an edit and delete option which can be used to update or delete any desired data from the table.

Conclusion

This brings us to the end of the “PHP with MySQL” tutorial. In this, you have learned how to create a database using XAMPP and MySQL and also create a registration page with the help of PHP. In the later sections, you learned how to connect the webpage to the database and then finally how to edit and delete the entries in the table using the phpmyadmin panel.

You can refer here for a video tutorial on the same. If you wish to learn more about PHP and Web Development check out the PostGraduate Program in Web Development offered by Simplilearn in collaboration with Caltech where you’ll learn modern coding techniques with boot camp-level intensity and gain all you need to be a full-stack technologist.

If you have any queries regarding the PHP with MySQL tutorial, do mention them in the comment section of this tutorial, and we’ll have our experts answer them for you.

Find our Full Stack Java Developer Online Bootcamp in top cities:

Name	Date	Place
Full Stack Java Developer	Cohort starts on 4th Aug 2023, Weekend batch	Your City	View Details
Full Stack Java Developer	Cohort starts on 25th Aug 2023, Weekend batch	Your City	View Details

About the Author

Kartik Menon

Kartik is an experienced content strategist and an accomplished technology marketing specialist passionate about designing engaging user experiences with integrated marketing and communication solutions.

Источник