loops and inserting to mysql

[Dougie]

Hi, I'm having troubles with a script here. If anyone could help me out that would be greatly appreciated. Let me explain my problem.

I'm making a combat tourny for a php game that I have just been coding recently and I am trying to make a random "schedule" of the fights and store it to a mysql database. Now I want there to be a max of 4 fights per day, no fighter fighting on the same day and no fights scheduled at the same time during the day.

I'll just show my snippet of code and then explain the problem I am having.

Code: [Select]

$res77 = mysql_query("SELECT * FROM fighters WHERE tourny = '$tourny[id]'");
while($home=mysql_fetch_array($res77))
{
$res88 = mysql_query("SELECT * FROM fighters WHERE tourny = '$tourny[id]' AND id != '$home[id]'");
while($away=mysql_fetch_array($res88))
{
do
{
srand ((double) microtime( )*1000000);
$fight_day = rand(1,$t_length);
srand ((double) microtime( )*1000000);
$fight_time = rand(1,4);

$resu = mysql_query("SELECT * FROM fights WHERE home_id='$home[id]' AND fight_day='$fight_day' AND fight_time='$fight_time'");
$numba = mysql_num_rows($resu);

$resu1 = mysql_query("SELECT * FROM fights WHERE away_id='$home[id]' AND fight_day='$fight_day' AND fight_time='$fight_time'");
$numba1 = mysql_num_rows($resu1);

$resu2 = mysql_query("SELECT * FROM fights WHERE home_id='$away[id]' AND fight_day='$fight_day' AND fight_time='$fight_time'");
$numba2 = mysql_num_rows($resu2);

$resu3 = mysql_query("SELECT * FROM fights WHERE away_id='$away[id]' AND fight_day='$fight_day' AND game_time='$fight_time'");
$numba3 = mysql_num_rows($resu3);

$resu4 = mysql_query("SELECT * FROM fights WHERE fight_day='$fight_day' AND t_id='$tourny[id]'");
$numba4 = mysql_num_rows($resu4);

}
while($numba >= "1" && $numba1 >= "1" && $numba2 >= "1" && $numba3 >= "1" && $numba4 >= "4");

mysql_query("insert into fights (id, tourny_id, fight_day, home_id, away_id, fight_time) values('', '$tourny[id]', '$fight_day', '$home[id]', '$away[id]', '$fight_time')");


}
}

So my problem here is that I end up getting fighters fighting on the same day, sometimes at the same time too. So my question is: does the database insertion happen while the loop is going or after the page is loaded. Because I suspect that is why i get fights schduled like that. As the do while loop is done upon checking if those records have been inserted or not.


I know there is probably other ways to go about this, if anyone has some suggestions that would be great. Thanks in advance.

[JGadrow]

I'm thinking you have more problems going on here than you know. First off, that's a ton of select queries which are all using the same variables and tables. You can pull all the data you need with one query:

Code: (sql) [Select]

SELECT * FROM fights WHERE fight_day=1 && fight_time=1 && (home_id=1 || away_id=2);
If I had knowledge of the table structures, this could probably be rewritten to utilize the INSERT INTO ... SELECT format and do the whole thing in a single query.

[dbest]

Yeah.. Too many SELECT queries.

Try to explain your table structures and maybe we could provide an optimized solution.

[Winawer]

So my question is: does the database insertion happen while the loop is going or after the page is loaded.

While the loop is going.

[Dougie]

Thanks for the input guys. Im just curious though, why does it matter if I prefer to code like that? I kind of self taught myself php just looking at other source code. So by no means am trying to say my method is better or anything. I just would like to know the downsides of having 4 query's as opossed to 1 or 2? Am I actually putting that much more strain on the database in the big picture? Like, would this even be noticed?

I do realize yeah, it is probably the best route to try and optimize your code as best you can, try to use as little code as possible, etc, etc.

Basicly what I would like to know is if I do keep coding like this (really, no optimization in mind), will my game be noticably slower?

Appreciate the help!

[Winawer]

