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

[mac_gyver]

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?

[Klyx99]

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 ?

 

[Klyx99]

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

[Klyx99]

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
                        ";

 

[Barand]

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.