About BlitzPlus and MySQL
Blitz3D Forums/Blitz3D Beginners Area/About BlitzPlus and MySQL
| ||
| I posted something similar in the past didn't get an answer I could understand, Okay the situation is, theres the CLIENT game, theres a Mysql database on a server which is not mine. the database is located on www.lexdark.com ------------------------------- database: lexdark_game host: Im guessing that in this case its not "localhost" user: lexdark_gameuser pass: test ------------------------------- table: gamewin fields: game_name player_name player_score player_kills ------------------------------- What I need is a example on how to connect to the database and write/read it. A little example for dummy, please. Think your smart enough to master this? Show me ur wisedom ;) Please, help I really need this to work, I thought this would be easy but it is not :( |
| ||
| Hi Kaisuo ! are you familiar with php, mysql and database ? I've not tested the following code. I've just adapted quickly it from my project. Blitzcode : Function Write_score (Player_name$, Player_score, Player_kills) www = OpenTCPStream( "www.lexdark.com",80 ) If www Then Requete$ = "page.php?player_name=" + Player_name$ + "&player_score=" + player_score Requete$ = Requete$ + "&player_kills=" + Player_kills + "&Op=Add" WriteLine www,"GET page.php?" + Requete$ + " HTTP/1.0" WriteLine www,"Host: www.lexdark.com" WriteLine www,"User-Agent: BlitzBrowser" WriteLine www,"Accept: */*" WriteLine www,"" End If CloseTCPStream www End Function Function Get_scores$() www=OpenTCPStream( "www.lexdark.com",80 ) Result$ = "" If www Then Requete$ = "page.php?Op=List" WriteLine www,"GET page.php?" + Requete$ + " HTTP/1.0" WriteLine www,"Host: www.lexdark.com" WriteLine www,"User-Agent: BlitzBrowser" WriteLine www,"Accept: */*" WriteLine www,"" While Not Eof(www) val$ = ReadLine(www) DebugLog "." + val$ + "." If Instr(val$, "score>") <> 0 Then Result$ = Result$ + Right$(val$, Len(val$) - 6) + "|" End If Wend End If CloseTCPStream www Return Result$ End Function page.php : php code example :
<?php
Function Connect_bdd()
{
Global $Link;
$BaseName = "lexdark_game";
$BasePassw = "test";
$Link = mysql_connect("localhost",$BaseName,$BasePassw) Or die ("can't open the database");
mysql_select_db($BaseName);
}
//------------------
Function Deconnect_bdd()
{
Global $Link;
mysql_close ($Link);
}
//-------------------
Function Add_player_score ($player_name, $player_score, $player_kills)
{
$player_name = strToLower ($player_name);
Connect_bdd();
$Requete = "Select * from gamewin ";
$Requete .= "where player_name = '$player_name'";
$Result = mysql_query($Requete) Or die ($Requete . "<br>" . mysql_error());
If (mysql_num_rows($Resultat) != 0)
{
$Requete = "update gamewin ";
$Requete .= "set player_score=$player_score, player_kill$=$player_kills ";
$Requete .= "where player_name = '$player_name'";
$Resultat = mysql_query($Requete) Or die ($Requete . "<br>" . mysql_error());
}
Else
{
$Requete = "insert into gamewin ";
$Requete .= "(player_name, player_score, player_kills ";
$Requete .= "where player_name = '$player_name'";
$Result = mysql_query($Requete) Or die ($Requete . "<br>" . mysql_error());
}
Deconnect_bdd();
}
//-------------------
Function List_scores()
{
Connect_bdd();
$Requete = "Select player_name, player_score, player_kills from gamewin order by player_score desc";
$Result = mysql_query($Requete) Or die ($Requete . "<br>" . mysql_error());
Deconnect_bdd();
If (mysql_num_rows($Result) != 0)
{
$strCache = "";
$Count = 0;
While ($Ligne=mysql_fetch_array($Result))
{
$Count++;
$strCache .= "score>";
$strCache .= "$Count" . ". ";
$strCache .= $Ligne["player_name"];
$strCache .= $Ligne["player_score"];
$strCache .= $Ligne["player_kills"];
$strCache .= "\n";
}
}
Else
{
$strCache .= "score>no scores !!!\n";
}
Print ($strCache);
}
//------------------- main program
If (isset($Op))
{
switch($Op)
{
Case "Add":
Add_player_score (player_name, player_score, player_kills);
break;
Case "List":
List_scores();
break;
}
}
|
| ||
| page.php should be on the server right? not on the client's machine? |
| ||
| Yes, php Pages are always server-side stored. Also: they include passwords, but the client-browsers cannot read the php, they can only execute it, where their browser acts as Output, like the Screen with "Print". As I said before, the easiest way, that let's you develop php very quickly, is to install php and MySQL on your local machine. If your Script once works you can upload it to a server that supports PHP and MySQL as well. I mean, installing these Things is only easy, when you use the Package I mentioned. To install the 3 Components (apache, php, mysql) manually is a bit harder. |
| ||
| I posted something similar in the past didn't get an answer I could understand, Okay the situation is, theres the CLIENT game, theres a Mysql database on a server which is not mine. the database is located on www.lexdark.com ------------------------------- database: lexdark_game host: Im guessing that in this case its not "localhost" user: lexdark_gameuser pass: test ------------------------------- table: gamewin fields: game_name player_name player_score player_kills ------------------------------- What I need is a example on how to connect to the database and write/read it. A little example for dummy, please. Think your smart enough to master this? Show me ur wisedom ;) Please, help I really need this to work, I thought this would be easy but it is not :( As I already told you in an other Thread that you didn't answer you can use mysql.exe together with a batchfile in batchmode. I explained and tested this. All you need to do is to read a Beginner Tutorial about the basic mysql Commands, and believe me, it is very simple. They cook with water like everybody else. There are some Commands like CREATE, SELECT etc. you don't need more than 5 Commands and their Parameters to handle everything. Just read the other thread about mysql that you started, I gave you a working example there. |
| ||
| My computer cant run php nor mysql for some reason. jfk to me your examples are a bit hard to understand, I know basic MySQL commands and php, I just don't know how to run stuff in Blitz |
| ||
| Just follow the instructions I gave you here: http://www.blitzbasic.com/Community/posts.php?topic=26421 You need mysql.exe, this is a 270kb win32 exe, get it from the current mysql distribution from www.mysql.com EDIT: the original MYSQL Release is about 20 Megs, so you better DL the EasyPHP Package mentioned in the link above that contains mysql as well, it's only about 10 Megs. |
| ||
| I downloaded an installed it, I tryed to run it but as espected apach part doesnt work[easyphp]. What do I do exactly, this is chinese to me. Didnt quite get the other post. |
| ||
| How do you mean, it doesn't work? What doesn't work? First you have to start easyphp from the start menu, this will activate the server etc. then open a browser and type 127.0.0.1 as Adress. This will show you the Startpage (index.php) that is located in the "www" Folder of the Myphp Apps Directories. php pages must be inside that Folder to be executed. ----------- BTW if you cannot run easyphp - just localize mysql.exe. You probably cannot experiment with a local database for some reason, but you still can use it to access an external database. I'll help you step by step to do that first run mysql.exe and try to connect to your database. for this you can make a msdos-batchfile (don't confuse this with the mysql-Batchfile), let's name it "runmysql.bat" it's a textfile containing this: mysql --user=NAME --host=URL --password=PASS --database=DATABASENAME where you should use your Data for the Uppercase things like USER, PASS aso. (needless to say, I hope). Then doubleclick this Batchfile. If everything is fine mysql will then tell you that you're connected and will accept Commands. Type exit to end this. IF you cannot connect this way you have to find the reason why it didn't work. Correct Password aso.? Maybe a Firewall that silently blocks it? Try to make this work! Next step would be to write a MySQL-Batchfile. |
| ||
| Okay I can't run apach, dunno why. However I tryed what you said but as soon as I open the file runmysql.bat it closes too fast to see whats writen :( Edited, ive pressed print screen before it closes >.> pain ... ERROR: unknow host http://www.lexdark.com >.> how do you make it wait i cant see whats writen when im testing >.> |
| ||
| open the MSDOS Console. then chanche to the directory where mysql.exe resides. the msdos command to move to a directory would be: cd c:\php\mysql\ (then hit enter) or whereever the file is. Once there type the batchname and hit enter BTW probably you have to omit the "www."part in the host name. Also make shure the mysql server is on that host, cause the mysql server does not have to be necessarily on the same server as the php Script that usually accesses the database. Do you have access to the php script? I mean, do you have FTP access to that server? |
| ||
| yes I do have a FTP access to the server, however how do you put a thing that the console stays open that it doesnt close ride away cause I cant see what its writing for error???? |
| ||
| as I said, use the msdos console: use the Start App Tool in the start Menu. enter "command", now you have a msdos console, that will remain there until you enter "exit" or use the close-button. When you have ftp access to that server then you can check the real hostname of the database. the php-scripts are referencing it somewhere, usually in an includefile named inc/const.php. There you can see the correct username, password and hostname (assuming that there are already some php-scripts dealing with the database). |
| ||
| I have a controle panel to create databases(max:5) as its paid business hosting... gonna edit when I try the stuff u just told me.. EDIT Okay well console works fine but.. when I run the .bat ... i use lexdark.com as host... i tryed my ftp address but hmmm access denied. lexdark.com access denied aswell... do I need to put my ftp host add and username and password for the ftp... because my Account's user name and password is different from the username and password of the database. Dunno what to do |
| ||
| well, ftp was only to check the database hostname for the mysql access you only need mysql user, pass and host. Us it as i said, but without "www.". Does this work? Does mysql.exe connect? |
| ||
| mysql --user=lexdark_gameuser --host=lexdark.com --password=test --database=lexdark_game I run the .bat but .. ERROR: Access Denied for user: 'lexdark_gameuser@...' <Using Password: Yes> :( |
| ||
| ok, try to omit the password: mysql --user=lexdark_gameuser --host=lexdark.com --database=lexdark_game it will then ask you one step later for the password. Make shure to use the right password (lower/uppercase!). (at least you were able to knock on the door :) ) |
| ||
| Yup knock Knock :P Gives me the save error but "<Using Passowrd: No>" |
| ||
| Are you using the correct Username? |
| ||
| the database has been deleted dunno why... gonna recreate and test and ill come back on what happens... |
| ||
| the database has been deleted dunno why... gonna recreate and test and ill come back on what happens... |
| ||
| hrmm... still access denied >.> u should try it, host: lexdark.com username: lexdark_gameuser password: test db: lexdark_game edit more info: lexdark_game Users in lexdark_game lexdark_gameuser Connection Strings Perl $dbh = DBI->connect("DBI:mysql:lexdark_game:localhost","lexdark_gameuser","<PASSWORD HERE>"); PHP $dbh=mysql_connect ("localhost", "lexdark_gameuser", "<PASSWORD HERE>") or die ('I cannot connect to the database because: ' . mysql_error()); mysql_select_db ("lexdark_game"); ---- something on my page dunno if its relavent ---------------------- edited: more info > Access Hosts: localhost Host (% wildcard is allowed): TEXT FIELD Add Host (BUTTON) ------- Hopefully we'll get this working |
| ||
| Nice to see this quety - and well done to JFK for being so informative and so patient. Can't wait to see the result. |
| ||
Well, here it works, I just connected. use:mysql --host=lexdark.com --database=lexdark_game --user=lexdark_gameuser --password=test use copy/paste tell me it it works. Are you using Win XP? And if yes, do you use it's internal Firewall? |
| ||
| BTW: if you post the password here, maybe some fool picked it up and tried to sabotage you, maybe that was the reason why the table was deleted? Use an other Password! |
| ||
| I FK CONNECTED OH MY GOD! WOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT |
| ||
| However just checked my Business technical help thing they said: Dear Alex, Thank you for using the Online Support Agent at Global Internet Solutions. We have enabled remote access to the database. The proof is given below ================== mysql -u lexdark_gameuser -h 216.240.136.190 -p lexdark_game Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 90903 to server version: 3.23.56 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> ================== Please check it from your end. If you have any further questions please do not hesitate to reply to this ticket. Sincerely, Technical Support Department I however used: mysql --user=lexdark_gameuser --host=lexdark.com --password=**** --database=lexdark_game which worked! |
| ||
ok, now make a *.bat file that does the following:mysql --user=lexdark_gameuser --host=lexdark.com --password=**** --database=lexdark_game <batch.txt Now you can feed the mysql client with the "batch.txt" (you have to create) textfile that must contain the mysql query or queries. Best would be to try the following query first: SELECT game_name, player_name, player_score, player_kills FROM gamewin; Or, if it didn't work for some reason, write it on one line: SELECT game_name, player_name, player_score, player_kills FROM gamewin; This should print the Enties of the Tables Variables to your screen. Since it runs in batchmode, it will disconnect automaticly after proceeding. Tell me if this works. |
| ||
| doesnt seem to work.... it doesnt give an error but it doesnt seem to read the txt file :| edited: I even tryed to type it manually when im connected.. didnt work... tryed to add it to the .bat file... didnt work either.. |
| ||
| Hard to say what's wrong. Check your Email! |
| ||
| Email Checked. |
| ||
| Ok, the reason why there wasnt any output was because the database was empty! I added a test entry, now it works. to add an entry use the following Query:
INSERT INTO gamewin
(game_name,player_name,player_score,player_kills)
VALUES
('Da supadupa Game',
'Superhero',
'1234',
'51');
Use the same Order for the Parameters like you used for the variable names. Now all you need is a description af the basic mysql commands, I suggest to use yahoo with "mysql beginner tutorial". I learned all required commands in 3 hours. Ok I had a good tutorial. Unfortunately it is a german tutorial, so I am afraid it isn't useful for you. But I guess there are lots of other good Tuts out there. |
| ||
| Ok the next step is now to run it from Blitz. simply use: Execfile "command /c mysql ..." where ... are the parameters like before. Now this isn't very useful since it will simply output the data to the msdos console that will disappear quickly. Now you need to redirect the output to a textfile. The easiest way is to use the msdos redirection feature that goes like this: simply add the string ">answer.txt" at the end of the line, so it will store the answer in the file "answer.txt". You can then use Blitzes File commands to read and parse the File line by line. So in blitz your call is (a Blitz Command!): ExecFile "command /c mysql --user=lexdark_gameuser --host=lexdark.com --password=**** --database=lexdark_game <batch.txt >answer.txt" This means, whatever you want to do, you just write the batch.txt using Blitzes Writefile Commands, then execute the mysql Path using ExecFile, then read in the answer.txt. This will give you total control over the database. There is only a little drawback now, it's the msdos box that will popup for some millisecs, but even this can be hidden if it needs to be like that. |
| ||
| I know English, French and a bit of japanese but thats about it :| However, how can I used it in Blitz thats the part I don't get? :( |
| ||
| Seems like you limited the length of the game name to 10 Chars? BTW the Values in the Output are delimited by Tabs (wasn't that Ascii Code 8? Well, use Getkey to ckeck the Tabs ASC Code in Blitz or any ASCII Table). Ok, here are some standart queries: To output a whole table sorted by index you would use this:
SELECT
game_name,
player_name,
player_kills,
player_score
FROM
gamewin
ORDER BY
myindex DESC;
where myindex is a integer Field named "myindex"you added to the table and activated AUTOINCREMET (!). This Field will contain the Creation Counter, so if an Entry is the fifth addition to the Table, this number will be "5" automaticly. Using ORDER BY myindex DESC will result in a Output that is sorted by "myindex" You can also request for a special Entry, eg: use a players name to get his Data, for this you can use the WHERE switch: SELECT game_name, player_name, player_score, player_kills FROM gamewin WHERE player_name='jfk'; So this would only display the data of player_name "jfk" |
| ||
| Oh, sorry, I made a mistake, go back to "so this would be the blitz call", I just correct it... Make shure to read eversthing I said here. Most of it is important. Ok, here are some more basic MySQL Queries:
DELETE FROM
gamewin
WHERE
player_name='jfk';
This would delete all entries where the player nick was "jfk". Of course you can also use other criteria after WHERE. The next Query is used to edit something in a Table:
UPDATE
gamewin
SET
player_score = '0',
player_kills = '0'
WHERE
player_name = 'jfk';
This would set my score and frags to Zero which would be pretty mean! ;) Make shure to use the correct Commas and Semicolons. |
| ||
| Dont you need to use something like include 'runmysql.bat" in blitz if you wana be able to use it ? I know basic MySQl commands(just cant remember them sometimes) You sure I can use thoses commands in a .bb file? |
| ||
The Blitz Command is:ExecFile "command /c mysql --user=lexdark_gameuser --host=lexdark.com --password=**** --database=lexdark_game <batch.txt >answer.txt" the file "batch.txt" must contain the MYSQL Queries. I tried it and it works. Of course the mysql.exe and all batch Files must be in the same Directory like your .bb Prog. You don't need the .bat File anymore, this was only thought to test the mysql COnnection. Try it with that Select thing, I added a Set with player_name "jfk". |
| ||
| could you tell me what I did wrong says End-Of-Line.. or something..? However do you really need a .txt or can you do it within blitz so you can make fontions like: get_score(player_name) ???? |
| ||
| End of line? Maybe you did something wrng with the batch.txt. Make shure there is a ";" at the end of the command. As far as I see there is no easy way for something like "get_score", although you could write a Function that is doing the same. There might be a way to use a diffrent Device for the output of the commandine interpreter, but this is a bit heavy imho. IT is possible to do it with the Textfile, although you need some lines of code to use the Textfiles, but it's simple. Then there is the way using a PHP-Script on the Serverside, as some People mentioned. You can receive those Dates on the TCP Ports without a Textfile, but it requires to use PHP on the Server and doesn't allow direct access to the Database. Well, now you got all the Infos I can give you. Good luck! |
| ||
sorry forgot to past my code :(AppTitle "Blitz MySQL" Graphics 800,600,16,2 SetBuffer BackBuffer() If ExecFile "command /c mysql --user=lexdark_gameuser --host=lexdark.com --password=**** --database=lexdark_game <batch.txt >answer.txt" Then Text 0,0, "This Works" Else Text 0,0, "Loser!" EndIf Flip While Not KeyHit(1) Wend |
| ||
I'd suggest to do something like this:a$="answer.txt" if filetype(a$)=1 then deletefile(a$) ExecFile "command /c mysql --user=lexdark_gameuser --host=lexdark.com --password=**** --database=lexdark_game <batch.txt >answer.txt" while filetype(a$)<>1 delay 1 if keydown(1) then end wend delay 100 r=readfile(a$) while eof(r)=0 print readline(r) wend closefile r Not shure if ExecFile will return a Value, I never use it like that, maybe that caused the Error. However, here you can see how you could read in the answer.txt. Important is you have to wait until the File exists, because Blitz would continue right after starting mysql.exe and therefore the answer would still be pending. I'd suggest to wait up to maybe 10 Seconds and then assume that thre isn't an answer. Well, if this is executed in a Game, you probably need an other solution anyway. Maybe something where ExecFile and the Reading of the answer.txt is done independently from eachother, whenever it's possible and/or required. |
| ||
| I hope this whole thing was useful after all. It's always a good Idea to say "thanks" when somebody is trying to help. |
| ||
| lol sorry, thanks I guess you didnt receive my email oh well lol. Im working on something else right now, since my skill with this are limited. |
| ||
| That's ok. |
   |