Extract all the data from a database

Question

Hey, I am wondering how to extract the data from a table in a database onto a table in a page (users.php),

For example: I want to be able to get all of the usernames and all the id's from my database onto a table. So if I have in my database:

1 - Fred

2 - Frank

3 - Margret

It will see that I have them user's and id's in the database and print them onto a table.

Any help would be great, Thanks.

Answer 1

You can do something like the following (using the built-in PHP MySQL functions):

// assuming here you have already connected to the database

$query = "SELECT id,username FROM users";
$result = mysql_query($query, $db);
while ($row = mysql_fetch_array($result))
{
  print $row["id"] . " - " . $row["username"] . "\n";
}

which will give you (for example):

1 - Fred
2 - Frank
3 - Margret

Where I've put the print statement, you can do whatever you feel like there eg put it into a table using standard HTML etc.

Answer 2

Connect to your database. Host is the location, like localhost if its on your computer, or on the same server as your code. User and Password are self explanatory.

mysql_connect("host", "user", "pass");

The name of the database you want to access.

mysql_select_db("database");

The actual mysql query.

$result = mysql_query('SELECT `User_Name`, `User_ID` FROM TABLE');

Sort it into an array

while($temp = mysql_fetch_array($result)
{
    $id = $temp['User_ID'];
    $array[$id]['User_ID'] = $id;
    $array[$id]['User_Name'] = $temp['User_Name'];
}

Turn the array into a table. (You could skip the last step and go right to this one.

$html ='<table><tr><td>User ID</td><td>User Name</td></tr>';
foreach($array as $id => $info)
{
    $html .= '<tr><td>'.$info['User_ID'].'</td><td>'.$info['User_Name'].'</td></tr>';
}
echo $html . '</table>';

Or, the formatting you wanted

$html ='User Id - User Name';
foreach($array as $id => $info)
{
    $html .= $info['User_ID'].' - '.$info['User_Name'].'<br>';
}
echo $html;

Answer 3

(For this answer, I will use the mysqli extension -- you could also want to use PDO ;; note that the mysql extension is old and should not be used for new applications)

You first have to connect to your database, using mysqli_connect (And you should test if the connection worked, with mysqli_connect_errno and/or mysqli_connect_error).

Then, you'll have to specifiy with which database you want to work, with mysqli_select_db.

Now, you can send an SQL query that will select all data from your users, with mysqli_query (And you can check for errors with mysqli_error and/or mysqli_errno).

That SQL query will most likely look like something like this :

select  id, name
from your_user_table
order by name

And, now, you can fetch the data, using something like mysqli_fetch_assoc -- or some other function that works the same way, but can fetch data in some other form.

Once you have fetched your data, you can use them -- for instance, for display.

Read the pages of the manual I linked to : many of them include examples, that will allow you to learn more, especially about the way those functions should be used ;-)

For instance, there is a complete example on the page of mysqli_fetch_assoc, that does exactly what you want -- with countries insteand of users, but the idea is quite the same ^^