Php mysql array key value

mysql_fetch_assoc

This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide. Alternatives to this function include:

Description

Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead. mysql_fetch_assoc() is equivalent to calling mysql_fetch_array() with MYSQL_ASSOC for the optional second parameter. It only returns an associative array.

Parameters

The result resource that is being evaluated. This result comes from a call to mysql_query() .

Return Values

Returns an associative array of strings that corresponds to the fetched row, or false if there are no more rows.

If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you either need to access the result with numeric indices by using mysql_fetch_row() or add alias names. See the example at the mysql_fetch_array() description about aliases.

Examples

Example #1 An expanded mysql_fetch_assoc() example

$conn = mysql_connect ( «localhost» , «mysql_user» , «mysql_password» );

if (! $conn ) echo «Unable to connect to DB: » . mysql_error ();
exit;
>

if (! mysql_select_db ( «mydbname» )) echo «Unable to select mydbname: » . mysql_error ();
exit;
>

$sql = «SELECT id as userid, fullname, userstatus
FROM sometable
WHERE userstatus = 1″ ;

if (! $result ) echo «Could not successfully run query ( $sql ) from DB: » . mysql_error ();
exit;
>

if ( mysql_num_rows ( $result ) == 0 ) echo «No rows found, nothing to print so am exiting» ;
exit;
>

// While a row of data exists, put that row in $row as an associative array
// Note: If you’re expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you’ll
// then create $userid, $fullname, and $userstatus
while ( $row = mysql_fetch_assoc ( $result )) echo $row [ «userid» ];
echo $row [ «fullname» ];
echo $row [ «userstatus» ];
>

Notes

Note: Performance

An important thing to note is that using mysql_fetch_assoc() is not significantly slower than using mysql_fetch_row() , while it provides a significant added value.

Note: Field names returned by this function are case-sensitive.

Note: This function sets NULL fields to the PHP null value.

See Also

• mysql_fetch_row() — Get a result row as an enumerated array
• mysql_fetch_array() — Fetch a result row as an associative array, a numeric array, or both
• mysql_data_seek() — Move internal result pointer
• mysql_query() — Send a MySQL query
• mysql_error() — Returns the text of the error message from previous MySQL operation

User Contributed Notes 15 notes

It appears that you can’t have table.field names in the resulting array.
Just use an alias if your results come up empty and you are using multi-table query’s:

$res=mysql_query(«SELECT user.ID AS uID, order.ID AS oID FROM user, order WHERE ( order.userid=uID )»;
while ($row=mysql_fetch_assoc($res)) echo «

userid: $row[‘uID’], orderid: $row[‘oID’]

«;
>

Worth pointing out that the internal row pointer is incremented once the data is collected for the current row.

This means that multiple calls will iterate through the row data, so you DONT need to mysql_data_seek(..) between calls.

This is noted in the mysql_fetch_row() docs, but not here!?

Please be advised that the resource result that you pass to this function can be thought of as being passed by reference because a resource is simply a pointer to a memory location.

Because of this, you can not loop through a resource result twice in the same script before resetting the pointer back to the start position.

// Assume We Already Queried Our Database.

while( $queryContent = mysql_fetch_row ( $queryResult )

// We looped through the resource result already so the
// the pointer is no longer pointing at any rows.

// If we decide to loop through the same resource result
// again, the function will always return false because it
// will assume there are no more rows.

// So the following code, if executed after the previous code
// segment will not work.

while( $queryContent = mysql_fetch_row ( $queryResult )

// Because $queryContent is now equal to FALSE, the loop
// will not be entered.

The only solution to this is to reset the pointer to make it point at the first row again before the second code segment, so now the complete code will look as follows:

// Assume We Already Queried Our Database.

while( $queryContent = mysql_fetch_row ( $queryResult )

while( $queryContent = mysql_fetch_row ( $queryResult )

Of course you would have to do extra checks to make sure that the number of rows in the result is not 0 or else mysql_data_seek itself will return false and an error will be raised.

Also please note that this applies to all functions that fetch result sets, including mysql_fetch_row, mysql_fetch_assos, and mysql_fetch_array.

Thanks to to R. Bradley for the implode idea. The following fixes a few bugs and includes quote_smart functionality (and has been tested)

function mysql_insert_assoc ( $my_table , $my_array )

//
// Insert values into a MySQL database
// Includes quote_smart code to foil SQL Injection
//
// A call to this function of:
//
// $val1 = «foobar»;
// $val2 = 495;
// mysql_insert_assoc(«tablename», array(col1=>$val1, col2=>$val2, col3=>»val3″, col4=>720, col5=>834.987));
//
// Sends the following query:
// INSERT INTO ‘tablename’ (col1, col2, col3, col4, col5) values (‘foobar’, 495, ‘val3’, 720, 834.987)
//

// Find all the keys (column names) from the array $my_array
$columns = array_keys ( $my_array );

// Find all the values from the array $my_array
$values = array_values ( $my_array );

// Compose the query
$sql = «INSERT INTO $my_table » ;

// create comma-separated string of column names, enclosed in parentheses
$sql .= «(» . implode ( «, » , $columns ) . «)» ;
$sql .= » values » ;

// create comma-separated string of values, enclosed in parentheses
$sql .= «(» . implode ( «, » , $values ) . «)» ;

$result = @ mysql_query ( $sql ) OR die ( «
\nQuery: $sql UNsuccessful : » . mysql_error () . «\n
» );

return ( $result ) ? true : false ;
>
?>

Источник

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

Источник