First row of mysql data fetched through PHP is not displayed in the table

Question

I have written a function to display the data fetched from MySQL database using PHP mysqli_query( $db_conn, $sql ); when I am calling the following display function first row of the data is not displayed. Please suggest how can I get the first row.

function display_data($retval) {
$output = '<table border="1">';
foreach($retval as $key => $var) {
    $output .= '<tr>';

    foreach($var as $k => $v) {
        if ($key === 0) {
            $output .= '<td><strong>' . $k . '</strong></td>';
        } else {
            $output .= '<td>' . $v . '</td>';
        }
    }
    $output .= '</tr>';
}
$output .= '</table>';
echo $output;
}

Code for display data

if (isset( $_POST['submit'])){

   $_date      = $_POST["date"];
   //echo $_date;
   $resultArray = array();
   $sql = "SELECT * FROM outstanding WHERE ondate='$_date' ";

   };



   /*echo json_encode($retval);*/
   $retval = mysqli_query( $db_conn, $sql );
   display_data($retval);
    }

EDIT 2:

I was able to achieve this by the following code, please suggest if there are any better methods to display in HTML/web page.

//code to display data in table form
function display_data($retval)
{
    $output = '<table border="1">';
    foreach ($retval as $key => $var)
    {

        echo $key;

        if ($key === 0)
        {
            $output .= '<tr>';
            foreach ($var as $k => $v)
            {
                $output .= '<th><strong>' . $k . '</strong></th>';

            };
            $output .= '</tr>';
        };
        $output .= '<tr>';
        foreach ($var as $k => $v)
        {
            if ($key === 0)
            {
                $output .= '<td>' . $v . '</td>';
                echo $v;
            }
            else
            {
                $output .= '<td>' . $v . '</td>';
            };
        };

        $output .= '</tr>';
    }
    $output .= '</table>';
    echo $output;
}

What have you tried to debug the problem? Additionally, be warned that your code is widely open for SQL injections — Nico Haase, Apr 14 '20 at 15:33
@NicoHaase yes I have tried debugging the problem as I am new to HTML and PHP. Can you please tell me how the code is open for SQLi?? — mahanthesh, Apr 14 '20 at 16:25

score 1 · Answer 1 · edited Apr 15 '20 at 01:03

The reason why you cannot see the first row is that you never display it. If the iteration is on the first row, you only display the header, but you skip the data.

foreach ($var as $k => $v) {
    if ($key === 0) {
        // If this is the first row, you display the key, but not the value.
        $output .= '<td><strong>' . $k . '</strong></td>';
    } else {
        $output .= '<td>' . $v . '</td>';
    }
}

Your suggested solution is the correct one. You need to loop the first row twice. Once to display the header and then once to display the data.

function display_data1($retval) {
    $output = '<table border="1">';
    foreach ($retval as $key => $var) {
        if ($key === 0) {
            // If key is 0, meaning first row...
            $output .= '<thead><tr>';
            // create new HTML row and loop the first MySQL row 
            foreach ($var as $k => $v) {
                $output .= '<th>' . $k . '</th>';
            }
            $output .= '</tr></thead>';
        }

        // Then loop as normal all rows including the first one.
        $output .= '<tr>';
        foreach ($var as $k => $v) {
            $output .= '<td>' . $v . '</td>';
        }
        $output .= '</tr>';
    }

    $output .= '</table>';
    echo $output;
}

Warning: You are wide open to SQL Injections and should use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. Never trust any kind of input! Even when your queries are executed only by trusted users, you are still in risk of corrupting your data. Escaping is not enough!

First row of mysql data fetched through PHP is not displayed in the table

1 Answers1