setting field of database equal to another....

pointsplat

Heres the deal I have a teacher rating system in which I have 2 tables....1 table holds all the teacher info including their ratings and NUMBER OF COMMENTS, and the other table contains the comments and information on those.

Currently the number of comments field in the 1 table is just increased everytime someone adds a comment (via PHP/SQL). My problem is I know I will be doing alot of deleting of comments via phpmyadmin until I make up an admin center for me to administer the comments with.

Before I do that is there a way to make one field in a certain table the "num_comments" field equal to the total number of rows that match a certain query and have this update everytime that the number of matches does?

Not sure if im clear if nim not please someone let me know.

timj

Isn't there an SQL solution for this?
I suppose you could use a trigger (if you can use triggers in your database). This trigger would work "on each update", setting the number in the num_comments field to the number of rows returned by a certain query.
The "update" itself does not have to do anything (or can be overwritten by the trigger if you use a trigger that executes "after" each update).

See your SQL manual on triggers if you need more info.

drawmack

In my database classes, I've had 5 of em, one thing that they beat us over the head with was do not store that which you can calculate. It leads to problems.

Instead of storing the number of comments use the count command here is an example working on hypothetical tables.

Table Structure:

CREATE TABLE IF NOT EXISTS teachers (
  ID BIGINT NOT NULL auto_increment,
  fName VARCHAR(50) NOT NULL,
  lName VARCHAR(50) NOT NULL,
  School VARCHAR(100) NOT NULL,
  PRIMARY KEY (ID),
  INDEX fName (fName),
  INDEX lName (lName),
  INDEX School (School)
);

CREATE TABLE IF NOT EXISTS comments (
  ID BIGINT NOT NULL auto_increment,
  TeachID BIGINT NOT NULL,
  Posted DATE NOT NULL,
  Edited  DATE NOT NULL,
  Poster  VARCHAR(50) NOT NULL,
  Cmnt    TEXT,
  PRIMARY KEY (ID),
  INDEX Edited (Edited),
  INDEX Posted (Posted)
);

Assuming you want to list all of the teachers with the number of comments they have received you would do something like this.

$sql  = "SELECT a.fName,a.lName,a.School,COUNT(b.ID) ";
$sql .= "FROM teachers as a, comments as b ";
$sql .= "WHERE a.ID = b.TeachID ";
$sql .= "GROUP BY b.TeachID ";
$sql .= "ORDER BY a.School";

This has the advantage of not needing to keep the number of comments field updated because it is updated when there is a change to the comments table.

pointsplat

drawmack

Originally posted by pointsplat
If I do it via code in the page when it loads it would bog things down trying to count and set the field for each teacher.

No it won't and storing this information in the database is just bad form. It will lead to problems and errors. Go read a book on database deisn and normalization. The higher the degree of normalization the better and you can't get past second normal form if you're doing this in a database.

Here are some resources for you:
http://www.databasejournal.com/sqletc/article.php/1428511
http://www.phpbuilder.com/columns/barry20000731.php3
http://databases.about.com/library/weekly/aa080501a.htm

Also by doing the work right in sql, as I proposed above, you'll be very pleasantly surprised with the speed. I'll even include the queries below to do this on your database.

 $sql  = "SELECT a.prof_name,a.department,a.prof_id,count(b.comment_id) ";
 $sql .= "FROM teachertable as a, teachercomments as b ";
 $sql .= "WHERE a.prof_id = b.prof_id ";
 $sql .= "GROUPD BY b.prof_id ";
 $sql .= "ORDER BY a.department,a.prof_name";

If you think the performance hit for this is going to be that bad then run it and see. As long as your tables are indexed properly (i.e. prof_name, department & prof_id are all indexed) this query will run pretty fast.

pointsplat

drawmack

 $sql  = "SELECT a.prof_name,a.department,a.prof_id,count(b.comment_id) as num_comments";
 $sql .= "FROM teachertable as a, teachercomments as b ";
 $sql .= "WHERE a.prof_id = b.prof_id ";
 $sql .= "GROUPD BY b.prof_id ";
 $sql .= "ORDER BY num_comments,a.department,a.prof_name";

