Php database data to array

PHP: Save MySQL Result in Array

When we want to process the result of a MySQL query in PHP, it is often practical, to store the data directly into an array. Impcityant functions in this context are mysqli_fetch_array(), mysqli_fetch_row() and mysqli_fetch_assoc(). In this info, I would like to go over this and explain the difference.

Numerically indexed array with mysqli_fetch_row

The function mysqli_fetch_row() returns a numerically indexed array. This means, that we can access the data, for example, with $arr[1] or $arr[3]:

$res = mysqli_query($db, "SELECT name, city, country FROM tab WHERE = mysqli_fetch_row($res); echo $arr[0]; // name echo $arr[1]; // city echo $arr[2]; // country

As the example shows, the order of the elements in the array corresponds to the order of the fields in the query. So we can use the query to determine how our array should be filled.

Associative array with mysqli_fetch_assoc

The function mysqli_fetch_array() returns an associative array. This means, that the fields can not be addressed with the index number but with the name of the field. And this field name is the name of our column from our MySQL database:

$res = mysqli_query($db, "SELECT name, city, country FROM tab WHERE = mysqli_fetch_assoc($res); echo $arr['name']; // name echo $arr['city']; // city echo $arr['country']; // country

Interesting in this context is the function extract(). This function can automatically create individual variables with the respective name from the array, which are then available to us in the code, as the following example shows:

$res = mysqli_query($db, "SELECT name, city, country FROM tab WHERE = mysqli_fetch_assoc($res); extract($arr); echo $name; // name echo $city; // city echo $country; // country

Also the function list() can be used to make individual variables out of the array, as the following example shows:

$res = mysqli_query($db, "SELECT name, city, country FROM tab WHERE $city, $country) = mysqli_fetch_row($res); echo $name; // name echo $city; // city echo $country; // country

In contrast to extract(), however, with list() we have the option of freely defining what our variables should be called. We can, but don’t have to, stick to the names of the columns in our table.

The all-rounder mysqli_fetch_array

The function mysqli_fetch_array() masters to output the data set both numerically indexed as well as an associative array. This can be controled with the parameters MYSQLI_NUM or MYSQLI_ASSOC.

If you omit the parameter completely or you are using MYSQLI_BOTH, you can use both types of indexes:

$arr = mysqli_fetch_array($res); // equivalent to mysqli_fetch_array($res, MYSQL_BOTH); echo $arr[0]; // name echo $arr['city']; // city echo $arr[2]; // country

Calling mysqli_fetch_array($res, MYSQLI_NUM) is equivalent to the function mysqli_fetch_row($res).

Calling mysqli_fetch_array($res, MYSQLI_ASSOC) is equivalent to the function mysqli_fetch_assoc($res).

About the Author

You can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? — sttmedia.com/contact
Show Profile

Related Topics

Send HTML5 Canvas as Image to Server

HTML5 Canvas: Beginner Tutorial Chapter 3 — Rectangles and Circles

jQuery: Send HTML5 Canvas to Server via Ajax

HTML5 Canvas: Beginner Tutorial Chapter 2 — Drawing Lines

Units: SI Prefixes for Powers of Ten

XLS and XLSX: Maximum Number of Columns and Rows

jQuery: Read and Change Data Attribute Value

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.

Источник

Insert Array into MySQL Database using PHP

Today, let’s see how to store and retrieve arrays into the database using PHP and MySQL. MySQL’s databases don’t accept objects or array data types. The array value directly inserts into the MySQL database, not support. But you can convert an array into a string. Four ways to insert an array into a MySQL database.

MySQL database

Create a database called devooti and create a table called list with fields:

 
devooti
 CREATE TABLE `list` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` varchar(80) NOT NULL, `country` varchar(255) NOT NULL, `status` varchar(255) NOT NULL, `details` longtext COLLATE utf8mb4_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Method 1) Insert PHP Array into MySQL database using Repetitive.

 
index.php
  array("Tricia Allison", "USA", "True"), "1" => array("Karla Newman", "China", "True"), "2" => array("Jessica Munoz", "UK", "False") ); if(is_array($user_data)) < foreach ($user_data as $row) < $val1 = mysqli_real_escape_string($db_conn, $row[0]); $val2 = mysqli_real_escape_string($db_conn, $row[1]); $val3 = mysqli_real_escape_string($db_conn, $row[2]); $query ="INSERT INTO list (name, country, status) VALUES ( '".$val1."','".$val2."','".$val3."' )"; mysqli_query($db_conn, $query); >> ?> 

Output

Method 2) Insert PHP Array into MySQL database table using onetime.

 
index.php
  array("Tricia Allison", "USA", "True"), "1" => array("Karla Newman", "China", "True"), "2" => array("Jessica Munoz", "UK", "False") ); if(is_array($user_data)) < $DataArr = array(); foreach($user_data as $row)< $val1 = mysqli_real_escape_string($db_conn, $row[0]); $val2 = mysqli_real_escape_string($db_conn, $row[1]); $val3 = mysqli_real_escape_string($db_conn, $row[2]); $DataArr[] = "('$val1', '$val2', '$val3')"; >$sql = "INSERT INTO list (name, country, status) values "; $sql .= implode(',', $DataArr); mysqli_query($db_conn, $sql); > ?>

Output

Method 3) To Convert Array into serialized string

We can convert the array to string with serialized function and converting string to array using serialize () function and then insert into the database. Here’s the PHP code to do it.

 
index.php
  array("Tricia Allison", "USA", "True"), "1" => array("Karla Newman", "China", "True"), "2" => array("Jessica Munoz", "UK", "False") ); $serialized_data = serialize($user_data); $sql = "insert into list (details) value ('$serialized_data')"; mysqli_query($db_conn, $sql); ?>

Output

Retrieve data from the database and convert into an array

Output

Array ( [0] => Array ( [0] => Tricia Allison [1] => USA [2] => True ) [1] => Array ( [0] => Karla Newman [1] => China [2] => True ) [2] => Array ( [0] => Jessica Munoz [1] => UK [2] => False ) )

Method 4) To Convert Array into JSON string

We can convert the array to JSON string with json_decode() function and convert a JSON string to array using json_decode() function and then insert into the database. Here’s the PHP code to do it.

 
index.php
  array("Tricia Allison", "USA", "True"), "1" => array("Karla Newman", "China", "True"), "2" => array("Jessica Munoz", "UK", "False") ); $json_data = json_encode($user_data); $sql = "insert into list (details) value ('$json_data')"; mysqli_query($db_conn, $sql); ?>

Output

[[“Tricia Allison”,”USA”,”True”],[“Karla Newman”,”China”,”True”],[“Jessica Munoz”,”UK”,”False”]]

Retrieve JSON data from the database

Output

Array ( [0] => Array ( [0] => Tricia Allison [1] => USA [2] => True ) [1] => Array ( [0] => Karla Newman [1] => China [2] => True ) [2] => Array ( [0] => Jessica Munoz [1] => UK [2] => False ) )

Conclusion

In These tutorials, we have learned how to store and retrieve arrays into the database using PHP and MySQL. There are two ways to insert a PHP array into a MySQL table database, using serialize() and unserialize() function.

Related keywords

• How to Store Array in MySQL with PHP
• How to Enter PHP Array within MySQL Database
• Store & Retrieve Arrays Into Database With PHP MYSQL
• PHP Insert Array
• Array value insert into PHP MySQL Database
• How to insert multiple array values into database PHP
• Insert multiple arrays values into a MySQL table
• I want to inserting multiple array values in PHP
• Insert into array php Code Example
• Insert an item at a specific position in an array in PHP
• Learn PHP Array Push: PHP Add to Array Explained
• Array push key value pair php
• php array push key value Code Example
• PHP insert into array at index 0
• Insert array into one column mysql
• In mysql insert array values in one column
• Save Array into one column

Recent Posts

• Updated Angular, Asp.net Core 3, Entity Framework Core 3 July 14, 2022
• Asp.net Core, Angular, And Bootstrap In Front-end Development July 14, 2022
• hands-on and pragmatic journey to master Angular with Typescript July 14, 2022
• Reactive Patterns with RxJS for Angular: A practical guide to managing your Angular application’s data reactively and efficiently using RxJS 7 July 14, 2022
• Pro Angular: Build Powerful and Dynamic Web Apps July 14, 2022
• The Basics Of Html, Css And Some Intermediate To Advanced Coding In A Fast Paced, Detailed System July 14, 2022
• Developing Database-Driven Websites With PHP 7, MySQL 8 & MariaDB July 14, 2022
• PHP: PHP Security and Session Management July 14, 2022
• An Instruction to HTML, CSS, PHP, and MySQL To Create Data-Driven Web Sites July 14, 2022
• PHP: Advanced PHP functions July 14, 2022
• Design Patterns In PHP & Laravel Using Real-world Examples July 14, 2022
• HP: 3 Books in 1: PHP Basics for Beginners + PHP Security and Session Management + Advanced PHP Functions July 14, 2022
• Html, Css, Php, Bootstrap, Javascript And Mysql To Create A Dynamic Site July 14, 2022
• Advanced Javascript Design Patterns July 14, 2022
• How To Code in React.js July 14, 2022
• Docker, Node.js, React, Typescript & Webpack To Develop Modern Full-stack July 14, 2022
• React App vs NextJS July 14, 2022

Источник