count problem

[7724]

I have a problem with this code:

<?php

// login stuff

include("../*****.php");
include("../******.php");

$sql = "SELECT COUNT(*) FROM game WHERE shirtone = 'Whincup'"; 
$result = mysql_query($sql) or die(mysql_error()); 
echo mysql_result($result, 0);

?>

The above code sees how many times Whincup has been selected in my 'game' table in the 'shirtone' variable and outputs the value. What I want it to do is look in several variables with the output displayed on the page with them all added together, how would that work?

ie:

shirttwo= 'Whincup'
shirtthree= 'Whincup'

Ta

Chris

 

[spookie]

Is shirtone a variable or a column name ?


The below query will give a count having atleast one of the three shirtone,shirttwo,shirtthree having value 'Whincup'.

$sql = "SELECT COUNT(*) FROM game WHERE shirtone = 'Whincup' OR shirttwo = 'Whincup' OR shirtthree= 'Whincup' ";

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[7724]

THANKS!!! cool, worked it out, but what if I wanted to get hold of a varible ie:

<?php

$sql = "SELECT COUNT(*) FROM game WHERE shirtone = '<?php echo $secondname; ?>' OR shirttwo = '<?php echo $secondname; ?>' "; 
$result = mysql_query($sql) or die(mysql_error()); 
echo mysql_result($result, 0);

?>

It does not work - any ideas for getting hold of the variable?

 

[spookie]

Are you getting variable values correctly ?
To check it out print the query on a browser..

<?php

$sql = "SELECT COUNT(*) FROM game WHERE shirtone = '<?php echo $secondname; ?>' OR shirttwo = '<?php echo $secondname; ?>' "; 
echo $sql ;
?>

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[7724]

Yeah, sorry did a bit of playing around and now using this code which works:

<?php

$sql = "SELECT COUNT(*) FROM game WHERE shirtone = '$secondname' OR shirttwo = '$secondname' ";
$result = mysql_query($sql) or die(mysql_error()); echo mysql_result($result, 0);

?>

But the $secondname variable is from a different table - the 'profiles' table - how do I declare that in the same statement?

Ta

 

[Olavxxx]

You can most likely do this with a "natural join".

$sql = "SELECT COUNT(*) FROM `game`, `profiles` WHERE `shirtone` = `field_from_second_table` OR shirttwo = `field_from_second_table` "; 
$result = mysql_query($sql) or die(mysql_error()); echo mysql_result($result, 0);

You have however several improvements to make here:
* SELECT only fields that you have to retrieve data from
* Secure your query (

http://www.php.net/mysql_real_escape_string)

If there are very many results, you might also consider making a paged listing, with a LIMIT on your query.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[7724]

Thanks - so kinda going by that I created this code.

Although that seems to output every single player on one page - which I don't want. So I created this code:

<?php  

$sql = "SELECT COUNT(*) FROM `game`, `profiles` WHERE '$shirtone' = '$secondname' OR '$shirttwo' = '$secondname' "; 
$result = mysql_query($sql) or die(mysql_error()); echo mysql_result($result, 0); 

?>

Which should in theory take the $secondname (value picked up from the 'profiles' table) of the player from the records I recorded and see how many times he/she has appeared in $shirtone and $shirttwo (values picked up from the 'game' table )- but the output value is just 0?

Any ideas why it's outputting as 0 when I have say the variable 'Carbon' (value picked up from the 'profiles' table) saved in a two different records in variable $shirtone and $shirttwo?

Ta

Chris