pointsplat

Woohoo got it working! Only one slight problem, with this it will only display the teachers that have comments (ones that have rows created) I need it to display everyone. Any suggestions?

Grant

drawmack

hmmm, since you want it to sort by number of comments how about two queries then?

run the query you have now and along with normal processing but the prof_id into an array called $hasComments then do this

$sql  = "SELECT prof_name,department,prof_id  ";
$sql .= "FROM teachers ";
$sql .= "WHERE prod_id NOT IN (" . implode(',',$hasComments) . ") ";
$sql .= "ORDER BY prof_name";

timj

You can also accomplish this directly in SQL by using the Union operator.
Syntax:
query1 union query2
Where query1 is the query you use for getting the profs with their number of comments and query2 is the query from the previous post of drawmack, with only 1 slight adjustment:
SELECT prof_name,department,prof_id
must have a fourth field, like:
SELECT prof_name,department,prof_id,0
(this will display "0" as number of comments for the profs that have no comments.
The fourth field in the select is required because the first query also has 4 fields (for a union operator to work, both queries must have the same amount and type of fields). Since the number of comments is probably an integer, I'd advise you to take an integer value like 0 for the fourth field of query2.

pointsplat

timj

Sorry, my mistake. I should also have told you that the second query needs to be different.

The goal is that the first query searches all your profs, counting their number of comments (in the second table). Both tables (a and b) are joined by what is called an inner-join, which eliminates every element of a (every prof) that has no comments (in table b).
The union statement will combine the results of this with the second query, that will give a zero in the column of output that specifies the number of comments. But the second query thus needs to search all the profs that DON'T have any comments (no corresponding rows in b). So for the second query, you can't use the join "a.prof_id=b.prof_id", since this will eliminate all the profs that don't have comments (it's the same inner-join as in the first query).
Your second query must therefor be something like:
select a.*,0
from teachers as a

where a.prof_id not in (select profid from teacher_comments)

order by a.prof_name;
(you'll need the review this for correct syntax of table and field names, I'm just improvising it).

Note: a field or column of output is an element in the "select" part of your query, so it appears in the comma separated list between the keywords "select" and "from".

pointsplat

timj

Do you know the SQL error that is generated?

I think it might be due to your "order by" clauses. They have a habit of failing when working with a union. It would be better to omit the order by clause in the first query. I think you may only specify 1 order by clause for the entire union-ed query, at the end of query 2.
Note that - for simplicity - you should specify the order by clause not as "order by a.somefield" (since it is confusing to your database to which a it refers - the a from your first query or from the second). Instead, use something like "order by 1" to order by the first column of output (if prof_name is the first field in table a (a.*). If it turns out that it's not column 1 but some other column that needs to be sorted on, just replace the "1" with the right column number.

timj

Here's a little tip:
can you test the query in your DB before you implement it in PHP?
I'm not experienced with MySQL (I use Oracle normally), but I suppose it will give you a better description of what's wrong with the query.

timj

I don't know if MySQL uses a slightly different SQL syntax, but I think the following is wrong:
FROM teachers as a,...
Shouldn't it be
FROM teachers a,... (no "as")?

timj

You should also change the Order by clause, so that it does not mention table "a". Does table b also store the prof_name? I think not (because if it does, the prof_name is stored twice and redundancy is bad DB design!).

Let's say that table a (teachers) has 3 fields, prof_id, prof_name and the department. To order on both the number of comments and on the prof_name (in that order), you could specify something like:
ORDER BY 4 ASC,prof_name ASC (assuming b has no prof_name).
Since table a has only 3 columns (fields), the 4th output column will be the column with the number of comments. If you happen to know that prof_name is let's say the second column in a.*, then you could also do: ORDER BY 4 ASC,2 ASC.

Normally, the "a.prof_name" should not give any problems (it is semantically correct SQL), but perhaps there is some confusion for MySQL.

Please note also that, since ascending is the normal order method (at least in Oracle), you don't need to specify it. But you can, it won't harm.

But hmmm, this seems to have become more of an SQL debate than a PHP forum...
😉