Jump to content
Klyx99

Help Faster way to create a dynamic website with HUGE XML files in PHP

Recommended Posts

it's generally not a good idea to store the same information in multiple places. if you already have the playercount and gameid stored in the playercount table, just use a JOIN query between the onlinegames table and the playercount table when you want to display the information.

as to the performance of the posted code -

1) you need to prepare the UPDATE query once, before the start of the loop, and only execute it inside the loop with each set of values

2) is the onlinegames table gameid column defined as an index? if it's not, then every execution of the query will scan the table to find the row to update.

also, i suspect that your $db->connect() method is creating a database connection each time it is called, which is a time consuming and wasteful process.

you are also executing the SELECT query twice, once at the ->query() method call and then again at the ->execute() call. why are you doing that?

Share this post


Link to post
Share on other sites

in that particular script, 1st to load all the player counts $onlinecounts

then loop through that array using the gameid and loop through all theonline games updating the player count form the online counts.

I didnt think about JOINING. let me try that.

 

Agreed on the connect(), I am still cleaning up the code - that is on my list 🙂

 

Share this post


Link to post
Share on other sites

OK, so Joining works (makes my prev post and example looks trashy LOL) meh, learning process - thanks for the Joining tip.

Keep in mind, IM old school from the 80's, so qb, gb, pascal basic and vb1-5. Recently last several years, chose php/mysql as my fav language 🙂 But its a hobby an love learning it

Share this post


Link to post
Share on other sites

OK, here is the source (for anyone interested)

I got the online games to use the player count tables, thus eliminating that horrible script in previous post LOL. Much faster. JOIN was tricky to learn. but works here prefectly.
 

 $sql            = "
                        SELECT *
                        FROM onlinegames AS og                                               
                        INNER JOIN playercount AS pc
                        ON og.gameid = pc.gameid                        
                        WHERE gamename LIKE concat('%', :needle, '%')
                        ORDER BY pc.playercount DESC
                        ";

 

Share this post


Link to post
Share on other sites
11 hours ago, Klyx99 said:

Keep in mind, IM old school from the 80's, so qb, gb, pascal basic and vb1-5

As are many of us. I first started with SQL using dBase and Foxbase.

Get out of the habit of using "SELECT * ". You should specify just the columns you need. Dragging all the data from the server makes your queries really inefficient.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.