MySQL Query

[halcyonforever]

Hey everyone,
I am putting this out here incase anyone might have some MySQL experience that I could borrow.

I need to create a query in a MySQL database that references 2 tables

table one has two columns that I need
Column A is a list of transactions
Column B is a list of employees

Table 2 has several columns one (call it y) of which matches column A from table 1

What I need is all records from table 2 where column Y matches an item on table 1:column A and Table2:Date = given date

my thought at a query is something like

SELECT * FROM TABLE2 WHERE ColumnY IS LIKE Table1:ColumnA and Date=today;

Basically I want to know how many transactions someone did on a given day but the system doesn't store the userID with the dates...

It has a report tool but something has become corrupted and the official fix is to re-install but it wipes the database(restoring from the old database carries the same corruption, but I can get command line access with out problems)

[halcyonforever]

Ok so I got some of the information I need done... now just to make it look pretty.

[.Maleficus.]

So you figured it out?

[halcyonforever]

Just nailed out the last bugs.

Basically using a Nortel Reporting for Call Center system, it has a built in report generator, which keeps breaking.

I wrote a vbscript that preps the files, get the date desired from the user and generates a msql query file, launches a batch file that logs into the mysql database and starts the query file, then launches a second script to modify the output into a useable report format.

The killer for me was creating a cross table query, I had to use LEFT JOIN to align the tables to the common variable and the it worked great!.

[Airbozo]

Glad you got it figured out. Now I know where to go when I have Sql Questions...

BTW this is what I read...


Just nailed out the last bugs.

Basically using a Nortel Reporting for Call Center system, it has a built in report generator, which keeps breaking.

我寫了準備好文件的一vbscript，從用戶得到日期渴望并且引起msql詢問文件，發射日誌到mysq l數據庫里和開始詢問文件，然后發射第二個劇本修改產品入一個能用的报告格式的批文件。 我的兇手創造一次發怒桌詢問，我必須使用左加入對公用变量排列桌，并且它运作偉大!



HEHEH

[halcyonforever]

So, if anyone is interested here is the code for my little project.

This is the first pass. What it does is figure out yesterday's date, then format it as YYYY-MM-DD.

then it reads a set of MySQL commands from a text file, and writes the query to a new text file with the date first and then the other commands(all this because I need it to set a variable on line 1 and you can't write to the front of a file with vbs)

then finally it calls a batch file to start MySQL with the appropriate query file.


Option Explicit
Dim objFSO, objFolder, objShell, objTextFile, objFile, objApp, strFile2, strFile3
Dim strDirectory, strFile, strText1, strText2, strText3, strText4, strText5, strText6
Dim f, fso, ReadAllTextFile, dFile, strBat
Dim strDate, strYear, strMonth, strDay

strDirectory = "U:\Phone"
strFile = "\MySQL-PC.txt"
strFile2 = "\MySQL-PC1.txt"
strFile3 = "\MySQL-OUT.txt"
strBat = "\MySQL.bat"
strText3 = "SET @D1 = '"
strText5 = "SET @D2 = '"
strText4 = " 00:00:00';"
strText6 = " 23:00:00';"
Set objShell = CreateObject("WScript.Shell")

'This section gathers the user definded dates (removed for automatic date generation)
'strText1 = InputBox("Please input start date to report", "", "YYYY-MM-DD")

'strText2 = InputBox("Please input end date to report", "", "YYYY-MM-DD")

strDate = DATE() - 1 'get yesterday's date
strYear = YEAR(strDate) 'get the year
strMonth = MONTH(strDate) 'get the month
strMonth = 100 + strMonth 'get the month plus 100
strMonth = CStr(strMonth) 'convert the value to a text string
strMonth = Right(strMonth, 2) 'crop to the right two digits
strDay = DAY(strDate) 'repeat month procedure to convert to
strDay = 100 + strDay
strDay = CStr(strDay)
strDay = Right(strDay, 2)

strText1= strYear & "-" & strMonth & "-" & strDay

strText2 = strText1


' Create the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Check that the strDirectory folder exists
If objFSO.FolderExists(strDirectory) Then
Set objFolder = objFSO.GetFolder(strDirectory)
Else
Set objFolder = objFSO.CreateFolder(strDirectory)
WScript.Echo "Just created " & strDirectory
End If

If objFSO.FileExists(strDirectory & strFile) Then
Set objFolder = objFSO.GetFolder(strDirectory)
Else
Set objFile = objFSO.CreateTextFile(strDirectory & strFile)
Wscript.Echo "Just created " & strDirectory & strFile
End If

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(strDirectory & strFile2, 1)
ReadAllTextFile = f.ReadAll

set objFile = nothing
set objFolder = nothing
' OpenTextFile Method needs a Const value
' ForAppending = 8 ForReading = 1, ForWriting = 2
Const ForWriting = 2

Set objTextFile = objFSO.OpenTextFile(strDirectory & strFile, ForWriting, True)

' Writes strText 1 & 2 every time you run this VBScript
objTextFile.WriteLine(strText3&strText1&strText4)
objTextFile.WriteLine(strText5&strText2&strText6)
objTextFile.Write(ReadAllTextFile)
objTextFile.Close


' Bonus or cosmetic section to launch explorer to check file
'If err.number = vbEmpty then

' objShell.run ("Explorer" &" " & strDirectory & "\" )
'Else WScript.echo "VBScript Error: " & err.number
'End If


' This sections executes some file clean-up and launches mysql

'Find the output file and delete it.
If objFSO.FileExists(strDirectory & strFile3) Then
Set dFile = objFSO.GetFile(strDirectory & strFile3)
dFile.Delete
Else
End If
objShell.run(strDirectory & strBat)

'MsgBox "Ending sub-routine 1"

WScript.Quit

[halcyonforever]

It's funny to come by this post after several months of tinkering. It has now grown into a fully compiled program in .net which updates an excel workbook, emails specific totals to different managers, maintains event logs and is error correcting. Instead of a invisible script it runs a complex UI that gives up to the minute totals, statistics and projections.

I haven't had any programming experience since a class my freshman year of highschool (10+ years ago) so it's been fun basically learning as I go and seeing what all I can combine into this program.