CODEWITHSUNDEEP
phpmysql
user2289881
user2289881

Reputation: 73

Update a table with the average from another table - MySQL

I have two tables (ca and average_table).

ca has three columns: Group_id, Group_name, ca_value
average_table has two columns: Group_name, ca_value

I want to calculate the ca_value average of all groups in ca and update *average_table* with this data.

This is what I have

    //create average table
$average_table="CREATE TABLE average_table(Group_name CHAR(30) Primary Key, ca_value FLOAT)";

// Execute query
if (mysql_query($average_table))
  {
  echo "Table average created successfully";
  }
else
  {
  echo "Error creating table: " . mysql_error();
  }

//Calculate average
$ca = mysql_query("Select AVG(ca_value) FROM ca");

$data_sql = "INSERT INTO average_table (Group_name, ca_value) Values ('Average all groups',$ca)";
// Execute query
if (mysql_query($data_sql))
  {
  }
else
  {
  echo "Error Selecting group: " . mysql_error();
  }

Upvotes: 1

Views: 519

Answers (1)

Adrian
Adrian

Reputation: 46552

mysql_query returns a result resource pointer, not the actual results of the query. You have to get the results out using mysql_fetch_array, mysql_fetch_assoc, etc. Also, mysql_* is deprecated; please consider switching to mysqli or PDO. Per the manual at https://www.php.net/manual/en/function.mysql-query.php:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

Upvotes: 1

Related Questions