Jump to content

Archived

This topic is now archived and is closed to further replies.

Sharon Ann

query on dates with php mysql

Recommended Posts

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

Share this post


Link to post
Share on other sites
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>";
}

?>

Share this post


Link to post
Share on other sites
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???

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.