How can I set the sql results index value by desired column name in mysql? For example
Table name: country
+-----------+----------------+--------------------+
| id | code | name |
+-----------+----------------+--------------------+
| 1 | 88 | Bangladesh |
+-----------+----------------+--------------------+
| 2 | 966 | Saudi Arabia |
+-----------+----------------+--------------------+
| 3 | 967 | Yemen |
+-----------+----------------+--------------------+
| 4 | 963 | Syria |
+-----------+----------------+--------------------+
| 5 | 249 | Sudan |
+-----------+----------------+--------------------+
create table query is as follows:
CREATE TABLE `country` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` int(20) NOT NULL,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
and insert data to table is as follows:
insert into `country`(`id`,`code`,`name`) values (1,88,'Bangladesh'),
(2,966,'Saudi Arabia'),(3,967,'Yemen'),(4,963,'Syria'),(5,249,'Sudan');
In result will show like this way:
If I execute the following query and my desired result as bellow:
select code, name from country order by code ;
Desired result:
[88] => Array
(
[code] => 88,
[name] => Bangladesh
)
[249] => Array
(
[code] => 249,
[name] => Sudan
)
[963] => Array
(
[code] => 963,
[name] => Syria
)
[966] => Array
(
[code] => 966,
[name] => Saudi Arabia
)
[967] => Array
(
[code] => 967,
[name] => Yemen
)
I need the array keys will be the code by direct SQL query. Is it possible?
Very easy to do this using PDO, its PDOStatement::fetchAll() method and the build of the keys you need using the array_column() function, as described in these steps:
The code may look something like this:
const DB_DSN = "mysql:host=mysql.hostname.com;dbname=yourdb;port=3306";
const DB_USERNAME = "username";
const DB_PASSWORD = "P4ssW0rd!";
try
{
$db = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$results = $db->query("SELECT code, name FROM country ORDER BY code")->fetchAll(PDO::FETCH_ASSOC);
$results = array_column($results, null, "code");
}
catch ( PDOException $e )
{
throw new Exception("An error occurred during the interaction with the database (" . DB_DSN . ").", 0, $e);
}
var_dump($results);
Which will give you exactly the output you desire.
It's a simple loop reading the results:
$result = mysqli_query($db,$query);
$array = array();
while ($row = mysqli_fetch_assoc($result)) {
$array[$row['code']] = $row;
}
$servername = "localhost";
$username = "root";
$password = "root";
// Create connection
$conn = mysqli_connect($servername, $username, $password,'test');
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}else{
$query = 'SELECT code, name FROM country ORDER BY code';
$result = mysqli_query($conn,$query);
$result = mysqli_fetch_all($result,MYSQLI_ASSOC);
$result = array_column($result, null, "code");
echo "<pre>"; print_r($result);
}