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

Question

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?

Get rid of these $stmt->bind_param("sss",c1,c2,c3) You're not binding anything and have no parameters. — AbraCadaver
– AbraCadaver, Commented Feb 12, 2019 at 15:27
Then you will need to fetch into an array php.net/manual/en/mysqli-result.fetch-assoc.php — AbraCadaver
– AbraCadaver, Commented Feb 12, 2019 at 15:30
why should i get rid of this? its a prepared statement? please explain — DealZg
– DealZg, Commented Feb 12, 2019 at 15:30
No it's not. You have no placeholders and are not binding any variables. What the heck are c1,c2,c3? — AbraCadaver
– AbraCadaver, Commented Feb 12, 2019 at 15:31
Your problem is the SQL is incorrect.. All columns in the SQL should be comma separated. So this seams to a simple typographical error question? — Raymond Nijland
– Raymond Nijland, Commented Feb 12, 2019 at 15:39

blupointmedia · Accepted Answer · 2019-10-21 17:06:03Z

3

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);

edited Oct 21, 2019 at 17:06

answered Feb 12, 2019 at 15:46

blupointmedia

6044 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

blupointmedia Over a year ago

Why the down vote? I think a user should give a reason for giving a down vote when they give it. I answered the user's question and gave a better solution to their problem.

ToolmakerSteve Over a year ago

minor typo: $result2 vs $result, in second code snippet.

Tim Biegeleisen · Accepted Answer · 2019-02-12 15:28:46Z

2

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;

answered Feb 12, 2019 at 15:28

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Comments

AbraCadaver · Accepted Answer · 2019-02-12 15:41:31Z

2

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.

edited Feb 12, 2019 at 15:41

answered Feb 12, 2019 at 15:37

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

2 Comments

Raymond Nijland Over a year ago

The SQL is incorrect.. All columns in the SQL should be comma separated you copy/paste from the topicstarters code without validating.

ToolmakerSteve Over a year ago

Minor simplification (when applicable): don't need $rows2 and the array_merge - unless there is a reason to work with the two answers separately. Simply use $merged_results directly in both while loops: while($merged_results[] = ....

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related