0

I am a bit new to programming, I have a query in PHP with SQLSRV but when exporting to Excel does not give me the result, I throw the error

Warning: sqlsrv_num_rows () Expects parameter 1 to be resource, boolean given in

this is the code, the SQL query itself executes in SQL Server, but here the Excel gives me that error, and internet search and search but can not find an answer, this is the code:

<?php

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Movimientos Cancelados del Mes.xls"');
header('Cache-Control: max-age=0');

$server = "server";
$info = array("Database"=>"DB","UID"=>"USR","PWD"=>"" );
$conn = sqlsrv_connect($server, $info);
$param = array('ReturnDatesAsStrings'=> true);
$opt = array("Scrollable" => SQLSRV_CURSOR_KEYSET);

$per = $_GET["periodo"];
$eje = $_GET["ejercicio"];
$mov = 'Movimiento';
$est = 'Estatus';
$cli = 'Cliente';
$rfc = 'RFC';
$tot = 'Total';
$fec = 'Fecha Timbrado';
$uuid = 'UUID';
$cert = 'Certificado SAT';
$sql = "select v.MovID as '$mov',v.Estatus as '$est',v.Cliente as '$cli',cte.rfc as '$rfc',(v.Importe+v.Impuestos)as '$tot', c.FechaTmibrado as '$fec', c.UUID as '$uuid',c.noCertificadoSAT as '$cert'
from Venta V join CFD c on v.MovID = c.MovID join cte on v.cliente = cte.cliente 
where V.Estatus = 'Cancelado' and c.Periodo = '$per' and c.Ejercicio = '$eje' and  c.Empresa = 'MGJ' 
order by FechaEmision";

$query = sqlsrv_query($conn, $sql);

$campos = sqlsrv_num_rows($query);

$i = 0;

echo "<table border=''><tr>";
echo "<th>$mov</th>";
echo "<th>$est</th>";
echo "<th>$cli</th>";
echo "<th>$rfc</th>";
echo "<th>$tot</th>";
echo "<th>$uuid</th>";
echo "<th>$cert</th>";

while ($i<$campos) {
    echo "<td>".sqlsrv_get_field($query,$i);
    echo "</td>";
    $i++;
}
    echo "</tr>";
while($row=sqlsrv_fetch_array($query)){
    echo "<tr>";
    for ($j=0; $j < $campos; $j++) { 
        echo "<td>".$row[$j]."</td>";
    }
    echo "</tr>";
}
echo "</table>";
   sqlsrv_close( $conn);
print_r(sqlsrv_errors(),true);
?>
chris85
  • 23,846
  • 7
  • 34
  • 51
Ricardo Romero
  • 21
  • 7
  • Your `$query` failed, run check for errors. I'm also not clear what `excel` has to do with this, does Excel have a driver that can execute PHP? You also are open to SQL injections. – chris85 Sep 29 '16 at 23:28
  • It also gives you a line number on that error. Would you like to give us a bit of a clue as to which line of your code is erroring, ro shall we just look at it all until we manually compile it in our heads – RiggsFolly Sep 29 '16 at 23:29
  • Read http://php.net/manual/en/function.sqlsrv-query.php you'll see that when you're running your query it's failing which means that $query is FALSE; which in turn causes the error when you try to pass a bool instead of a resource. May I suggest that you look into using PDO instead? – Kit Sep 29 '16 at 23:29
  • 1
    Been a while since I sqlservered but I would take a guess that `'$mov'` should be just `$mov` etc etc etc – RiggsFolly Sep 29 '16 at 23:34
  • can you please check if is there anything wrong with columnnames, `sqlsrv_errors()` – Vural Sep 29 '16 at 23:36
  • @chris85 My query runs perfectly in SQL, if I print on screen the result shows me what (unformatted) but I want to download to Excel so they can manipulate the information. – Ricardo Romero Sep 29 '16 at 23:38
  • @RicardoRomero Just an idea... But are you sure that your server name is 'server'? Shouldn't you be connecting to 'localhost'? You may very well have named your server 'server' but it's just not something I'd normally do. – Kit Sep 29 '16 at 23:38
  • @RiggsFolly the error line that's shown is 30 – Ricardo Romero Sep 29 '16 at 23:41
  • Okay, so the Excel information is not needed here. Try to keep the issue as simple as possible (as little information needed to reproduce the error). – chris85 Sep 29 '16 at 23:41
  • @Rawrskyes Change the information brother, this is not a problem, the file is correct. – Ricardo Romero Sep 29 '16 at 23:43
  • @RicardoRomero Yes I know, that comment was more for future reference – RiggsFolly Sep 29 '16 at 23:50
  • @VuralAcar I realized that the name of one of the columns was misspelled, now if you run the query but Excel to download this blank... – Ricardo Romero Sep 29 '16 at 23:51
  • @RiggsFolly thanks for the observation – Ricardo Romero Sep 29 '16 at 23:52
  • Can you please rephrase `now if you run the query but Excel to download this blank`? If you just run the query without Excel involvement does it work? – chris85 Sep 29 '16 at 23:54
  • @chris85 It's hard for me not to mention Excel because what I want is to download it in Excel and it's what I have right now, I know I just said yes printed on screen the result, but as I said initially, the programming is new for me, I do not know much about programming ... – Ricardo Romero Sep 30 '16 at 00:00
  • The question you asked about though is `Warning: sqlsrv_num_rows` which you have resolved, so you are asking a different question here. I'm not an Excel user so I can't help you with this. The PHP execution works so it is something with the Excel interaction. – chris85 Sep 30 '16 at 00:02
  • @chris85 If i run query on SQL there's no problem, now the problem is the Excel exporting thanks to VuralAcar for the observation – Ricardo Romero Sep 30 '16 at 00:04
  • Yes @chris85, so thanks a lot everyone that commented the post, you just opened my eyes – Ricardo Romero Sep 30 '16 at 00:05

1 Answers1

0

Warning: sqlsrv_num_rows () Expects parameter 1 to be resource, boolean given in

It means, your query does not return a resource object because the query failed to compile or run.

To see why this happens, you can use sqlsrv_errors()

if( ($errors = sqlsrv_errors() ) != null) {
    foreach( $errors as $error ) {
        echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
        echo "code: ".$error[ 'code']."<br />";
        echo "message: ".$error[ 'message']."<br />";
    }
}
Vural
  • 8,666
  • 11
  • 40
  • 57