selecting and summing from table in mysql???

[staceywc]

Ok, I'm a complete newb with php. I'm writing most of the stuff in perl. I just want to embed a scoreboard in my header for my webpage so I need to retrieve the teams from a table that essentially has a table with the username, team (specific to user), and points (specific to user). All I have so far is:

<?
$dbcnx=@mysql_connect ("localhost", "root", "password");
if (!$dbcnx){
echo ("??");
exit()}


Add HERE
?>

Where I noted Add HERE, it needs to run through all usernames in the table and select only those that are on team a but I don't care about that part per se, I just want a running total of all points which would be the sum of the points by row, if that makes sense??? I'm getting so confused with the php since I'm focused on learning perl right now, it just won't work for me to embed the perl in the particular page and this SEEMED like the easier route. Heck I'm not even sure if the above will access the database...grrr. I feel very frustrated.

I apologize in advance if this question seems too simple. I have just been reading since yesterday all sorts of online tutorials and I can't figure it out to save my life.

Thanks for any advice you have.

[Zipline]

You could do something like

$sql = "SELECT count(teams) as teams FROM table";
$sql = mysql_query($sql);
$result = mysql_fetch_assoc($sql);

echo($result['teams']);


This should select the right information and count it for you. You may have to tweak the query to pull the info you need on that specific page because I don’t know the exact structure of your table but that is just an idea on how you could do that.

[staceywc]

I'm not sure I'm following. The table right now is laid out with 3 columns: student, house, and points (these are the exact titles). What I need the query to do is to select only students that are in house "A" and sum each of those student's points "points", so it isn't a count, I don't think??? I'm honestly so confused I don't know LOL Can you clarify if that code will sum the corresponding rows for the selected students?

Thanks!

[Zipline]

ok in that case your query would be more like.


$sql = "SELECT sum(points) as points FROM your_table WHERE house = 'A'";
$sql = mysql_query($sql);
$result = mysql_fetch_assoc($sql);

echo($result['points']);


Note: you need to select a database. Here is an example connection that I use its very simple:


$db = mysql_connect ('localhost','user', 'pass') or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ($database) or die ('I could not select the database because: ' . mysql_error());

[staceywc]

Thanks so much, both of those codes make sense and are a lot easier than what I was looking at as an example. I really appreciate it. Hopefully it will work no problem too :)

[staceywc]

I'm getting errors with the fetch command that I don't really understand. You can see it working on my page at www.al-fidelis.com (in the main header) but the warning is: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home3/jmcenter/al-fidelis-www/header.html

Since I'm not really understanding this mysql thing so much I really don't even know where to start debugging that. :confused:

[Zipline]

The first thing to do is modify the script to use your specific database and its fields. For example if the column in your table is Points not points you will need to update that so they match... also, "A" is just an example taken from your script. If that isn’t in your database then the script won’t like the query. Usually when you receive that error it means that the query isn’t working. That is simply a template for how one can be created but you need to modify the statement to match your specific situation, without seeing your entire database there is no way to write a query to fit your application exactly.

[staceywc]

Ok, I double checked everything, finally worked out that I had to add from 'tablename' to your code to get it to work. Man that nearly caused my brain to fry LOL now I'm off back to perl....Thanks so much!

[chazzy]

ok in that case your query would be more like.


$sql = "SELECT sum(points) as points WHERE house = 'A'";
$sql = mysql_query($sql);
$result = mysql_fetch_assoc($sql);

echo($result['points']);




this is one of the reasons why I dislike PHP. this should be invalid. you should not use one variable for both your strings and mysql resources.

$sql = "SELECT sum(points) from `yourtable` as points WHERE house = 'A'";
$query = mysql_query($sql);
$resultset = mysql_fetch_assoc($query);

echo($result['points']);

[Zipline]

Chazzy, thanks for catching the error in my SQL statment. When I wrote that second query I forgot to specify a table! I have gone back and edited my second query post to include that information incase someone happens to copy that code they get it right.

[chazzy]

staceywc already pointed that out.

i was clearing up the fact that you had some cross variable type migration going on. while it works in PHP, people like me would argue that it shouldn't work and shouldn't be used.

take this for example

Int bob=new Int();
String bob = new String();
Object bob = new Object();

Java and PHP would have different reactions to this code, in theory. Grammatically, you wouldn't want to call something you sit on the same thing that you call something you write on.