Php To Echo Sql Server Query Results

[ncurran217]

I have other things that work, but for some reason when trying this one is does not want to put the results into the table. Here is my code:

 

echo "<table border='1px' cellpadding='5' style='border:3px solid black; text-align: center;'>
 <tr>
  <th style='border:3px solid black;'>Rep</th>
  <th style='border:3px solid black;'>Pmts Collected</th>
 </tr>";
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
{   
[code]  echo "<tr>
   <td>{$row['Rep']}</td>
   <td>{$row['Num_Payments']}</td>
  </tr>";

}
echo "</table>";
sqlsrv_free_stmt ($result);
sqlsrv_close( $connection);
?>
[/code]

 

When I view source of the page this part is not in there:

 

 echo "<tr>
   <td>{$row['Rep']}</td>
   <td>{$row['Num_Payments']}</td>
  </tr>";

 

So I know I have the while part of the code wrong in the table somehow but not sure how to make it work.

 

Let me know if I need to provide more information. Thanks in advance!

[Jessica]

Maybe try using PHP tags instead of our forum's code tags? :-P

Are you using short tags? Try full tags.

[ncurran217]

I am using full tags.

 

The code tag, I accidently copied into there. while adding the other code part, that isn't actually in my code so disregard that

 

This is what it was supposed to be:

 

echo "<table border='1px' cellpadding='5' style='border:3px solid black; text-align: center;'>
 <tr>
  <th style='border:3px solid black;'>Rep</th>
  <th style='border:3px solid black;'>Pmts Collected</th>
 </tr>";
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
{   
echo "<tr>
       <td>{$row['Rep']}</td>
       <td>{$row['Num_Payments']}</td>
  </tr>";

}
echo "</table>";
sqlsrv_free_stmt ($result);
sqlsrv_close( $connection);
?>

Edited December 31, 2012 by ncurran217

[Jessica]

while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
{
[code] echo "<tr>
<td>{$row['Rep']}</td>
<td>{$row['Num_Payments']}</td>
</tr>";

}
[/code]

 

 

You don't see how you have random code tags in there??

[ncurran217]

while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
{
[code] echo "<tr>
<td>{$row['Rep']}</td>
<td>{$row['Num_Payments']}</td>
</tr>";

}
[/code]

 

 

You don't see how you have random code tags in there??

 

Read my edited post.

[Jessica]

Although re-reading your post it sounds like your while loop simply has no values to loop over. Did you verify that your query produces results?

[ncurran217]

Although re-reading your post it sounds like your while loop simply has no values to loop over. Did you verify that your query produces results?

 

Yes, when I copy the query into SSMS it runs and returns the proper results, and with the current query results should return 2 rows.

Edited December 31, 2012 by ncurran217

[Jessica]

try within your loop doing a print_r($row); and see what's in $row.

[ncurran217]

I get nothing still. I will try something else with the print in a bit when i get back to my desktop. thanks for helping though!

[Jessica]

Without seeing more of your code it's impossible to tell.

[ncurran217]

here is the full php code:

 

<?php
/*print_r($_POST);*/
$serverName = 'localhost\SQLEXPRESS';
$connectionInfo = array('Database'=>'test', 'UID'=>'cslogslogin', 'PWD'=>'123456','ReturnDatesAsStrings'=>true,);
$connection = sqlsrv_connect($serverName, $connectionInfo);
$Start_Date = $_GET['start_date'];
$End_Date = $_GET['end_date'];


$query = '  SELECT Reps.Rep, SUM(Logs.Num_Payments) AS \'Num_Payments\'
  FROM Logs INNER JOIN
           Reps ON Logs.ForteID = Reps.ForteID
  GROUP BY Reps.Rep, Logs.Date
  Having Date BETWEEN \''.$Start_Date.'\' AND \''.$End_Date.'\' AND SUM(Logs.Num_Payments) > \'0\'';


$result = sqlsrv_query($connection,$query);
echo $query;
if (!$result)
{
       return 'ERROR: ' . sqlsrv_errors();
}
echo "<table border='1px' cellpadding='5' style='border:3px solid black; text-align: center;'>
 <tr>
  <th style='border:3px solid black;'>Rep</th>
  <th style='border:3px solid black;'>Pmts Collected</th>
 </tr>";
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
{   
print_r($row);
 /*echo "<tr>
   <td>{$row['Rep']}</td>
   <td>{$row['Num_Payments']}</td>
  </tr>";*/
}
echo "</table>";
sqlsrv_free_stmt ($result);
sqlsrv_close( $connection);
?>

 

Well i am pretty sure the SQL is fine, because if I do the echo $query; and put what the output is into SSMS it returns values perfectly fine. The PHP code is where it is going wrong but I am not sure what is wrong. mthanks again for the help though hopefully with the full php code there will help out more!

[Jessica]

Why are you trying to return the errors? Is this in a function? You need to echo the error, or trigger the error.

[Barand]

Your alias shouldn't be in single quotes, they are for strings.

 

Also you need to handle the error reporting,something like this (it gives an errors array, not an error string)

 

$res = sqlsrv_query ($cnx, $sql) or die(FormatErrors(sqlsrv_errors()));

// function to display error messages
function FormatErrors($ea)
{
$str = '<pre>SQL Server Error:' . "\n";
$str .= $ea[0]['message'];
$str .= '</pre>' . "\n";
return $str;
}

Edited December 31, 2012 by Barand

[ncurran217]

Your alias shouldn't be in single quotes, they are for strings.

 

Also you need to handle the error reporting,something like this (it gives an errors array, not an error string)

 

$res = sqlsrv_query ($cnx, $sql) or die(FormatErrors(sqlsrv_errors()));

