php array format from the mysqli_fetch_assoc rows

[amirelgohary1990]

I need to output the exact below array format from the mysqli_fetch_assoc rows

The Array "Need to achieve like this"

["172"=>["4","6"],"174"=>["4","6"],"175"=>["4","3","6"],"176"=>["4","3"],"177"=>["4","6"],"181"=>["3","6"],"182"=>["7"],"183"=>["3","4"],"184"=>["4","3","6"],"185"=>["3","6"],"186"=>["8","6"],"188"=>["3","6"],"189"=>["3","6"],"190"=>["6"],"191"=>["3","6","4","7"]];

It's var_dump "Need to achieve like this"

array(15) {
[172]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "6"}
[174]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "6" }
[175]=> array(3) { [0]=> string(1) "4" [1]=> string(1) "3" [2]=> string(1) "6" }
[176]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "3" }
[177]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "6" }
[181]=> array(2) { [0]=> string(1) "3" [1]=> string(1) "6" }
[182]=> array(1) { [0]=> string(1) "7" }
[183]=> array(2) { [0]=> string(1) "3" [1]=> string(1) "4" }
[184]=> array(3) { [0]=> string(1) "4" [1]=> string(1) "3" [2]=> string(1) "6" }
[185]=> array(2) { [0]=> string(1) "3" [1]=> string(1) "6" }
[186]=> array(2) { [0]=> string(1) "8" [1]=> string(1) "6" }
[188]=> array(2) { [0]=> string(1) "3" [1]=> string(1) "6" }
[189]=> array(2) { [0]=> string(1) "3" [1]=> string(1) "6" }
[190]=> array(1) { [0]=> string(1) "6" }
[191]=> array(4) { [0]=> string(1) "3" [1]=> string(1) "6" [2]=> string(1) "4" [3]=> string(1) "7" }
}

 

My Code

$query = mysqli_query($dbConnection,"SELECT id, job_category_id FROM jobs");
while($row = mysqli_fetch_assoc($query)){
$job_id = $row['id'];
$job_category_id = htmlspecialchars($row['job_category_id']);
$job_category_id = unserialize(base64_decode($job_category_id));

$asscArrays = [$job_id=>$job_category_id];   // Here I am trying to achieve the array template like the I mentioned above

}

var_dump $asscArrays

array(1) {
[172]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "6" }
}
array(1) {
[174]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "6" }
}
array(1) {
[175]=> array(3) { [0]=> string(1) "4" [1]=> string(1) "3" [2]=> string(1) "6" }
}
array(1) {
[176]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "3" }
}
array(1) {
[177]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "6" }
}
array(1) {
[181]=> array(2) { [0]=> string(1) "3" [1]=> string(1) "6" }
}
array(1) {
 [182]=> array(1) { [0]=> string(1) "7" }
}

 

[ginerjm]

You are showing us a couple of arrays but you are not showing us how the data that is being queried is stored.  Are you trying to display query results of arrays?

[amirelgohary1990]

23 minutes ago, ginerjm said:

You are showing us a couple of arrays but you are not showing us how the data that is being queried is stored.  Are you trying to display query results of arrays?

Hello, I am selecting two rows, first is 'id' and second row I am selecting serialized 'category_ids', then I unserialized it
Those two rows I selected 'id' + unserialized 'category_ids' ,, I am trying to output then into specific array template like the below array example

The Array "I Need to achieve like this"

["172"=>["4","6"],"174"=>["4","6"],"175"=>["4","3","6"],"176"=>["4","3"],"177"=>["4","6"],"181"=>["3","6"],"182"=>["7"],"183"=>["3","4"],"184"=>["4","3","6"],"185"=>["3","6"],"186"=>["8","6"],"188"=>["3","6"],"189"=>["3","6"],"190"=>["6"],"191"=>["3","6","4","7"]];

It's var_dump "I Need to achieve like this"

