Merge Query Results into One Array

Hi ImagineItDigital,

When you say the emails from the previous step are from a different database, are they truly from a new database or just a different table in the same database?

Regardless of the actual answer above, do the two tables have similar structures or linking keys that would allow us to do a JOIN?  That's a heckuva lot easier than adding values into an array (although that can be done too) as complex as a multi-row recordset.

Hi Jason,

Yes, I do mean another database, not just another table.  Never done that before.

The two tables that I am pulling from do have somewhat similar structure.  The information I need is email, firstname, and lastname, and those fields all exist in both tables.  

One table is named users and among other fields has user_id, firstname, lastname, email_address.

The other table (in a different database) is named clients, and among other fields has client_id, firstname, lastname, email.

But there would be no linking key per se that would be the same in both tables.  Again, I have the email addresses stored as session variables for use somehow in this page.

Thanks for the help.

If you have two databases an 2 queries, why not using array_merge and array_unique after getting results?

All you have to do is to make both array looking the same:

SELECT user_id AS 'id', firstname, lastname, email_address AS 'email' FROM table1

SELECT client_id AS 'id', firstname, lastname, email FROM table2

Select allOpen in new window

OK this sounds exactly like what I was looking for.  I've spent some working on it, but I still don't quite know how to put the PHP code together.  I have both recordsets finished and working, and they are parallel (firstname, lastname, email, access).  Do I need the user_id as well?

I'm attaching the code (without the MySQL) that is basically generated by ADDT and shows the results of the Queries and how they're set up.  How do I merge them?  

Karey

$rsTrainersToAdd = mysql_query($query_rsTrainersToAdd, $bootcampadmin) or die(mysql_error());
$row_rsTrainersToAdd = mysql_fetch_assoc($rsTrainersToAdd);
$totalRows_rsTrainersToAdd = mysql_num_rows($rsTrainersToAdd);

$rsEmails = mysql_query($query_rsEmails, $select_database) or die(mysql_error());
$row_rsEmails = mysql_fetch_assoc($rsEmails);
$totalRows_rsEmails = mysql_num_rows($rsEmails);

//Combine emails from client database with emails from back end database for final send
$FinalSendList = array_merge($row_rsEmails, $row_rsTrainersToAdd);
print_r($FinalSendList);

Select allOpen in new window

Please note that for array_unique only remove unique double items (all fields). If you have same emails but different name, the email can be in array twice. To avoid this, only place the email field in the array/query.

I don't know if you need user_id, it's you code.

What is ADDT?

After your listed code, add the following:

$FinalSendList = array_unique($FinalSendList);
print_r($FinalSendList);

Select allOpen in new window

ADDT is Adobe Dreamweaver Developer's Toolbox.  It's not supported by Adobe anymore (what else is new), but it's a nice set of dynamic tools originally made by InterAkt.

Anyway, the problem I'm having is that it seems like this only returns one line of data instead of all the rows.  Do we need to loop it or is it this simple to get both two dimensional arrays merged?

Ugh I'm lost.  I've been at this all day, and can't quite pull it off.  I found this thread:

https://www.experts-exchange.com/questions/23141215/Combining-Recordsets-in-Dreamweaver.html#discussion

that is exactly what I'm asking.  I was able to complete this, and I'm pasting the code.  But I guess the new array ($rsFinalSendList) is not considered a recordset.  Is there a difference between a recordset and an array?  Argh.  So close and so far.

//Combine emails from client database with emails from back end database for final send
$rsFinalSendList = array();
 
//loop out first result into array
if($row_rsEmails) {
	
	$i = 0;
	
	do{
		
		$i++;
		$rsFinalSendList[$i]['firstname'] = $row_rsEmails['firstname'];
		$rsFinalSendList[$i]['lastname'] = $row_rsEmails['lastname'];
		$rsFinalSendList[$i]['email'] = $row_rsEmails['email'];
		$rsFinalSendList[$i]['access'] = $row_rsEmails['access'];
		
		
	  } while ($row_rsEmails = mysql_fetch_assoc($rsEmails));
}
 
