[Tutorial] Php scripting and SQL basics

[xXxExosoulerxXx]

Last time many people asking for PHP scripts so I made this small tutorial how to make own scripts for common tasks. I hope it will be useful. Notice: You will not find here working examples! You have to do it by urself its easy!

 

Basics

 

PHP

 

If wiki said so it have to be true. But for l2 developers it's simple, fast, easy to learn and flexible tool for display and manage data of ur server.

There are many tutorial trough the web so I will not copy that. All u have to know is on www.php.net.

 

SQL

SQL is query language for handling DB's. L2off use MSSQL but basic grama and structure is similar for all sql db's like MySql etc. Use google to find out basics.

 

What u need to use it

You need web server ( like apache ) and php. Here u got easy tutorial how to set it up on windows. You can also download pack like Xamp if feel that u can not handle doing it by urself...

 

Connecting to db

First u have to enable mssql extension in php.ini ( if ur using windows ).

Then we use mssql_connect function to  connect to db, like:

<?php
$db = @mssql_connect("host","user","password") or die("Unable to connect to server");
?>

We save our connection into variable. If connection faild script will end with msg.

 

Selecting db

 

Now we have to select db what we want working on like:

<?
mssql_select_db('[some db name]', $db);
?>

 

Queries

 

Queries is like asking db for something what we want. Result is not a table.

<?php
$result = mssql_query( "SELECT level FROM characters WHERE name = 'Plum'");
?>

 

Fetch result

 

You can use for that one of the msssql_fetch_* functions like mssql_fetch_array.

<?php
while ( $row = mysql_fetch_array($result) ) {
print_r $row; // print each row as small array
echo $row['level']; // show column level in current row
}
?>

 

Queries

Notice: I'm not using here orginal l2off db scheme.

SELECT what_rows FROM tables WHERE conditions;

 

So if we want for example get levels, nick form table chars where all lvls are grater then 50 and nick starts with "s" we do smth like this:

SELECT level, nick FROM chars WHERE level > 50 AND nick LIKE "s%";

 

If we want also to select how much adena they have ( form let say table inventory ) we do it like that:

SELECT chars.level, chars.nick, invetory.item_count AS adena_count FROM chars, inventory WHERE chars.level > 10 AND chars.nick LIKE "s%" AND inventory.item_name = 'adena' AND inventory.item_owner = chars.nick;

 

Now lets order them from lower lvl to higher

{...} ORDER BY level;

If we want to change order we add DESC

 

To select only rows from 10 to 50 results add to query this:

{...} LIMIT 10, 50

 

Easy? Yeah I told u ;)

Now lets check how many online players we got. 'online' is bool row in table users and its shows users current status.

SELECT COUNT(*) as number_of_online_chars FROM users WHERE online  = 'TRUE';

 

Etc. etc. etc. google is ur best friend so for more examples search there.

 

Closeing connection

mssql_close($db);

 

Few words about how to display results

 

There are few functions to display smth in php:

echo, fprintf, print, printf and vprintf

Most basic and usefull for u will be echo.

How to insert variables into string?

<?php
echo "smth $users_online"; // Will display 'smth 3503' for example
echo 'smth $users_online'; // This will show 'smth $users_online'
echo $result['nick'];
echo "today we got ".$result['online']." players online"; // It will work
echo "today we got {$result['online']} players online"; // It will also work
?>

But the best way is to put everything into table and display it using templates engine. If ur script is small u dont have to think about it.

 

What next?

You can use to mange ur db connection PDO, Pear or another DAO.

There are many articles trought the web about php.  Take a look on object oriented programing in php ( I suggest to find info about 5.2 version ). Check also programming patterns.

If u want to make ur code cleaner think about using templates engine like Smarty or Open Power Templates.

Good luck ;]

 

Tools

Eclipe php ide ( free; work on windows, linux and ever platform with java )

Zend Studio ( only windows and not free )

 

 

Important: If u think about adding control panel on ur website where user can choose smth and he send to script in GET/POST some data  that u use forward in query READ ABOUT SQL INJECTION etc. It can save ur db. Even if u think ur script is secure its NOT. Its not good idea at all to do smth like that if ur beginer. Belive me.

 

 

I will try to update this small tutoriall with more links and examples of using php for cached.

If u have any question ask here or send me pm on forum/msn. I will try to help u :)

 

Update1: About output functions and tools

 

source:postpacific.com

[fdLP.]

Very Nice Share Exosouler ! Keep up!

[Stealth]

ThX I need to learn programming php with databases for real :D Thx again

[Th3oneGandalF]

Nice Tutorial m8

thnx

it has some basic knowledge that

will help much ppl ;D

[Zeronimo]

thank you i though sql was nerd talk but it seems to be a bit easier now :D well i think i will need some help but nvm

 

good  job keep on ;)

[cyberghoser1]

this is good for newbies to learn some php and sql, that way they can do much work by themeselves

[Feelmyspells]

This is very good guide...Nice work..

[bourbouli8ras]

someone give him +1,very good tutorial for php.gracias amigo

[lion2486]

good tutorial!

[Stefoulis15]

nice guide exosouler...

 

 

thanks

[TheRuleZzZ]

nice guide exosouler keep up my friend ;)

[XaioXaio]

nice topic all guides/tutorials are usefull , couse eaven if u are good at it u can get stuck at 1 point and u can cheak it :)

[rekuraab]

Thanks exosouler thats a good guide.

[arkang31]

thx i need

[Delete]

nice guide thx.