amirelgohary1990 Posted July 16, 2023 Share Posted July 16, 2023 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" } } Quote Link to comment Share on other sites More sharing options...
ginerjm Posted July 16, 2023 Share Posted July 16, 2023 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? Quote Link to comment Share on other sites More sharing options...
amirelgohary1990 Posted July 16, 2023 Author Share Posted July 16, 2023 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) Quote Link to comment Share on other sites More sharing options...
Barand Posted July 16, 2023 Share Posted July 16, 2023 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. Quote Link to comment Share on other sites More sharing options...
amirelgohary1990 Posted July 16, 2023 Author Share Posted July 16, 2023 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 ? Quote Link to comment Share on other sites More sharing options...
Barand Posted July 16, 2023 Share Posted July 16, 2023 7 minutes ago, amirelgohary1990 said: is not a good practice for such storing ? The understatement of the year - it's terrible. Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted July 16, 2023 Solution Share Posted July 16, 2023 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. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted July 16, 2023 Share Posted July 16, 2023 Why did you choose to store your data in such a strange way??? Quote Link to comment Share on other sites More sharing options...
amirelgohary1990 Posted July 17, 2023 Author Share Posted July 17, 2023 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 Quote Link to comment Share on other sites More sharing options...
amirelgohary1990 Posted July 17, 2023 Author Share Posted July 17, 2023 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.