CODEWITHSUNDEEP
phpmysqldatabasearrays
WittyPleb
WittyPleb

Reputation: 553

Display MySQL Database as an array

I have a MySQL database full of user information, like their username, password, email, etc.

I want a PHP script that allows me to pull JUST their username and display it like so:

"username1","username2","username3"

Literally exactly like that, the quotes and all.

EDIT: Sorry for not supplying enough information.

The table is named "users" the field I want to pull off it is "username" I can get it to pull and display all the information, my only problem is imploding it.

Upvotes: 1

Views: 17749

Answers (4)

Flukey
Flukey

Reputation: 6555

I've seen all the other answers, however have you considered using PDO instead of mysql_query functions? It's a much nicer way to work with the database.

Here's what you want to achieve in a few lines of code (using lamba functions):

 $dbh = new PDO("mysql:host=localhost;dbname=test", "yourusername", "yourpassword");
 $results = $dbh->prepare("SELECT u.username FROM users u");
 $results->execute();
 $results = $results->fetchAll();
 echo implode(", ", array_map(function(&$r) { return $r['username']; }, $results));

Output: Jamie, Bob, Chris Nice and clean. Also, you should check if you have any results that have been returned and if the query was successful.

Just another approach.

EDIT: I've just realised you're a beginner so my answer may be a bit too advanced. However, i'll leave it for others to see as a solution, and perhaps you might look into using PDO an lamba functions when you learn a bit more. Best of luck.

Upvotes: 2

hijarian
hijarian

Reputation: 2228

Let's assume that you have a 'mydb' database and 'users' table in it.

SQL needed:

USE mydb;
SELECT username from users;

Short version:

  1. Wrap it in PHP calls to mysql PHP library
  2. Get result as an array then implode it with comma symbol.

Long version:

First we need to connect to database:

$db = mysql_connect('DATABASE_HOST', 'USER', 'PASSWORD');
if (!$db) {
    die('Not connected : ' . mysql_error());
}

$db_selected = mysql_select_db('mydb', $db);
if (!$db_selected) {
    die ('Can\'t use mydb: ' . mysql_error());
}

Remember to always check the return values of functions.

Then we query the database:

$result = mysql_query('select username from users', $db);

...and fetch results in flat array (we need only usernames):

while ($row = mysql_fetch_array($result, MYSQLI_ASSOC))
{
    $data[] = $row['login'];
}

Then we format the returned data according to your specs:

$string_result = '"'. implode('", "', $data) . '"';

You can do with $string_result anything you want, just close the database connection immediately after use:

mysql_close($db);

Good luck with learning PHP, BTW. ;)

Upvotes: 1

Thomas Clayson
Thomas Clayson

Reputation: 29925

OK dude, read the comments

<?php // open a php tag

$dbc = mysql_connect("host", "username", "password"); // connect to database
mysql_select_db("db_name", $dbc) // select the database

$sql = "SELECT `username` FROM `users_table`"; // select only the username field from the table "users_table"
$result = mysql_query($sql); // process the query

$username_array = array(); // start an array

while($row = mysql_fetch_array($result)){ // cycle through each record returned
  $username_array[] = "\"".$row['username']."\""; // get the username field and add to the array above with surrounding quotes
}

$username_string = implode(",", $username_array); // implode the array to "stick together" all the usernames with a comma inbetween each

echo $username_string; // output the string to the display

?>

Upvotes: 5

Mark Byers
Mark Byers

Reputation: 838156

You could using PHP's implode, but it's probably easier just do it in SQL assuming that the list won't be too long:

SELECT GROUP_CONCAT(CONCAT('"', username, '"')) AS usernames
FROM your_table

Upvotes: 0

Related Questions