5120bee 5120bee - 13 days ago 5
PHP Question

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();}
}

Answer

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;
 }
Comments