Good evening guys, I'm in the following situation,
I have a table called cars, and each car can be evaluated by different users, so I have created a secondary table called ratings and each car can be evaluated by several different users.
Now I need every car to show an overall rating, which is to get all ratings from all users on a given car and show up on a list with all the cars.
That's my question, how to recover all evaluations from the evaluation table.
I inserted the Table to better understand, I will get only the sum of the general evaluation and how many people evaluated the same car example
GOLF - 2 Ratings - 7 points
$sql = $conex->query("SELECT * FROM carros");
while($rs=$conex->result($sql)){
$id_car=$rs['id_car'];
$nome_car=$rs['nome_car'];
};
// Table -------------------------------------------- --------------------
CREATE TABLE IF NOT EXISTS 'avaliacao' (
'id_ava' int(11) NOT NULL,
'id_car_ava' int(11) NOT NULL,
'id_usu_ava' int(11) NOT NULL,
'dirigibilidade_ava' int(11) NOT NULL,
'estabilidade_ava' int(11) NOT NULL,
'potencia_ava' int(11) NOT NULL,
'consumo_ava' int(11) NOT NULL,
'geral_ava' int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela 'avaliacao'
--
INSERT INTO 'avaliacao' ('id_ava', 'id_car_ava', 'id_usu_ava', 'dirigibilidade_ava', 'estabilidade_ava', 'potencia_ava', 'consumo_ava', 'geral_ava') VALUES
(1, 1, 2, 4, 2, 2, 4, 4),
(2, 1, 6, 4, 4, 5, 5, 5),
(3, 2, 7, 5, 5, 5, 4, 3),
(4, 3, 9, 4, 4, 4, 4, 3);
-- --------------------------------------------------------
--
-- Estrutura da tabela 'carros'
--
CREATE TABLE IF NOT EXISTS 'carros' (
'id_car' int(11) NOT NULL,
'model_car' varchar(200) NOT NULL,
'ano_car' int(12) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
--
-- Extraindo dados da tabela 'carros'
--
INSERT INTO 'carros' ('id_car', 'model_car', 'ano_car') VALUES
(1, 'Golf', 2000),
(2, 'Pálo', 1998),
(3, 'Jeta', 2015),
(4, 'Peugeout 206', 2006);
--
-- Indexes for dumped tables
--
--
-- Indexes for table 'avaliacao'
--
ALTER TABLE 'avaliacao'
ADD PRIMARY KEY ('id_ava');
--
-- Indexes for table 'carros'
--
ALTER TABLE 'carros'
ADD PRIMARY KEY ('id_car');
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table 'avaliacao'
--
ALTER TABLE 'avaliacao'
MODIFY 'id_ava' int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table 'carros'
--
ALTER TABLE 'carros'
MODIFY 'id_car' int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;
/*!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 */;