i'm new on php and i have a problem to fetch some data on mysql.
I need to fetch the information that are stored in 3 different tables, i managed to get the data but i have a problem how i fetch the result.
$stmt = $this->conn->prepare("SELECT bike.id, bike.photo, bike.gallery, bike.motorbike_id, bike.privacy,
bike.nickname, bike.data_acquire, dets.name, dets.company, dets.release_year,
dets.displacement, dets.horsepower, dets.type, pers.id, pers.name, pers.photo,
pers.cost, pers.type, pers.date_acquire, manu.id, manu.cost, manu.name, manu.date
FROM mt_userbikes AS bike
LEFT JOIN mt_motorbikes AS dets ON dets.id = bike.motorbike_id
LEFT JOIN mt_userbike_personalizations AS pers ON pers.userbike_id = bike.id
LEFT JOIN mt_userbike_maintenances AS manu ON manu.userbike_id = bike.id
WHERE bike.id = ?");
$stmt->bind_param("i", $motorBikeId);
if ($stmt->execute()) {
$stmt->bind_result($id, $photo, $gallery, $motorbike_id, $privacy, $nickname,
$data_acquire, $motoName, $motoCompany, $motoReleaseYear,
$motoDisplacement, $motoHorsepower, $motoType, $persId,
$persName, $persPhoto, $persCost, $persType, $persDateAcquire,
$manuId, $manuCost, $manuName, $manuDate);
$data = array();
$other = array();
$i = 0;
while ($stmt->fetch()) {
if ($i === 0) {
$data["id"] = $id;
$data["photo"] = $photo;
$data["gallery"] = $gallery;
$data["motorbikeId"] = $motorbike_id;
$data["privacy"] = $privacy;
$data["nickname"] = $nickname;
$data["dataAcquire"] = $data_acquire;
$info = array();
$info["name"] = $motoName;
$info["company"] = $motoCompany;
$info["releaseYear"] = $motoReleaseYear;
$info["displacement"] = $motoDisplacement;
$info["horsepower"] = $motoHorsepower;
$info["type"] = $motoType;
$data["info"] = $info;
}
$values = array();
$values["pers.id"] = $persId;
$values["pers.name"] = $persName;
$values["pers.photo"] = $persPhoto;
$values["pers.cost"] = $persCost;
$values["pers.type"] = $persType;
$values["pers.date_acquire"] = $persDateAcquire;
$values["manu.id"] = $manuId;
$values["manu.cost"] = $manuCost;
$values["manu.name"] = $manuName;
$values["manu.date"] = $manuDate;
$other[$i] = $values;
$i++;
}
$data["other"] = $other;
This is how I get the results now:
{
"id": 1,
"photo": null,
"gallery": null,
"motorbikeId": 1,
"privacy": null,
"nickname": "maro",
"dataAcquire": null,
"info": {
"name": "MT-07",
"company": "Yamaha",
"releaseYear": 2013,
"displacement": "689 cc",
"horsepower": "55,0 kW (74,8CV)",
"type": "Naked"
},
"other": [
{
"pers.id": 1,
"pers.name": "cupolino",
"pers.photo": null,
"pers.cost": 100,
"pers.type": "accessorio",
"pers.date_acquire": null,
"manu.id": 1,
"manu.cost": 80,
"manu.name": "controllo annuale",
"manu.date": "2015-07-28"
},
{
"pers.id": 2,
"pers.name": "copriradiatore",
"pers.photo": null,
"pers.cost": 80,
"pers.type": "accessorio estetico",
"pers.date_acquire": null,
"manu.id": 1,
"manu.cost": 80,
"manu.name": "controllo annuale",
"manu.date": "2015-07-28"
},
{
"pers.id": 3,
"pers.name": "protezioni laterale radiatore sportive",
"pers.photo": null,
"pers.cost": 100.5,
"pers.type": "accessorio estetico",
"pers.date_acquire": null,
"manu.id": 1,
"manu.cost": 80,
"manu.name": "controllo annuale",
"manu.date": "2015-07-28"
},
{
"pers.id": 4,
"pers.name": "copri avviatore",
"pers.photo": null,
"pers.cost": 15,
"pers.type": "accessorio estetito",
"pers.date_acquire": null,
"manu.id": 1,
"manu.cost": 80,
"manu.name": "controllo annuale",
"manu.date": "2015-07-28"
},
{
"pers.id": 1,
"pers.name": "cupolino",
"pers.photo": null,
"pers.cost": 100,
"pers.type": "accessorio",
"pers.date_acquire": null,
"manu.id": 2,
"manu.cost": 100,
"manu.name": "controllo 1000km",
"manu.date": "2014-09-02"
},
{
"pers.id": 2,
"pers.name": "copriradiatore",
"pers.photo": null,
"pers.cost": 80,
"pers.type": "accessorio estetico",
"pers.date_acquire": null,
"manu.id": 2,
"manu.cost": 100,
"manu.name": "controllo 1000km",
"manu.date": "2014-09-02"
},
{
"pers.id": 3,
"pers.name": "protezioni laterale radiatore sportive",
"pers.photo": null,
"pers.cost": 100.5,
"pers.type": "accessorio estetico",
"pers.date_acquire": null,
"manu.id": 2,
"manu.cost": 100,
"manu.name": "controllo 1000km",
"manu.date": "2014-09-02"
},
{
"pers.id": 4,
"pers.name": "copri avviatore",
"pers.photo": null,
"pers.cost": 15,
"pers.type": "accessorio estetito",
"pers.date_acquire": null,
"manu.id": 2,
"manu.cost": 100,
"manu.name": "controllo 1000km",
"manu.date": "2014-09-02"
}
]
}
what I want to get
{
"id": 1,
"photo": null,
"gallery": null,
"motorbikeId": 1,
"privacy": null,
"nickname": "maro",
"dataAcquire": null,
"info": {
"name": "MT-07",
"company": "Yamaha",
"releaseYear": 2013,
"displacement": "689 cc",
"horsepower": "55,0 kW (74,8CV)",
"type": "Naked"
},
"personalizations": [
{
"pers.id": 1,
"pers.name": "cupolino",
"pers.photo": null,
"pers.cost": 100,
"pers.type": "accessorio",
"pers.date_acquire": null,
},
{
"pers.id": 2,
"pers.name": "copriradiatore",
"pers.photo": null,
"pers.cost": 80,
"pers.type": "accessorio estetico",
"pers.date_acquire": null,
}
...
]
"maintenances": [
{
"manu.id": 1,
"manu.cost": 80,
"manu.name": "controllo annuale",
"manu.date": "2015-07-28"
},
{
"manu.id": 2,
"manu.cost": 100,
"manu.name": "controllo 1000km",
"manu.date": "2014-09-02"
}
...
]
}
how can i distinguish the rows from one table to another? i hope that what i did is not stupid, if so can you help me?
