Search code examples
phpdatabaseselectmysqliappointment

What is the best practice for selecting and displaying all values from an 'Appointment' table with a matching userID from a 'User' table?


I'm very new to coding with MySQLi using PHP. I'm currently trying to code a simple appointment-user application with 2 tables in my database which I've already input some test values in as shown below.

I am really trying all my best but I don't know everything about MySQLi yet and I'm willing to learn. I've already spent all of yesterday looking around this site but I haven't come across any previous posts that answered my problem. Thank you for any help you can offer a newbie like me.

What I have working so far:

  • I've saved the user's login SESSION in another session. It works fine and retrieves the correct user row based on their username login.
  • The $_SESSION['appointment'] displays the appointment array for the particular user with the matching userID

The problem

  • The $_SESSION['appointment'] ONLY displays the first result of theappointment array for the particular user with the matching userID.
  • In my case, it only displays the $_SESSION['appointment']['date'] = 2016-12-20 for Bob Marley and does not display all his other appointments 2016-12-31 and 2016-12-29 even when it has the same matching userID = 3

How do I display all of a user's related appointment dates if they have a matching userID in the 'appointment' table?

My database tables:

'users' table:

userID (column 1) // username (column 2) // name (column 3)

1 // user1 // Bob Dylan

2 // user2 // John Lennon

3 // user3 // Bob Marley

-

'appointment' table

'appointmentID' (column 1) // userID (column 2) // date (column 3)

1 (appointmentID) // 1 (userID) // 2016-12-07

2 (appointmentID) // 2 (userID) // 2016-12-15

3 (appointmentID) // 3 (userID) // 2016-12-20

4 (appointmentID) // 3 (userID) // 2016-12-31

5 (appointmentID) // 3 (userID) // 2016-12-29

My SESSIONS:

 //Displays the array of the user
 $_SESSION['profile'] = displayProfile($_SESSION['user']);

 //Displays the array of the appointment based on the matching userID
 $_SESSION['appointment'] = displayAppointment($_SESSION['profile']['userID']);

My mySQLi functions:

 function displayProfile($username)
 {
     global $ db_con; //my database connection

     $query = 'SELECT * FROM users WHERE username=\''.mysqli_escape_string($db_con, $username).'\'';
     $result = $db_con-> query($query);

     //This fetches the associative array for the user row 
     //(i.e. $_SESSION['profile']['userID'] outputs the userID of the user logged into the system
     if ($result->num_rows) { return $result->fetch_assoc();}
 }

  //////////////////////////////

   function displayAppointment($userID)
 {
     global $ db_con; //my database connection

     //This passes the current logged on user's userID into the query.
     //For example, if Bob Marley was currently logged on, the $userID that will be passed is userID = 3.
     $query = 'SELECT * FROM appointment WHERE userID='.$userID;
     $result = $db_con-> query($query);

     //This fetches the associative array for the appointment row with the correct matching userID
     //(i.e. $_SESSION['profile']['userID'] for Bob Marley is '3'
     if ($result->num_rows) { return $result->fetch_assoc();}
 }

Solution

  • so what you require is to return everything from the function right then you should use

    fetch_all()

    editenote: becouse he doesnt have the drivers for fetch_all

    function displayAppointment($userID)
     {
         global $ db_con; //my database connection
    
         //This passes the current logged on user's userID into the query.
         //For example, if Bob Marley was currently logged on, the $userID that will be passed is userID = 3.
         $query = 'SELECT * FROM appointment WHERE userID='.$userID;
         $result = $db_con-> query($query);
    
         //This fetches the associative array for the appointment row with the correct matching userID
         //(i.e. $_SESSION['profile']['userID'] for Bob Marley is '3' 
        $resultout=array();
        while ($row = mysqli_fetch_assoc($result)) {
            array_push($resultout, $row);
        }
        return $resultout;
     }