Webslesson Tutorial | Insert data into Table using OOPS in PHP

Webslesson

PHP, MySql, Jquery, AngularJS, Ajax, Codeigniter, Laravel Tutorial

Thursday, 21 July 2016

PHP OOPS — Insert Data into MySql Database

In this post you can learn how to use PHP Object Oriented Programming for Insert or Add data or item into MySql Table. If you are using OOPS in PHP then it will be become very simple to use and can do such function like Add or Insert data into mysql table. In this post you can find I have make one PHP Class for Inserting Item into Mysql table. I have use __construct() megic methods of PHP for making database connection. In This PHP Megic method whenever new object of class will created then which ever code run under this method it will run, So I have write Mysql Database connection code write under this __construct() method.

I have provide complete source code with PHP Programming video tutorial with this post. You find source code below and video tutorial on above the post.

Source Code

Table — tbl_posts

 -- -- Table structure for table `tbl_posts` -- CREATE TABLE IF NOT EXISTS `tbl_posts` ( `post_id` int(11) NOT NULL AUTO_INCREMENT, `post_title` varchar(150) NOT NULL, `post_desc` text NOT NULL, PRIMARY KEY (`post_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -- Dumping data for table `tbl_posts` -- 

Class — database.php

 con = mysqli_connect("localhost", "root", "", "testing"); if(!$this->con) < echo 'Database Connection Error ' . mysqli_connect_error($this->con); > > public function insert($table_name, $data) < $string = "INSERT INTO ".$table_name." ("; $string .= implode(",", array_keys($data)) . ') VALUES ('; $string .= "'" . implode("','", array_values($data)) . "')"; if(mysqli_query($this->con, $string)) < return true; >else < echo mysqli_error($this->con); > > > ?> 

For Insert Data — test_class.php

  mysqli_real_escape_string($data->con, $_POST['post_title']), 'post_desc' => mysqli_real_escape_string($data->con, $_POST['post_desc']) ); if($data->insert('tbl_posts', $insert_data)) < $success_message = 'Post Inserted'; >> ?>       
 
 
 
Post Title  
Post Description 
   ?> 
 
   

Источник

PHP: Save an object to MySQL database.

This is a tutorial on how to save PHP objects to a MySQL database. This method will also work with other DBMS systems such as SQL Server and PostgreSQL.

Firstly, lets take a look at an example class that I’ve made:

name = $name; $this->gender = $gender; > public function speak()< echo 'Hi, my name is ' . $this->name . '!'; > >

As you can see, this Person class is extremely simple and straight-forward. It has two properties: A public property called $name and a protected property called $gender. It also has a constructor, as well as a function called “speak”.

To store this object in our MySQL database, we will need to:

1. Instantiate the object.
2. Serialize the object.
3. Insert it into our MySQL database.

For the purpose of this example, I’ve created a table called “objects”:

//Connect to the MySQL database using the PDO object. $pdo = new PDO('mysql:host=localhost;dbname=test', 'root', ''); //Instantiate object. $person = new Person('John Doe', 'Male'); //var_dump the object so that we can see what its structure looks like. var_dump($person); //Serialize the object into a string value that we can store in our database. $serializedObject = serialize($person); //Prepare our INSERT SQL statement. $stmt = $pdo->prepare("INSERT INTO objects (data) VALUES (?)"); //Execute the statement and insert our serialized object string. $stmt->execute(array( $serializedObject ));

In the code snippet above, we:

1. Connected to MySQL using the PDO object.
2. We instantiated our Person object.
3. We used var_dump, just to see what the structure of the object looks like.
4. We serialized the object into a string using the PHP function serialize.
5. We prepared our INSERT statement.
6. We executed the statement and inserting our serialized object into our database.

In this case, the serialized string looks like this:

Retrieving the object.

Now, we need to retrieve our PHP object from our MySQL database so that we can unserialize it and use it again:

//Connect to the MySQL database using the PDO object. $pdo = new PDO('mysql:host=localhost;dbname=test', 'root', ''); //Prepare our select statement. $stmt = $pdo->prepare("SELECT * FROM objects WHERE the statement. $stmt->execute(array(1)); //Fetch the table row in question. $row = $stmt->fetch(PDO::FETCH_ASSOC); //Unserialize the data. $person = unserialize($row['data']); //Lets call the speak method / function on our object. $person->speak();

In the example above, I retrieved the serialized data from my database. I then unserialized the data back into an object before calling the speak function – just to make sure that everything was working as intended.

Note: More often than not, serializing PHP arrays and objects is a bad idea. Please make sure that this is the best solution to your problem before you embark down this path.

Источник

PHP MySQL Insert Data

After a database and a table have been created, we can start adding data in them.

Here are some syntax rules to follow:

• The SQL query must be quoted in PHP
• String values inside the SQL query must be quoted
• Numeric values must not be quoted
• The word NULL must not be quoted

The INSERT INTO statement is used to add new records to a MySQL table:

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

In the previous chapter we created an empty table named «MyGuests» with five columns: «id», «firstname», «lastname», «email» and «reg_date». Now, let us fill the table with data.

Note: If a column is AUTO_INCREMENT (like the «id» column) or TIMESTAMP with default update of current_timesamp (like the «reg_date» column), it is no need to be specified in the SQL query; MySQL will automatically add the value.

The following examples add a new record to the «MyGuests» table:

Example (MySQLi Object-oriented)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>

$sql = «INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘John’, ‘Doe’, ‘john@example.com’)»;

if ($conn->query($sql) === TRUE) echo «New record created successfully»;
> else echo «Error: » . $sql . «
» . $conn->error;
>

Example (MySQLi Procedural)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>

$sql = «INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘John’, ‘Doe’, ‘john@example.com’)»;

if (mysqli_query($conn, $sql)) echo «New record created successfully»;
> else echo «Error: » . $sql . «
» . mysqli_error($conn);
>

Example (PDO)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDBPDO»;

try $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = «INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘John’, ‘Doe’, ‘john@example.com’)»;
// use exec() because no results are returned
$conn->exec($sql);
echo «New record created successfully»;
> catch(PDOException $e) echo $sql . «
» . $e->getMessage();
>

Источник

How to Insert Multiple JSON Data into MySQL Database in PHP

Hi! I have discussed about inserting json into mysql using php a while back. And I got several queries from readers about inserting multiple json objects into DB using the exact method. Inserting multiple objects involves multiple database transactions. There are less elegant ways to do this but using mysqli library’s Prepared Statement is the effective and secure way for this type of job. Prepared Statements has several advantages like utilizing fewer resources, preventing sql injection etc. I’ll show you here with an example, how to use mysqli prepared statement to insert multiple json data into mysql database in php.

Create MySQL Database & Table

First we need to create mysql database required for our example. Run this below sql command to create it.

CREATE DATABASE `employee`; USE `employee`; CREATE TABLE IF NOT EXISTS `emp` ( `id` int(8) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, `gender` varchar(10) NOT NULL, `designation` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Sample JSON File with Multiple Objects

We also need a json file to use for our example. Below is the file ‘empdata.json’ with multiple json objects containing some employee details.

File Name: empdata.json

We have to read through the above json file and insert the objects (i.e., employee details) one by one into mysql database.

Inserting Multiple JSON Data into MySQL Database in PHP

This is the php code snippet for inserting multiple json objects into mysql database.

 //close connection mysqli_close($con); ?>

As you can see, first we connect to the mysql database using the statement mysqli_connect() .

Next we use prepare statement mysqli_prepare() to prepare the insert query. Since we have to do multiple insertions into the database, preparing the query is quite effective and consumes less database resources.

The ? (question mark) in the INSERT query indicates that there are three parameters to be inserted into the database.

Next using the statement mysqli_stmt_bind_param() , we bind the insert query parameters to the upcoming variables that hold the json data. The function’s second param, ‘sss’ resembles the data type of the parameters. ‘s’ stands for string values. (Check here for rest of the type options)

Next we read the json file and convert the data into php associative array. The method file_get_contents($filename); returns the contents of the file into a variable. And this in turn is converted into array using json_decode() function.

Since there are multiple json data to be inserted into the db, we have to loop through the array using foreach statement. And store the employee details into variables and insert them into database using the mysqli_stmt_execute($st); statement. This will execute the insert query we have prepared earlier.

Once the database insertion is complete, we have to release the database handle we created at the beginning. The statement mysqli_close($con); will close the opened mysql database connection and empty the handle.

That was all about inserting multiple json data into mysql database using php. You can run the above given php snippet and it will insert the json file contents into the database. I hope you like this post. If you like it, please share it in your social circle.

Источник