I am currently working on a PHP script that encodes user data into JSON, as well as associated actions/events a user might perform. For example, some fields from the user data (defined in a 'USER' MySQL table) include the user's first and last name, website login, and points for certain actions they complete. In a second table, known as 'MAYDO', the actions performed by the user are stored (referenced by IDs), and include information such as what the user did (went to a Starbucks, purchased X, did the action between dates Y and Z, etc.)
My Question was, how do I go about referencing each element in my 'MAYDO' table and associate it with the correct user, in the form of a JSON list? I want to have some sort of functionality like this:
{
'USER' {
'Name': 'John Doe',
'Occupation': 'Farmer',
'Age': 39
'User_id': 1
},
'MAYDO' [{
'User_Id': 1,
'Maydo_Id': 1,
'Event': 'Go to Farmer\'s Market',
'When': '2015-10-13 16:30:05'
},
{
'User_Id': 1,
'Maydo_Id': 2,
'Event': 'Sell chickens at the auction',
'When': '2015-11-13 12:00:00'
}]
}
Essentially, I want all the rows in the 'MAYDO' table to associate with their respective Users who performed them, and basically become the list for that User's JSON dataa (one JSON file per person). The code I have currently tested only takes the last entry from the 'MAYDO' table (in the event of multiple events), and so I wanted to get some insight into how to fix this. Any help or tips are appreciated. Thank you!
Current code (Encodes the User data perfectly; only encodes the last 'MAYDO' entry)
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>USER DATA TO JSON</title>
</head>
<body>
<?php
# Define the connection to the database
DEFINE('DB_SERVER', 'localhost');
DEFINE('DB_USER', 'root');
DEFINE('DB_PASSWORD', '');
DEFINE('DB_NAME', 'Maydo');
# Create a connection to the database
$connection = @mysqli_connect(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);
$index = 1;
$query1 = "SELECT * FROM USER WHERE USER_ID = " . $index;
$userinfo = array();
$username = "";
$result1 = mysqli_query($connection, $query1) or die("ERROR: " .
mysqli_error($connection));
while($row = mysqli_fetch_assoc($result1)) {
$userinfo['User'] = $row;
$username = $row['USER_NAME'];
}
$query2 = "SELECT * FROM MAYDO WHERE USER_ID = " . $index;
$result2 = mysqli_query($connection, $query2) or die("ERROR: " .
mysqli_error($connection));
while($row = mysqli_fetch_assoc($result2)) {
$userinfo['Maydo'] = array($row);
}
echo json_encode($userinfo, JSON_NUMERIC_CHECK);
?>
</body>
</html>
