Jump to content

defcon2000

Members
  • Posts

    5
  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

defcon2000's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. Hello All, We have a table in MySQL that we extract into an array as follows: $tmpdetails = mysql_query("SELECT * FROM params", $conn) or die('Query failed: ' . mysql_error()); $details = mysql_fetch_array($tmpdetails, MYSQL_ASSOC); The table is called params and has the following fields and data: [b]Id parameter detail[/b] 1 smtpserveraddress blowfish.com 2 serveraddress 192.168.0.10 3 copyinvoiceto jane@blowfish.com 4 billingemailaddress billing@blowfish.com 5 localcurrencysymbol GMD We want to be able to access the result without having to loop through the array. We want to use the results of parameter as a reference point to access detail. For example, we want to access the result "192.168.0.10" using "serveraddress" in an array type format. Thanks in advance for your help.
  2. Hello Everyone, I want to insert a new record with data from 2 tables, based on a condition from a table . The insert should be as follows: INSERT INTO subscriptiontrans (subscriptiontrans_id, subscription_id, datecreated, paymentdue, datepaid, subscriptiontype_id, subscriptiondescription, service_description, occurence, occurencetype, currencysymbol, amount, amountlocal, amountdiscount, amounttax, amountnet, subscriptiontrans_status, printed) VALUES (????????????) The tables to get data from are as follows: -------------------------------------------------- Table name: subscription `subscription_id` `customer_id` `datecreated` `datedue` `subscriptiontype_id` `amountdiscount` `specialnotes` `subscription_status` -------------------------------------------------- Table name: subscriptiontype `subscriptiontype_id` `subscription_type` `subscriptiondescription` `occurence` `occurencetype` `currency_id` `amount` `taxpercent` `subscriptiontype_status` -------------------------------------------------- Table name: currency `currency_id` `currency` `currencysymbol` `rate` -------------------------------------------------- The conditions for insert should be as follows: subscription.datedue = CURDATE() subscription.subscription_status = 'Active' The following are calculations and defaults: subscription_id = subscription.subscription_id datecreated = CURDATE() paymentdue = 7 days from CURDATE() datepaid = NULL subscriptiontype_id = subscriptiontype.subscriptiontype_id subscriptiondescription = subscriptiontype.subscriptiondescription service_description = subscriptiontype.service_description occurence = subscriptiontype.occurence occurencetype = subscriptiontype.occurencetype currencysymbol = currency.currencysymbol amount = subscriptiontype.amount amountlocal = currency.rate * subscriptiontype.amount amountdiscount = subscription.amountdiscount amountnet = (amountlocal - amountdiscount) * ((100 - amounttax)/100) subscriptiontrans_status = 'Unpaid' printed = 'N' Thanks in advance for your help. Rgds,
  3. Hello, Pls tell me what is wrong with my statement: UPDATE reminder set reminderdate = (IF (status = 'Open' and reminded = 'Y' and autoclose = 'Y' and recur = 'Y', ADDDATE(reminderdate, INTERVAL occurence occurencetype), reminderdate)) occurencetype can be "DAY", "MONTH" or "YEAR" and stored as a VARCHAR. occurenace can be any value such as 1, 2, 5, 12, 10, etc ... and is an INT. Thanks for your help. Rgds,
  4. Hello Everyone, I have the following 2 tables as follows: Reminder Table `reminder_id` int(10) unsigned NOT NULL auto_increment, `username` varchar(50) default NULL, `reminderdate` date default NULL, `reminder` varchar(255) default NULL, `autoclose` varchar(1) default NULL, `recur` varchar(1) default NULL, `occurence` int(3) default NULL, `emailme` varchar(3) default NULL, `emailtoperson1` varchar(100) default NULL, `emailtoperson2` varchar(100) default NULL, `emailtoperson3` varchar(100) default NULL, `reminded` varchar(1) default NULL, `status` varchar(10) default NULL, PRIMARY KEY (`reminder_id`) Occurence `occurence_id` int(11) NOT NULL auto_increment, `occurence` varchar(50) collate latin1_general_ci default NULL, `periodtype` varchar(11) collate latin1_general_ci default NULL, `period` int(2) default NULL, PRIMARY KEY (`occurence_id`) occurence.occurence_id relates to reminder.occurence The occurence table stores the type of occurences available for example: Occurence_ID Occurence Period Period Type 1 1 Week 7 Week 2 Quarterly 3 Month 3 Annual 1 Year The idea of the occurence table is so that dates can be change by day, month or year and not having to go deep into mathematical calculations. A sample data for reminder is as follows: `reminder_id` 10 `username` defcon2000 `reminderdate` 2005-12-03 `reminder` "hello this is a test" `autoclose` Y `recur` Y `occurence` 2 (meaning it will recur every quarter) `emailme` Y `emailtoperson1` "defcon2000@hammer.com" `emailtoperson2` "" `emailtoperson3` "" `reminded` N `status` "Open" -------------------------------------------------------------------------------- The logic I am look for in an SQL format is: SELECT * From reminder WHERE "status" = "open" and "reminded" ="N" DO THE FOLLOWING FOR EACH ROW ROUND IF "reminderdate" = "today's date" THEN "reminded" = "Y" IF "reminderdate" > "7 days from today's date" and "reminded" = "Y" and "autoclose" = "Y" and “recur” = "N" THEN "status" = "Closed" ELSE "status" = "Closed" COPY RECORD into reminder CHANGE “reminderdate” meet “occurrence” requirement, SET “reminded” = “N” SET “recur” = “Y”, SET “autoclose” = “Y” SET "status" = "Open" -------------------------------------------------------------------------------- I hope someone can help me as I am quite new to MySQL and esp SQL. Thanks in advance. Rgds,
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.