Trying to find a way to be able to show the number of discounts left to each user in their account area (userindex.tpl). Sql Query? Any ideas? We use the discount system to give our users credits they purchase to place ads, I have set up an auto pay system, Just need them to know when they are running low.
I'm not sure what you are referring to, "discount system". Can you be more specific in what you are currently doing and what you want to do?
We use the coupons & discount section in the admin for our members, I give a member 100 adverts with a discount of 100% set to expire at a certain date (12months from purchase). What I want to do is display the remaining number of discounts left to them in their My Account section (userindex.tpl), as I have done a simple paypal button for purchasing more "credits" and I manually add more to their account. In the admin in couponform.tpl its known as {$cNumAvailable}. Was hoping there was an easy way other than trying to write a sql query?
You would need to have an SQL query as all of the data for the coupons are in the database. If it were me, I would do this with a simple custom Smarty plugin. You just need the total number of active coupons that are assigned to the user (assuming that when you create the coupon(s), you are assigning them to the individual user). Create a text file called "function.credits.php" with this code in it (untested): PHP: <?php function smarty_function_credits($params, &$smarty) { global $db; $uid = $params['uid']; $sql = "SELECT COUNT(*) AS total FROM " .PREFIX."coupon WHERE `cUserID` = '" .$uid. "' AND `cEndDate` > NOW() AND `cNumAvailable` > '0'; $result = $db->query($sql); $rs = $result->fetch(); return $rs['total']; } Put this file in your "plugins" folder on your web hosting server. Then in your template file, "userindex.tpl" put the smarty plugin call "{credits $uid=$smarty.session.uid}" (w/o the quotes, of course) wherever you want the totall credited to show. Example: You have {credits $uid=$smarty.session.uid} left in your account.
Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in /home/**/public_html/plugins/function.credits.php on line 11
Try assigning the result to a different variable to be returned like this: PHP: $rs = $result->fetch();$credits = $rs['total']; return $credits;
Fatal error: Smarty error: [in user/userindex.tpl line 107]: syntax error: invalid attribute name: '$uid' (Smarty_Compiler.class.php, line 1559) in /home/ozgunsales/public_html/includes/classes/smarty/Smarty.class.php on line 1092 There appears to be a syntax error on line 10. Think it's missing a " somewhere
Oops! Need to remove the "$" before uid. Example: You have {credits uid=$smarty.session.uid} left in your account.
Notice: Query failed: 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 '1' at line 1 SQL: 1 in /home/ozgunsales/public_html/includes/classes/database/mysql.phpon line 251 I kept gettin syntax error on line 10 so I added the " at the end of `cNumAvailable`" <?php function smarty_function_credits($params, &$smarty) { global $db; $uid = $params['uid']; $sql = "SELECT COUNT(*) AS total FROM " .PREFIX."coupon WHERE `cUserID` = '" .$uid. "' AND `cEndDate` > NOW() AND `cNumAvailable`" > '0'; $result = $db->query($sql); $rs = $result->fetch(); $credits = $rs['total']; return $credits; }
PHP: $sql = "SELECT COUNT(*) AS total FROM " .PREFIX."coupon WHERE `cUserID` = '" .$uid. "' AND `cEndDate` > NOW() AND `cNumAvailable` > '0'"; Add the quote at the very end after '0', not after `cNumAvailable'.
Success, sort of. Its showing only a 0 for not active (if out of date or 0 ads) or a 1 if credits remain & is still active.. no actual amount.
Ok I figured it out. I had to change a few things but it works now. Thank you so much for your help. this is the code I used - PHP: <?php function smarty_function_credits($params, &$smarty) { global $db; $uid = $params['uid']; $sSql = "SELECT `cNumAvailable` FROM " .PREFIX."coupon WHERE `cUserID` = '" .$uid. "' AND `cEndDate` > NOW() AND `cNumAvailable` > '-1'"; $result = $db->query($sSql); $rs = $result->fetch(); return $rs['cNumAvailable']; } }
I was also able to work out how to display their end date for their membership (expiry of the coupons) {membership|date_format:$dateformat uid=$smarty.session.uid} inserted into the .tpl I created a file called function.membership.php with this code. PHP: <?php function smarty_function_membership($params, &$smarty) { global $db; $uid = $params['uid']; $sSql = "SELECT `cEndDate` FROM " .PREFIX."coupon WHERE `cUserID` = '" .$uid. "' AND `cEndDate` > NOW() AND `cNumAvailable` > '-1'" ; $result = $db->query($sSql); $rs = $result->fetch(); return $rs['cEndDate']; }
