$result = array();
array_walk($users, function($x) { $result[$x["userid"]] = $x; });
array_walk($usersmeta, function($x) { $result[$x["userid"]] = array_key_exists($x["userid"],$result) ? array_merge($x,$result[$x["userid"]]) : $x; });
Note: I am assuming you dont have duplicate userid’s in your $users array.
Those anonymous functions don’t have access to $result, so it doesn’t quite work. This does:
$result = array();
array_walk($users, function($x) use (&$result) { $result[$x["userid"]] = $x; });
array_walk($usersmeta, function($x) use (&$result) { $result[$x["userid"]] = array_key_exists($x["userid"], $result) ? array_merge($x, $result[$x["userid"]]) : $x; });
It is fun to try and write minimal code but sometimes you can end up with a working solution that can be a real pain to maintain.
If you simply transform your meta array to be indexed by userid then the merge becomes trivial and probably significantly more performant. Especially for larger datasets. Just a suggestion.
Any way you’ll look at it, you’ll need to loop all data at least 2 times. Whether you do it like @m_hutley (two loops disguised as array_walk), or transforming (which is also a loop) and then using the result to merge (another loop). Performance wise both of these are O(2n).
That said, as is often the case, the question should be raised; where are you getting these arrays from? Is there a better way to retrieve the data? (Like I said a few posts ago… you’re basically doing an OUTER JOIN… so… if you’re pulling this data from a database… do an outer join, and dont bother with all the merging in the first place. Make the database do it for you and hand you the data in the form that you want it in the first place.)
Not really if you can be sure that the user ids in the array are always ordered ascending…
But we do not know…
@pandglobal, let the database do the work for you as @m_hutley suggested. In the future it would be helpful to specify where your data is coming from.
SELECT u.userid, u.email, u.phone, um.city, um.state, um.country
FROM users u
JOIN usersmeta um ON u.userid = um.userid;
DB Fiddle: https://www.db-fiddle.com/f/o1G3nfaxJgjyPmm7SQfe6d/0
CREATE TABLE users (
userid INT(11) NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(20) NOT NULL,
PRIMARY KEY (userid)
);
INSERT INTO users (userid, email, phone)
VALUES
(1, 'lewn@example.com', '667790897'),
(2, 'hen@example.com', '4477905534');
CREATE TABLE usersmeta (
metaid INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
city VARCHAR(255) NOT NULL,
state VARCHAR(255) NOT NULL,
country VARCHAR(255) NOT NULL,
PRIMARY KEY (metaid),
FOREIGN KEY (userid) REFERENCES users(userid)
);
INSERT INTO usersmeta (userid, city, state, country)
VALUES
(1, 'Atlanta', 'Georgia', 'United States'),
(2, 'Sheridan', 'Wyoming', 'United States');
Yes there is no duplicate userids in the users ids,
Yes very helpful however i think the database structure is best the way i made it, and i wouldn’t want a repeating of foreach so i sort for a more php default functions to archive it in a breeze.
Very interesting question
Here are my data structure
// Users table
userid | password | email | phone | status | regdate
// Usersmeta table
userid | metakey | metavalue
However I wanted to know how to merge such output in a more simplified way, incase i only have a set of arrays to work with without a database to fetch from
Thanks alot but the database structure isn’t good with outer join or any join at all. i tried joining and figured out i still have to loop.
Here are my data structure
// Users table
userid | password | email | phone | status | regdate
// Usersmeta table
userid | metakey | metavalue
CREATE TABLE users (
userid INT(11) NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(20) NOT NULL,
PRIMARY KEY (userid)
);
INSERT INTO users (userid, email, phone)
VALUES
(1, 'lewn@example.com', '667790897'),
(2, 'hen@example.com', '4477905534');
CREATE TABLE usersmeta (
metaid INT(11) NOT NULL AUTO_INCREMENT,
userid INT(11) NOT NULL,
metakey VARCHAR(255) NOT NULL,
metavalue VARCHAR(255) NULL,
PRIMARY KEY (metaid)
);
INSERT INTO usersmeta (userid, metakey, metavalue)
VALUES
(1, 'city', 'Georgia'),
(1, 'address', '9 road park avenue'),
(2, 'state', 'Wymoing'),
(1, 'country', 'United States'),
(2, 'city', 'Sheridan'),
(1, 'state', 'Georgia'),
(2, 'country', 'United States'),
(1, 'gender', 'Male'),
(2, 'age', '35');
The data in the usermeta can be endless and in no particular order on entry, userids are repeatative.
// Get users
$users = "SELECT * FROM users";
// Get usersmeta
$usersmeta = "SELECT * FROM usersmeta";
Then start merging the data so as to have userid have accurate data from the usersmeta table where the userid occurred.
// i have tried
$sql = "SELECT * FROM users OUTER JOIN usersmeta ON users.userid = usersmeta.userid";
The results from join still leaves me with a loop
So you actually have a one to many relation between user and meta (pardon my abbreviations). Which frankly is not reflected in your data that you posted when you started the thread. I would have expected something like:
$meta = [
[userid = 42, key='city', value='Huntsvile'],
[userid = 42, key='state', value='AL'],
Regardless, when doing joins (probably want a LEFT JOIN and not an OUTER JOIN), sql flattens things out into one data structure and basically repeats the data of the master table. So you should be getting something like:
$results = [
[userid = 42, email='bbb@mail.com', key='city', value='Huntsvile'],
[userid = 42, email='bbb@mail.com', key='state', value='AL'],
So basically you just need to loop over and stuff the metadata into a new user array. Creating a new user whenever the userid changes.
If you do want to keep the datasets separate then index your users data by userid which takes one loop. The do one loop over your meta data and copy the attribute(s). Pretty sure there are no shortcuts as far as looping goes.
By the way, only do this key/value stuff if you really need it. They can be a real pain to maintain. Might consider looking a NoSql databases if you really need a flexible data structure.
or if you tell us which database you are using we can give you a query which aggregates all meta data to a JSON object inside the user data
okay the results of the array returned from usersmeta when you query alone looks very different and i had to loop through it to have it in the way it is now when i posted, so was avoiding a second looping that was the reason for posting.
if you create similar usersmeta table and insert the values as i stated above, then run a simple select query.
The results will look like this.
array(
[0] => [
'metaid' => 1,
'userid' => 1,
'metakey => 'city',
'metavalue' => 'Atlanta'
],
[1] => [
'metaid' => 2,
'userid' => 1,
'metakey => 'address',
'metavalue' => '9 road avenue'
],
[2] => [
'metaid' => 3,
'userid' => 2,
'metakey => 'city',
'metavalue' => 'Sheridan'
],
[3] => [
'metaid' => 4,
'userid' => 1,
'metakey => 'state',
'metavalue' => 'Georgia'
],
[4] => [
'metaid' => 5,
'userid' => 2,
'metakey => 'state',
'metavalue' => 'Wyoming'
]
Thats how the original data from the usersmeta will look like when queried, i had to use for each loop to make it look like the way i posted it.
the user table needs not much as is already in the desired format.
So the remaining job now to merge both data together and have it file up correctly.
Am using MySQL database
So if you have Multiple meta data sharing the same user just revert the join query
SELECT u.userid, u.email, u.phone, um.city, um.state, um.country
FROM usersmeta um
INNER JOIN users u ON u.userid = um.userid;
Yep. I did not do the key value thing quite right.
I updated my last post.
No Sir this can’t go, the usermeta table is designed to take any new metakey that can be added in the future or anytime depending on what a given user wants in his or her profile.
So it must not be hard coded the way you pointed out.
What if a user has a metakey called age or school or anything else?
So hard coding the city and other given metakey will not include enough of users inputted data
please how did you get this data? is it from the query? and if not how did you get it to this format