Joining three queries which are in array

Question

I'm trying to add three queries:

$query1 = "SELECT * FROM sailors S";
$query2 = "SELECT * FROM boats B";
$query3 = "SELECT * FROM reserves R";

which i need to store in array:

$arr1 = array($query1);
$arr2 = array($query2);
$arr3 = array($query3);

I tried this :

$queryall = 
"SELECT q1.sname, q1.age, q1.rating, q2.bname, q2.color, q3.rdate 
FROM ($arr1) AS q1, ($arr2) AS q2, ($arr3) AS q3 
WHERE q1.sid = q3.sid AND q2.bid = q3.bid";

but because of the arrays, it give me array to string conversion error. I need to join those three tables while they are in arrays, and get corresponding data. How can i do such thing?

This is an exercise for class. I know how to do it without storing in to array (and did it) but we need to do it with array, and i couldn't find any other way. — coras09, Mar 26 '14 at 10:53
Is there anything else in the arrays? (do they just have the single elements)? — tomb, Mar 26 '14 at 10:53
As in, does each array only have the one element? (Is their length more than 1?) — tomb, Mar 26 '14 at 10:55

score 0 · Answer 1 · edited May 23 '17 at 11:49

If I understand correctly, you should just be able to implode the arrays, to get a string result, and use that.

This is, effectively, just converting the arrays to a strings, and then using them in your query.

See this question on how to convert arrays with single elements to strings in php.

$text1 = array_shift($arr1);
$text2 = array_shift($arr2);
$text3 = array_shift($arr3);

$queryall = 
"SELECT q1.sname, q1.age, q1.rating, q2.bname, q2.color, q3.rdate 
FROM ($text1) AS q1, ($text2) AS q2, ($text3) AS q3 
WHERE q1.sid = q3.sid AND q2.bid = q3.bid";

bashleigh · Accepted Answer · 2014-03-26T11:12:44.857

SELECT q1.sname, q1.age, q1.rating, q2.bname, q2.color, q3.rdate FROM sailors AS q1, boats AS q2, reserves AS q3 WHERE q1.sid=q3.sid AND q2.bid=q3.bid

My snytax is probably all wrong. SQL isn't my strong point. You could use a JOIN rather than selecting all of them.

Doing it by array.

In your queries for the Array's you've added a name for the table and selected all columns from each. To build it you want this query:

 (SELECT * FROM sailors S) (SELECT * FROM boats B) (SELECT * FROM reserves R) WHERE S.sid=R.sid AND B.bid=R.bid

So

$query="($arr1)($arr2)($arr3) WHERE S.sid=R.sid AND B.bid=R.bid";

Or, if you wanted to use AS

$arr1="SELECT q1.name,q1.age,q1.rating FROM sailors AS q1";
$arr2="SELECT q2.bname,q2.color FROM boats AS q2";
$arr3="SELECT q3.rdate FROM reserves AS q3";

$query="($arr1)($arr2)($arr3) WHERE q1.sid=q3.sid AND q2.bid=q3.bid";

@user3463745 Yea join is a little tricky. Try it with 2 tables at first and progress. — bashleigh, Mar 26 '14 at 11:14

Joining three queries which are in array

2 Answers2