// function to display error messages
function FormatErrors($ea)
{
$str = '<pre>SQL Server Error:' . "\n";
$str .= $ea[0]['message'];
$str .= '</pre>' . "\n";
return $str;
}

I put that in and still no errors popped up. As for the single quotes, which part are you talking about?

 

Jessica, which errors are you talking about?

[Barand]

you only have one alias in quotes

 

$query = ' SELECT Reps.Rep, SUM(Logs.Num_Payments) AS \'Num_Payments\'

FROM Logs INNER JOIN

Reps ON Logs.ForteID = Reps.ForteID

GROUP BY Reps.Rep, Logs.Date

Having Date BETWEEN \''.$Start_Date.'\' AND \''.$End_Date.'\' AND SUM(Logs.Num_Payments) > \'0\'';

 

 

Also, numeric values should not be quoted ie 0 not '0'.

 

Plus I would put the date between bit in a where clause and just have the SUM in the HAVING clause.

Edited December 31, 2012 by Barand

[ncurran217]

I put in some of the error reporting for PHP that you had in your signature and nothing came up either Jessica!

[ncurran217]

you only have one alias in quotes

 

$query = ' SELECT Reps.Rep, SUM(Logs.Num_Payments) AS \'Num_Payments\'

FROM Logs INNER JOIN

Reps ON Logs.ForteID = Reps.ForteID

GROUP BY Reps.Rep, Logs.Date

Having Date BETWEEN \''.$Start_Date.'\' AND \''.$End_Date.'\' AND SUM(Logs.Num_Payments) > \'0\'';

 

It has to be in single quotes for SQL Server. This is how it is printed out from my echo $query;

 

SELECT Reps.Rep, SUM(Logs.Num_Payments) AS 'Num_Payments' FROM Logs INNER JOIN Reps ON Logs.ForteID = Reps.ForteID GROUP BY Reps.Rep, Logs.Date Having Date BETWEEN '12/28/2012' AND '12/28/2012' AND SUM(Logs.Num_Payments) > '0'

 

And that works perfectly fine in SQL.

[Barand]

Does it help if you put

$serverName = 'localhost\\SQLEXPRESS';

 

instead of

$serverName = 'localhost\SQLEXPRESS';

[Jessica]

I'm talking about the part of your code that says RETURN.

[ncurran217]

Does it help if you put

$serverName = 'localhost\\SQLEXPRESS';

 

instead of

$serverName = 'localhost\SQLEXPRESS';

 

No putting the extra \ in there doesn't do anything. All my other pages works with just the one \ in there as well.

[PFMaBiSmAd]

The external data values you are putting into your query likely contain some non-printing characters (tab, new-line, null) or perhaps even some html/url entities that are resulting in no match between the actual data in the table. When you copy/paste the echoed query, those non-printing characters/html/url entities are no longer present or are the actual characters.

 

Where and how are your $_GET['start_date'] and $_GET['end_date'] being produced and why are you putting them directly into a query statement without filtering/validating/escaping them?

 

P.S. I recommend building your query statements using overall (initial/final) double-quotes so that you can put single-quotes inside the query statement without needing to escape them. This would also allow you to put php variables directly into the query statement without needing to concatenate them.

 

$query = "SELECT Reps.Rep, SUM(Logs.Num_Payments) AS 'Num_Payments'
  FROM Logs INNER JOIN
  Reps ON Logs.ForteID = Reps.ForteID
  GROUP BY Reps.Rep, Logs.Date
  Having Date BETWEEN '$Start_Date' AND '$End_Date' AND SUM(Logs.Num_Payments) > 0";

Edited January 2, 2013 by PFMaBiSmAd

[ncurran217]

The external data values you are putting into your query likely contain some non-printing characters (tab, new-line, null) or perhaps even some html/url entities that are resulting in no match between the actual data in the table. When you copy/paste the echoed query, those non-printing characters/html/url entities are no longer present or are the actual characters.

 

Where and how are your $_GET['start_date'] and $_GET['end_date'] being produced and why are you putting them directly into a query statement without filtering/validating/escaping them?

 

P.S. I recommend building your query statements using overall (initial/final) double-quotes so that you can put single-quotes inside the query statement without needing to escape them. This would also allow you to put php variables directly into the query statement without needing to concatenate them.

 

I have a form before this page where the user will select the dates from a datepicker, or by default today's date is in the two fields.

 

As for the query, everything that is in the code is showing up when printed out and is how it should look, so I am not sure of the non-printing characters you are talking about. I changed my query statement in the code to this with double quotes as you said and still no results:

 

$query = "  SELECT Reps.Rep, SUM(Logs.Num_Payments) AS 'Num_Payments'
  FROM Logs INNER JOIN
           Reps ON Logs.ForteID = Reps.ForteID
  GROUP BY Reps.Rep, Logs.Date
  Having Date BETWEEN '$Start_Date' AND '$End_Date' AND SUM(Logs.Num_Payments) > '0'";

[Barand]

Seems odd to me that you should be grouping by date when you don't select the date

[ncurran217]

SQL throws an error if I do not have it in the GROUP BY since I have it in the HAVING statement. This is the error in SSMS I get when I take the GROUP BY date part out:

 

 

Msg 8121, Level 16, State 1, Line 5

Column 'Logs.Date' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 8121, Level 16, State 1, Line 5

Column 'Logs.Date' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

 

I just do not want the date column to show with the results, that is why it is not in the SELECT statement.

[Barand]

I have already suggested putting the dates in a WHERE clause

 

But before you do that, remove it from the GROUP BY, so you know there should be an error generated, then run you your code to see if the PHP version produces the same error

Edited January 2, 2013 by Barand