CODEWITHSUNDEEP
phpmysql
DealZg
DealZg

Reputation: 135

How to merge two different mysql select results in php with array_merge?

I have two different SELECT queries in php mysql.

I want to combine these two results into one array of objects. I don't want to use SQL UNION because there is some work to be done with the results before the merging.

There is php array_merge() function but when I try to use it, I get the following error:

array_merge(): Argument #1 is not an array

The parameters are the results of a SQL select query.

$result1 = "";
$result2 = "";
$merged_results = "";

$stmt = $db->prepare("SELECT col1, col2 col3 from table1");
$stmt->execute();
$result1 = $stmt->get_result();

$stmt = $db->prepare("SELECT col1, col2 col3 from table2");
$stmt->execute();
$result2 = $stmt->get_result(); 

$merged_results = array_merge($result1,$result2);

My goal is an array of objects where every object represents a dataset from the mysql select, something like that:

[{name:"Jonny",age:23},{name:"Bonny",age:25},{name:"Flower", age:21}]

so please how to merge these to results into one result of array of objects?

Upvotes: 0

Views: 12817

Answers (3)

blupointmedia
blupointmedia

Reputation: 604

get_result() is a mysqli result object, not your returned data. http://php.net/manual/en/mysqli-stmt.get-result.php

You would need to do something like this.

$merged_results = [];

$query = 'SELECT col1, col2 col3 from table1';
$stmt = $mysqli->stmt_init();
$stmt->prepare($query);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_array(MYSQLI_ASSOC))
{
    $merged_results[] = $row;
}

$query = 'SELECT col1, col2 col3 from table2';
$stmt = $mysqli->stmt_init();
$stmt->prepare($query);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_array(MYSQLI_ASSOC))
{
    $merged_results[] = $row;
}

var_dump($merged_results);

or you can do it in one query with union

$stmt = $db->prepare("SELECT col1, col2 col3 from table1 UNION SELECT col1, col2 col3 from table2");
$stmt->bind_param("sss",c1,c2,c3);
$stmt->execute();
$result = $stmt->get_result(); 

while ($row = $result->fetch_array(MYSQLI_ASSOC))
{
    $merged_results[] = $row;
}

var_dump($merged_results);

Upvotes: 3

AbraCadaver
AbraCadaver

Reputation: 78994

You have no placeholders and are not binding any variables, so I don't know why you're using bind_param. Also, you will need to fetch the rows into an array which you are not doing:

$stmt = $db->prepare("SELECT col1, col2, col3 from table1");
$stmt->execute();
$result1 = $stmt->get_result();
while($rows1[] = $result1->fetch_assoc());

$stmt = $db->prepare("SELECT col1, col2, col3 from table2");
$stmt->execute();
$result2 = $stmt->get_result();
while($rows2[] = $result2->fetch_assoc());

$merged_results = array_merge($rows1, $rows2);

There are other ways to go about this since you aren't actually binding any variables, but I used your code as you can extend it if you ever need to bind variables.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

I am actually going to suggest that you just run a union query on MySQL directly:

SELECT col1, col2 col3 FROM table1
UNION ALL
SELECT col1, col2 col3 FROM table2;

This will avoid one extra round trip between PHP and MySQL.

$sql = "SELECT col1, col2 col3 FROM table1 UNION ALL ";
$sql .= "SELECT col1, col2 col3 FROM table2";

$stmt = $db->prepare($sql);
$stmt->execute();
$resultset = $stmt->get_result();

If you also want to keep track of the source of each record, this too can be handled on MySQL using a computed column:

SELECT col1, col2 col3, 'table1' AS source FROM table1
UNION ALL
SELECT col1, col2 col3, 'table2' FROM table2;

Upvotes: 2

Related Questions