MYSQL Nightmare

[hepkat63]

HI All,
I hope that someone can help me with what should have been a simple problem to solve, but I have been pulling out my hair now for a week.

BACKGROUND:
At each end of month, I need to insert some records into my billing table. This is based on username, planamount and plan type. Plan type is only there because it needs to exclude a certain type (bulk plan).

I had this working perfectly however, the amount that it inserted into the billing table was a static amount. Further, I had to copy, paste and alter the script for each and every plan that we have AND then when the prices chaged, update them all.

So, I tried to modify the script to be able to put in the variable into the billing table that came from the planamount column. This works only so far as it dumps the WHOLE amount into each user. What I need to do, is to isolate each planamount for each user.

MICROSOFT ACCESS.
In MS Access, I get it working everytime using this query:

INSERT INTO billtest ( username, invamnt, plan, invno, paiddate, paidamnt, [month], [year] )
SELECT userbase.username, userbase.planamount, userbase.plan, Right(Rnd([planamount]),6) AS invoiceno, Format(Date()) AS paiddate, (0) AS paidamnt, Format(Now(),"mmmm&quot AS [month], Format(Now(),"yyyy&quot AS [year]
FROM userbase
WHERE (((userbase.planamount) Is Not Null) AND ((userbase.plan)<>&quot;bulk&quot);


MYSQL:
This is my current code (Which does not work and which I need help on)

#!/bin/sh
#
# End Of Month Billing Script.
#
# This Script performs end of month billing procedures.
#Obtain a list of billable users that are not BULK access plan customers from the MySQL DATABASE. and output to a temp file for processing.
mysql -s -e &quot;SELECT * FROM userbase WHERE plan <> 'bulk'&quot; internet|awk '{print $1}' > /tmp/.sql.user
#echo &quot;itgroup&quot; > /tmp/.sql.user
# Perform billing functions on each user listed in the above processing file.
for i in `cat /tmp/.sql.user` ; do
#Now we have this information, we set some variables to add into the billing table for the customers.
#This gives us a unique invoice number
INV=`/itg-scripts/generate-inv-no`
MONTH=`date +%B`
DATE=`date +%d-%m-%y`
YEAR=`date +%Y`
AMOUNT=`mysql -s -e &quot;SELECT userbase.planamount FROM userbase WHERE username IN ('$1')&quot; internet`
PLAN=`mysql -s -e &quot;SELECT userbase.plan FROM userbase WHERE username IN ('$1')&quot; internet`
#Execute SQL Syntax to update billing database
mysql -s -e &quot;INSERT INTO billtest (username,invno,invamnt,paidamnt,month,paiddate,plan,year) VALUES ('$i','$INV','$AMOUNT','0','$MONTH','$DATE','$PLAN','$YEAR');&quot; internet
done
##Clean up
rm /tmp/.sql.user


Any ideas would be greatly appreciated
thanks
Steve

 

[hos2]

why don't you use the access query for the mysql sollution ?? or do you keep on working in access ??

you can at least use

SELECT userbase.username, userbase.planamount, userbase.plan, Right(Rnd(planamount),6) AS invoiceno, Format(Date()) AS paiddate, (0) AS paidamnt, Format(Now(),"mmmm") AS month, Format(Now(),"yyyy") AS year
FROM userbase
WHERE (((userbase.planamount) Is Not Null) AND ((userbase.plan)<>&quot;bulk&quot;));

query to generate to you're output file

 

[hos2]

and you have to look at the right sql functions for mysql. like date_format

http://www.mysql.com/doc/en/Date_and_time_functions.html

 

[hos2]

why is it necessary to work with the output file, you make a query on mysql output it to a file and then read the file and use it again ????

you can also put all the values in a array if you like. or as said rewrite the access query

 

[hos2]

and why not use the autoincrement key as the invoicenumber ??

 

[hepkat63]

Hi Guys,
thanks for the comments. I only have the msaccess query as a 'proof of concept' and yes, I could just use this, however I then need to open access to run the query. The installation of MYSQL resides on my Red Hat linux box and the script is called from a cron job.

I am certainly no programmer, so if the syntax is wrong or indeed if it could be done a better way, please let me know. I only posted the script to show what it was I am trying to achive (albeit yukky code).
thanks
Steve

 

[hos2]

why do you need to open access to run the query ?? you can also run the same query in mysql if you rewrite it with the proper mysql commands

 

[hepkat63]

Hi,
thanks for your reply. I would LOVE to be able to rewrite the proper mysql commands, but I don't know enough to do this - hence the post on the forum

I have spent nearly a week crusing MYsql sites on the net and trying different commands. I must have run this script over a hundred times - only to keep getting the incorrect result and it is driving me crazy.

Can you tell me how I could write this query correctly? I have two tables, userbase (which contains all my users etc) and billing (which contains the billing information).

thanks
STeve