Mysql help

[mircea]

Hello guys, i want to make a program which connects to a mysql database and all it has to do it's to create a database and insert some data. I searched the forum for some udf's cause i understand that autoit has only sqlite. I looked up on some udf's and i found them but i don't really understand them so well.

So what im trying to ask is ... can you guys give me some examples of mysql scripts, i just want some simple's one no fancy things, and from there i think i can start to make an ideea and make some program for myself. I hope im not asking to much

Thank you

[SmOke_N]

Progandy provides some pretty simple examples.

[mircea]

Ah yes, i didn't see the examples, i will try to see if it works for me, but first i have to learn soemthing about mysql.

Thanks for the help. Cheers

[SmOke_N]

but first i have to learn soemthing about mysql.

That helps.

[mircea]

Yes, i learned the most of them and with the help from the mysql udf i think im going to finish the script tonight. But i have a problem that i never meet before. What could be the cause?

I was trying to test the code to add a few changes to see if it works and stuff like that but it give's me this error

Error

C:\Documents and Settings\Administrator\Desktop\Test mysql\mysql.au3 (2) : ==> #include depth exceeded.  Make sure there are no recursive includes.: 
#include <mysql.au3>

The code

#include <array.au3>
#include <mysql.au3>

_MySQL_InitLibrary()
If @error Then Exit MsgBox(0, 'Error', "Lipsa fisier libmysql.dll")
MsgBox(0, "DLL Version:",_MySQL_Get_Client_Version()&@CRLF& _MySQL_Get_Client_Info())

$MysqlConn = _MySQL_Init()

$connected = _MySQL_Real_Connect($MysqlConn,"localhost","root","","mhell")

If $connected = 0 Then
    $errno = _MySQL_errno($MysqlConn)
    MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn))
    If $errno = $CR_UNKNOWN_HOST Then MsgBox(0,"Error:","$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST)
Endif

MsgBox(0, "XAMPP-Cdcol-demo", "XAMPP-Cdcol-demo")

$connected = _MySQL_Real_Connect($MysqlConn, "localhost", "root", "", "mircea")
If $connected = 0 Then Exit MsgBox(16, 'Connection Error', _MySQL_Error($MysqlConn))

$query = "SELECT * FROM emulatoare"
_MySQL_Real_Query($MysqlConn, $query)


$res = _MySQL_Store_Result($MysqlConn)

$fields = _MySQL_Num_Fields($res)

$rows = _MySQL_Num_Rows($res)
MsgBox(0, "", $rows & "-" & $fields)


$array = _MySQL_Fetch_Result_StringArray($res)
_ArrayDisplay($array)

Nevermind ... too blind to see some error and to check some files

Edited May 31, 2011 by mircea

[mircea]

I have 1 question is there a way i can run a .sql file i mean something like this _MySQL_Real_Query($MysqlConn, "example.sql") ? Cause i don't understand why i can't make a table with data, i can make a database, but when i try to make a table it dosen't make it

$connected = _MySQL_Real_Connect($MysqlConn, "localhost", "root", "", "")
If $connected = 0 Then Exit MsgBox(16, 'Connection Error', _MySQL_Error($MysqlConn))

$query = "CREATE DATABASE emulatoare"
_MySQL_Real_Query($MysqlConn, $query)


$query2 = "CREATE TABLE Emulatoare (Id Int, Nume varchar(255), Prenume varchar(255) )"
_MySQL_Real_Query($MysqlConn, $query2)

[Zedna]

I was trying to test the code to add a few changes to see if it works and stuff like that but it give's me this error

Error

C:\Documents and Settings\Administrator\Desktop\Test mysql\mysql.au3 (2) : ==> #include depth exceeded.  Make sure there are no recursive includes.: 
#include <mysql.au3>

The code

#include <array.au3>
#include <mysql.au3>

...

In your script or in MySql.au3 should be #include-once

#include-once 
#include <array.au3>
...

[Zedna]

I have 1 question is there a way i can run a .sql file i mean something like this _MySQL_Real_Query($MysqlConn, "example.sql") ? Cause i don't understand why i can't make a table with data, i can make a database, but when i try to make a table it dosen't make it

$connected = _MySQL_Real_Connect($MysqlConn, "localhost", "root", "", "")
If $connected = 0 Then Exit MsgBox(16, 'Connection Error', _MySQL_Error($MysqlConn))

$query = "CREATE DATABASE emulatoare"
_MySQL_Real_Query($MysqlConn, $query)


$query2 = "CREATE TABLE Emulatoare (Id Int, Nume varchar(255), Prenume varchar(255) )"
_MySQL_Real_Query($MysqlConn, $query2)

I use SQLite not MySql so I don't know exact syntax

but in general:

- after _MySQL_Real_Query($MysqlConn, $query2) check error code/message

- after "create database" command add new "use database" command and finally your "create table"

[BrewManNH]

Is the name of YOUR script mysql.au3 by any chance? If so, by including <mysql.au3> the script is trying to include your script into itself.

[mircea]

@Zedna i checked

$query2 = "INSERT INTO Emulatoare * Values (4,Mirceasss,hEllo)"
_MySQL_Real_Query($MysqlConn, $query2)

If @error then 
    msgbox(0,"Error",@error)
endif

and it didn't give me any error

@BrewManNH i tried what you said but no luck it didnt work

i maked a table and tried to insert the data into the table but still no luck.

[Zedna]

From topic about MySql UDF:

$mysql_bool = _MySQL_Real_Query($MysqlConn, $query)
If $mysql_bool = $MYSQL_SUCCESS Then
    MsgBox(0, '', "Query OK")
Else
    $errno = _MySQL_errno($MysqlConn)
    MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn))
