I'm using Mysql.
if i decide in the future switching to another database
how can i avoid rewriting everything?
there are too many MYSQL_queries....

Use a function to create and execute the query, so in the future you only need to change the function.

I guess most sql queries should work every where because SQL is a standard query language.
anyway i suggest to use class that is responsible for interacting with database that has all needed function to either get or add or modify or delete data.
like function to get users in an array or so
and a function to add new user giving the new member data
and a function to modify existing user data giving his id or so
and a function to delete a user using his is (username) or given condition

in short when you decide to change database you will just need to rewrite that class for the new database and replace the old file. then every thing should work smoothly

k, so if i use the following function

function runSql($sqlQuery) {
return mysql_query($sqlQuery);
}


do i have to do the same for mysql_fetch_array?
because i'm guessing it won't work with access selected query.
and what about the list function?
isn't it simpler using ODBC?


nayes84, you've mentioned classes and i have a little problem
deciding when to use them.
in this case for example, why putting those functions in a class
if i can just create functions called
getNameById()
getIdByName()
deleteUserById()
like what difference does it make putting those in a class?

Well I'll try to give you an idea about the concept of using a class to handle database queries.
I guess you know that class is a definition of an object which has properties and members (functions and variables)
for a class to handle connection to database
you can think of it like a tool you can use to exchange data with your database
here is a simple prototype example:

PHP Code:

class db_con
{
     //you write here variables needed between functions like sql query string and connection handle
     var mysql_handle,query;

     //this is the constructor function, will handle establishing a connection to database, and initialize other variables if necessary
     function db_con(username,password,host)
     {
          //connect using given data and assign connection handle to mysql_query

     }

     //example function for handling adding a new member
     function new_member(firstname,lastname,email,etc...)
     {
           //this function generate an sql query using given data
           //optionally you can run this query directly or you can buffer it in query variable, and execute query using another function (this way is used for running big number of queries together to decrease load on database)

        //return indicator indicate if function failed or succeeded ex(success:0, failure:-1)
     }

     function modify_member(....)
     {
     }

     function delete_member(....)
     {
     }

     function search_member(....)
     {
     }

     //you should make functions to access or update any record in your database, never send a sql query as a parameter to function just to execute it it will have no meaning if you do that. since if you moved to another database you may face incompatibility issues with some sql expressions
     //inside the class you can make helper functions to help forming up the query and to help validating values etc... these functions should be private (inaccessible from outside the class)
} 


other code I want to write. assuming you already made the class. this is a simple example code to show how are you going to initiate the class and start using it

PHP Code:

include "db_class.php"//file having the db class

$mycon=new db_con(username,password,host);

//after that you can add,modify,delete data using the functions you implemented
$res=$mycon->new_member(firstname,lastname,email,...);
//check on returned value
if($res)
     //success
else
     //failed 


nayes84, you've mentioned classes and i have a little problem
deciding when to use them.
in this case for example, why putting those functions in a class
if i can just create functions called
getNameById()
getIdByName()
deleteUserById()
like what difference does it make putting those in a class?

if you just moved those functions into a class it will not make a difference. in short you shouldn't have direct access to mysql queries or tables structures from outside the class. instead like I said before you use your own created function with your defined parameters (firstname,lastname,email,etc...)
and the class mission is to construct sql and execute it then return appropriate result (every thing is in your defined form not in mysql form)

ps. I know this is quite hard for you but when you start doing it your self you are going to understand it over time. anyway you are free to ask any thing you don't understand

Sorry for the late reply, haven't been in front of the computer for a couple of days (damm army)..

I do use classes and i understand how they work but when it comes to queries, i use classes just for selecting things i use pretty often because when I'm thinking about it, it seems like
in a big project with a huge database
there are so many different things to update and insert
if i create a function for each scenario, will it not make things just more
complicated? i'll get to a point where it's hard to keep trace of all those
functions.
or maybe i'm missing something here

i'll get to a point where it's hard to keep trace of all those
functions.

I personally use this principle more like an ORM does.
For example, I create a class "users", and I populate it with methods "saveUser", "updateUser", "dropUser" and so on...

so if i keep coding in the following way, is it alright?

PHP Code:

