moskitos moskitos - 4 months ago 11
PHP Question

Create a custom array in php which contain datas from mysql database

I can't find the solution of my problem, I would like to create an array which contains data from 2 different tables from my database.

So first I get those data by doing:

$customArray = [];
$arrayTicket = [];
$sqlTicket = "SELECT * FROM ticket";
foreach ($conn->query($sqlTicket) as $datas) {

$arrayTicket[] = $datas;
}


$myarray = [];
$sqlUsers = "SELECT * FROM organisation";
foreach ($conn->query($sqlUsers) as $data) {

$myarray[] = $data;
}


Then I try to push those data in one array:

foreach ($myarray as $keys => $values) {

foreach ($arrayTicket as $key => $value) {



if($arrayTicket[$key]["organisation_id"] == $myarray[$keys]["id_orga"]){



$customArray[$keys] = $values;
$customArray[$keys]["tickets"][] = $value;


}
}
}
echo '<pre>';
print_r($customArray);
echo '</pre>';
}


And finally the problem is that I get only one Ticket and I should get many tickets with key [0],[1],[2]:

Array
(
[1] => Array
(
[id] => 2
[id_orga] => 37520251
[nom] => Agricole
[url] => https://cubber.zendesk.com/api/v2/organizations/37520251.json
[created] => 2015-01-22 07:59:13
[updated] => 2015-01-22 07:59:13
[tickets] => Array
(
[0] => Array
(
[id] => 2291
[t_user_id] => 2401621445
[id_ticket] => 7280
[tags] => pole_basegi
[titre] => LDM par Marque / AGP-AUFICOM
[date_ouvert] => 2016-07-05 17:20:41
[date_resolv] => 2016-07-06 07:18:16
[statut] => pending
[comment] => Sur les dossiers de RAMBERVILLERS, Commercial / Lettre de Mission / Par Marque => message : " La marque AGP-AUFICOM n'a pas de modèle de lettre de mission"
[custom_field] =>
[organisation_id] => 37520251
)

)

)


I would like to get an array like that with every tickets from the organisation in the key [tickets]:

Array
(
[1] => Array
(
[id] => 2
[id_orga] => 37520251
[nom] => Agricole
[url] => https://cubber.zendesk.com/api/v2/organizations/37520251.json
[created] => 2015-01-22 07:59:13
[updated] => 2015-01-22 07:59:13
[tickets] => Array
(
[0] => Array
(
[id] => 2291
[t_user_id] => 2401621445
[id_ticket] => 7280
[tags] => pole_basegi
[titre] => LDM par Marque / AGP-AUFICOM
[date_ouvert] => 2016-07-05 17:20:41
[date_resolv] => 2016-07-06 07:18:16
[statut] => pending
[comment] => Sur les dossiers de
[custom_field] =>
[organisation_id] => 37520251
)
[1] => Array
(
[id] => 2292
[t_user_id] => 594578401
[id_ticket] => 7281
[tags] => maintenance
[titre] => probleme recup donnée bancaire
[date_ouvert] => 2016-07-06 06:33:53
[date_resolv] => 2016-07-06 07:44:17
[statut] => solved
[comment] => Bonjour,
[custom_field] => 20
[organisation_id] => 34304612
)


)

)

Answer

You're being very inefficient by loading entire MySQL tables in memory just so you can do work with a fraction of them. You're much better off learning how to build SQL queries that get you exactly what you need

SELECT * FROM organisation AS t
LEFT JOIN ticket AS t2
ON t.`id_orga` = t2.`organisation_id`

This query will join the tables into one table; the rows will be matched by the organization's id. It will be much easier to work with.

One other problem I see in your code. Consider the following lines:

$customArray[$keys] = $values;
$customArray[$keys]["tickets"][] = $value;

First off, it's a big problem that you're not using intuitive names for your variables. It increases the likelihood of bugs, and makes code difficult to understand: $customArray, $myArray, $value, $values don't give any indication of what they represent! Names such as $row_organisation, $row_ticket, $key_organization and $key_ticket would be better.

The problem with those two lines though is that you keep overwriting the work you've already done. $customArray[$keys] is set to the same $values many times since $values comes from the outer loop. But then you again set it to a completely different array with the line immediately below it.

My advice. Start over, with a proper SQL query that gets you only what you need.

Comments