CODEWITHSUNDEEP
phpmysqlarrayspdo
Alphaspida
Alphaspida

Reputation: 27

Need help returning an array of everything in a table of a database PHP/PDO

I want to return an array of everything found in a table of a database. The table has only 2 columns: id, and song_url. I would like the array to look like this:

Array {
    1 => songurl1,
    2 => songurl2,
    3 => songurl3
}

The keys are the id of each row and the values are the song_url of each row. I'm using PDO for this.

Thanks.

Edit (didn't see the edit button :s):

Here's my current code for doing this:

class Database {

protected $connected = false;
protected $dbh = null;
public $test = array();

function __construct($host=null, $user=null, $pass=null, $db=null) {
    if ($host && $user && $pass && $db) {
        try {
            $this->dbh = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
            $this->connected = true;
        } catch (PDOException $e) {
            echo "Could not connect to the database. Please contact an administrator.";
            $this->connected = false;
            exit();
        }
    } else {
        echo "No or not all variables are passed to the constructer. Contact an administrator.";
        $this->connected = false;
        exit();
    }
}

public function fetchAllSongs() {
    $q = $this->dbh->query("SELECT * FROM song_directories");
    $result = $q->fetch(PDO::FETCH_ASSOC);

    return $result; // this returns an array, yes, but only 1 row

}

}

Also, the return in fetchAllSongs only displays one row (when I use print_r()), and not all of the rows. Why is that?

Upvotes: 0

Views: 139

Answers (3)

Paul
Paul

Reputation: 6871

Look at the example on the manual page for query. In the foreach loop make your array:

$data[$row['id']] = $row['songurl'];

Don't use 2 queries for something as simple as this.

Your edited code is very close. Just change fetch to fetchAll, then rearrange the array as you want it.

Upvotes: 2

Nonym
Nonym

Reputation: 6299

How about this?:

$testArray = array();
for ($i = 0; $i<=count($rsArr)-1;$i++) {
    $testArray[$rsArr[$i]->id] = $rsArr[$i]->song_url;
}

Just to note: The danger here is if you have any repeating id values.

Upvotes: 0

Ayman Safadi
Ayman Safadi

Reputation: 11562

You can run 2 queries (one for each column), then use array_combine to create the array you want.

<?php
/*
$column_id       = // Get the ID column
$column_song_url = // Get the song_url column
*/

$result = array_combine($column_id, $column_song_url);
?>

Upvotes: 0

Related Questions