Upload mysql query to list

Question

I am making a query in a database that returns a list of users, which I am printing in a table.

I would like to turn this query into a list and then insert these users into another table

This is how I consult:

 $sql= mysqli_query($conect,"select u.username as username,c.fullname from
  mdl_course c left outer join mdl_context cx on c.id = cx.instanceid
  left outer join mdl_role_assignments ra on cx.id = ra.contextid
  left outer join mdl_user u on ra.userid = u.id 
  where ra.roleid in (5) and u.username like '$usuario'
  and cx.contextlevel = 50 and c.id = 5 order by u.username") 
  or die(mysqli_error());

  while($row = mysqli_fetch_array($sql)){  

  }

I’d like to save username, firstname, lastname and email.

How can I do that?

Answer 1

A good way to do this is by creating a class Usuario, so you save "Users" inside a list and you don’t have to keep creating two-dimensional array so you don’t mix the returned users.

class Usuario {

    private $username;
    private $firstname;
    private $lastname;
    private $email;

    public function getUsername() {
        return $this->username;
    }

    public function setUsername($username) {
        this->username = $username;
    }

    public function getFirstname() {
        return $this->firstname;
    }

    public function setFirstname($firstname) {
        this->firstname = $firstname;
    }

    public function getLastname() {
        return $this->lastname;
    }

    public function setLastname($lastname) {
        $this->lastname = $lastname;
    }

    public function getEmail() {
        return $this->email;
    }

    public function setEmail($email) {
        $this->email = $email;
    } 
}

Save it to a file php different and name it as Usuario.php, and make the file include in your bank code.

Then, just start an object, retrieve the data from select and save to array:

include_once(Usuarios.php);//altere de acordo com o caminho onde salvou

...

$arr = Array();

while($row = mysqli_fetch_array($sql)){  

$user = new Usuario();
$user->setUsername($row['username']);
$user->setFirstname($row['firstname']);
$user->setLastname($row['lastname']);
$user->setEmail($row['email']);
$arr[] = $user;

}

And to display/retrieve users of array:

foreach($arr as $user){
  echo $user->getUsername();
  echo $user->getFirstname();
  echo$user->getLastname();
  echo $user->getEmail();
}

If you don’t want to or don’t know how to work with classes and objects, another solution to not mess up multiple users in a list, would be to use a array two-dimensional:

    $usuarios = Array();

   while($row = mysqli_fetch_array($sql)){  

   $user = Array();
   $user['username'] = $row['username'];
   $user['firstname'] = $row['firstname'];
   $user['lastname'] = $row['lastname'];
   $user['email'] = $row['email'];
   $usuarios[] = $user;
   }

To display a user, just use one foreach thus:

foreach($usuarios as $user){
  echo $user['username'];
  echo $user['firstname'];
  echo$user['lastname'];
  echo $user['email'];
}

References:

http://php.net/manual/en/mysqli-result.fetch-array.php

http://forum.imasters.com.br/topic/503939-pegar-valor-retornado-no-mysqli-fetch-array/