Greystoke

The "sales" Table: The "bonus" Table: The affiliate_id, affiliate_billing_staus, affiliate_payment_id are the same in both tables. The affiliate _id in both table is the same person. This is how I want it displayed: Using the Union option it displays correctly, but the "bonus" table may not always have a matching row for each affiliate. If the affiliate has no sales then the bonus won't be paid as that affiliate_id won't be checked for in the "bonus" table.

The tables are been used in the invoice report to show a breakdown of the payments to the affiliate. The 2 tables are matched using the affiliate_payment_id. In the original invoice it only showed the sales breakdown. The affiliate_orders_id, affiliate_date, affiliate_value, affiliate_percent, affiliate_payment are used to for the report. This is how it looks using this: $affiliate_sales_query = tep_db_query("(select affiliate_orders_id, affiliate_date, affiliate_value, affiliate_percent, affiliate_payment from " . TABLE_AFFILIATE_SIGNUP_BONUS . " where affiliate_payment_id = '" . $payments['affiliate_payment_id'] . "' order by affiliate_payment_date desc) UNION (select affiliate_orders_id, affiliate_date, affiliate_value, affiliate_percent, affiliate_payment from " . TABLE_AFFILIATE_SALES . " where affiliate_payment_id = '" . $payments['affiliate_payment_id'] . "' order by affiliate_payment_date desc)");

Hi fugix, The "bonus" table isn't always going to have a matching row for each affiliate in the "sales" table, I was trying do what MrAdam said, but I can't figure out how to get the LEFT JOIN to work correctly.

Hi, Thanks for your replies. I went with the UNION option first: $affiliate_sales_query = tep_db_query("(select affiliate_orders_id, affiliate_date, affiliate_value, affiliate_percent, affiliate_payment from " . TABLE_AFFILIATE_SIGNUP_BONUS . " where affiliate_payment_id = '" . $payments['affiliate_payment_id'] . "' order by affiliate_payment_date desc) UNION (select affiliate_orders_id, affiliate_date, affiliate_value, affiliate_percent, affiliate_payment from " . TABLE_AFFILIATE_SALES . " where affiliate_payment_id = '" . $payments['affiliate_payment_id'] . "' order by affiliate_payment_date desc)"); This worked. Then after reading MRAdam post I tried to use LEFT JOIN, but it only outputted the sales info not both. $affiliate_sales_query = tep_db_query("select asb.*, s.* from " . TABLE_AFFILIATE_SALES . " s left join " . TABLE_AFFILIATE_SIGNUP_BONUS . " asb on (s.affiliate_id = asb.affiliate_id) where s.affiliate_payment_id = '" . $payments['affiliate_payment_id'] . "' order by asb.affiliate_payment_date desc"); Can anyone help with this please.

Hi, I need some help getting results from 2 tables. Table sales: Table bonus: At the moment I have: $affiliate_sales_query = tep_db_query("select * from " . TABLE_AFFILIATE_SALES . " where affiliate_payment_id = 7 order by affiliate_payment_date desc"); while ($affiliate_sales = tep_db_fetch_array($affiliate_sales_query)) { ?> <tr class="dataTableRow"> <td class="dataTableContent" align="right" valign="top"><?php echo $affiliate_sales['affiliate_orders_id']; ?></td> <td class="dataTableContent" align="center" valign="top"><?php echo tep_date_short($affiliate_sales['affiliate_date']); ?></td> <td class="dataTableContent" align="right" valign="top"><b><?php echo $currencies->display_price($affiliate_sales['affiliate_value'], ''); ?></b></td> <td class="dataTableContent" align="right" valign="top"><?php echo $affiliate_sales['affiliate_percent']; ?><?php echo ENTRY_PERCENT; ?></td> <td class="dataTableContent" align="right" valign="top"><b><?php echo $currencies->display_price($affiliate_sales['affiliate_payment'], ''); ?></b></td> </tr> <?php } ?> This outputs the sales table like: How can I get it to also output the bonus table in the same report.

Thanks for all your help. That's what I was looking for.

Sorry about not posting the print_r correctly. What I want it to do is if 'pro_install' or 'custom_install' are not in the array, then redirect. E.g. If 'pro_install' is there don't redirect. or If 'custom_install' is there don't redirect. or If 'pro_install' and 'custom_install' are there don't redirect.

Hi, I tried this, but it didn't work. $products = $cart->get_products(); print_r($products); if(!isset($products['pro_install']) || !isset($products['custom_install'])) { echo 'test'; } It still outputs 'test', even when 'pro_install' was in the Array.

