SQL info on data integrity & querrying procedure

[ajoo]

Hi all ! I have been learning MySQL and the gurus have been so patient and helpful. So here are some more queries from a beginner.

 

First is regarding the SQL query procedure from PHP. Every time a query is created and we have to go thru the following:

 

////////////////////////////////////////////////////////////////// query 1 

query = "Select * from club";

$result = mysqli_query($link, $query);

if ($result)

{

  do whatever for a successful query;

}

else 

{

 display or log an error

}

 

///////////////////////////////////////////////////////// query 2

query = "Update club

             SET visit = '2';

$result = mysqli_query($link, $query);

if($result)

... 

 

So my first question is that if we are accessing the same Table , namely clubs, in the above example, then is it not possible to avoid repetitive steps. Can't both & more statements be processed at one go or must each and every query be checked for successful execution? Is there no way to send a group query and have it processed at one go. It would greatly reduce the coding size and therefore chances of errors.

 

The second question is that if more than one person is accessing a database at the same time, trying to read or write into the tables, and there is a time clash, how will mysql handle it? Would it automatically queue the entries or would some of the entries be lost ? If the entries are lost, then how can this be prevented ?

 

Thanks all the gurus for all the help so far and that which would ensue.

Cheers !

 

 

 

 

 

    

[ajoo]

Hi friends, I am still awaiting a reply, comment to these issue. I hope someone will be forthcoming. Thanks

[Barand]

Have a look at stored procedures and triggers

 

http://dev.mysql.com/doc/refman/5.6/en/stored-programs-views.html

[ajoo]

Thanks Guru Barand,  Will do so as suggested. Was expecting a reply from none other !    

[ajoo]

Hi Barand, 

 

I read through that which you suggested and am none the wiser.  Gurus like yourself are able to understand such complex noob stuff but i just felt sleepy !! I'ld be grateful if you can answer my two questions yourself in a simplified language.

 

I'll take an example for the 2nd question. Suppose I have a game being played by 100's of persons simultaneously and their scores are recorded in a database. Now when each person logs into the game he accesses the database for his initial settings ( READ ONLY) like level etc and maintains his connection with the DB ( mysqli_connect). Once he finishes the game he updates his score and quits. If the entire process lasted for 10 minutes, from login to playing the game, updating the score and logging out, would another gamer be not able to update his score to the same database in those 10 minutes. Is it that bad? Couldn't be. I don't think so and certainly certainly hope not. 

 

For my first question, i read through the MySql  chapter 19 as suggested by you, but did not find any practical example of using the procedures/functions using php. Will try and look up on the net still even after i write this mail. 

 

Please help. Thanks loads !!

[Barand]

 

 

would another gamer be not able to update his score to the same database in those 10 minutes

 

Not sure to whom the "his" refers in that question.

 

Player A can update Player A's score and Player B can update Player B's score. You should not be allowing a player to update any score that is not their own.

[ajoo]

Hi Barand, Thanks. I'll try and clarify. OK his refers to the players own. To clarify, I'll take your example of 2 players A and B. Lets say player A logs in first. As mentioned, the game accesses player A's initial values and settings and he plays his game for 10 minutes all the while he's connected to the DB i.e. the program does not use a command like mysqli_close to close the connection with the database for those 10 minutes A is playing the game.

 

Now if player B tries to log into the same game / program/ application after say 2 minutes of player A's login and the program / application tries to access the database to get player Bs settings, would the program be able to do so or would the fact that the database is already opened by player As instance of the game / prog/ application be a hindrance? 

 

Surely the program will update both player As and player Bs scores and add it to the database. No question of the players updating their own or another person's score. The program will update their scores into a common database once they have played their games because it would need those to rank the players.

 

Finally I have also read some articles on database locking and what I have gathered is that just because a database connection is opened by one player, it does not mean that it cannot be accessed by another especially if we define the SQL commands using NOLOCK or ROWLOCK commands if we are sure that it would be just one or 2 rows that would be affected by our SQL commands. And for some commands we don't even have to have a lock and so we can use NOLOCK. However the programmer should be clear of the amount of database entries that his SQL commands are affecting.

 

So I'll be happy if you can confirm what i have construed is correct regarding locks and also look into the players example and answer that as well. Thanks especially Barand and all gurus on this forum. You guys rock !

Edited December 16, 2013 by ajoo

[ajoo]

Hi Barand, 

 

I have another small question. I have the following query : Select Distance, SUM((Score/10)*100)/MAX(ID) as Racetime FROM Race, 

Here the Racetime is an alias. While the value of Racetime calculated is perfect, I am unable to access this value from php using the usual procedure,

$query = "Select Distance, SUM((Score/10)*100)/MAX(ID) as Racetime FROM Race";
$result = mysqli_query($link,$query);
$row=mysqli_fetch_assoc($result);
$dis = $row['Distance'];
$RT = $row['Racetime'];

I have left out some commands for checking the status of the query's output for brevity. So please explain how can a calculated alias field like 'Racetime' be received into PHP. 

Thanks

[kicken]

Now if player B tries to log into the same game / program/ application after say 2 minutes of player A's login and the program / application tries to access the database to get player Bs settings, would the program be able to do so or would the fact that the database is already opened by player As instance of the game / prog/ application be a hindrance?

Yes, player B will be able to load all their settings. Just because a program has a connection open to the database does not mean the database is inaccessible to anyone else. In order to make a table inaccessible to others, you have to acquire a LOCK on the table, which is unnecessary for read-only operations like a SELECT statement. Unless you intentionally code your program to acquire a lock in such an instance, none would be acquired and thus not block anyone else from that table.

 

 

Now, a write statement such as the UPDATE statement to change their scores will acquire a lock on that table (or row, depending on table type) which would prevent anyone else from accessing that table. However, the lock is only active for the duration of the UPDATE statement, which should be just a few milliseconds so at worst if two people tried to update at the same time, one of them would just have to wait for a few milliseconds before their update goes though.

 

Finally I have also read some articles on database locking and what I have gathered is that just because a database connection is opened by one player, it does not mean that it cannot be accessed by another especially if we define the SQL commands using NOLOCK or ROWLOCK commands if we are sure that it would be just one or 2 rows that would be affected by our SQL commands. And for some commands we don't even have to have a lock and so we can use NOLOCK. However the programmer should be clear of the amount of database entries that his SQL commands are affecting.

There are ways to manually control table locking however unless you know exactly what you are doing and why, you should just let the database handle the locking implicitly as it processes your normal SQL statements. Failing to handle locking properly can result in exactly the situation you are concerned about where a user would not be able to access the DB while another person is using it.

 

I have left out some commands for checking the status of the query's output for brevity. So please explain how can a calculated alias field like 'Racetime' be received into PHP.

What you have is the proper way of accessing the column's value. Make sure you have everything spelled properly and the case is correct. An aliased column is accessed the same way as any other column, you just use the alias name as the column's name.

[ajoo]

Thanks Kicken, That gave me a lot of confidence. I'll try the alias again now. I have tried it twice but it did not work but maybe i did make some mistake. Thanks loads. Gurus are great !