[RESOLVED] Getting a County total

amax

Hi all,
I have created my first MySql database to use with a small flash game and PHP. Its a simple 4 field table - userid (primary key and auto incremented), username, co_name, and score_ref.

What I need to be able to do using PHP is search my table for all co_names that equal say "Dublin" and return a new co_total i.e. the total of all the score_ref fields for co_name Dublin.

I am not sure what the SQL query is though now looking at this. Can someone please point me in the right direction. I am thinking that I have not structured my database correctly!

djjjozsi

I am thinking that I have not structured my database correctly!

I think you know it well. The country field should be stored in a related table. And a another database model (whould be )used to store user - country - score

first, lets create a form to get a user input, and build the entire part of your form...:

		print "co name:<br> <input type=\"text\" name=\"co_name\"><br>";

Now, check if the user filled the co_name:

if ( !empty( $_POST["co_name"] ) )
{
......*1 lets build the sql query...*2 run the query....    *3 print the results .....
}

//1 An sql query looks like:

/* Change the table and field names if its needed!*/
$table="Users_table";

	$SQL_QUERY = "co_name LIKE '%" . mysql_real_escape_string( $_POST["co_name"] ) . "%'";
	$sql = " SELECT co_name , SUM(score_ref) AS score_refsum FROM $table
WHERE $SQL_QUERY
GROUP BY co_name ORDER BY score_refsum DESC";

// *2

mysql_query($sql) or die("Problem with the query: $sql" . mysql_error());

And when you print the results, you reach the countries and the summed scores:
// *3

if ( mysql_num_rows( $res ) > 0 )
		{
				while ( $one_row = mysql_fetch_object( $res ) )
				{
						print "<Co name>:$one_row->co_name<<br />";
						print "<score>$one_row->score_refsum<br/>";
				} 
				print "<hr>";
		} 
		else
		{
				print "No CO found<br/>";
		}

hello,jjozsi.

amax

dear djjjozsi, thank you for your help. I am still a little confused though. I think im still going wrong with the insertion and structure.
I now have 2 tables as follows:
users_table (userid, username, score_ref and co_name)
county_table (co_name and co_score)

when a user plays the game the variables get sent to a single php page. Full code below:

<?php

$userid = $_POST['userid'];
$username = $_POST['username'];
$co_name = $_POST['co_name'];
$score_ref = $_POST['score_ref'];

$received = $userid." ".$username." ".$co_name." ".$score_ref ;
$toFlash = "&toFlash=";
$toFlash .= $received;

//Connects to the game Database
mysql_connect("xxxx", "xxxx", "xxxxxx") or die(mysql_error());
mysql_select_db("game") or die(mysql_error()); 

mysql_query("INSERT INTO users_table (userid, username, co_name, score_ref) VALUES ('$userid','$username','$co_name','$score_ref')");

if ( !empty( $_POST["co_name"] ) )
{

$table="users_table";

$SQL_QUERY = "co_name LIKE '%" . mysql_real_escape_string( $_POST["co_name"] ) . "%'";
$sql = " SELECT co_name , SUM(score_ref) AS score_refsum FROM $table WHERE $SQL_QUERY GROUP BY co_name ORDER BY score_refsum DESC"; 

mysql_query($sql) or die("Problem with the query: $sql" . mysql_error()); 


if ( mysql_num_rows( $res ) > 0 )
        {
                while ( $one_row = mysql_fetch_object( $res ) )
                {
                        print "<Co name>:$one_row->co_name<<br />";
                        print "<score>$one_row->score_refsum<br/>";
                }
                print "<hr>";
        }
        else
        {
                print "No CO found<br/>";
        } 

}
?>

the users_table appears to be updated fine but Im not sure how I actually link up my new county_table djjjozsi

djjjozsi

hey amax,
sorry, i don't want to confuse you.

Make this project with the original idea.. My posted example is searching in a single table, i was just talking about a better idea, which is the database normalization: http://databases.about.com/od/specificproducts/a/normalization.htm

Hello,jjozsi.

bradgrafelman

Well you would want to use MySQL's SUM() function on the score_ref fields, and you'd want to GROUP BY the co_name column.

Since this would give you a score total for all co_name values whereas you only want a single value for the "Dublin" group, add a WHERE clause that specified this.

bradgrafelman

Also, please do not post the same topic in multiple forums; this is called cross-posting and is generally frowned upon on the web.

amax

Hey Guys, I finally figured out my query with a magical RIGHT JOIN tutorial. Ended up sticking with the more efficient way jjozsi pointed out:

$HIGH_SCORE_TABLE_QUERY = "SELECT county_table.co_name , SUM(score_ref) AS score_refsum FROM users_table RIGHT JOIN county_table ON users_table.co_name=county_table.co_name GROUP BY county_table.co_name ORDER BY score_refsum DESC;";

jjozsi, bradgrafelman thanks for all the help guys.

Sorry about the cross-post guys, I was not aware / didnt know that wasnt allowed, wont happen again