//loop out second result into array
if($row_rsTrainersToAdd) {
	
	do{
		
		$i++;
		$rsFinalSendList[$i]['firstname'] = $row_rsTrainersToAdd['firstname'];
		$rsFinalSendList[$i]['lastname'] = $row_rsTrainersToAdd['lastname'];
		$rsFinalSendList[$i]['email'] = $row_rsTrainersToAdd['email'];
		$rsFinalSendList[$i]['access'] = $row_rsTrainersToAdd['access'];
		
		
	  } while ($row_rsTrainersToAdd = mysql_fetch_assoc($rsTrainersToAdd));
}

Select allOpen in new window

For instance, why can't I do this?

$row_rsFinalSendList = mysql_fetch_assoc($rsFinalSendList);

after the above code that merges the two recordsets?

$row_rsFinalSendList = mysql_fetch_assoc($rsFinalSendList);

Select allOpen in new window

Sorry, i didn't noticed, that you are not getting all rows. If you use the code below, you will get all rows from the mysql result.

while (($row_rsEmails = mysql_fetch_assoc($rsEmails))==true){
                $rsFinalSendList[] = $row_rsEmails;
          } ;

Select allOpen in new window

So is there no way to combine the two recordsets into one actual recordset.  I guess an array is not the same thing as a recordset, correct?

Whats the problem with the above code?

If you want to have all the data in one array/recordset (the record set result is an array) with one query, you have to use the UNION statement

$q = "SELECT firstname, lastname, email_address AS 'email' FROM table1
UNION
SELECT firstname, lastname, email FROM table2";

$rsEmails = mysql_query($q) or die(mysql_error());
$totalRows_rsEmails = mysql_num_rows($rsEmails);
while (($row_rsEmails = mysql_fetch_assoc($rsEmails))==true){
   $rsFinalSendList[] = $row_rsEmails;
} ;

print_r($totalRows_rsEmails);
print_r($FinalSendList);

Select allOpen in new window

Thanks for staying with me, mcb.

The problem with using UNION is that the data is coming from two distinct databases.  I don't think I can use one MYSQL SELECT to query two databases, right?  

The reason I need it to be in an actual recordset is because of how the email function works.  I know you're not familiar with ADDT (Adobe Dreamweaver Developer's Toolbox), but it's basically a major developer plugin for Adobe Dreamweaver, with tools for dynamic development.  The Email function that I'm using emails a recordset, and I don't really know enough to change the whole email process or how to make it consistent with emailing an array rather than a recordset from a MySQL query.

I'll attach some code to show you how ADDT codes the email section.  Obviously, it's using some includes to do additional work for the email function.

// Load the common classes
require_once('includes/common/KT_common.php');

// Load the tNG classes
require_once('includes/tng/tNG.inc.php');

//start Trigger_EmailRecordset trigger
//remove this line if you want to edit the code by hand
function Trigger_EmailRecordset(&$tNG) {
  $rsemailObj = new tNG_EmailRecordset($tNG);
  $rsemailObj->setRecordset("rsEmails");
  $rsemailObj->setFrom("{SESSION.trainer_name} <{SESSION.kt_login_user}>");
  $rsemailObj->setTo("email");
  $rsemailObj->setSubject("{SESSION.subject}");
  //FromFile method
  $rsemailObj->setContentFile("correspondence_template.html");
  $rsemailObj->setEncoding("ISO-8859-1");
  $rsemailObj->setFormat("HTML/Text");
  $rsemailObj->setImportance("Normal");
  return $rsemailObj->Execute();
}
//end Trigger_EmailRecordset trigger

Select allOpen in new window

Thanks, Jason.  I appreciate all the information and the time it takes.

1) I do realize that Adobe dropped support for ADDT.  However, I like thousands of other developers had bought it from them (for $200) and used it to develop tons of dynamic websites.  It's a pain in the butt how they seem to abuse developers like that.  InContext Editing is another example - selling developers on the service and then yanking the plug when everybody is in and got all their clients using it.  Though ADDT is problematic, I have learned how to use it, and then edit the code many times to get what I want.  It saves me a lot of time and there doesn't seem to be anything else around that has the kind of full functionality that it had.