Thanks for the input guys. Im just curious though, why does it matter if I prefer to code like that? I kind of self taught myself php just looking at other source code. So by no means am trying to say my method is better or anything. I just would like to know the downsides of having 4 query's as opossed to 1 or 2? Am I actually putting that much more strain on the database in the big picture? Like, would this even be noticed?

I believe in this case 1 query should be faster, because you're scanning the same table many times with multiple queries. There are also situations (some big joins, etc.) where splitting a query in more parts may make it execute faster. It's possible the strain won't be noticed, depending on the size of the table, traffic, etc.

I do realize yeah, it is probably the best route to try and optimize your code as best you can, try to use as little code as possible, etc, etc.

The amount of code isn't really a big issue. Clarity>Brevity (of course sometimes brevity adds clarity).

Basicly what I would like to know is if I do keep coding like this (really, no optimization in mind), will my game be noticably slower?

It will be slower, but it's possible it won't be noticeable. I'd say you shouldn't worry too much about optimization, just try to get the features working and if something is slow, then optimize.

[JGadrow]

I do realize yeah, it is probably the best route to try and optimize your code as best you can, try to use as little code as possible, etc, etc.

The amount of code isn't really a big issue. Clarity>Brevity (of course sometimes brevity adds clarity).

Yup, I definitely agree. The issue in this case is that the code is not clear.

Thanks for the input guys. Im just curious though, why does it matter if I prefer to code like that?

It doesn't really matter. However, a bit of advice that I think is good for any developer is, when asking for help or discussing code, is to check your ego at the door. I didn't mean that as "You sound egotistical." It's just general advice that being open to new ideas will help you realize some of your own mistakes. And less mistakes = greater productivity.

I kind of self taught myself php just looking at other source code. So by no means am trying to say my method is better or anything.

I'm also self-taught so I, for one, won't hold that against you.

I just would like to know the downsides of having 4 query's as opossed to 1 or 2? Am I actually putting that much more strain on the database in the big picture? Like, would this even be noticed?

It depends entirely upon the amount of traffic you receive. If you have 1 visitor, you've made 4 queries to load their page. Probably not going to be that noticeable. If you have 40 visitors, you had to make 160 queries to load the pages. Now we may be talking about a slow-down that might be noticeable.

However, the reason I mentioned it was because you're actually getting multiple copies of what is, essentially, the same data. That's why you're getting duplicates in your final inserts. As a general rule, just take a moment and think to yourself, "What's the bare minimum of info I need to do what I want?"

I do realize yeah, it is probably the best route to try and optimize your code as best you can, try to use as little code as possible, etc, etc.

There's a difference between optimized code and reducing code complexity. In this case, it just happened to accomplish both goals simultaneously.

Basicly what I would like to know is if I do keep coding like this (really, no optimization in mind), will my game be noticably slower?

Absolutely. However, you'll probably finish faster. Again, it's not the fact that your code wasn't optimal it's more about being able to look at a piece of code and tell, at a glance, what it's supposed to be doing. Actual performance tweaks can be done later as needed.

[Winawer]

I do realize yeah, it is probably the best route to try and optimize your code as best you can, try to use as little code as possible, etc, etc.

The amount of code isn't really a big issue. Clarity>Brevity (of course sometimes brevity adds clarity).

Yup, I definitely agree. The issue in this case is that the code is not clear.

Yeah, I was speaking in general, so I'm not disagreeing with you there, either.

[Chris]

You have 8 queries while this can be done with 3. Premature optimization is one thing, a code designed to kill the server is another

Will this matter? Depends. If your game sux and few people play then not. But if your game become successfull then you will have a problem. Generally, optimization do not aim to reduce queries (because everyone minimize queries at the very beginning) but rather to set proper indexes, compound indexes, vertical/horizontal partitioning. It is not about "style" of coding, the code is just badly written.

I do really wish your game to get popular so it crashes even with very heavy optimization


Tips:
Do  not use rand, use mt_rand instead. It's faster and provide better quality randomization. You do not need srand, actually this make your randomizer worse quality. You use srand if you want to store/restore seed.