Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Simple PHP Question


sammedit

Recommended Posts

Hi,

I am trying to get a count from multiple database tables and I am having a hard time understanding the syntax examples on the PHP websites.

 

My code is:

 

 

$mail_query = tep_db_query("select count(*) as count from " . TABLE_CUSTOMERS . " where customers_newsletter = '1'");

 

I want to add TABLE_VISITOR to the count in the above code which is found on catalog/admin/includes/modules/newsletters/newsletter.php around line 29. There is only one field in TABLE_VISITOR table and it is called "email". It lists email addresses of visitors who want to receive the newsletter without signing up for an account.

 

Any suggestions? Thank you in advance.

 

RS

Link to comment
Share on other sites

@@sammedit

 

RS have you tried just doing a similar query on the TABLE_VISITORS table and adding the results together? That might be the easiest way to do it unless you're wanting to join the tables together for some reason.

 

Dan

Link to comment
Share on other sites

The problem is that I don't know how to add them together.

 

I tried adding

$mail_query = tep_db_query("select count(*) as count from " . TABLE_VISITOR);

 

underneath the

 

$mail_query = tep_db_query("select count(*) as count from " . TABLE_CUSTOMERS . " where customers_newsletter = '1'");

 

but that just cancelled out the original code and made the entire count come from TABLE_VISITOR instead of TABLE_CUSTOMERS.

Link to comment
Share on other sites

I think this will do what you want.

$mail_query = tep_db_query("select count(*) as count from " . TABLE_CUSTOMERS . " c inner join " . TABLE_VISITORS . " v on c.customers_email_address = v.email and  c.customers_newsletter = '1'");

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

@@sammedit

 

If Jack's join doesn't do it for you, change the name of the $mail_query variable in one of the queries and then add the two variables together to get the total count.

 

Dan

Link to comment
Share on other sites

Unfortunately that didn't do it. I think you might be over complicating what I am trying to do. Probably because what I am trying to do is so simple that I would have to be stupid to ask about it. On newsletters.php after you create a newsletter, lock it, and click send, you see, "Customers receiving newsletter: 1" which shows how many customers, who subscribe to the newsletter, are going to be emailed.All I am trying to do is include the number of visitors who signed up for the newsletter without creating an account, using the Simple Visitor Newsletter contribution, in this count. This way I know exactly how many emails I am sending out. Currently it just shows the number of subscribed customers who will get emails. Does this make sense?

 

How exactly can I add them together? Something like:

 

$mail_query1 = tep_db_query("select count(*) as count from " . TABLE_CUSTOMERS . " where customers_newsletter = '1'");
  $mail_query2 = tep_db_query("select count(*) as count from " . TABLE_VISITOR);
  $mail_query1 + $mail_query2 = $mail_query;

 

Is that sort of what you are talking about? Thank you both for your help.

Link to comment
Share on other sites

or try this:

$mail_query1 = tep_db_query("select c.customers_id from " . TABLE_CUSTOMERS . " c, " . TABLE_VISITORS . " v where c.customers_newsletter = '1' and c.customers_email_address = v.email");

Quantity of records you can get by display

tep_db_num_rows($mail_query1);

function

Link to comment
Share on other sites

In that file (admin/includes/modules/newsletter/newsletter.php or whatever), find

	  $mail_query = tep_db_query("select count(*) as count from " . TABLE_CUSTOMERS . " where customers_newsletter = '1'");
  $mail = tep_db_fetch_array($mail_query);

  $confirm_string = '<table border="0" cellspacing="0" cellpadding="2">' . "\n" .
					'  <tr>' . "\n" .
					'	<td class="main"><font color="#ff0000"><strong>' . sprintf(TEXT_COUNT_CUSTOMERS, $mail['count']) . '</strong></font></td>' . "\n" .
					'  </tr>' . "\n" .

replace with

  $mail_query = tep_db_query("select count(*) as count from " . TABLE_CUSTOMERS . " where customers_newsletter = '1'");
  $mail = tep_db_fetch_array($mail_query);
$visitor_query = tep_db_query("select count(*) as count from " . TABLE_VISITOR);
$visitor = tep_db_fetch_array($visitor_query);

  $confirm_string = '<table border="0" cellspacing="0" cellpadding="2">' . "\n" .
					'  <tr>' . "\n" .
					'	<td class="main"><font color="#ff0000"><strong>' . sprintf(TEXT_COUNT_CUSTOMERS, $mail['count']+ $visitor['count']) . '</strong></font></td>' . "\n" .
					'  </tr>' . "\n" .

Two lines added; one line changed.

Always back up before making changes.

Link to comment
Share on other sites

Two results:

 

$mail_query = tep_db_query("select (select count(1) from " . TABLE_CUSTOMERS . " where customers_newsletter = 1) as customers_count,(select count(1) from  " . TABLE_VISITOR . ") as visitor_description_count");

 

One result:

 

$mail_query = tep_db_query("select (select count(1) from " . TABLE_CUSTOMERS . " where customers_newsletter = 1) + (select count(1) from " . TABLE_VISITOR . ") as total_count");

 

[edited] as forgot include the where clause.

Link to comment
Share on other sites

ecartz, your code did the job. Thank you!

 

FWR Media, would your version require changing the "sprintf(TEXT_COUNT_CUSTOMERS, $mail['count'])" to display the count?

 

Thank you everyone for helping me solve this.

Link to comment
Share on other sites

FWR Media, would your version require changing the "sprintf(TEXT_COUNT_CUSTOMERS, $mail['count'])" to display the count?

 

 $result = $mail_query = tep_db_query("select (select count(1) from " . TABLE_CUSTOMERS . " where customers_newsletter = 1) + (select count(1) from " . TABLE_VISITOR . ") as total_count");
 $mail = tep_db_fetch_array($result);
 tep_db_free_result($result);

 

Then you can use

 

  sprintf(TEXT_COUNT_CUSTOMERS, $mail['total_count'])

 

Easier though just to change the query return so the existing sprintf can be used ( I wasn't looking at the output code just offering the correct query )

 

 $result = $mail_query = tep_db_query("select (select count(1) from " . TABLE_CUSTOMERS . " where customers_newsletter = 1) + (select count(1) from " . TABLE_VISITOR . ") as count");
 $mail = tep_db_fetch_array($result);
 tep_db_free_result($result);

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...