Select a table and display its data in a table using a while loop

Question

I am trying to display 25 rows of data from my db.table in an html table using a php while loop to loop for 25 rows. Currently I do not have the 25 rows limiter just wanting to get data displayed at the moment. Here is what I have.

<table>
    <td><strong>User Name</strong></td>                 
    <td><strong>User Email</strong></td>                        
    <td><strong>Is User an Admin</strong></td>
    <td><strong>Is User Active</strong></td>
<?php
$sql = 'SELECT name, login, is_admin, active
        FROM db.users';
$result = db_exec_prepared_stmt($sql);
while($rows = mysql_fetch_assoc($result)) {
    $user_name = $rows['name'];
    $user_email = $rows['login'];
    $user_admin = $rows['is_admin'];
    $user_active = $rows['active'];

    echo '<tr>
             <td>' . $user_name . '</td>
             <td>' . $user_email . '</td>
             <td>' . $user_admin . '</td>
             <td>' . $user_active . '</td>
         </tr>';
}
?>
</table>

I know that mysql_fetch_assoc() will not work here but I am needing help to get functioning code.

Here is the db_exec_prepared_stmt() function.

function db_exec_prepared_stmt($sql, $params=array(), $query_type='select') {
$types = '';
foreach($params as $p)  {
    if( is_numeric($p)
        && ($p <= 2147483647) 
        && (numberOfDecimals($p) === 0)
    ) $types .= 'i';
    else $types .= 's';
}
$db = db_open_connection();

if($stmt = $db->prepare($sql))  { 
    if($types != '')    {
        $binds = array_merge( array($types), $params );


        call_user_func_array( array($stmt, 'bind_param'), makeValuesReferenced($binds) );
    }

    switch($query_type) {
        case 'select':
            $results = db_fetch_assoc($stmt);
            break;

        case 'insert':
            $stmt->execute();
            $results = $db->insert_id;
            break;

        default:
            $stmt->execute();
            $results = null;
            break;
    }

    if('' != $stmt->error) printf("Error %s: %s.\n", $stmt->errno, $stmt->error);
}
else    {
    printf("Error %s: %s.\n", $db->errno, $db->error);
    $results = null;
}

db_close_connection($db);

return $results;
}

This looks to be a homework like question. Also what are you using to connect to your db? mysql_connect_db? — Tyson of the Northwest
– Tyson of the Northwest, Commented Mar 27, 2013 at 19:08
new MySQLi(); Your answer below helped me figure it out. The connection and function db_exec_prepared_stmt() are all legacy code that does function properly. This is for my job. Dev in training. — ralliart2004
– ralliart2004, Commented Mar 27, 2013 at 19:14
keen, and remember echo"<pre>";var_dump($i); is your friend, especially when you are getting unexpected results from SELECT queries. — Tyson of the Northwest
– Tyson of the Northwest, Commented Mar 27, 2013 at 19:27

Tyson of the Northwest · Accepted Answer · 2013-03-27 19:03:53Z

2

I am unfamiliar with how your db_exec_prepared_stmt is preparing the connection. But it looks like db_exec_prepared_stmt is querying the database and using db_fetch_assoc to all results and return them as an array of results. I would var_dump($result = db_exec_prepared_stmt($sql)); and see what you are getting. If you are getting a single dimensional array then you need to modify db_exec_prepared_stmt so that when the query type is select

case 'select':
    while($row=db_fetch_assoc($stmt)){
      $results[]=$row;
    }
    break;

If you are getting an array containing an array of rows then I would switch to a foreach

foreach(db_exec_prepared_stmt($sql) as $row){
  //WORK
}

Or if you have to use a while loop

while(next($results)){
  //WORK
}

Honestly, I would not use the db_exec_prepared_stmt since it tries to be everything for everyone, and doesn't do it very well. Also you may want to look into PDO objects.

answered Mar 27, 2013 at 19:03

Tyson of the Northwest

2,1832 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ralliart2004 Over a year ago

Thank you much!! I used the foreach and it worked beautifully!! Upvote (as soon as I can upvote lol) and accepted answer. Thanks again.

Collectives™ on Stack Overflow

Select a table and display its data in a table using a while loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related