Merging Arrays, Adding values together

Question

I have two tables in my database:

webHistory

searchTerm | userId | date | hasLocation | historyId

LocationHistory.

I'm queuing each as so.

Web History:

$stmt_hour = $conn->prepare("SELECT HOUR(date) as hr, COUNT(*) as num_rows FROM    
                             webHistory WHERE userId=? AND date BETWEEN (SYSDATE() - INTERVAL 1 DAY) 
                             AND SYSDATE() GROUP BY HOUR(date);");

$stmt_hour->bind_param("s", $userid);
$stmt_hour->execute();
$stmt_hour->bind_result($a, $b, $c);
  while($stmt_hour->fetch()) {
    $hourArr[$a] = $c;
  }

Location History

$stmt_hour = $conn->prepare("SELECT HOUR(date) as hr, COUNT(*) as num_rows FROM    
                             locationHistory WHERE userId=? AND date BETWEEN (SYSDATE() - INTERVAL 1 DAY) 
                             AND SYSDATE() GROUP BY HOUR(date);");

$stmt_hour->bind_param("s", $userid);
$stmt_hour->execute();
$stmt_hour->bind_result($a, $b, $c);
  while($stmt_hour->fetch()) {
    $hourLocArr[$a] = $c;
  }

when a user searches with a word, it a row into webHistory, so to get all of the searches a user made in the last 24 hours I count the rows between two dates.

When a user searches by location, database is checked to see if the user has already searched in that location, if so the amount is incremented, if not the row is added with a value of one. I then get the SUM of this from the second query.

Lastly if a user searched by word & query both operations happen adding the row into webHistory and adding/incrementing in locationHistory.

I need to add the response of query 1 to the response of query two, in the format hour => amount. So:

Query 'Web History':

10 => 9
16 => 4
20 =>2

Query location History

4 => 45
10 => 2
22 => 12

Sum of query + location

4 => 45
10 => 11
16 => 4
20 => 2
22 => 12

Lastly if searched by word & query I will only need to take the values of location, I would guess I would do this through a unique Id. So far I do have a unique Id field. But as the locationHistory increments the amount field thus leading to the unique id to only be the most recent ID and redundant for this issue, I'm not sure how to handle it.

I was thinking for combining the to arrays to simply use array_marge()? But would would be the best way to go about this issue and most importantly how would I deal with the latter half of the issue.

Cheers

Why not combine the queries (if needed) and do the logic in SQL? — kero, Jun 17 '14 at 17:05
I thought about that but how would I go about combining the queries if the search is only on the webHistory or Location but not if it's on both. If the search is made with just a word, it adds a row into just webHistory. If it's made on just the location it adds a row/increments the amount in locationHistory. If the search is made By a word and location it adds / increments both, but it counts as only one search so I only need the result of one of the tables. — Jamie Turner, Jun 17 '14 at 17:12
The two tables have several fields in common. You may be able to use a join. There are many types so experiment. — Useless Intern, Jun 17 '14 at 17:14

score 0 · Accepted Answer · edited May 23 '17 at 12:13

After reading on stackoverflow array union be your best bet(for the second question) due to this paragraph

The + operator returns the right-hand array appended to the left-hand array; for keys that exist in both arrays, the elements from the left-hand array will be used, and the matching elements from the right-hand array will be ignored.

$webHistory = array(10 => 9,
16 => 4,
20 =>2);

$locationHistory = array(4 => 45,
10 => 2,
22 => 12);

print_r($locationHistory + $webHistory );

->Array ( [4] => 45 [10] => 2 [22] => 12 [16] => 4 [20] => 2 )

It preserves the keys and uses locationHistory as the primary source of information. :D

So make sure you merge webhistory onto locationhistory so if they overlap location will overwrite the previous value.

EDIT

The following solves the first question

$webHistory = array(10 => 9,
16 => 4,
20 =>2);

$locationHistory = array(4 => 45,
10 => 2,
22 => 12);

$tempArray = $locationHistory;
foreach ($webHistory as $key => $value){
   $tempArray[$key] = $value + $tempArray[$key];
}
print_r($tempArray);

->Array ( [4] => 45 [10] => 11 [22] => 12 [16] => 4 [20] => 2 )

Merging Arrays, Adding values together

1 Answers1