PHP and SQL, 1 Database - Two Tables, a dilemia

dglynch21

I have been looking online for what I thought would be a several extra lines of code. I am starting to think I might not be able to do that at all.

I am creating a open source discipline tracking tool for employers, I have one database and two tables personnel & discussions. Personnel has an unique id (5 digit number) and some other information. The discussions also has its own unique id (this one has 8 digits), some text, a point value, and a place to enter a "personnel" integer, basically the id of the person in the personnel table.

My dilemma comes to this point where I want to list all the employees, so far so good. Now I also want to SUM all their points up as well but that's in the other table. I haven't found any code that works. Below is what does work but with out SUMing up points. I want that to be output in place of NULL.

I am open to any thoughts and suggestions. Thank you all so much!

                <?php

$result = mysql_query("SELECT * FROM personnel");

while($row = mysql_fetch_array($result))
  { 
	echo "<table width=\"100%\" border=\"1\">";
	echo " <tr>";
	echo "<td colspan=\"2\">PDTS ID: " . $row['id'] . "</td>";
	echo " </tr>";
	echo "<tr>";
	echo "<td>".$row['fname']."</td>";
	echo "<td>".$row['middle']."</td>";
	echo " </tr>";
	echo " <tr>";
	echo "  <td>".$row['lname']."</td>";
	echo "  <td>NULL</td>";
	echo "  </tr>";
	echo "</table><br /> ";

  }
?>

Derokorian

First off - you should not use SELECT * to gather data, you should always list the columns you want to be listed. Second you could have a subquery to get the sum from the other table.

$result = mysql_query("SELECT id,fname,middle,lname,sum FROM personnel INNER JOIN (SELECT id,SUM(points) FROM discussion GROUP BY id) points on points.id=personnel.id");

dglynch21

I am by no means being disrespectful and I appreciate the help but I do have a question. The SELECT * is a shortcut to select all the columns, why not use it if you need to select all the columns? Would this not be an easy way to code it?

bradgrafelman

dglynch21;10990791 wrote:
The SELECT is a shortcut to select all the columns, why not use it if you need to select all the columns?

If you actually need to SELECT all columns, now and forever (such as in a 'INSERT INTO... SELECT FROM' type of query), then it might not be a bad idea.

However, more often than not this isn't the case. For example, can you predict the future and know that just because you're using data from every single column (you are, aren't you?) in the present that your needs will always adapt in the future with the table schema? Most likely not.

Also, say you're modifying a piece of code and you come across a query like:

SELECT * FROM users;

Can you answer all (or even any?) of these questions:

How many columns does that table have?
What are the names of the columns that you're using?
What type of data do you expect those columns contain?
How is data in this table referenced/related in other tables?

Assuming you came to the same conclusion I did (you can't answer any of those questions), you'll see that 'SELECT *' queries can actually do more harm than good.

Plus, we haven't even considered the possible performance impact introduced by making the SQL server first lookup the column names of the table since we didn't provide them in the query.

dglynch21;10990791 wrote:
Would this not be an easy way to code it?

"Easy"? Definitely. Does the "easy" way always (or even regularly) coincide with the "best", or "robust", or even "correct" wait? Not so much. :p

dglynch21

Got it, so performance and future improvements/changes to the code and/or the database all impact the decision NOT to use the SELECT * code. I went ahead and updated to your code revision, found out it important to add

or die("cannot execute query: " . mysql_error());

to the code. Now I am getting an saying

cannot execute query: Column 'id' in field list is ambiguous

Does MySQL want me to update the column name? This is what I updated the code to (some minor changes from yours)

<?php

$result = mysql_query("SELECT id,fname,middle,lname,sum FROM personnel INNER JOIN (SELECT id,SUM(point) FROM discussions GROUP BY id) point on point.id=personnel.id") or die("cannot execute query: " . mysql_error()); 

while($row = mysql_fetch_array($result))

Any clues or pointers to some reference material?

Derokorian

$result = mysql_query("SELECT id,fname,middle,lname,sum FROM personnel INNER JOIN (SELECT id as did,SUM(point) FROM discussions GROUP BY id) point on point.did=personnel.id") or die("cannot execute query: " . mysql_error());

Try that instead, my bad.

dglynch21

cannot execute query: Unknown column 'sum' in 'field list'

I attempted to remove the sum list here:

$result = mysql_query("SELECT id,fname,middle,lname,sum FROM personnel INNER J

But i then got a really nasty long message saying consult my manual near FROM personnel.

Any other ideas, what are we doing as well is there manual with this particular syntax?

Derokorian

Bah, my bad I don't have the tables to try it on:

$result = mysql_query("SELECT id,fname,middle,lname,sum FROM personnel INNER JOIN (SELECT id as did,SUM(point) as sum FROM discussions GROUP BY id) point on point.did=personnel.id") or die("cannot execute query: " . mysql_error());

bradgrafelman

One alternative:

SELECT d.id, fname, lname, SUM(d.point) sum
FROM personnel p
  INNER JOIN discussions d
    ON d.id = p.id
GROUP BY d.id

(although I'm sure just by posting this johanafm will be by any minute now to berate me on my use of non-aggregate columns missing from the GROUP BY statement).

EDIT: Forgot to mention, I omitted some of the column names from personnel for brevity while testing.

dglynch21

Your fine, I appreciate the help immensely, the good news is I have no more errors, and the menu HTML which is after the code loads as well except the entire table and the data contained in it that the PHP code is suppose to write is not there. 😕??????

                

<?php

$result = mysql_query("SELECT id,fname,middle,lname,sum FROM personnel INNER JOIN (SELECT id as did,SUM(point) as sum FROM discussions GROUP BY id) point on point.did=personnel.id") or die("cannot execute query: " . mysql_error()); 

while($row = mysql_fetch_array($result))
  { 
	echo "<table width=\"100%\" border=\"1\">";
	echo " <tr>";
	echo "<td colspan=\"2\">PDTS ID: " . $row['id'] . "</td>";
	echo " </tr>";
	echo "<tr>";
	echo "<td>".$row['fname']."</td>";
	echo "<td>".$row['middle']."</td>";
	echo " </tr>";
	echo " <tr>";
	echo "  <td>".$row['lname']."</td>";
	echo "  <td>NULL</td>";
	echo "  </tr>";
	echo "</table><br /> ";

  }
?>

Derokorian

bradgrafelman;10990801 wrote:
(although I'm sure just by posting this johanafm will be by any minute now to berate me on my use of non-aggregate columns missing from the GROUP BY statement)

That's why I did the the group by / sum in a sub query... to avoid johanafm 😛