从两个MYSql表中获取数据并显示为HTML表

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'.

我有两个表,第一个是'用户'表,其中有一个名为'store'的列,我还有一个名为'stores'的表,其中包含列'store number''store location'。

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

users表中的“store”列是“商店编号”。

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

我要做的是创建一个类似的HTML表

Sample data:

Store number: 34 Store location: London Users: 34

店铺数量:34店铺位置:London用户: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.

所以它会像商店中的select *和每个创建新行。

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

并且对于用户的数量,例如来自用户的sum *,其中'store'='store number'来自stores表。

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 (

CREATE TABLE IF NOT NOT EXISTS用户(

  `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 ;

4 个解决方案

#1

Try this SQL:

试试这个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.

打印用户数量。

#2

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>";

#3

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.

如果您可以提供精确的表格结构,我可以更准确地构建它。

#4

-1

Something like this:

像这样的东西:

<table width="100%">
                                        <tr>
                                            <td>Store Number</td>
                                            <td>Store Location</td>
                                            <td>Number of Users at this store</td>
                                        </tr>

                                        <tr>

                                                <?php
                $found = 0;
                                                        $res = $info->get_info($r['info']);
                                                                while($r = mysql_fetch_array($res)){
                                                            ?>
                                                                    <td align="center">
                                          <?php echo $r['Store_Number']; ?>
                        </td>
                                        <td align="center">
                                          <?php echo $r['Store_location']; ?>
                        </td>
                                        <td align="center">
                                          <?php echo $r['user_Number']; ?>
                        </td>
</tr>
<?php
                                            $found++;   } if ($found == 0) {  print '<tr><td align="center"</td>'; }
                                                        ?>


</table>

#1