<?php
class fighters{
    function select_all_fighters() {   
        global $fighters_list_table;
        $query = mysql_query("select * from $fighters_list_table") or die("Error with select_all_fighters");
        return $query;
    }
    
}

?>

and on a different page

PHP Code:

$my_fighters = new fighters();

if ($logged==1){
    $selected_fighters = $my_fighters->select_all_fighters();
    $count = mysql_num_rows($selected_fighters);
    
    
    if (empty($count)) {

..... 


Looks good to me.
I'd do it a bit like that.

Originally Posted by asmon

so if i keep coding in the following way, is it alright?

PHP Code:

<?php
class fighters{
    function select_all_fighters() {   
        global $fighters_list_table;
        $query = mysql_query("select * from $fighters_list_table") or die("Error with select_all_fighters");
        return $query;
    }
    
}

?>

and on a different page

PHP Code:

$my_fighters = new fighters();

if ($logged==1){
    $selected_fighters = $my_fighters->select_all_fighters();
    $count = mysql_num_rows($selected_fighters);
    
    
    if (empty($count)) {

..... 


this is a way to go. and you an do it many ways depending on what you think. the important thing is to keep your most of your code isolated from inner mysql functions and sql queries. and use only one file (one class) to handle every thing for you. that way when you want to switch to different database you will need only to replace that file (class)

sorry for all those questions, i just want to make sure i'm doing things
right before i keep going..

here's how those 2 pages look now.
classes:

PHP Code:

<?php
class db
{   
    function check_query($query, $error = "query error") {    
        return $query ? true : die("{$error}");
    }
    
    function sql_query($sqlQuery) {
        return mysql_query($sqlQuery);
        
    }

    function sql_fetch_array($sql_fetch_array) {
        return mysql_fetch_array($sql_fetch_array);
    }
    
    function db_connection() {
        if (!$GLOBALS['DB'] = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD)) {
            die('Error: Unable to connect to database server.');
        }
        if (!mysql_select_db(DB_SCHEMA, $GLOBALS['DB'])){
            mysql_close($GLOBALS['DB']);
            die('Error: Unable to select database schema.');
        }
        return true;
    }
}

class city extends db {
    
    
    var $city_name;
    var $min_pot;
    var $max_pot;
    var $overall_pot;
    

    
    function create_fighter() {    
        global $fighters_list_table;
        global $user_id;    
        
        do {
            $fighter_name =    "alex";
            $fighter_surname = "zhivotovsky";
            $punch_pot =       rand($this->min_pot, $this->max_pot);
            $kick_pot =        rand($this->min_pot, $this->max_pot);
            $bunt_pot =        rand($this->min_pot, $this->max_pot);
            $health_pot =      rand($this->min_pot, $this->max_pot);
            $speed_pot =       rand($this->min_pot, $this->max_pot);
            $defense_pot =     rand($this->min_pot, $this->max_pot);
            $allpot = ($punch_pot + $kick_pot + $bunt_pot + $health_pot + $speed_pot + $defense_pot);
        }
        while($allpot != $this->overall_pot);
        

        $insert_query = $this->sql_query("insert into $fighters_list_table(
        userid, name, surname, punch_pot, kick_pot, bunt_pot, health_pot, speed_pot, defense_pot) 
        values ('$user_id', '$fighter_name', '$fighter_surname', '$punch_pot', '$kick_pot', '$bunt_pot', '$health_pot', '$speed_pot', '$defense_pot')");
        $this->check_query($insert_query, "error creating a fighter");
        
        echo "you've got alex as your first fighter";
        return true;
    }
}

class fighters extends db {
    function select_all_fighters() {    
        
        global $fighters_list_table;
        $query = $this->sql_query("select * from $fighters_list_table");
        $this->check_query($query, "error selecting fighters");
        
        return $query;
    }
    
}


?>

index:

PHP Code:

<?php
require('includes/Top.php');
if (isset($_POST['createFighter'])){
    
    
    $first_fighter = new city();  
    $first_fighter->min_pot =          1;
    $first_fighter->max_pot =          5;
    $first_fighter->overall_pot =      18;
    $first_fighter->create_fighter();
    
}


$my_fighters = new fighters();

if ($logged==1){
    $selected_fighters = $my_fighters->select_all_fighters();
    $count = mysql_num_rows($selected_fighters);
    
    
    if (empty($count)) {
        echo "You have no fighters yet, please click the button below in order to get your first Fighter!";
        echo "<form method='post'>";
        echo "<input type='submit' name='createFighter' value='Create Fighter'>";
        echo "</form>";
    }    
}
else {
    echo "please log in";
}
require('includes/Bottom.php');



?>

i would like to hear suggestions if you have any.
if it's still good, i will keep coding this way.

I also have a small question about htmlspecialchars and htmlentities.
i know the difference between those 2 but
in what cases would i want to use htmlspecialchars instead of htmlentities?
i use htmlentities on all post and get data i retrieve just in case i will echo that at some point...
and theoretically, if i use htmlentities(with ENT_QUOTES) instead of mysql_real_escape_string it will also make it safe using that string in a query, isn't it? (inserting something for example).

same thing about strip_tags. when would i want to strip the whole tag instead of just making it safe using htmlentities\htmlspecialchars?

one last thing about classes.
i am wondering if i should use static.
i've been reading that i shouldn't mix static methods with instance methods.
therefore i shouldn't make "check_query" static and call it from city->create_fighter.
so i should just create an object when ever i want to use check_query in another file even tho it's always the same?

you had made it almost correct
there is only one thing better to correct in the function select_all_fighters
you return mysql result which is a mysql thing that also made you use

mysql_num_rows($selected_fighters);

which is mysql dependent.
I suggest to fetch the data and put them into associate array then return that array instead of mysql result
this way you will not need to use mysql_num_rows outside the class

in what cases would i want to use htmlspecialchars instead of htmlentities?

htmlspecialchars converts only characters that needs to be converted or your string will be messed up if you used it as a text data in some html tags.
htmlentities converts all defined in XHTML as special characters including the ones that htmlspecialchars converts
so in short using htmlentities all the time should be safe

and theoretically, if i use htmlentities(with ENT_QUOTES) instead of mysql_real_escape_string it will also make it safe using that string in a query, isn't it? (inserting something for example).

yes it will work but remember in that case you will need to convert the code back using html_entity_decode when you need to modify it or process it (you don't need that if you are just going to display it inside a html tag like textbox or whatever)

even you can use addslashes($str) to make your string safe for use in mysql query
however, you don't know how those functions would change in future releases. even if they work similary in the current release.
so in short it is always preferable to use the function specified for use with what you are doing. mysql_escape_real_string is specifically made for escaping string for use in mysql queries so whatever it changed in the future it must be fully compatible with mysql queries all the time. simply because it is made specifically for it.

i've been reading that i shouldn't mix static methods with instance methods.
therefore i shouldn't make "check_query" static and call it from city->create_fighter.
so i should just create an object when ever i want to use check_query in another file even tho it's always the same?

you can write both static methods alongside with instance methods. there is nothing bad with that!
I guess the thing you read just meant you shouldn't call instance method from a static method (reverse is ok) because this well work when you make object. but if you called it without initiation it will not work correctly.
and in my opinion check_query is better to be static. since it doesn't rely on any data in the class nor it call non-static methods.

thank you for your time
and effort understanding my English.
everything is much clearer now.
thanks again...

Originally Posted by asmon

thank you for your time
and effort understanding my English.
everything is much clearer now.
thanks again...

You are welcome

Stick to MySQL !!

Its a pretty cool database for small to medium applications. And when your application grows bigger than that, it is advisable to re-write everything(including SQL). At that point, saving programming manhours is likely to be less important than writing optimized code that saves you on hardware and improves performance.

A commonly used technique is to keep UI separate. UI <-> XML <-> DB Interactive code <-> DB Server. If your application is designed like the above, you can **** DB by only replacing the DB interactive code. I'd still recommend, that you re-write the queries, based on the usage of the website at "that later stage".

Originally Posted by amitsoni

Stick to MySQL !!

Its a pretty cool database for small to medium applications. And when your application grows bigger than that, it is advisable to re-write

completely disagree. mysql is able to handle tens of millions or records with no problem at all. I had my self worked with a mysql db having over 60 millions of records with 6 GB in size and had no problem at all

over 60 millions of records with 6 GB in size and had no problem at all

I seriously hope it's able to cope with numbers that low !
It would be a joke of a product if it would not.