EndIf

Edited June 1, 2011 by Zedna

[mircea]

Thanks for the error code dunno why i didn't see it. Well... now this is a bit funny, i have this error and i don't really know what to dom i tried to use the * like i read about mysql but no succes and if i remove the * it gives me another error

Error 1 with the *

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* Values (3,Mirceasss,hEllo)' at line 1>Exit

Error 2 without the *

1054
Unknown column 'Mircea' in 'field list'>Exit code: 0    Time: 2.927

the sql file is this

/*
Navicat MySQL Data Transfer

Source Server         : test
Source Server Version : 50508
Source Host           : localhost:3306
Source Database       : mircea

Target Server Type    : MYSQL
Target Server Version : 50508
File Encoding         : 65001

Date: 2011-06-01 16:13:19
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `emulatoare`
-- ----------------------------
DROP TABLE IF EXISTS `emulatoare`;
CREATE TABLE `emulatoare` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `nume` char(23) NOT NULL,
  `parola` char(23) NOT NULL,
  `Serie Emulator` char(23) NOT NULL,
  `Reparat` enum('nu','da') CHARACTER SET utf8 NOT NULL DEFAULT 'nu',
  `Defect` enum('da','nu') CHARACTER SET utf8 NOT NULL DEFAULT 'nu',
  PRIMARY KEY (`id`,`nume`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of emulatoare
-- ----------------------------
INSERT INTO `emulatoare` VALUES ('1', 'mircea', '123456', 'serie', 'nu', 'nu');
INSERT INTO `emulatoare` VALUES ('2', 'dorin', 'caca', 'asd', 'nu', 'nu');

[mircea]

I found out what was the problem and thank you @Zedna for the error code display. So the problem was that i needed to put all the data sequence

Before

after

$query2 = "INSERT INTO Emulatoare Values ('','vasile','hEllo','sss','nu','nu')"

[mircea]

Hello guys and thank you again for the help you provided me, but now i have a problem which i don't know how to solve it. I found out how to insert information into mysql, but now i don't know how to get it, better say i want to know how can i make the program search's a specific column and if he find a duplicate to give me a error.

I tried this method but no succes

The code i tried to use form the mysql library

So i know that _mysql_data_seek gives me the information from the first pannel and 0 means from which to start.

Mysql_fetch_row_StringArray get's the data from the table (accounts) and number 1 which column

Edit:

I made a mistake the number 1 is how many column's to show (my bad)

What i don't know how can i get the information from there (better say when i use msgbox it dosen't give me any text only blank)

MsgBox(0, '', "Zugriff Methode 2 - Reihe für Reihe")
_MySQL_Data_Seek($res, 0) ; nur zum zum Zurücksetzen an den Anfang der Abfrage
Do
$row1 = _MySQL_Fetch_Row_StringArray($res,1)
If @error Then ExitLoop
msgbox(0,"asd",$row1)
;_ArrayDisplay($row1)
Until @error

The code for the new username

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Crypt.au3>
#include <Date.au3>
#include <file.au3>
#include <array.au3>
#include <string.au3>
#include <GuiListView.au3>
#include <ListViewConstants.au3>
#include <GuiTreeView.au3>
#include <TreeViewConstants.au3>
#include <mysql.au3>
                    
                    $NewUser = GUICreate("New username", 209, 188, -1, -1)
                    $newuserinput = GUICtrlCreateInput("", 8, 40, 185, 21)
                    $newpassinput = GUICtrlCreateInput("", 8, 96, 185, 21, BitOR($GUI_SS_DEFAULT_INPUT,$ES_PASSWORD))
                    $Createnewuser = GUICtrlCreateButton("&OK", 14, 128, 75, 25, $BS_NOTIFY)
                    $Cancelnewuser = GUICtrlCreateButton("&Cancel", 111, 128, 75, 25, $BS_NOTIFY)
                    $EnterPassLabel = GUICtrlCreateLabel("Enter password", 8, 68, 77, 17, 0)
                    $newusername = GUICtrlCreateLabel("Enter New Username", 8, 16, 105, 17)
                    GUISetState(@SW_SHOW)
                    $citescini=IniReadSectionNames("accounts.ini")
                        While 1
                        $nMsg = GUIGetMsg()
                            Switch $nMsg
                                Case $GUI_EVENT_CLOSE
                                    ExitLoop
                                case $cancelnewuser
                                    Exitloop
                                case $createnewuser
                                    if ControlGetText("","",$newuserinput) = "" or ControlGetText("","",$newpassinput) = "" then 
                                        msgbox(0,"Error", "Username/Password can't be blank")
                                    Else 
                                        
                                            _MySQL_InitLibrary()
                                            If @error Then Exit MsgBox(0, 'Error', "Lipsa fisier libmysql.dll")
                                        

                                            $MysqlConn = _MySQL_Init()
    
                                            $connected = _MySQL_Real_Connect($MysqlConn,"localhost","root","","emulatoare")

                                            If $connected = 0 Then
                                            $errno = _MySQL_errno($MysqlConn)
                                            MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn))
                                            If $errno = $CR_UNKNOWN_HOST Then MsgBox(0,"Error:","$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST)
                                            Endif
                                            
                                            $query = "SELECT Nume_user FROM Accounts"
                                            _MySQL_Real_Query($MysqlConn, $query)
                                            
                                            $res = _MySQL_Store_Result($MysqlConn)
                                            ;if NOT $res = guictrlread($newuserinput) then here is my problem checking out if the name exists or not 
                                            
                                            $query2 = "INSERT INTO Accounts (nume_user,Parola,data,ora) values ('"& Guictrlread($newuserinput) &"','"& Guictrlread($newpassinput)&"','"& @MDAY & "-"& @MON &"-"& @YEAR &"','"& @hour&":"& @MIN & ":" & @SEC&"')"

                                            $mysql_bool = _MySQL_Real_Query($MysqlConn, $query2)
                                            If $mysql_bool = $MYSQL_SUCCESS Then
                                            MsgBox(0, '', "Query OK")

                                            Else
                                                $errno = _MySQL_errno($MysqlConn)
                                                ;MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn))
                                                ConsoleWrite($errno & @LF & _MySQL_error($MysqlConn))
                                            EndIf
                                            _MySQL_Free_Result($res)
                                            _MySQL_Close($MysqlConn)
                                            _MySQL_EndLibrary()
                                            ;else 
                                            ; msgbox(0,"Error", "The username allready exists")
                                        
                                    Endif
                                
            

                            EndSwitch
                        WEnd

Edited June 5, 2011 by mircea

[ProgAndy]

$row1 is an array. When using "SELECT a, b FROM ..." Then $row1[0] is the value in column a, $row1[1] is the value in b. When using "SELECT * FROM ..." Then $row[0] is the value in first column of the table etc.

The second parameter of _MySQL_Fetch_Row_StringArray has to be the count of columns the result has. If it is less than 1, the function will get it with _MySQL_Num_Fields. The parameter is there since it is inefficent to call that function every time if you fetch the rows in a loop.

Edited June 5, 2011 by ProgAndy

[mircea]

Ok, i understand that is array but then how can i get the data so i can make a comparasion ? I tried with your example only that i use my database but no luck it tell's me that he found it but actually he didn't it

i tried to use arraybinarysearch, arrayfind all and so on.

$query = "SELECT Nume_user FROM accounts"
_MySQL_Real_Query($MysqlConn, $query)


res = _MySQL_Store_Result($MysqlConn)

_arraysearch

_MySQL_Data_Seek($res, 0) ; nur zum zum Zurücksetzen an den Anfang der Abfrage
Do
$row1 = _MySQL_Fetch_Row_StringArray($res)

If @error Then ExitLoop
;msgbox(0,"asd",DllStructGetData($row1,1,5) 
;_ArrayDisplay($row1)
if _ArrayBinarySearch($res,"dorin") Then
    msgbox(0,"am gasit","am gasit")
else 
    MsgBox(0,"nu am gasit","nu am gasit")
endif
Until @error

Edit: I found out how to get pass with that using the first example you provide us

; Zugriff 1

MsgBox(0, '', "Zugriff Methode 1- Handarbeit")

Edit 2: Is there anyway to search via a value? for example in a tabel Account on the first column (nume) there is data TEST1 and to show me all the data row from there?

I read your mysql.udf a lot and i found this command but i cannot find it in the mysql.au3

I'm talking about the _MySQL_Real_Escape_String

$yam = "testuser"
$pass = "pass"

$query = "SELECT * FROM users WHERE yam='"  & _MySQL_Real_Escape_String($MysqlConn,$yam) & "' AND password='" & _MySQL_Real_Escape_String($MysqlConn,$pass) & "'"
MsgBox(0, '', $query)
If _MySQL_Real_Query($MysqlConn, $query) = $MYSQL_ERROR Then
    MsgBox(0, 'Error', _MySQL_Error($Mysql

Edited June 5, 2011 by mircea

[ProgAndy]

Why is it so difficult to access an item in the array? You know at which index the value will be, so use $row[0] or $row[1], ...

[mircea]

@ProgAndy i tried what did you say but no luck. I tried many ways but no succes in what im trying to make.

First way.

- If i make the program to check for errors the $mysql bool where checks if it can querry the afirmation, If the information is Valid it tell me that is valid and if it's not valid isn't tell me anything

i tryied to change from the MYSQL_Succes to MYSQL_OK but still no succes, after that i was thinking maybe the problem is that the program it's running the querry but isn't store the information in the mysql_store_results but no succes with that too.

What im trying to make it's a login username, and im trying to make the program to search the field for the username and if he find out the username after that to compare if the password it's corectly with the user input.

What i done so far it's to make him search for the password if it's find it i display the table if isn't find it isn't display anything. (on the first one )

First way

expandcollapse popup

#include <array.au3>
#include "mysql.au3"

; MYSQL starten, DLL im PATH (enthält auch @ScriptDir), sont Pfad zur DLL angeben. DLL muss libmysql.dll heißen.
_MySQL_InitLibrary()
If @error Then Exit MsgBox(0, '', "")
MsgBox(0, "DLL Version:",_MySQL_Get_Client_Version()&@CRLF& _MySQL_Get_Client_Info())

$MysqlConn = _MySQL_Init()

 
MsgBox(0,"Fehler-Demo","Fehler-Demo")
$connected = _MySQL_Real_Connect($MysqlConn,"localhost","root","","emulatoare")
If $connected = 0 Then
    $errno = _MySQL_errno($MysqlConn)
    MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn))
    If $errno = $CR_UNKNOWN_HOST Then MsgBox(0,"Error:","$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST)
Endif



$query = "SELECT * FROM accounts where Parola='9c40'"


$mysql_bool = _MySQL_Real_Query($MysqlConn, $query)
            If $mysql_bool = $MYSQL_SUCCESS Then
                $res = _MySQL_Store_Result($MysqlConn)
                
                ;MsgBox(0, '', "Zugriff Methode 2 - Reihe für Reihe")
                _MySQL_Data_Seek($res, 0) ; nur zum zum Zurücksetzen an den Anfang der Abfrage
                Do
                $row1 = _MySQL_Fetch_Row_StringArray($res)
                If @error Then ExitLoop
                _ArrayDisplay($row1)
                Until @error
                
                
            ;MsgBox(0, '', "Query OK")
        ElseIf
            MSgbox(0,"error", "Wrong username/passsword")
        Else
            
            $errno = _MySQL_errno($MysqlConn)
                            MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn))
                    ;ConsoleWrite($errno & @LF & _MySQL_error($MysqlConn))
            
            EndIf

The second thing i tried is this, and this one it's working untill i want to make him check the second field. The first field (nume_user) find's it and searches to see if it's there but when im trying to make him search for the password (the second field, Parola) it dosen't search for it.

$query = "SELECT * FROM accounts where Nume_user='Mircea'"
_MySQL_Real_Query($MysqlConn, $query)


$res = _MySQL_Store_Result($MysqlConn)

$fields = _MySQL_Num_Fields($res)

$rows = _MySQL_Num_Rows($res)
MsgBox(0, "", $rows & "-" & $fields)

; Zugriff 1
MsgBox(0, '', "Zugriff Methode 1- Handarbeit")
Dim $array[$rows][$fields+1]
For $k = 1 To $rows
    $mysqlrow = _MySQL_Fetch_Row($res,$fields)

    $lenthsStruct = _MySQL_Fetch_Lengths($res)

    For $i = 1 To $fields
        $length = DllStructGetData($lenthsStruct, 1, $i)
        $fieldPtr = DllStructGetData($mysqlrow, 1, $i)
        $data = DllStructGetData(DllStructCreate("char[" & $length & "]", $fieldPtr), 1)
        $array[$k - 1][$i - 1] = $data
    Next
Next
_Arrayfindall($array,"Mircea")
If @error Then
    msgbox(0,"error","Wrong username")
Else
    msgbox(0,"am gasit","amgasit ")
endif

I hope you guys can help me.

Thanks in advance

[mircea]

I have fixed the problem i used arrays and the example from @ProgAndy but i have a problem, if i enter blank in the guictrlinput and the mysql query dosen't find any username it give's me a array error. I tried to put the @error in the front so if it see a error to give a mesage but no succes.

Array variable subscript badly formatted.:

Dim $array[$rows][$fields]

Dim $array[^ ERROR

>Exit code: 1 Time: 3.038

expandcollapse popup

_MySQL_InitLibrary()
                If @error Then Exit MsgBox(0, 'Eroare', "Eroare lipsa fisiere .dll")
                $MysqlConn = _MySQL_Init()

                $connected = _MySQL_Real_Connect($MysqlConn,"localhost","root","","emulatoare")
                If $connected = 0 Then
                    $errno = _MySQL_errno($MysqlConn)
                    MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn))
                    If $errno = $CR_UNKNOWN_HOST Then MsgBox(0,"Error:","$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST)
                Endif

            

                $query = "SELECT * FROM accounts where Nume_user='"& Guictrlread($Loginput)&"'"
                _MySQL_Real_Query($MysqlConn, $query)


                $res = _MySQL_Store_Result($MysqlConn)

                $fields = _MySQL_Num_Fields($res)

                $rows = _MySQL_Num_Rows($res)
                                
                Dim $array[$rows][$fields]
                For $k = 1 To $rows
                    $mysqlrow = _MySQL_Fetch_Row($res,$fields)

                    $lenthsStruct = _MySQL_Fetch_Lengths($res)

                    For $i = 1 To $fields
                        $length = DllStructGetData($lenthsStruct, 1, $i)
                        $fieldPtr = DllStructGetData($mysqlrow, 1, $i)
                        $data = DllStructGetData(DllStructCreate("char[" & $length & "]", $fieldPtr), 1)
                        $array[$k - 1][$i - 1] = $data
                    Next
                Next

                
                 
                ;msgbox(0,"asd",$array[0][0])

                If $array[0][0] = Guictrlread($Loginput) Then
                    if $array[0][1] = _StringEncrypt(1,guictrlread($Loginpass),$criptarekey,$passlevel) Then

                                 Msgbox(0,"Succes","Succes you have succesfully log in")
                        else 
                        msgbox(0,"error","Sorry wrong username/password")
                    endif
                Else
                    msgbox(0,"error","Sorry wrong username/password")
                endif


                _MySQL_Free_Result($res)

                _MySQL_Close($MysqlConn)
            
                _MySQL_EndLibrary()

[mircea]

Bump? And i have one more problem when im trying to make a new username, it shows me that it cannot have a duplicate but after that the scripts give's a error and exit