[Putting Data into a Multi-Dimentional Array]

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.

[MySQL statement - Help]

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,

[WHAT IS WRONG WITH THIS, HELP PLEASE]

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,

[Reminder Database]

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,