Getting no result rows/fields from php/mysqli query while there is data

Question

Explanation of the issue is:

Query sql returns 6 rows of data (of 14 fields each) when run in phpMyAdmin
On the php page mysqli_num_rows returns: 6
print_r($sql_res) returns: mysqli_result Object ( [current_field] => 0 [field_count] => 14 [lengths] => [num_rows] => 6 [type] => 0 )
var_dump($sql_res) returns: object(mysqli_result)#1 (5) { ["current_field"]=> int(0) ["field_count"]=> int(14) ["lengths"]=> NULL ["num_rows"]=> int(6) ["type"]=> int(0)

There is data in the db, but is not being parsed into the table structure since no data containing field values are returned. Any help or pointers would highly be appreciated. DB connection is OK, my other pages are working, I'm using session variables in query ($selected and $selfair), my php code that gives the headache is:

<?php
$sql = "SELECT har_id, har_fair_id, har_kat_id, har_tarih, har_co_id, har_desc, har_docno, har_kur, har_debit, har_credit, har_used, har_invoiced, har_cancelled, har_schid FROM hareketler WHERE har_co_id =". $selected." AND har_fair_id =". $selfair;
echo $sql."<br><br>";
//sql returns correct statement
$sql_res = mysqli_query($con, $sql);
print_r($sql_res)."<br><br>";
echo "<br><br>".mysqli_num_rows($sql_res);
// row count is 6 but no other data is fetched from db!
echo "<br><br>".var_dump($sql_res);
while($row = mysqli_fetch_array($sql_res,MYSQLI_ASSOC));{
// detail rows are NOT fetched!
    if($row<>''){
    echo '<tbody>';
        echo '<tr>';
            echo '<td class="katdata">'.$row['har_id'].'</td>';
            echo '<td class="katdata">'.$row['har_fair_id'].'</td>';
            echo '<td class="katdata">'.$row['har_kat_id'].'</td>';
            echo '<td class="katdata">'.$row['har_tarih'].'</td>';
            echo '<td class="katdata">'.$row['har_co_id'].'</td>';
            echo '<td class="katdata">'.$row['har_desc'].'</td>';
            echo '<td class="katdata">'.$row['har_docno'].'</td>';
            echo '<td class="katdata">'.$row['har_kur'].'</td>';
            echo '<td class="katdata">'.$row['har_debit'].'</td>';
            echo '<td class="katdata">'.$row['har_credit'].'</td>';
            echo '<td class="katdata">'.$row['har_used'].'</td>';
            echo '<td class="katdata">'.$row['har_invoiced'].'</td>';
            echo '<td class="katdata">'.$row['har_cancelled'].'</td>';
            echo '<td class="katdata">'.$row['har_schid'].'</td>';
        echo '</tr>';
    echo '</tbody>';
    }
    else {
        echo "<br><br>No data returned!";
        // sql is correct but no detail data is fetched!
    }
}
?>

Your code is prone to SQL injections..(https://stackoverflow.com/questions/601300/what-is-sql-injection) you should prevent them ( https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php ) — Raymond Nijland, Sep 27 '18 at 10:14
@kerbholz Thanks a million! I guess I really need better glasses! I'm also sorry for occupying time here for a stupid typo... Thanks again. — Alp54, Sep 27 '18 at 10:23
@Raymond Nijland Thanks Raymond, I will read those articles. — Alp54, Sep 27 '18 at 10:26
"_I guess I really need better glasses!_" Or a decent IDE! Or error_reporting! ;) Glad it works. — brombeer, Sep 27 '18 at 10:27
I use Sublime Text, not any IDE's maybe I should turn to one... Well at 64, that's what you get. :-D — Alp54, Sep 27 '18 at 10:51

score 1 · Answer 1 · answered Sep 27 '18 at 10:16

1

You have a typo:

while($row = mysqli_fetch_array($sql_res,MYSQLI_ASSOC));{

should be

while($row = mysqli_fetch_array($sql_res,MYSQLI_ASSOC)){

answered Sep 27 '18 at 10:16

verhie

1,298
1
7
7

score 0 · Answer 2 · answered Sep 27 '18 at 10:43

You have to make sure that there is a single quote(') around the values you are comparing. For instance

SELECT * FROM table WHERE column_name = 'value';

will work, but

SELECT * FROM table WHERE column_name = value;

will not work.

So make sure your output is:

SELECT har_id, har_fair_id, har_kat_id, har_tarih, har_co_id, har_desc, har_docno, 
har_kur, har_debit, har_credit, har_used, har_invoiced, har_cancelled, har_schid 
FROM hareketler WHERE har_co_id ='selected' AND har_fair_id ='selfair';

To achieve this, change your sql to

$sql = "SELECT har_id, har_fair_id, har_kat_id, har_tarih, har_co_id, har_desc, 
har_docno, har_kur, har_debit, har_credit, har_used, har_invoiced, har_cancelled, 
har_schid FROM hareketler WHERE har_co_id ='". $selected."' AND har_fair_id ='". 
$selfair."';";

This Should Solve your problem, that, and there is a misplaced semicolon in your while loop. while($row = mysqli_fetch_array($sql_res,MYSQLI_ASSOC));{. You need to fix that Thank You, hope this was helpful.

Whilst that's all true, concatenating a value into a SQL string is not a good idea as it opens you up to SQL injection attacks. Better approaches here: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Rup, Sep 27 '18 at 11:04

Getting no result rows/fields from php/mysqli query while there is data

2 Answers2