Hi, Can anyone help with this please. $products = $cart->get_products(); print_r('$products'); } print_r('$products'); outputs: Array ( [0] => Array ( [id] => 28{7}14 [name] => Pro Install [model] => pro_install [image] => [price] => 25.0000 [quantity] => 1 [weight] => 0.00 [final_price] => 25 [tax_class_id] => 0 [attributes] => Array ( [7] => 14 ) ) [1] => Array ( [id] => 25 [name] => Keyboard [model] => KB [image] => intkeyboardps2.gif [price] => 69.9900 [quantity] => 1 [weight] => 8.00 [final_price] => 69.99 [tax_class_id] => 1 [attributes] => ) ) How do I get it to check the Array for 2 values, 'pro_install' and 'custom_install'. If the values don't exits, I need it to redirect to index.php.

Hi, How can I get it to show the Username instead of the userid. The Username is stored in a different Table in the database: CREATE TABLE `user` ( userid int(10) unsigned NOT NULL auto_increment, username varchar(100) collate utf8_unicode_ci NOT NULL default '', PRIMARY KEY (userid), KEY username (username), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ; -- -- Dumping data for table 'user' -- INSERT INTO `user` (userid, username) VALUES (1, 'Admin');

That's just what I needed. Thank you.

Can anyone help with this, please.

Hi, Can anyone help with this please. <?php $check = tep_db_query("SELECT * FROM tickets ORDER BY `ticket` ASC"); while ($ticket = tep_db_fetch_array($check)) { print_r($ticket); ?> <div> <?php echo $ticket['userid']; ?></div> <div><?php echo $tickets_qty;?></div> <?php } ?> print_r($ticket); is outputting: Array ( [ticket] => 1 [userid] => 2 [day] => 8 [time] => 1299558047 [winner] => 0 ) Array ( [ticket] => 2 [userid] => 1 [day] => 8 [time] => 1299558236 [winner] => 0 ) Array ( [ticket] => 3 [userid] => 1 [day] => 8 [time] => 1299558047 [winner] => 0 ) Array ( [ticket] => 4 [userid] => 1 [day] => 8 [time] => 1299558236 [winner] => 0 ) What I want is for it to display the Userid and the number of tickets that user has. e.g. Userid_____________________Tickets 1___________________________3 2___________________________1

Thank you

Hi, Just trying to update the image link. The code is no longer supported by the coder, so trying to do it myself. I don't know why it was done that way.

How do I change the img src in echo ' <td class="dataTableContent" valign="middle" height="15"> <script language="JavaScript" type="text/javascript"> <!-- document.write("<span id=\"update_totals['.$i.']\"><a href=\"javascript:setCustomOTVisibility(\'update_totals['.($i+1).']\', \'visible\', \'update_totals['.$i.']\');\"><img src=\"order_editor/images/plus.gif\" border=\"0\" alt=\"' . IMAGE_ADD_NEW_OT . '\" title=\"' . IMAGE_ADD_NEW_OT . '\"></a></span>"); //--> </script></td>' . "\n"; to have this <?php echo tep_image(DIR_WS_IMAGES . '../order_editor/images/plus.gif'); ?>

Hi, I want this to run only once every 5 days or greater. The problem is, it is running every time the page is opened. It updates the last_modified field but not the configuration_value field. Can anyone help with this please. <?php $prev = tep_db_query("select * from " . TABLE_CONFIGURATION . " where configuration_key = 'CRON_BD_RUN'"); if (tep_db_num_rows($prev) > 0) { $prev_val = tep_db_fetch_array($prev); if($prev_val['configuration_value'] <= (date("Ymd") - 5)) { echo 'Test Code'; tep_db_query("UPDATE " . TABLE_CONFIGURATION . " SET configuration_value = '" . date("Ymd") . "', last_modified = now() WHERE configuration_key = 'CRON_BD_RUN'"); } } else { tep_db_query("INSERT INTO ".TABLE_CONFIGURATION." (CONFIGURATION_KEY, configuration_value, CONFIGURATION_GROUP_ID) VALUES('CRON_BD_RUN', '".date("Ymd")."', 6)"); } ?> CREATE TABLE IF NOT EXISTS `osc_configuration` ( `configuration_id` int(11) NOT NULL auto_increment, `configuration_title` varchar(255) collate utf8_unicode_ci NOT NULL, `configuration_key` varchar(255) collate utf8_unicode_ci NOT NULL, `configuration_value` varchar(255) collate utf8_unicode_ci NOT NULL, `configuration_description` varchar(255) collate utf8_unicode_ci NOT NULL, `configuration_group_id` int(11) NOT NULL, `sort_order` int(5) default NULL, `last_modified` datetime default NULL, `date_added` datetime NOT NULL, `use_function` varchar(255) collate utf8_unicode_ci default NULL, `set_function` varchar(255) collate utf8_unicode_ci default NULL, PRIMARY KEY (`configuration_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=201 ;

Can anyone help, please.

PHP Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 30515201 bytes) line 160 line 160 = echo "$sql_insert;$crlf";

Hi, Thank for taking a look. That kind of fixed it. It is only putting one line of data from each table. # ======================================================== # # Database saved : osc # On 09-03-2009 at 17:09 by ! # # ======================================================== # -------------------------------------------------------- # # Table structure for table 'address_book' # CREATE TABLE address_book ( address_book_id int(11) NOT NULL auto_increment, customers_id int(11) NOT NULL, entry_gender char(1) NOT NULL, entry_company varchar(32), entry_firstname varchar(32) NOT NULL, entry_lastname varchar(32) NOT NULL, entry_street_address varchar(64) NOT NULL, entry_suburb varchar(32), entry_postcode varchar(10) NOT NULL, entry_city varchar(32) NOT NULL, entry_state varchar(32), entry_country_id int(11) DEFAULT '0' NOT NULL, entry_zone_id int(11) DEFAULT '0' NOT NULL, PRIMARY KEY (address_book_id), KEY idx_address_book_customers_id (customers_id) ); # # Dumping data for table 'address_book' # INSERT INTO address_book VALUES ( '1', '1', 'm', 'ACME Inc.', 'John', 'Doe', '1 Way Street', '', '12345', 'NeverNever', '', '223', '12') # -------------------------------------------------------- # # Table structure for table 'address_format' # CREATE TABLE address_format ( address_format_id int(11) NOT NULL auto_increment, address_format varchar(128) NOT NULL, address_summary varchar(48) NOT NULL, PRIMARY KEY (address_format_id) ); # # Dumping data for table 'address_format' # INSERT INTO address_format VALUES ( '5', '$firstname $lastname$cr$streets$cr$postcode $city$cr$country', '$city / $country')

Can anyone help please

$schema .= get_table_content($dbname, $table, "my_handler");

Hi, Some the sql database is not getting added to the file when using fputs Code been used: <?php if (!defined('ADMIN_FILE')) { die ("Access Denied"); } global $prefix, $db, $admin_file; $aid = substr("$aid", 0,25); $row = $db->sql_fetchrow($db->sql_query("SELECT radminsuper FROM " . $prefix . "_authors WHERE aid='$aid'")); if ($row['radminsuper'] == 1) { $DATESTRING = 'd-M-Y_His'; $DATESTRING1 = 'l, d F Y @ H:i:s T'; $database = $dbname; $sql_filename = $database.'_'.date($DATESTRING).'.sql'; $backups_dir = '../SQL_Backups/'; if (!function_exists('file_put_contents')) { function file_put_contents($filename, $content, $flags = null, $resource_context = null) { // If $content is an array, convert it to a string if (is_array($content)) { $content = implode('', $content); } if (!is_scalar($content)) { user_error('file_put_contents() The 2nd parameter should be either a string or an array', E_USER_WARNING); return false; } $length = strlen($content); $mode = ($flags & FILE_APPEND) ? 'a' : 'w'; $use_inc_path = ($flags & FILE_USE_INCLUDE_PATH) ? true : false; if (($fh = @fopen($filename, $mode, $use_inc_path)) === false) { user_error('file_put_contents() failed to open stream: Permission denied', E_USER_WARNING); return false; } $bytes = 0; if (($bytes = @fwrite($fh, $content)) === false) { $errormsg = sprintf('file_put_contents() Failed to write %d bytes to %s', $length, $filename); user_error($errormsg, E_USER_WARNING); return false; } @fclose($fh); if ($bytes != $length) { $errormsg = sprintf('file_put_contents() Only %d of %d bytes written, possibly out of free disk space.', $bytes, $length); user_error($errormsg, E_USER_WARNING); return false; } return $bytes; } } if (!function_exists('file_get_contents')) { function file_get_contents($filename, $incpath = false, $resource_context = null) { if (false === $fh = fopen($filename, 'rb', $incpath)) { user_error('file_get_contents() failed to open stream: No such file or directory', E_USER_WARNING); return false; } clearstatcache(); if ($fsize = @filesize($filename)) { $data = fread($fh, $fsize); } else { $data = ''; while (!feof($fh)) { $data .= fread($fh, 8192); } } fclose($fh); return $data; } } switch($op) { case "backup": @set_time_limit(600); $crlf="\n"; switch($lang) { case french : // French Text $strNoTablesFound = "Aucune table n'a été trouvée dans cette base."; $strHost = "Serveur"; $strDatabase = "Base de données"; $strTableStructure = "Structure de la table"; $strDumpingData = "Contenu de la table"; $strError = "Erreur"; $strSQLQuery = "requête SQL"; $strMySQLSaid = "MySQL a répondu:"; $strBack = "Retour"; $strFileName = "Sauvegarde BD"; $strName = "Sauvegarde de la base de données"; $strDone = "effectuée le"; $strat = "à"; $strby = "par"; $date_jour = date ("d-m-Y"); break; default : // English Text $strNoTablesFound = "No tables found in database."; $strHost = "Host"; $strDatabase = "Database "; $strTableStructure = "Table structure for table"; $strDumpingData = "Dumping data for table"; $strError = "Error"; $strSQLQuery = "SQL-query"; $strMySQLSaid = "MySQL said: "; $strBack = "Back"; $strFileName = "Save Database"; $strName = "Database saved"; $strDone = "On"; $strat = "at"; $strby = "by"; $date_jour = date ("m-d-Y"); break; } // header("Content-disposition: filename=$strFileName $dbname $date_jour.sql"); // header("Content-type: application/octetstream"); // header("Pragma: no-cache"); // header("Expires: 0"); // doing some DOS-CRLF magic... $client = $_SERVER["HTTP_USER_AGENT"]; if(ereg('[^(]*\((.*)\)[^)]*',$client,$regs)) { $os = $regs[1]; // this looks better under WinX if (eregi("Win",$os)) $crlf="\r\n"; } function my_handler($sql_insert) { global $crlf; echo "$sql_insert;$crlf"; } // Get the content of $table as a series of INSERT statements. // After every row, a custom callback function $handler gets called. // $handler must accept one parameter ($sql_insert); function get_table_content($db, $table, $handler) { $result = mysql_db_query($db, "SELECT * FROM $table") or mysql_die(); $i = 0; while($row = mysql_fetch_row($result)) { // set_time_limit(60); // HaRa $table_list = "("; for($j=0; $j<mysql_num_fields($result);$j++) $table_list .= mysql_field_name($result,$j).", "; $table_list = substr($table_list,0,-2); $table_list .= ")"; if(isset($GLOBALS["showcolumns"])) $schema_insert = "INSERT INTO $table $table_list VALUES ("; else $schema_insert = "INSERT INTO $table VALUES ("; for($j=0; $j<mysql_num_fields($result);$j++) { if(!isset($row[$j])) $schema_insert .= " NULL,"; elseif($row[$j] != "") $schema_insert .= " '".addslashes($row[$j])."',"; else $schema_insert .= " '',"; } $schema_insert = ereg_replace(",$", "", $schema_insert); $schema_insert .= ")"; $handler(trim($schema_insert)); $i++; } return (true); } // Return $table's CREATE definition // Returns a string containing the CREATE statement on success function get_table_def($db, $table, $crlf) { $schema_create = ""; //$schema_create .= "DROP TABLE IF EXISTS $table;$crlf"; $schema_create .= "CREATE TABLE $table ($crlf"; $result = mysql_db_query($db, "SHOW FIELDS FROM $table") or mysql_die(); while($row = mysql_fetch_array($result)) { $schema_create .= " $row[Field] $row[Type]"; if(isset($row["Default"]) && (!empty($row["Default"]) || $row["Default"] == "0")) $schema_create .= " DEFAULT '$row[Default]'"; if($row["Null"] != "YES") $schema_create .= " NOT NULL"; if($row["Extra"] != "") $schema_create .= " $row[Extra]"; $schema_create .= ",$crlf"; } $schema_create = ereg_replace(",".$crlf."$", "", $schema_create); $result = mysql_db_query($db, "SHOW KEYS FROM $table") or mysql_die(); while($row = mysql_fetch_array($result)) { $kname=$row['Key_name']; if(($kname != "PRIMARY") && ($row['Non_unique'] == 0)) $kname="UNIQUE|$kname"; if(!isset($index[$kname])) $index[$kname] = array(); $index[$kname][] = $row['Column_name']; } while(list($x, $columns) = @each($index)) { $schema_create .= ",$crlf"; if($x == "PRIMARY") $schema_create .= " PRIMARY KEY (" . implode($columns, ", ") . ")"; elseif (substr($x,0,6) == "UNIQUE") $schema_create .= " UNIQUE ".substr($x,7)." (" . implode($columns, ", ") . ")"; else $schema_create .= " KEY $x (" . implode($columns, ", ") . ")"; } $schema_create .= "$crlf)"; return (stripslashes($schema_create)); } function mysql_die($error = "") { echo "<b> $strError </b><p>"; if(isset($sql_query) && !empty($sql_query)) { echo "$strSQLQuery: <pre>$sql_query</pre><p>"; } if(empty($error)) echo $strMySQLSaid.mysql_error(); else echo $strMySQLSaid.$error; echo "<br><a href=\"javascript:history.go(-1)\">$strBack</a>"; exit; } global $dbhost, $dbuname, $dbpass, $dbname; mysql_pconnect($dbhost, $dbuname, $dbpass); @mysql_select_db("$dbname") or die ("Unable to select database"); $tables = mysql_list_tables($dbname); $num_tables = @mysql_numrows($tables); if($num_tables == 0) { echo $strNoTablesFound; } else { $fp = fopen($backups_dir . $sql_filename, 'w'); $i = 0; $heure_jour = date ("H:i"); $schema .= "# ========================================================$crlf"; $schema .= "#$crlf"; $schema .= "# $strName : $dbname$crlf"; $schema .= "# $strDone $date_jour $strat $heure_jour $strby $name !$crlf"; $schema .= "#$crlf"; $schema .= "# ========================================================$crlf"; $schema .= "$crlf"; while($i < $num_tables) { $table = mysql_tablename($tables, $i); $schema .= $crlf; $schema .= "# --------------------------------------------------------$crlf"; $schema .= "#$crlf"; $schema .= "# $strTableStructure '$table'$crlf"; $schema .= "#$crlf"; $schema .= $crlf; $schema .= get_table_def($dbname, $table, $crlf).";$crlf$crlf"; $schema .= "#$crlf"; $schema .= "# $strDumpingData '$table'$crlf"; $schema .= "#$crlf"; $schema .= $crlf; $schema .= get_table_content($dbname, $table, "my_handler"); $i++; } } fputs($fp, $schema); fclose($fp); break; } } else { echo "Access Denied"; } ?> Example of output: # ======================================================== # # Database saved : osc # On 09-02-2009 at 23:18 by ! # # ======================================================== # -------------------------------------------------------- # # Table structure for table 'address_book' # CREATE TABLE address_book ( address_book_id int(11) NOT NULL auto_increment, customers_id int(11) NOT NULL, entry_gender char(1) NOT NULL, entry_company varchar(32), entry_firstname varchar(32) NOT NULL, entry_lastname varchar(32) NOT NULL, entry_street_address varchar(64) NOT NULL, entry_suburb varchar(32), entry_postcode varchar(10) NOT NULL, entry_city varchar(32) NOT NULL, entry_state varchar(32), entry_country_id int(11) DEFAULT '0' NOT NULL, entry_zone_id int(11) DEFAULT '0' NOT NULL, PRIMARY KEY (address_book_id), KEY idx_address_book_customers_id (customers_id) ); # # Dumping data for table 'address_book' # 1 # -------------------------------------------------------- # # Table structure for table 'address_format' # CREATE TABLE address_format ( address_format_id int(11) NOT NULL auto_increment, address_format varchar(128) NOT NULL, address_summary varchar(48) NOT NULL, PRIMARY KEY (address_format_id) ); # # Dumping data for table 'address_format' # 1 # -------------------------------------------------------- # # Table structure for table 'administrators' # CREATE TABLE administrators ( id int(11) NOT NULL auto_increment, user_name varchar(32) NOT NULL, user_password varchar(40) NOT NULL, PRIMARY KEY (id) ); # # Dumping data for table 'administrators' # 1 # -------------------------------------------------------- # # Table structure for table 'banners' # CREATE TABLE banners ( banners_id int(11) NOT NULL auto_increment, banners_title varchar(64) NOT NULL, banners_url varchar(255) NOT NULL, banners_image varchar(64) NOT NULL, banners_group varchar(10) NOT NULL, banners_html_text text, expires_impressions int(7) DEFAULT '0', expires_date datetime, date_scheduled datetime, date_added datetime NOT NULL, date_status_change datetime, status int(1) DEFAULT '1' NOT NULL, PRIMARY KEY (banners_id), KEY idx_banners_group (banners_group) ); # # Dumping data for table 'banners' # 1 # -------------------------------------------------------- Its putting 1 in instead of the data.