[SOLVED] mysql table join help

[bbobchef]

Hi I am new to php and mysql, I have built a simple program and am having a problem trying to get a query working, the query uses two tables and avg one column

 

Here is the query

 

<?php
include 'datalogin.php'; 

$query = "SELECT AVG(rate.rating), cars.name_, cars.engine, cars.wheels,  FROM rate, ratings WHERE cars.name_ = rate.name_ GROUP BY name_ ORDER BY AVG(rate.rating) ";

$result = mysql_query($query) or die(mysql_error());
while($values = mysql_fetch_row($result)){
      $name_ = $values[0];
      $rating = number_format($values[1], 2, '.', '');

echo "$name_ $engine $wheels User Rating $rating <br>\n";
echo "<br />";
}

?>

 

It is supposed to call car name, engine, wheels from database cars and avg the rating column from the rate table to 2 decimal places. It takes the rating but doesn’t show the info from the first table.

 

Any help would be much appreciated.

 

[bbobchef]

Server version: 5.0.67-log

 

Protocol version: 10

 

MySQL client version: 5.0.16

 

 

CREATE TABLE `rate` (

`uid` int(11) unsigned NOT NULL auto_increment,

`item_id` varchar(30) NOT NULL,

`name_` varchar(30) NOT NULL,

`rating` tinyint(4) NOT NULL,

PRIMARY KEY  (`uid`)

) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=utf8

 

 

CREATE TABLE `cars` (

`uid` int(11) unsigned NOT NULL auto_increment,

  `name_` varchar(30) NOT NULL,

`img` varchar(30) NOT NULL,

`url` varchar(30) NOT NULL,

`wheels` varchar(30) NOT NULL,

`engine` varchar(30) NOT NULL,

`audio` varchar(30) NOT NULL,

`color` varchar(30) NOT NULL,

PRIMARY KEY  (`uid`)

) ENGINE=MyISAM AUTO_INCREMENT=133 DEFAULT CHARSET=utf8

 

[Gamic]

You don't include the cars table. You instead include rate and ratings.

 

SELECT 
AVG(rate.rating), 
cars.name_, 
cars.engine, 
cars.wheels
FROM 
rate inner join cars on rate.name_ = cars.name_
GROUP BY cars.name_, cars.engine, cars.wheels
ORDER BY AVG(rate.rating)

[bbobchef]

thanks for the reply

 

I tried

 

 

<?php
include 'datalogin.php'; 

$query = "SELECT
   AVG(rate.rating),
   cars.name_,
   cars.engine,
   cars.wheels
FROM
   rate inner join cars on rate.name_ = cars.name_
GROUP BY cars.name_, cars.engine, cars.wheels
ORDER BY AVG(rate.rating)";

$result = mysql_query($query) or die(mysql_error());
while($values = mysql_fetch_array($result)){
      $name_ = $values[0];
      $rating = number_format($values[1], 2, '.', '');
echo " $rating $name_ $engine $wheels ";
}

?>

 

but can't get any output from the statement, perhaps it is my php?

[xtopolis]

If you did a print_r($values) I'm fairly certain the values wouldn't map to numerical indexes..

 

Try using their associative names; the names of the columns.  Also, modify the query to use column alias:

 

$query = "SELECT
   AVG(rate.rating) as avgRating,
   cars.name_ as name,
   cars.engine as engine,
   cars.wheels as wheels
FROM
   rate inner join cars on rate.name_ = cars.name_
GROUP BY cars.name_, cars.engine, cars.wheels
ORDER BY AVG(rate.rating)";

 

And/or access them like this:

while(list($avgRating,$name,$engine,$wheels) = mysql_fetch_array($result)){
      $avgRating = number_format($avgRating, 2, '.', '');
   echo " $avgRating $name $engine $wheels ";
}

 

Didn't test, but should be close.

[bbobchef]

thats works great...

 

thanks so much