[Help]Comparing two multi-dimensional arrays

[lahonda_99]

Hello fellow coders! My brain is about to explode! Does anyone have some time to help me with a small problem?

 

I have two multi-dimensional arrays similar to the below:

 

mysql_table_schedule
[0]
  [firstname] = Roger
  [id] = xxx
[1]
  [firstname] = Pamela
  [id] = xxy
mysql_table_roster
[0]
  [firstname] = Roger
  [id] = xxx
[1]
  [firstname] = Pamela
  [id] = xxy
[2]
  [firstname] = Orsen
  [id] = xyy

 

I can build both arrays above after grabbing the data from the MySQL database by using a quick for function

for($i = 0; $array[$i] = mysql_fetch_assoc($mysql_roster); $i++) ;

but after I have both arrays how do I keep only the records in mysql_table_roster that are in the schedule. Out of each daily schedule only a few people work. I'd like to keep all of the associated information from the roster like first name, last name, phone number, etcetera.

 

If yesterday only Roger and Pamela worked how do I compare by [firstname] and trim the mysql_table_roster to inlclude only the arrays related to Roger and Pamela?

 

I've looked into array_intersect, but have no clue how to specify a comparison by a specific array key within multi-dimensional arrays.

[Muddy_Funster]

Have you thought about changing the actual query to only return the information that you want?

[lahonda_99]

Thank you for replying Muddy.

 

I can use a single or multiple mysql queries, but I am needing to look up probably 55 entries at a go and I read in another forum to never put a query inside of a while loop. One way to do it would be to grab the ids of people that worked and look those up in the roster:

 

while($result=mysql_fetch_assoc(mysql_query(SELECT * FROM SCHEDULE WHERE worked='yes')))
{
  while($secondresult=mysql_fetch_assoc(mysql_query(SELECT * FROM SCHEDULE WHERE id='$result[id]')))

 

Do you think this is feasible? My gut tells me that this page wouldn't load quickly from my company servers.

 

Another way to look up the info would be to grab the people working from the schedule then look up all id numbers in a single query to the roster:

 

mysql_query(SELECT * FROM ROSTER WHERE id='$result[0][id]' OR  id='$result[1][id]' OR  id='$result[2][id]'...{all the way to 54}...)

 

I'll try both of these when I get back from work today. Do you have any suggestions on how I could speed up this look-up? Both of these suggestions I thought of in the shower a few minutes ago seem heavy handed and slow.

[Muddy_Funster]

let me see your tables, the thing about running queries in loops is that I have only ever found 1 instance where it HAD to be done, all the rest just needed a single querie with joins

[lahonda_99]

Schedule; perhaps 50 people working per day.

 

Roster; the total 65 people employed in the division.

 

I would like to check the unique_ids of the people in the 'schedule' working today against the unique_ids in the 'roster' resulting in an array from the roster that retains each records' extra fields like phone, cell, eval, active...

[Muddy_Funster]

Then something like:

SELECT phone, cell, eval, active, division, station, email_work, email_home 
FROM roster LEFT JOIN schedual ON (schedual.unique_id = roster.unique_id)
WHERE (
DATE(NOW()) = CAST(CONCAT_WS('-',year,month,day) as date)
)

Should get back the information that you want in a single query.

[lahonda_99]

I just read some tutorials on MySQL JOIN. This is amazing! I didn't know this command existed! Muddy, thank you so much for sticking with me these last days to prevent my brain from exploding.   I'm going to try out that command, but looking at it after reading those tutorials, I can see that this is what I was searching for.