PHP + MySQL query : Unexpected results (for small number of results only)
Trying to show results from the MySQL DB based on the start date and end date given by the user. Following is the code :
//Getting Values From Other Page
$start_date = date("Y-m-d", strtotime($_GET["date3"]));
$end_date = date("Y-m-d", strtotime($_GET["date4"]));
//Server Connection Info
$server = "server_name";
$db_user = "username";
$db_pass = "pass";
$db_name = "dbname";
//Server Connection + Query
$link = mysql_connect($server,$db_user,$db_pass);
if(!$link)
{
die("Could Not Connect:".mysql_error());
}
mysql_select_db($db_name, $link) or die('Can\'t use db:'. mysql_error());
if ($start_date == $end_date){
$query = "SELECT col_a, col_b, col_c, col_d FROM main WHERE date='".$start_date."'";
}
else {
$query = "SELECT col_a, col_b, col_c, col_d FROM main WHERE date BETWEEN '".$start_date."' AND '".$end_date."'";
}
$result = mysql_query($query,$link) or die('Query Error'.mysql_error());
?>
<!-- HTML CODE STARTS HERE -->
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="qc_style.css" />
<link href='http://fonts.googleapis.com/css?family=Days+One' rel='stylesheet' type='text/css' />
</head>
<body>
<?php
$row = mysql_fetch_assoc($result);
$row_count = mysql_num_rows($result);
echo $row;
echo $row_count;
if($row_count!=0){
echo "<table>";
while($row = mysql_fetch_assoc($result))
{
echo "<tr><td>";
foreach($row as $value)
{
echo $value;
echo "</td><td>";
}
echo "<a href=\"qc_campedit.php?id=".$row['call_id']."\">Edit</a></td></tr>";
}
echo "</table>";
} //end of if statement
else {echo " Sorry, No records match your query";}
?>
</body>
</html>
I have two rows of data in my database for the date 2011-08-25 and one row of data for the date 2011-08-24.
- When I specify the start date and end date as 2011-08-24, I get no response.
- However when I specify the start date and end date as 2011-08-25, I get only one row of data belonging to 2011-08-25
- When I specify the start date as 2011-08-24 and end date as 2011-08-25, I get the two rows of data belonging to 2011-08-25 and the row belonging to 2011-08-25.
- However, when I specify the start date and end date as 2011-08-23, I get all the 20 rows of data belonging to 2011-08-23
Not开发者_运维百科e: The number of arrays in $result for that particular timeframe is displayed correctly. i.e., $row_count = 1 for 2011-08-24 and $row_count = 2 for 2011-08-24
How do I solve this issue? Thanks in advance.
If you would like to select fewer columns, do not use "SELECT * FROM..." instead use "SELECT name_of_col_A, name_of_col_B FROM table...".
Also I recommend sanitizing the date string you are accepting via $_REQUEST and passing into your query. Something along the lines of
$start_date = date("Y-m-d", strtotime($_REQUEST["date3"]));
should suffice.
BETWEEN is just a way to specify inclusive range. I think in db you have DATETIME column for your date column, so BETWEEN '2011-08-24' AND '2011-08-24' returns just records with '2011-08-24 0:00:00'. To do what you want you need to write
WHERE DATE(your_datetime_column) BETWEEN '2011-08-24' AND '2011-08-24'
Your first call to mysql_fetch_assoc() is fetching the first row. Then you get into your loop and the first one it fetches is the next row, which is the first. I updated the code slightly below to use mysql_num_rows() to show your number of results.
<?php
$row_count = mysql_num_rows($result);
echo $row_count;
if($row_count==0) {
echo "Sorry, no records match your query";
else {
echo "<table>";
while($row = mysql_fetch_assoc($result))
{
echo "<tr><td>";
foreach($row as $value)
{
echo $value;
echo "</td><td>";
}
echo "<a href=\"qc_campedit.php?id=".$row['call_id']."\">Edit</a></td></tr>";
}
echo "</table>";
}
?>
I'm sorry, not examining your three useCases exactly. But first of all I see in your code, that you're fetching the first row only for knowing "row_count". I have added some comment on this:
$row = mysql_fetch_assoc($result); // get row 1 from mysql-result
$row_count = count($row); // always the number of attributes returned in one row
echo $row;
echo $row_count;
if($row_count!=1) { // always true
// fetch lines 2-n and write to table
}
use mysql_num_rows() if you need the number of returned rows.
Just one thing to add. If your know where your data is coming from use that source specifically and avoid $_REQUEST.
It isn't dangerous by itself but in certain situations it can present a security risk because it makes use of a fallback system which might actually pull data from places you don't wan to. I.e. $_REQUEST follows your EGPCS settings which means if no requested data is found in $_ENV it moves forward to $_GET, $_POST, $_COOKIE etc. and you don't want that.
If your data is coming specifically from GET then use $_GET['date3']
加载中,请稍侯......
精彩评论