2) I don't know why I didn't think of applying another behavior to a second trigger and call both triggers upon submission of the form.  That makes sense and I think I can make that work (maybe) if ADDT allows it.

3) I'll definitely look at the paid solution you suggested for email.  I just know all the ins and outs of using the email function with ADDT (until now).  I hate switching to another service unless I have to, but if that's the only option...

4) So I think I understand - a recordset is an array, but an array is not necessarily a recordset...?

5) I was able to merge the recordsets into one array, but ADDT didn't seem to like it - didn't work.

Thanks, I'll let you guys know what works.

>> It saves me a lot of time and there doesn't seem to be anything else around that has the kind of full
>> functionality that it had.

WebAssist was the main competition to InterAKT (the dudes who created the ADDT) before InterAKT was bought out by Adobe.  Their products are mostly similar to what ADDT offers (with some exceptions that I won't go into here...check my profile and contact me if you want to get into a deep discussion about pros/cons) but have the added bonuses of

a) working most of the time
b) being a lot easier to edit and create triggers
c) good support from the developers

I had both of them installed (ADDT since removed) and I consistently preferred WA's method of doing things.  YMMV.

>>  However, I like thousands of other developers had bought it from them (for $200) and used it to develop tons of
>>  dynamic websites

How pissed were you when Adobe started giving it away for free if you registered DW CS3?

>> I think I can make that work (maybe) if ADDT allows it.

It should.  Multiple actions per trigger are allowed...

>> a recordset is an array, but an array is not necessarily a recordset...?

Oversimplification, but essentially correct

A recordset is a type of an array called an "associative array" where everything is stored in name=>value pairs.  To make things more complicated, it's actually a multidimensional associative array since you have multiple versions of those name=>value pairs based on the number of rows returned by the query.  Just for kicks and giggles, make this recordset on a new page and then add the following PHP in the body:

<?php // SHOW THE CONTENTS OF THE RECORSET
 echo "<pre>\n";
 echo "row_rsEmails: "; var_dump($row_rsEmails);
 echo "</pre>\n";
?>

That should show you the complexity of the problem.  It's why I said initially that merging is possible, just difficult.

>> I was able to merge the recordsets into one array, but ADDT didn't seem to like it - didn't work.

Probably changed the structure of the array to the point where ADDT didn't recognize it.

Hi ImagineItDigital, hi Jason,

the result from is a resource which you loop through mysql_fetch_assoc.

I don't know ADDT but is it possible, that ADDT uses the resource (from mysql_qeury) directly in Trigger_EmailRecordset and does fetching itself?

>> is it possible, that ADDT uses the resource (from mysql_qeury) directly in Trigger_EmailRecordset and does
>> fetching itself

I'm unsure as to what the ADDT does when you give it a recordset and I no longer have the code (or the desire to wade through it) to look at it.  

My gut feeling is that while you fetch the data "normally" for use in the page, the ADDT performs its own actions to get the data for its use and merely copies the SQL in order to requery.

That did it, guys.  A second email behavior worked just fine attached to the same custom trigger.  So simple.  Argh.  I appreciate your help, both of you.  And, Jason, I'll definitely look into WA - thanks for the information.

I did not know about the deal where they gave ADDT away for registering.  Wow - those turkeys.

I've attached a picture just so you can see what the product we've been working on looks like.  It's the back end administration section of a boot camp administration application I've developed.  About 30 boot camps around the country use it and I service them.

I'm not quite sure how to give proper points here.  Is it unjust if I split points?  I appreciate all the help.

Karey


email-snapshot.jpg

Wow, the screenshot got warped upon export.  Here it is again.
email-snapshot.jpg

No, absolutely split the points.  Both of us provided working solutions.  The difference is that mcbSolutions apprached it a PHP problem and I approached it as a DW-specific issue.

Boot Camp as in exercise?  *shudder*

I'll stick to fencing.  Workout + getting to hit people with a metal stick.

Hey, it looks great and it's nice to hear that its working.