Members only Section – update – 6 March 2009
You are welcome to use these notes and programs for your own use at your own risk!!
If you can improve anything, that would be very welcome – please post on the forum.
Please backup all your files before any mods.
Use customers newsletter (char (1) field on database (customers)) to determine membership status 1 = member, 0 = non member
Set the newsletter field = 0 for all customers using program below
Set up membership category to purchase subscription
ClubBaitworks member - if subscribed manually set the newletter to subscribed using edit customer details (this sets the field to “1”)
For general email correspondence use the Tools/email option not newsletter.
Tools/newsletter to be used for members.
Configuration/Customer details set the show newsletter subscription to off, to remove option at customer account set up and ability of customer to edit his account details
Configuration/Email options remove unsubscribe link
This is a general program to send queries to the zencart (mysql) database
<!-- Program: mysql_send.php
Desc: PHP program that sends an SQL query to the
MySQL server and displays the results.
-->
<html>
<head><title>SQL Query Sender</title></head>
<body>
<?php
$host="localhost";
$user="username";
$password="password";
/* Section that executes query */
if(@$_GET['form'] == "yes")
{
mysql_connect($host,$user,$password);
mysql_select_db($_POST['database']);
$query = stripSlashes($_POST['query']);
$result = mysql_query($query);
echo "Database Selected: <b>{$_POST['database']}</b><br>
Query: <b>$query</b><h3>Results</h3><hr>";
if($result == 0)
{
echo "<b>Error ".mysql_errno().": ".mysql_error().
"</b>";
}
elseif (@mysql_num_rows($result) == 0)
{
echo("<b>Query completed. No results returned.
</b><br>");
}
else
{
echo "<table border='1'>
<thead>
<tr>";
for($i = 0;$i < mysql_num_fields($result);$i++)
{
echo "<th>".mysql_field_name($result,$i).
"</th>";
}
echo " </tr>
</thead>
<tbody>";
for ($i = 0; $i < mysql_num_rows($result); $i++)
{
echo "<tr>";
$row = mysql_fetch_row($result);
for($j = 0;$j<mysql_num_fields($result);$j++)
{
echo("<td>" . $row[$j] . "</td>");
}
echo "</tr>";
}
echo "</tbody>
</table>";
} //end else
echo "
<hr><br>
<form action=\"{$_SERVER['PHP_SELF']}\" method=\"POST\">
<input type='hidden' name='query' value='$query'>
<input type='hidden' name='database'
value={$_POST['database']}>
<input type='submit' name=\"queryButton\"
value=\"New Query\">
<input type='submit' name=\"queryButton\"
value=\"Edit Query\">
</form>";
unset($form);
exit();
} // endif form=yes
/* Section that requests user input of query */
@$query=stripSlashes($_POST['query']);
if (@$_POST['queryButton'] != "Edit Query")
{
$query = " ";
}
?>
<form action="<?php echo $_SERVER['PHP_SELF'] ?>?form=yes"
method="POST">
<table>
<tr>
<td align=right><b>Type in database name</b></td>
<td><input type="text" name="database"
value=<?php echo @$_POST['database'] ?> ></td>
</tr>
<tr>
<td align="right" valign="top">
<b>Type in SQL query</b></td>
<td><textarea name="query" cols="60"
rows="10"><?php echo $query ?></textarea>
</td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit"
value="Submit Query"></td>
</tr>
</table>
</form>
</body></html>
A typical query might be:
SELECT customers_firstname, customers_lastname, customers_id, customers_newsletter from customers
Which will display the database info in tabular form
Program to change newsletter status to zero on customers database
<!-- Program: newsletter field set.php
Desc: PHP program that sets the newsletter field to 0
-->
<html>
<head><title>Newsletter set</title></head>
<body>
<?php
$host="localhost";
$user="username";
$password="password";
$databasename = "databasename";
$connection = mysql_connect($host, $user, $password)
or die ("Could not connect to server");
$db = mysql_select_db($databasename,$connection)
or die ("Could not select derekbry_zen database");
$query = "update customers SET customers_newsletter = 0 WHERE customers_newsletter != 0";
$result = mysql_query($query)
or die ("Could not execute query");
echo "customers_newsletter field set to 0";
?>
</body>
</html
The define pages are stored as .php files and are therefore used as the membership home page
To change define page names modify:
/zen/includes/languages/english/page_4.php
Line #22 : define('NAVBAR_TITLE', 'clubderek');
Line #23 : define('HEADING_TITLE', 'clubderek');
At the top of the define page used as the membership home page insert the following code:
<?php
session_start();
$custID = $_SESSION['customer_id'];
if ($custID == "")
echo "<br>Please log in at the top of the screen to enter Club Baitworks<br> ";
stop();
/*
else
{
$host="localhost";
$user="username";
$password="password";
$database="databasename";
$connection = mysql_connect($host, $user, $password)
or die ("Could not connect");
$db = mysql_select_db($database, $connection)
or die ("Could not connect");
$query = "SELECT customers_firstname, customers_lastname, customers_id, customers_newsletter from customers WHERE customers_id=$custID";
$result = mysql_query($query)
or die ("couldn't execute query");
$row = mysql_fetch_array($result,MYSQL_BOTH);
$newlettercode = $row[3];
if ($newlettercode != 1)
{echo "non ClubBaitworks member - please purchase membership subscription above at Club Baitworks";
stop(); }
else
{
$_SESSION['auth'] = "YES";
/* echo "carry on to members section";*/
}
}
*/
?>
The html code for the members home page follows this php code
