php - mysql在多个表中检索数据

Question

我有一个名为类别的表，它包含 2 列(id 主键，item)

根据项目名称，我还有另一个表

我使用foreach循环获取类别表中的数据，

在该循环中，每个项目都有一个表格。我试图检索这些数据，但我只得到一个表数据。

这是我的代码:

$con=mysql_connect('localhost','root','');
$db="testing";
$a=mysql_select_db($db,$con);
$c=mysql_query("select * from category");
$d=mysql_fetch_array($c);
mysql_data_seek($c,0);
foreach($d as $x)
{ 
    echo $x['item']." ";
    $e=mysql_query("select * from ".$x['item']);

        if($e)
        {
            $f=mysql_fetch_array(mysql_query("select * from ".$x['item']));
            mysql_data_seek($e,0);
            foreach($f as $y)
            { 
                echo $y['price']." ".$y['Quantity'];
            }
        }
        else continue;//break;
} 

Answer 1

我已尽力了解您希望从帖子中获得什么。此外，从 PHP 5.5.0 开始，mysql 已被弃用。你应该使用 mysqli 来代替。我的答案是用 mysqli 和 mysql 完成的。

结果:

Item - car
Price: 29000.00 Quantity: 5
Price: 18000.00 Quantity: 6
Item - house
Price: 50000.00 Quantity: 4

源代码 - 使用 mysqli:

$con=mysqli_connect('localhost','root','');
$db="testing";
$a=mysqli_select_db($con, $db);
$c=mysqli_query($con,"select * from category");
while($x = mysqli_fetch_array($c)){
    echo "Item - ".$x['item']."<br/>";
        $e=mysqli_query($con,"select * from ".$x['item']);
        if($e)
        {
            while($y = mysqli_fetch_array($e)){
                echo "Price: ".$y['price']." Quantity: ".$y['Quantity']."<br/>";
            }       
        }
        else continue;//break;  
}

源代码 - 使用 mysql:

$con=mysql_connect('localhost','root','');
$db="testing";
$a=mysql_select_db($db, $con);
$c=mysql_query("select * from category");
while($x = mysql_fetch_array($c)){

    echo "Item - ".$x['item']."<br/>";
        $e=mysql_query("select * from ".$x['item']);
        if($e)
        {
            while($y = mysql_fetch_array($e)){
                echo "Price: ".$y['price']." Quantity: ".$y['Quantity']."<br/>";
            }       
        }
        else continue;//break;  
}

SQL 转储:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `car`
--

CREATE TABLE IF NOT EXISTS `car` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `price` decimal(10,2) NOT NULL,
  `Quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `car`
--

INSERT INTO `car` (`id`, `price`, `Quantity`) VALUES
(5, 29000.00, 5),
(6, 18000.00, 6);

-- --------------------------------------------------------

--
-- Table structure for table `category`
--

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`id`, `item`) VALUES
(1, 'car'),
(2, 'house');

-- --------------------------------------------------------

--
-- Table structure for table `house`
--

CREATE TABLE IF NOT EXISTS `house` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `price` decimal(10,2) NOT NULL,
  `Quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `house`
--

INSERT INTO `house` (`id`, `price`, `Quantity`) VALUES
(2, 50000.00, 4);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;