php - How to set the sql results index value by desired column name in mysql?

How can I set the sql results index value by desired column name in mysql? For example

Table name: country

create table query is as follows:

{-code-2}

and insert data to table is as follows:

{-code-3}

In result will show like this way:

If I execute the following query and my desired result as bellow:

{-code-4}

Desired result:

{-code-5}

I need the array keys will be the code by direct SQL query. Is it possible?

104

Answer

812

Answer

------+

360

Answer

+ | id | code | name | +

465

Answer

220

Answer

------+

605

Answer

+ | 1 | 88 | Bangladesh | +

318

Answer

------+

Answer

+ | 2 | 966 | Saudi Arabia | +

738

Answer

563

Answer

------+

991

Answer

+ | 3 | 967 | Yemen | +

650

Answer

------+

318

Answer

+ | 4 | 963 | Syria | +

766

Answer

882

Answer

------+

277

Answer

+ | 5 | 249 | Sudan | +

Answer

615

Answer

------+

583

Answer

+|||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;|||insert into `country`(`id`,`code`,`name`) values (1,88,'Bangladesh'), (2,966,'Saudi Arabia'),(3,967,'Yemen'),(4,963,'Syria'),(5,249,'Sudan');|||select code, name from country order by code ;|||[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 )

469

Answer

Solution:

Very easy to do this using , its method and the build of the keys you need using the function, as described in these steps:

Connect to the DB using DSN, username and password.
Query the DB.
Manipulate the result.

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.

450

Answer

Solution:

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;
}

Answer

Solution:

    $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);
    }

People are also looking for solutions to the problem: php - Merge/Replace associative rows from one array with the associative rows of another array

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question