query on dates with php mysql

[Sharon Ann]

Hi

I want to query by date, the number of rows from a table (called faults) which represents the number of entries sent by a user between two dates. The user input will be,

Inputbox 1 (first_date_opened - which might be 2006-03-01 for example)

Inputbox 2(second_date_opened - which might be 2006-03-30 for example)


When this query is sent to the database I want to be able display the total number of rows (representing entries) that have been inputted between these dates on the same page. I have been trying to follow several tutorials and have become totally lost. My code so far is as follows,


<form name = "datequery" action = "faultCountByDate.php" method = "GET">

<p><u>Produce a count on the number of faults received between two given dates</u><br><br>

<p>Please enter the first date to query:
<input type="text" name="first_date_opened">

<p>Please enter the second date to query:
<input type="text" name="second_date_opened">

<input type = "submit" name = "Query" value = "Click to count"

</form>

<?php

$first_date_opened=$_GET['first_date_opened'];
$second_date_opened=$_GET['second_date_opened'];

$conn = @mysql_connect("","","")or die("sorry - could not connect to mysql");
$rs = @mysql_select_db("",$conn) or die("problem with database");

$result= mysql_query("SELECT COUNT (*) FROM fault WHERE date_opened > '$first_date_opened' AND date_opened < '$second_date_opened'")//Error message here for line 104 (Parse error: parse error, unexpected T_VARIABLE on line 104)
$query=$_GET['Query'];
$num_rows = mysql_num_rows($result);

if ($query=="Click to count")
{
echo "The number of faults is: ";
echo ($row['$num_rows']) . " ";
echo "<br><br>";
}

?>

Being I've been following several tutorials, I have ended up with a bit from each, I don't think I need the $row variable. Can anybody sort me out with the correct way?

Thanks

[spoofer26]

Hiya,

Your right about the $row variable. Since you haven't defined it earlier in the script it will just be empty and print nothing. You assigned teh "numer of rows" to $numrows, so just use that.

Also, You need to use {fancy brackets} around any variables you want inside of your SQL queries, otherwise it will search for a date > "$lastdate" (ie, including the dollar sign).


Lastly: "Select count(*)" will return a single row containing information on how many rows would have been selected in a full query......that means mysql_num_rows()==1 all the time for that query.

You forgot to end your line starting "mysql_query()" with a semicolon....thats whats making your T-String error .

Try This:
<?php

$first_date_opened=$_GET['first_date_opened'];
$second_date_opened=$_GET['second_date_opened'];

$conn = @mysql_connect("","","")or die("sorry - could not connect to mysql");
$rs = @mysql_select_db("",$conn) or die("problem with database");

$result= mysql_query("SELECT * FROM fault WHERE date_opened > '{$first_date_opened}' AND date_opened < '{$second_date_opened}'");

$query=$_GET['Query'];
$num_rows = mysql_num_rows($result);

if ($query=="Click to count")
{
echo "The number of faults is: ";
echo ($num_rows) . " ";
echo "<br><br>";
}

?>

[Sharon Ann]

Nope, its still not working. All I'm trying to get is the number of rows entered between one date and the other. Doesn't it drive you mad???

[Sharon Ann]

Yippee!!!! At last,

This is the code that worked.......

<?php

$first_date_opened=$_GET['first_date_opened'];
$second_date_opened=$_GET['second_date_opened'];

$conn = @mysql_connect("","","")or die("sorry - could not connect to mysql");
$rs = @mysql_select_db("",$conn) or die("problem with database");

$result="SELECT * FROM fault WHERE date_opened >= '{$first_date_opened}' AND date_opened <= '{$second_date_opened}'";
$query=$_GET['Query'];
$rs=mysql_query($result,$conn);
$num_rows = mysql_num_rows($rs);

if ($query=="submit the form")
{
echo "The number of faults between these two dates was: ";
echo ($num_rows) . " ";
echo "<br><br>";
}

?>

Thanks very much to Sp00fer26 for helping me out, I'd never have known about the {} otherwise.

Sharon