array(15 { [172]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "6"} [174]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "6" } [175]=> array(3) { [0]=> string(1) "4" [1]=> string(1) "3" [2]=> string(1) "6" } [176]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "3" } [177]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "6" } [181]=> array(2) { [0]=> string(1) "3" [1]=> string(1) "6" } [182]=> array(1) { [0]=> string(1) "7" } [183]=> array(2) { [0]=> string(1) "3" [1]=> string(1) "4" } [184]=> array(3) { [0]=> string(1) "4" [1]=> string(1) "3" [2]=> string(1) "6" } [185]=> array(2) { [0]=> string(1) "3" [1]=> string(1) "6" } [186]=> array(2) { [0]=> string(1) "8" [1]=> string(1) "6" } [188]=> array(2) { [0]=> string(1) "3" [1]=> string(1) "6" } [189]=> array(2) { [0]=> string(1) "3" [1]=> string(1) "6" } [190]=> array(1) { [0]=> string(1) "6" } [191]=> array(4) { [0]=> string(1) "3" [1]=> string(1) "6" [2]=> string(1) "4" [3]=> string(1) "7" } }

 

 

When I try to achieve this by the following 

 $asscArrays = [$job_id=>$job_category_id];  

It's printing the array result but separately array(1) not array(15) ,, like the below

array(1) { [172]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "6" } } array(1) { [174]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "6" } } array(1) { [175]=> array(3) { [0]=> string(1) "4" [1]=> string(1) "3" [2]=> string(1) "6" } } array(1) { [176]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "3" } } array(1) { [177]=> array(2) { [0]=> string(1) "4" [1]=> string(1) "6" } } array(1) { [181]=> array(2) { [0]=> string(1) "3" [1]=> string(1) "6" } } array(1) { [182]=> array(1) { [0]=> string(1) "7" } }

The array template I am willing to achieve is array(15)

[Barand]

If you are storing serialized, base64encoded lists of ids in a column in a relational database table then you are on your own.

Come back when you have correctly normalized data.

[amirelgohary1990]

15 minutes ago, Barand said:

If you are storing serialized, base64encoded lists of ids in a column in a relational database table then you are on your own.

Come back when you have correctly normalized data.

You mean that serialized, base64encoded is not a good practice for such storing ?

[Barand]

7 minutes ago, amirelgohary1990 said:

is not a good practice for such storing ?

The understatement of the year - it's terrible.

[Barand]

You have a situation where a job can have many categories and a category can contain many jobs; a many-to-many relationship. These a resolved by an intermediate table. Your data

model should like this ...

   +-------------+                                        +-----------------+
   | job         |                                        | category        |
   +-------------+        +--------------------+          +-----------------+
   | id          |---+    | job_category       |    +-----| id              |
   | job_title   |   |    +--------------------+    |     | cat_description |
   +-------------+   |    | id                 |    |     +-----------------+
                     +---<| job_id             |    |
                          | category_id        |>---+
                          +--------------------+

so that where you now the equivalent of this for your job table,

   +--------+--------------+
   | job_id | category_id  |
   +--------+--------------+
   |   3    | 2, 4, 5      |
   |   4    | 1, 2         |    
   +--------+--------------+

you would have a job_category table like this

   +----+--------+--------------+
   | id | job_id | category_id  |
   +----+--------+--------------+
   | 1  |   3    |     2        |
   | 2  |   3    |     4        |
   | 3  |   3    |     5        |
   | 4  |   4    |     1        |    
   | 5  |   4    |     2        |    
   +----+--------+--------------+

Now it's a simple to find which jobs are in a particular category as it is to find which categories a job belongs to.

[ginerjm]

Why did you choose to store your data in such a strange way???

[amirelgohary1990]

21 hours ago, ginerjm said:

Why did you choose to store your data in such a strange way???

I started using serialize because I wanted to store multiple steps of values to create dynamic taxes for a HRM system

I searched for a way I found that serialize can be used if these data will not be used for analytics, just will sore it, then later I  retrieve data as array normally

 

[amirelgohary1990]

22 hours ago, Barand said:

Now it's a simple to find which jobs are in a particular category as it is to find which categories a job belongs to.

Yes, I will do this, should be better, thanks