Mysql query result to php 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.

Источник

create array from mysql query php

I have a little problem that I don’t understand. I have a db that has an owner and type (and more off course). I want to get a list of all the type values that has owner equal to the current user, but I get only two result

$sql = "SELECT type FROM cars WHERE owner='".mysql_real_escape_string($_SESSION['username'])."' AND selling='0' ORDER BY id DESC "; $result = mysql_query($sql,$con); print_r(mysql_fetch_array($result)); 

$sql = "SELECT type FROM cars WHERE owner='".mysql_real_escape_string($_SESSION['username'])."' AND selling='0' "; 

And the result should be something like 19, 19, 18, 17, 16 in an array. Thats all the types that has me as set as owner. I have got this working now:

Here I print out all the values correctly, but I need to create an array with all the values. I though I could use array_push, but there most be a better way of doing it. I thought I would get all the type values with a simple mysql query.

7 Answers 7

Very often this is done in a while loop:

$types = array(); while(($row = mysql_fetch_assoc($result)))

Have a look at the examples in the documentation.

The mysql_fetch_* methods will always get the next element of the result set:

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

That is why the while loops works. If there aren’t any rows anymore $row will be false and the while loop exists.

It only seems that mysql_fetch_array gets more than one row, because by default it gets the result as normal and as associative value:

By using MYSQL_BOTH (default), you’ll get an array with both associative and number indices.

Your example shows it best, you get the same value 18 and you can access it via $v[0] or $v[‘type’] .

THE CORRECT WAY ************************ THE CORRECT WAY while($rows[] = mysqli_fetch_assoc($result)); array_pop($rows); // pop the last row off, which is an empty row 

You do need to iterate through.

$typeArray = array(); $query = "select * from whatever"; $result = mysql_query($query); if ($result)

while($row = mysql_fetch_assoc($result))

His code is already doing this. He wants it to become an array. Echoing is not going to solve this problem.

You could also make life easier using a wrapper, e.g. with ADODb:

$myarray=$db->GetCol("SELECT type FROM cars ". "WHERE owner=? and selling=0", array($_SESSION['username'])); 

A good wrapper will do all your escaping for you too, making things easier to read.

$type_array = array(); while($row = mysql_fetch_assoc($result))

You may want to go look at the SQL Injection article on Wikipedia. Look under the «Hexadecimal Conversion» part to find a small function to do your SQL commands and return an array with the information in it.

I wrote the dosql() function because I got tired of having my SQL commands executing all over the place, forgetting to check for errors, and being able to log all of my commands to a log file for later viewing if need be. The routine is free for whoever wants to use it for whatever purpose. I actually have expanded on the function a bit because I wanted it to do more but this basic function is a good starting point for getting the output back from an SQL call.

Источник

get array of rows with mysqli result

I need to get all the rows from result object. I’m trying to build a new array that will hold all rows. Here is my code:

$sql = new mysqli($config['host'],$config['user'],$config['pass'],$config['db_name']); if (mysqli_connect_errno()) < printf("Connect failed: %s\n", mysqli_connect_error()); exit(); >$query = "SELECT domain FROM services"; $result = $sql->query($query); while($row = $result->fetch_row()); < $rows[]=$row; >$result->close(); $sql->close(); return $rows; 

$rows is supposed to be the new array that contains all, rows but instead I get an empty array. Any ideas why this is happening?

2 Answers 2

You had a slight syntax problem, namely an errant semi-colon.

while($row = $result->fetch_row()); 

Notice the semi-colon at the end? It means the block following wasn’t executed in a loop. Get rid of that and it should work.

Also, you may want to ask mysqli to report all problems it encountered:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); $sql = new mysqli($config['host'], $config['user'], $config['pass'], $config['db_name']); $query = "SELECT domain FROM services"; $result = $sql->query($query); $rows = []; while($row = $result->fetch_row()) < $rows[] = $row; >return $rows; 

Newest versions of mysqli have some improvements that can simplify such a task.

First, of all, there is a useful function to return an array with all rows returned by a query, mysqli_fetch_all()
It means in case you need a simple enumerated array, the code would be much simpler:

$query = "SELECT domain FROM services"; $result = $sql->query($query); return $result->fetch_all(MYSQLI_ASSOC); 

return $sql->query("SELECT domain FROM services")->fetch_all(MYSQLI_ASSOC); 

However, if you need to use some column to index the resulting array, you still need a while loop like this:

$query = "SELECT id, domain FROM services"; $result = $sql->query($query); $data = []; while ($row = $result->fetch_assoc())

Note that you should always initialize an array before filling it up, because such a variable could already exist.

Also, mysqli_result class is now Traversable. It means you can use it in the foreach loop right away, as though it’s an array contains all rows from the database:

$query = "SELECT domain FROM services"; $result = $sql->query($query); foreach ($result as $row)

But it is actually just a syntax sugar for the while loop — you cannot access values of this «array» directly, which makes this feature of a little use actually.

Obligatory notes.

This question is a decade old, and the way a connection is made and the query is performed, both in the question and the accepted answer, are obsoleted and frowned upon nowadays.

When a connection is made, there are several things to keep in mind. I wrote an article on how to connect with mysqli properly that provides a correct connection example emphasizing on the following issues:

• a proper error reporting mode must be set
• a proper character set must be set
• no manual error reporting code should be ever used (like die(mysqli_connect_error()) )
• a connection has to be made only once, in a separate file, and then just included into every script that needs a database interaction. in case a database code is used in a function, a connection variable must be passed in as a function parameter.

When it goes to running a query, there are several things to keep in mind as well:

• when even a single variable is used in the query, a prepared statementmust be used instead of mysqli_query()
• as a result, a special function called mysqli_stmt_get_result() should be used in order to use familiar fetch functions to get the resulting rows. In case this function is not available you are probably to tick some checkbox in your cpanel (look for one labeled mysqlnd ).
• given a prepared statement with mysqli, although being obligatory, takes a lot code to write, it is advised to use a helper function for mysqli that would perform most of work automatically and make a mysqli prepared statement a smooth as a regular query.
• no manual error reporting code should be ever used (like die(mysqli_error()) ). Thanks to the proper error mode, mysqli will report all errors automatically.

Источник