[SOLVED] PHP / MySQL Question

[beaux1]

Hey.

 

Let's say I have an input box in PHP called Year: and the user types in the year to the box, such as 2007.

 

How would I go about using PHP to call how many columns had '2007' in the year row in the MySQL DB?

 

Basically, I want to know how to use PHP to count how many had a certain thing (which I could change from the PHP script) in the 'year' row.

 

Thanks,

Beau.

[craygo]

instead of using php use mysql

 

<?php
$year = $_POST['Year'];

$sql = "SELECT count(year) AS year_count WHERE year = '$year'";
$res = mysql_query($sql) or die (mysql_error());
$row = mysql_fetch_assoc($res);
echo $row['year_count'];
?>

 

Ray

 

[roopurt18]

I recommend storing dates as a DATETIME or DATE column type, which makes other manipulations with them easier in the long run.

[craygo]

I agree roopurt, but just coding for what people have. 

 

Ray

[beaux1]

Thanks, that helps alot.

[beaux1]

<?php
include 'config.php';
include 'opendb.php';
$year = $_POST['2007'];
$sql = "SELECT count(year) AS year_count WHERE year = '$year'";
$res = mysql_query($sql) or die (mysql_error());
$row = mysql_fetch_assoc($res);
echo $row['year_count'];
include 'closedb.php';
?>

My code, I put 2007 where it says $year= $_POST['2007'];

And I get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE year = ''' at line 1

It happens regardless of me putting in the 2007. Help would be much appreciated from you genius's!

[craygo]

the $_POST['Year'] comes from your form. If you want to test the script use

 

$year = '2007';

 

Ray

[beaux1]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE year = '2007'' at line 1

 

<?php
include 'config.php';
include 'opendb.php';
$year = '2007';

$sql = "SELECT count(year) AS year_count WHERE year = '$year'";
$res = mysql_query($sql) or die (mysql_error());
$row = mysql_fetch_assoc($res);
echo $row['year_count'];
include 'closedb.php';
?>

 

Eh?

[craygo]

I am a retard. I forgot the FROM clause

replace tablename with your tablename

 

$sql = "SELECT count(year) AS year_count FROM tablename WHERE year = '$year'";

 

Ray

[roopurt18]

I tend to write my SQL queries like so:

 

$sql = "SELECT "

      . "fld1, fld2, fld3 "

      . "BIG_FUNCTION(fld4, param1, param2) AS OutFld "

      . "FROM table1, table2, table3, table4 "

      . "WHERE "

etc.

 

Works well except sometimes I spend forever trying to debug a query only to realize I forgot a space between my last table name and FROM.

 

Argh!

[beaux1]

Hey, that works perfect Craygo, thanks!

 

Sorry but I have yet ANOTHER! question, lol.

 

Okay, well, I have a row called items, right, and it has a number in each row, I'd like to count all of the numbers in the 'items' row and echo it.

 

Any ideas? Is this even possible?

[craygo]

Do you want to Count them or SUM them. 2 different things

 

Count you would do the same as above, sum is a little different. With SUM you have to group your query and sometime can be a little tricky.

 

try this

 

<?php
$sql = "SELECT COUNT(year) AS year_count, SUM(items) AS item_sum 
FROM tablename 
WHERE year = '$year' 
GROUP BY year";
$res = mysql_query($sql) or die (mysql_error());
$row = mysql_fetch_assoc($res);
echo "Total for $year: ".$row['year_count']." Sum of items: ".$row['item_sum'];
include 'closedb.php';

 

Ray