Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Mailing List Report


pafranklin

Recommended Posts

Hi there,

 

I have a table in my database called `maillist` which captures people who want to sign up for my newsletter without actually creating an account. This works well but I am trying to build a report (similar to the stats_customers.php report) to display the customers last name, first name and email address.

 

So far I have got this far and have now managed to confuse myself completely.........

<?php
 if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;

 $customers_query_raw = "select customers_firstname, customers_lastname, customers_email_address from " . TABLE_MAILLIST . " , " . "order by lastname ASC";

 $customers_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $customers_query_raw, $customers_query_numrows);

// fix counted customers
 $customers_query_numrows = tep_db_query("select customers_email_address from " . TABLE_MAILLIST . " group by customers_lastname");
 $customers_query_numrows = tep_db_num_rows($customers_query_numrows);

 $rows = 0;
 $customers_query = tep_db_query($customers_query_raw);
 while ($customers = tep_db_fetch_array($customers_query)) {
$rows++;

if (strlen($rows) < 2) {
  $rows = '0' . $rows;
}
?>

 

With this I get the error

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

select count(*) as total from TABLE_MAILLIST ,

 

[TEP STOP]

 

Please could someone point me in the right direction? I am fairly new to php and am struggling which waht I know should be reasonably straightforward. I really would be very grateful..

 

Many thanks.

 

Paul.

You will never learn if you don't try. And boy am I trying....!

Link to comment
Share on other sites

I think that I have made some progress (I hope).

 

Here is the code from my new page (stats_customers2.php)

<?php
 if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;

 $newsletter_query_raw = "SELECT newsletter.customers_firstname, newsletter.customers_lastname, newsletter.customers_email_address from " . TABLE_NEWSLETTER .

 $newsletter_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $newsletter_query_raw, $newsletter_query_numrows);

// fix counted customers
 $newsletter_query_numrows = tep_db_query("select customers_email_address from " . TABLE_NEWSLETTER . " group by customers_lastname");
 $newsletter_query_numrows = tep_db_num_rows($newsletter_query_numrows);

 $rows = 0;
 $newsletter_query = tep_db_query($newsletter_query_raw);
 while ($newsletter = tep_db_fetch_array($newsletter_query)) {
$rows++;

if (strlen($rows) < 2) {
  $rows = '0' . $rows;
}
?>
		  <tr class="dataTableRow" onMouseOver="rowOverEffect(this)" onMouseOut="rowOutEffect(this)" onClick="document.location.href='<?php echo tep_href_link(FILENAME_CUSTOMERS, 'search=' . $newsletter['customers_lastname'], 'NONSSL'); ?>'">
			<td class="dataTableContent"><?php echo $rows; ?>.</td>
			<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CUSTOMERS, 'search=' . $newsletter['customers_lastname'], 'NONSSL') . '">' . $newsletter['customers_firstname'] . ' ' . $newsletter['customers_lastname'] . $newsletter['customers_email_address'] .'</a>'; ?> </td>

			</tr>
<?php
 }
?>
		</table></td>
	  </tr>
	  <tr>
		<td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2">
		  <tr>
			<td class="smallText" valign="top"><?php echo $customers_split->display_count($customers_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, $HTTP_GET_VARS['page'], TEXT_DISPLAY_NUMBER_OF_CUSTOMERS); ?></td>
			<td class="smallText" align="right"><?php echo $customers_split->display_links($customers_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, MAX_DISPLAY_PAGE_LINKS, $HTTP_GET_VARS['page']); ?> </td>

 

I now get the following error.........

TABLE_HEADING_NUMBER TABLE_HEADING_CUSTOMERS

1146 - Table 'dbxyz12345.maillistObject' doesn't exist

 

SELECT newsletter.customers_firstname, newsletter.customers_lastname, newsletter.customers_email_address from maillistObject

 

[TEP STOP]

The original code that I used to insert the table into my database was.....

 

CREATE TABLE `maillist` (

`customers_firstname` VARCHAR( 20 ) ,

`customers_lastname` VARCHAR( 20 ) ,

`customers_email_address` VARCHAR( 40 ) NOT NULL ,

`customers_newsletter` VARCHAR( 2 ) DEFAULT '1' NOT NULL ,

PRIMARY KEY ( `customers_email_address` )

);

 

Please please please would someone point me in the right direction?

Many thanks...

 

Paul.

You will never learn if you don't try. And boy am I trying....!

Link to comment
Share on other sites

Hi, Sorry to bump this, but I have tried every way that I can think of and am clearly missing something obvious.

 

I am just trying to make a simple report from the "maillist" table whci holds firstname, lastname and email_address.

 

Please could someone suggest how I can do that?

You will never learn if you don't try. And boy am I trying....!

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...