Php To Echo Sql Server Query Results

[ncurran217]

I get this error when putting it into a WHERE clause:

 

 

Msg 147, Level 15, State 1, Line 4

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

 

EDIT:

 

I changed it around to have the WHERE clause to work, still no results pull up. But when I put in the echo print out of the query from the code into SSMS it pulls results.

Edited January 2, 2013 by ncurran217

[Barand]

I get this error when putting it into a WHERE clause:

 

 

Msg 147, Level 15, State 1, Line 4

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

 

was that error generated using SSMS or your code? Can you force any errors to be generated by your code?

Edited January 2, 2013 by Barand

[ncurran217]

That was in SSMS, but I moved the where and group by clauses around and I got it to work. But when I put that in place in my php code, I do not get any results displayed like I do when I run the query in SSMS. I do not think it has anything to do with the SQL query.

[PFMaBiSmAd]

Someone already stated its likely your external data values being put into the query statement contain characters that result in a non-match with the actual data. You should be investigating what about that external data in the query is causing a non-match.

[Barand]

In which case you should get data or an error. But you seem reluctant to prove that your error reporting is working. So I am reluctant to carry on.

[ncurran217]

Someone already stated its likely your external data values being put into the query statement contain characters that result in a non-match with the actual data. You should be investigating what about that external data in the query is causing a non-match.

 

My form is setup the same way on all my other pages with the datepicker and all those queries work fine. That is the only thing that is inputted from the form.

[ncurran217]

In which case you should get data or an error. But you seem reluctant to prove that your error reporting is working. So I am reluctant to carry on.

 

I have put in all the error reporting that everyone has said to and still not receiving any errors. So I am not sure how you say I am reluctant.

[ncurran217]

I went back through all the posts and all the error reporting you and Jessica suggested I have in place and nothing has popped up with an error. If there is something else I am missing in it to show errors, please let me know, but as of this point I have done everything everyone has asked and still no results.

 

@PFMaBiSmAd - for the characters being passed on, which characters would not pass on to the echo $query; but possibly pass on to the actual query that would result in a non-match. Here is my form code from the previous page:

 

<form method="get" action="getcollect.php">
<table>
<tr>
<td>Start Date:</td>
<td><input name="start_date" id="datepicker" type="text" value="<?php echo $today;?>" autocomplete="off" required="required"></td>
</tr>
<tr>
<td>End Date:</td>
<td><input name="end_date" id="datepicker2" type="text" value="<?php echo $today;?>" autocomplete="off" required="required"></td>
</tr>
</table>
<br>
<input type="submit" name="getcollect" value="Get Report"><br><br>
<input type="button" value="Back to Reports" onclick="window.location.href='stats.php';"> <br>
<input type="button" value="Individual Reports" onclick="window.location.href='individualstats.php';"> <br><br>
</form>

Edited January 2, 2013 by ncurran217

[ncurran217]

@PFMaBiSmAd - I also took out in the query the $Start_Date and $End_Date and just manually put in 2012-12-28 for each and still no results are showing. Here is the code I have as of now. There are a few changes with the SQL error reporting, which I have found on Microsofts website, I just put in as another attempt to see if those would through me any errors, but they have not. Here is the code:

 

Code in another post:

 

Let me know if there are any other ideas or if I should scrap something to start fresh from somewhere. Thanks again and I am sorry if I seemed reluctant.

Edited January 2, 2013 by ncurran217

[ncurran217]

So the font and color tags are not in my code, but keep getting automatically put in there when I save changes to the post.

 

<?php
ini_set('error_reporting', E_ALL);
/*print_r($_POST);*/
$serverName = 'localhost\\SQLEXPRESS';
$connectionInfo = array('Database'=>'test', 'UID'=>'cslogslogin', 'PWD'=>'123456','ReturnDatesAsStrings'=>true,);
$connection = sqlsrv_connect($serverName, $connectionInfo);
if( $connection === false )
{
echo "Could not connect.\n";
die( print_r( sqlsrv_errors(), true));
}
$Start_Date = $_GET['start_date'];
$End_Date = $_GET['end_date'];


$query = " SELECT Reps.Rep, SUM(Logs.Num_Payments) AS Pmts_Collected
FROM Logs INNER JOIN
Reps ON Logs.ForteID = Reps.ForteID
WHERE Logs.Date BETWEEN '2012-12-28' AND '2012-12-28'
GROUP BY Reps.Rep
HAVING SUM(Logs.Num_Payments) > 0\n";

$result = sqlsrv_query($connection,$query);
/*echo $query;*/
if( $result === false )
{
echo "Error in statement execution.\n";
die( print_r( sqlsrv_errors(), true));
}
echo "<table border='1px' cellpadding='5' style='border:3px solid black; text-align: center;'>\n";
echo "<tr>\n";
echo "<th style='border:3px solid black;'>Rep</th>\n";
echo "<th style='border:3px solid black;'>Pmts Collected</th>\n";
echo "</tr>\n";
while ( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC ))
{
echo "<tr>";
echo "<td>{$row['Redp']}</td>";
echo "<td>{$row['Pmts_Collected']}</td>";
echo "</tr>\n";
}
echo "</table>\n";
sqlsrv_free_stmt ($result);
sqlsrv_close( $connection);
?>

 

Alright I got it to not add in the font and color tags!

Edited January 2, 2013 by ncurran217

[PFMaBiSmAd]

BETWEEN '2012-12-28' AND '2012-12-28'

 

A) That's not the date format you posted where you echoed your $query statement.

 

B) AFAIK, that's not the ms sql server date format (unless you have specifically set the dateformat to be that format.)

 

It's possible that the database management tool you are using is converting the copy/pasted date into the correct format to match the database setting, where as the query is being executed as is through the php script.

Edited January 2, 2013 by PFMaBiSmAd

[ncurran217]

Well if I just do a query in SSMS this is the format it outputs of a record of the date field:

 

2012-12-18

 

I could put in either way in a query in SSMS 2012-12-28 or 12/28/2012 and it still pulls up. The column type is set to (date,null). Also, I have in the $connectionInfo 'ReturnDatesAsStrings'=>true, which I took out but didn't seem to make a difference.

[ncurran217]

Thanks for all the help guys and gals. The day I had kept putting in the where clause was the only day that didn't have data. So it really wasn't anything wrong with the code. But you were right in a sense as it was not matching, because there was nothing for that date. I am sorry for the headaches I may have caused!

Edited January 2, 2013 by ncurran217