X-Cart forums

[John7]

What would the smarty code need to be just to display the content of the table that holds the email addresses of the newsletter subscribers?

Please help.

Thanks

John

[funkydunk]

Not quite a simple as that really.

The first step is creating a php script that will pull the info from the database and assign it to smarty. You can then display this with a template.

Or you could export the data straight from the database using phpmyadmin if you have it.

[kpriest]

You can use this code I modified from one I created for another purpose:

<?php

$connection = mysql_connect('localhost', 'YOUR_DB_USERNAME', 'YOUR_DB_PASSWORD');
$db = mysql_select_db("YOUR_DB_NAME",$connection) or die("Could not connect to SQL db");
$sql = "SELECT xcart_customers.firstname, xcart_customers.lastname, xcart_maillist.email FROM xcart_customers INNER JOIN xcart_maillist ON xcart_customers.email = xcart_maillist.email";

$sql_result = mysql_query($sql,$connection) or die ("Couldn't Execute");

echo "  <table width='100%' border='1' cellspacing='2' cellpadding='0'>";
echo "  <tr> 
  <td>First Name</td>
  <td>Last Name</td>
  <td>Email</td>
</tr>";

while ($row = mysql_fetch_array($sql_result)) {
$firstname = $row["firstname"];
$lastname = $row["lastname"];
$email = $row["email"];

echo "  <tr> 
  <td>$firstname </td>
  <td>$lastname </td>
  <td>$email       </td>
</tr>";
}
echo "</table>";

mysql_free_result($sql_result);
mysql_close($connection);


?>

This pulls the newsletter email addresses out as well as linking to the customer table and getting the first and last name.

Copy this into a text file, fill in your specific database information, save as newsletter_maillist.php or something like that, then post it up in a secure folder on your site (.htaccess protected or something) and just call it up by URL:http://www.yoursite.com/yoursecurefolder/newsletter_maillist.php

A better way would be to use the auth.php and config.php to get access to the db, but I couldn't figure out how to incorporate them into what I was trying to do. I'm fairly new to PHP and MySQL. Maybe funky can add advice on this and how to then assign to smarty if he's so inclined...

hth

[John7]

Looks good so far. Initial tests have been successful.

Will keep you posted.

[John7]

This script works.

Now how do I stick it inside an xcart template?

[kpriest]

Well, that would take me a bit of time to figure out (which I currently don't have at the moment), since it's the part where you would do that smarty stuff.

Sorry to bail on you, but maybe one of the gurus can post a quick reply...

[John7]

I appreciate your help anyways.

I can force it to work, but it'll be a little sloppy.


I'm sure it would be a breeze for one of the masters like Funkydunk.

For me so far I can get the job done but it might be a little messy under the hood.

You put me on the right track and your help is greatly appreciated.


Thanks, John

[funkydunk]

okay .... here goes

new file:
admin/subscriber_list.php

<?
/***************************\
a funkydunk creation : 2003
just cos i was taking a tea break - yes I know heather...it's not allowed :) - well it isn't tea really...beer
*/

require "../smarty.php";
require "../config.php";
require "./auth.php";
require "../include/security.php";

// put this to select all subscribers - that was the original post request

// this is the vastly complex query 
$query = "SELECT * FROM $sql_tbl[maillist]"; 

// assign the results of the query to a variable thereby making it an array
$subscribers = func_query($query);

// give it to smarty to play with
$smarty->assign("subscribers",$subscribers);

// assign a value to $main so that the home.tpl knows you want to look at the right template
$smarty->assign("main", "subscribers");

// tell it what template is the placeholder
$smarty->display("single/home.tpl");
?>

in skin1/single/home.tpl

add in to the central area:

{elseif $main eq "subscribers"}
{include file="admin/main/subscriber_list.tpl"}

new template:
admin/main/subscriber_list.tpl

{* subscriber_list.tpl - funkydunk 2003 *}
{include file="location.tpl" last_location="Subscriber List"}

{if $smarty.get.usertype eq ""}
{capture name=dialog}
<table border="0" cellpadding="2" cellspacing="0" width="100%">
{section name=subscriber loop=$subscribers}
<tr>
<td valign="top">
<a href=mailto:{$subscribers[subscriber].email}>{$subscribers[subscriber].email}</a>
</td>
</tr>
{/section}
</table>
{/capture}
{include file="dialog.tpl" title="Subscriber List" content=$smarty.capture.dialog extra="width=100%"}
{/if}

[kpriest]

Nice job funky! I knew you'd have no problem with this.

[funkydunk]

works on 3.3.x haven't tested on 3.4.x .

thanks