Fetch data from two MYSql tables and display as HTML table

Question

I have two tables, the first is a 'users' table which has a column called 'store' and I also have a table called 'stores' with columns 'store number' 'store location'.

The column 'store' in the users table is a 'store number'.

What I'm trying to do is create a HTML table that is something like

Sample data:

Store number: 34 Store location: London Users: 34

Store Number | Store Location | Number of Users at this store|

So it would be something like select * from stores and for each create new row.

and for the number of users be something like sum * from users where 'store' = 'store number' from stores table.

I hope this makes sense,

Jack.

UPDATE:

This is correct:

$result = mysql_query("SELECT * FROM stores", $con) or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Store Number</th> <th>Store Location</th> <th>Number of Users</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    echo "<tr><td>"; 
    echo $row['storenumber'];
    echo "</td><td>"; 
    echo $row['location'];
    echo "</td><td>"; 
    echo 'Amount of users here';
    echo "</td></tr>"; 
} 

echo "</table>";

Tables:

CREATE TABLE IF NOT EXISTS `stores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `storenumber` int(11) NOT NULL,
  `location` varchar(40) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

CREATE TABLE IF NOT EXISTS users (

  `id` int(50) NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `store` int(11) NOT NULL,
  `lastvisit` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;

One second i will update....

Jack
– Jack

2013-01-05 15:43:38 +00:00
Commented Jan 5, 2013 at 15:43 — Jack
– Jack, Commented Jan 5, 2013 at 15:43
Please update with tables structure and sample data.

Suresh Kamrushi
– Suresh Kamrushi

2013-01-05 15:47:16 +00:00
Commented Jan 5, 2013 at 15:47 — Suresh Kamrushi
– Suresh Kamrushi, Commented Jan 5, 2013 at 15:47

Erik Ekman · Accepted Answer · 2013-01-05 16:26:10Z

1

Try this SQL:

SELECT storenumber, location, COUNT(users.store) as nbr_users FROM stores
LEFT JOIN users ON stores.storenumber = users.store
GROUP BY store.id

and then do

echo $row['nbr_users'];

to print the number of users.

edited Jan 5, 2013 at 16:26

answered Jan 5, 2013 at 15:56

Erik Ekman

2,08612 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Erik Ekman Over a year ago

I have updated my solution. See sqlfiddle here also: sqlfiddle.com/#!2/f7a28/3

Erik Ekman Over a year ago

Please update the question with the CREATE TABLE statements for your two tables. And first you write 2 with '140' and then '140' is not showing??

Erik Ekman Over a year ago

I updated the SQL again, please use COUNT(users.store) as above.

Jack Over a year ago

Updated my post with create tables, your count update didnt change anything :(

Erik Ekman Over a year ago

users table is missing the store field in your table.. Also I changed GROUP BY operator to unique id field, may help with your missing 140.

|

Ghabriel Nunes · Accepted Answer · 2013-01-05 16:03:31Z

Try this:

$result = mysql_query("SELECT * FROM stores", $con) or die(mysql_error());
echo "<table border='1'>";
echo "<tr> <th>Store Number</th> <th>Store Location</th> <th>Number of Users</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
    // gets the total amount of users
    $query = mysql_query("SELECT COUNT(*) AS total FROM users WHERE `store`='".$row['storenumber']."'") or die( mysql_error() );
    $r = mysql_fetch_array( $query );
    $total = $r['total'];
    unset($query, $r);

    // Print out the contents of each row into a table
    echo "<tr><td>"; 
    echo $row['storenumber'];
    echo "</td><td>"; 
    echo $row['location'];
    echo "</td><td>"; 
    echo $total;
    echo "</td></tr>"; 
} 

echo "</table>";

Suresh Kamrushi · Accepted Answer · 2013-01-05 16:09:19Z

0

You can try this-

$result = mysql_query("Select count(user_id) as CNT,storenumber,location from store Left join user ON user.store_number = store.store_number group by store.store_number", $con) or die(mysql_error());
$row = mysql_fetch_assoc($result );

echo "<table border='1'>";
echo "<tr> <th>Store Number</th> <th>Store Location</th> <th>Number of Users</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    echo "<tr><td>"; 
    echo $row['storenumber'];
    echo "</td><td>"; 
    echo $row['location'];
    echo "</td><td>"; 
    echo $row['CNT'];
    echo "</td></tr>"; 
} 

echo "</table>";

If you can provide with exact table structure i can frame it more accurately.

answered Jan 5, 2013 at 16:09

Suresh Kamrushi

16.2k13 gold badges79 silver badges93 bronze badges

1 Comment

Erik Ekman Over a year ago

SUM() will give the sum of userids, not the number of them. Userid 1 and 2 will give 3 in this example.

Collectives™ on Stack Overflow

Fetch data from two MYSql tables and display as HTML table

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related