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']