CODEWITHSUNDEEP
phpmysqljsonmysqli
Evgenij Reznik
Evgenij Reznik

Reputation: 18594

Getting entire content from table

I have a table structure like this:

id,name,age,etc...

I want to get all entries and display them on my website in a table structure, just like they are organized in the MYSQL table itself:

| id  | name  | age |
| --- | ----- | --- |
| 1   | Peter | 23  |
| 2   | Susan | 34  |
| 3   | Tom   | 45  |

With the following code I can get all names:

$query = "SELECT * FROM users"; 

if ($result = $link->query($query)) {
    while($row = $result->fetch_assoc()){
        echo "name: "  . $row['name'] . "<br />";
    }
}

This is the result:

name: Peter
name: Susan
name: Tom

But I need to specify each column separately with $row['name']. What if there are >100 columns, do I need to specify each of them?

Is there any way to just get all data from my table (incl. all column names) in a JSON response:

{"users":[
    {"id":"1", "name":Peter", "age":"23"},
    {"id":"2", "name":"Susan", "age":"34"},
    {"id":"3", "name":"Tom", "age":"45"}
]}

Upvotes: 1

Views: 69

Answers (4)

AnkiiG
AnkiiG

Reputation: 3488

Try as below :

$query = "SELECT * FROM user";
$all_users = array();
if ($result = $link->query($query)) {
    $users = array();$i=0;
    while($row = $result->fetch_assoc()){
        $users[$i]['id'] = $row['id'];
        $users[$i]['name'] = $row['name'];
        $users[$i]['age'] = $row['age'];
        $i++;
    }
}
$all_users['users'] = $users;
echo json_encode($all_users);

Upvotes: 0

Rubin Porwal
Rubin Porwal

Reputation: 3845

$query = "SELECT * FROM users"; 
$result = mysqli_query($link, $query);
$users=array();
$response=NULL;
if ($result = $link->query($query)) {
    while($row = $result->fetch_assoc()){
       $users['users'][]=$row;
    }
   $response=json_encode($users);
}

Upvotes: 0

Wai Yan Hein
Wai Yan Hein

Reputation: 14791

You can use foreach loop like this

foreach($result as $column=>$value)
{
   echo $column." : ".$value;
}

But the point why are you worrying for 100 columns in a table ? According to normalization, you will not have so many columns in a single table. But even if you have, your column name will not be beautiful to display user because of naming convention like column_name.

Use json_encode to convert to json.

Upvotes: 0

Defiant
Defiant

Reputation: 156

Yes, just use something like:

 foreach($row as $colName => $colValue)

Inside your while loop.

Upvotes: 4

Related Questions