Selecting different variables from two mysql tables

Linking with ID

[s9901470]

Hi

I have a polling system storing votes for 50 polls.
I want to display the results to visitors, but I have stored them according to ID number in the results table. Their labels are stored in a different table.

I want to print:

Label (from polls)
Average rating (from polldata)
Total ratings (from polldata)

The two tables are linked by ID only.

The code below just prints the ID number and the average and total votes, and I don't know how to link up the labels from the polls table.

Any suggestions?

$query = "SELECT SUM(rating) AS total, AVG(rating) AS mean, id AS label
FROM polldata GROUP BY id";
$i = 0;
$ask = mysql_query($query) or die("Select Failed!");
while($row = mysql_fetch_array($ask))
{
echo "<p><strong>" . $row['label']. "</strong> ".$row['mean']." (".$row['total'].")<BR>";
}

[lammert]

You should modify the SQL query to search in the two tables at once. The following querystring might do the job:

SELECT polls.label AS label, SUM(polldata.rating) AS total, AVG(polldata.rating) AS mean FROM polls, polldata WHERE polls.ID = polldata.ID GROUP BY polldata.ID;

Maybe you have to tweak the code a little bit, because I didn't test this specific example myself, but you get the point I think. You also have to test performance. It may be necessary to add some indexes to the database to increase search speed. Also using foreign keys to be sure that every ID in polldata has a match in polls might be a good idea.

A very good way to learn to construct such more complex queries is using a graphical SQL query program. I myself use Microsoft Query, construct the connections between the tables with the mouse, and then look at the generated SQL code.

[s9901470]

Thanks, I will try that. What are foreign keys?

[lammert]

A foreign key is a relation you define between two tables. If you want high integrity of your database, a foreign key constraint tells the mySQL database to check the validity of the contents of records, everytime you perform an INSERT, UPDATE or DELETE action.

With a foreign key you define that a new record can only be inserted into table A, if the value of a specific field is present in table B. In your case, every time a record is written to `polldata`, a foreign key could check if the ID value in `polldata`.`ID` is already defined in `polls`.`ID`. If not, an error occurs. This is an automatic process inside the mySQL server. You don't need to add extra code to the client application to do the integrity check. Also, if you delete a record from `polls`, all entries from `polldata` with the same ID field will also be deleted.

One final remark, foreign keys are only properly implemented in the InnoDB data engine.

You can find more about foreign keys in the on-line mySQL manual at [dev.mysql.com...]

[s9901470]

Hi lammert

I've just tried your code and it works perfectly - thank you so much

:0)

[s9901470]

One quick question - how can I format the mean rating to two decimal places? It's currently printing a long string of numbers.
Thanks