Merge Query Results into One Array

Hello experts,

I'm really not sure which way to go on this.  I am using Dreamweaver CS4, ADDT (with my editing) and MySQL.

I'm using an email function in ADDT to email a particular group of people upon submission of the form.  The recordset is pulled from a database using a MySQL select statement.  On the previous page, I have collected dynamic email addresses from another database, and assigned them to specific session variables (just to pass them to the next page).  What I need to do is add the email addresses that were collected (now session variables - about 10) to the recordset from MySQL.  I can't seem to pull them in the Select statement, because they are coming from a separate database.  I need them in the recordset because the email function uses the recordset to send all the emails.  If i use the CC function of the email, every time an email is sent to a member of the recordset, a copy gets sent as well resulting in multiple emails to the CC person.

I basically just need to add a few rows to the end of the MySQL result.  I have the emails I need to add (as Session variables), but I just need to append them to the end of the result.

Argh.  Does this make sense?  I hope so.

The session variables are T0, T1, T2, T3, T4, T5, T6,T7, T8, and T9 and they represent the dynamic emails that I've collected from the previous page.

Any help would be greatly appreciated.  The MySQL is a bit quirky, but works fine.  I'll go ahead and post it.
$colname_rsEmails = "-1";
if (isset($_GET['camp_id'])) {
  $colname_rsEmails = $_GET['camp_id'];
}

$col2_rsEmails = "-1";
if (isset($_SESSION['current_camp'])) {
  $col2_rsEmails = $_SESSION['current_camp'];
}

$colname2_rsEmails = "-1";
if (isset($_GET['location_id'])) {
  $colname2_rsEmails = $_GET['location_id'];
}

mysql_select_db($database_select_database, $select_database);
	  $query_rsEmails = sprintf("SELECT DISTINCT firstname, lastname, email, access FROM clients LEFT JOIN registrations ON (clients.client_id = registrations.client_id) LEFT JOIN camp_dates ON (registrations.date_id = camp_dates.camp_id) LEFT JOIN locations_times ON (registrations.location_id = locations_times.location_id) WHERE (registrations.paid >= %s  AND registrations.date_id = %s AND registrations.location_id = %s) OR (registrations.location_id = %s AND camp_dates.sessions > 1 AND (DATEDIFF(camp_dates.start_date, %s) <= 0 AND DATEDIFF(%s, camp_dates.end_date) <= 0)) ORDER BY clients.lastname", GetSQLValueString($minimumToShow, "int"), GetSQLValueString($colname_rsEmails, "int"),GetSQLValueString($colname2_rsEmails, "int"),GetSQLValueString($colname2_rsEmails, "int"),GetSQLValueString($col2_rsEmails, "date"),GetSQLValueString($col2_rsEmails, "date"),GetSQLValueString($col2_rsEmails, "date"),GetSQLValueString($col2_rsEmails));
	  $rsEmails = mysql_query($query_rsEmails, $select_database) or die(mysql_error());
	  $row_rsEmails = mysql_fetch_assoc($rsEmails);
	  $totalRows_rsEmails = mysql_num_rows($rsEmails);

Open in new window

ImagineItDigitalAsked:
Who is Participating?
 
Jason C. LevineNo oneCommented:
Sorry I dropped out.  Got infected with a virus and am finally back online

>> I don't think I can use one MYSQL SELECT to query two databases, right?  

Correct.  You can, however, create a FEDERATED table in database #2 that provides access to the table in database #1 and from there do a UNION query.  

http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html

>> Adobe Dreamweaver Developer's Toolbox, but it's basically a major developer plugin for Adobe Dreamweaver

The problem with the ADDT is that it was fairly crappy and REALLY hard to use and wrote code in such a way as to prevent easy editing/distribution.  Adobe dropped support for it completely over a year ago so you really are working without a net.  

You can do a couple of different things at this point:

1) Bring the table from the other database into this database via a FEDERATED table and then use a join or union query to get what you want.

2) Have two recordsets on the page (one from each database) and apply the email behavior twice.

3) A recordset IS an array, albeit a complex one.  You should be able to merge them but not sure what the ADDT will make of it.

4) Lose the ADDT email function and just hand-code something that loops through whatever and emails.

----------------------------------

Paid solutions:

5) Switch to WebAssist's Universal Email if you want a wizard based solution for this.  It's easier to use than ADDT and you can specify more options for pulling from dynamic lists.   It is not free but right now, WebAssist is running a 50% off sale so it's only $50

http://www.webassist.com/dreamweaver-extensions/universal-email/
0
 
Jason C. LevineNo oneCommented:
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.
0
 
ImagineItDigitalAuthor Commented:
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
mcbSolutionsCommented:
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

Open in new window

0
 
ImagineItDigitalAuthor Commented:
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);

Open in new window

0
 
mcbSolutionsCommented:
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);

Open in new window

0
 
ImagineItDigitalAuthor Commented:
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?

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

http://www.experts-exchange.com/Web_Development/Software/Macromedia_Dreamweaver/Q_23141215.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));
}

Open in new window

0
 
ImagineItDigitalAuthor Commented:
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);

Open in new window

0
 
mcbSolutionsCommented:
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;
          } ;

Open in new window

0
 
ImagineItDigitalAuthor Commented:
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?
0
 
mcbSolutionsCommented:
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);

Open in new window

0
 
ImagineItDigitalAuthor Commented:
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

Open in new window

0
 
mcbSolutionsCommented:
Your welcome.

Does the ADDT fetch the recordset by itself? If so, this will be a problem.

Is it possible to send the mails in other ways (like phpMailer)?

Is it possible to call Trigger_EmailRecordset twice (with the 2 record sets)?
0
 
ImagineItDigitalAuthor Commented:
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.
0
 
Jason C. LevineNo oneCommented:
>> 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.
0
 
mcbSolutionsCommented:
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?
0
 
Jason C. LevineNo oneCommented:
>> 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.
0
 
ImagineItDigitalAuthor Commented:
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
0
 
ImagineItDigitalAuthor Commented:
Wow, the screenshot got warped upon export.  Here it is again.
email-snapshot.jpg
0
 
Jason C. LevineNo oneCommented:
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.
0
 
mcbSolutionsCommented:
Hey, it looks great and it's nice to hear that its working.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.