Merging arrays derived from tables

[techlighthouse]

As part of my "big" project I need to append the info in a couple of MySQL database tables into a single table. I'd prefer to do this in php rather than create a new table, even temporary, in MySQL. It also seems like it should be an easy task but I can't quite figure it out.

Here're the particulars...

Both tables, "responses"and "comments" have the general structure of
"userid", "questionid", "choiceid", and "response".

The first three fields are identical in structure. The fourth one, though, is typed as charvar(30) in "responses" and contains "short" responses to questions. In the second table, "comments", field4 is typed as "text" for long, extended answers to questions.

The code I'm currently using is:


$query2="select * from responses
where userid='$keyid'";
$query3="select * from comments
where userid='$keyid'";
$result2 = mysql_query($query2, $db_conn);
$num_results2 = mysql_num_rows($result2);
$result3 = mysql_query($query3, $db_conn);
$num_results3 = mysql_num_rows($result3);

$j=0;
for ($j; $j<$num_results2; $j++) {
$row4=mysql_fetch_array($result2);
$registrant=$row4["userid"];
$question=$row4["questionid"];
$choice=$row4["choiceid"];
$response1=$row4["response"];
echo $n." ".$registrant." ".$question." ".$choice." ".$response1."<br>";
$n++;
}

$j=0;
for ($j; $j<$num_results3; $j++) {
$row5=mysql_fetch_array($result3);
$registrant=$row5["userid"];
$question=$row5["questionid"];
$choice=$row5["choiceid"];
$response1=$row5["response"];
echo $n." ".$registrant." ".$question." ".$choice." ".$response1."<br>";
$n++;
}


This results in the contents of the two tables being listed sequentially, with the contents of "responses" being first and "comments" second. What I'd like to do is have the responses in a single array sorted on $question and $choice so the results are listed sequentially, based on $question.

Suggestions will be greatly appreciated!

Thanks,
Jim

[techlighthouse]

Okay, no suggestions... Well, I've been working through the MySQL manual and thought I found the answer. Create a new table and the MERGE UNION the other two. It works fine with the small example in the manual, http://dev.mysql.com/doc/mysql/en/me...ge-engine.html, (even when extended to 6 fields) but crashes with my tables. The results from running the sql commands are given below.


CREATE TABLE total(
a INT NOT NULL AUTO_INCREMENT ,
KeyID SMALLINT( 6 ) ,
UserID SMALLINT( 6 ) ,
QuestionID TINYINT( 4 ) ,
ChoiceID TINYINT( 4 ) ,
Response TEXT,
INDEX ( a )
) TYPE = MERGE UNION = ( responses, comments ) ;
# MySQL returned an empty result set (i.e. zero rows).


In addition, the table "total" is "in use" when I look at the database structure.

I've defined the table structures for both "responses" and "comments" as:

KeyID smallint(6)
UserID smallint(6)
QuestionID tinyint(4)
ChoiceID tinyint(4)
Response text

and have 165 rows in the "responses" table and 5 rows in the "comments" table.

Once again, comments and suggestions would be appreciated.

[gerilya]

Why don't you try CREATE TABLE ... SELECT syntax?

You can create quite complex set of results that way and use it to create a new table.
For example:

CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works
FROM artist LEFT JOIN work ON artist.id = work.artist_id
GROUP BY artist.id;

http://dev.mysql.com/doc/mysql/en/create-table.html

If you want to do the same via PHP, that's OK too. Create SELECT query, which will retrieve all the necessary information from BOTH tables at once (grouped if necessary) and just insert the ersulting rows into a new table one by one.

[techlighthouse]

Well, I guess I just don't understand what's happening. I took Gerilya's code and modified to look like the following:


CREATE TABLE responses_comments
SELECT responses.UserID, responses.QuestionID, responses.ChoiceID, responses.Response,
COUNT(responses.ChoiceID) AS SeqNo
FROM responses LEFT JOIN comments ON responses.UserID = comments.UserID
GROUP BY UserID;


This one at least runs but gives a single row, derived from the first row in the responses table, in the result instead of the expected 169 results.

Once again, all I want to do is append the data in the comments table to the bottom of the data (i.e., add rows) already in the responses table. Gerilya's code appears to add additional columns to the table and that's not what I need.

I think maybe end up doing it in what seems the hard way: Select the records I want from the larger table and then use the "input" statement to add the others from the second table.

[gerilya]

Once again, all I want to do is append the data in the comments table to the bottom of the data (i.e., add rows) already in the responses table. Gerilya's code appears to add additional columns to the table and that's not what I need.

That actually depends on how you construct your SELECT statement.
Imagine you want to output all this data to a web page.
Create SELECT statement that that would do just that (i.e, SELECT statement that retrieves data from 2 tables one table after the other) and add CREATE TABLE ... before the SELECT statement. That's it.

If it's hard for you to construct a proper SQL, you can just export all the data from both tables into TAB delimited file (using phpMyAdmin, for example) concatenate the files and import it to a new table.

Hope that helps.

[techlighthouse]

Thanks, Gerilya! I knew it had to be a simple task.

I'm not sure exactly what it was you said in the above quote but something clicked. I went back to the MySQL manual and found Section 1.5.5.2, pg. 28, the "SELECT INTO TABLE" syntax and that, along with your comment about two select statement led me to


CREATE TABLE temp
SELECT responses.KeyID, responses.QuestionID, responses.ChoiceID, responses.Response
FROM responses WHERE responses.UserID=1;
INSERT INTO temp
SELECT comments.KeyID, comments.QuestionID, comments.ChoiceID, comments.Response
FROM comments WHERE comments.UserID=1;

which works wonderfully. Now I've got to index and/or sort the table so it can be output in the format my client wants.

Thanks for your help. I can be a really slow learner.

Jim