Best way to query SQL then update data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
JustPlainJef
Forum Commoner
Posts: 42
Joined: Tue Jan 04, 2011 5:04 am
Location: McHenry, IL

Best way to query SQL then update data

Post by JustPlainJef »

Howdy folks.

Got more of a general question this morning.

I think I'm pretty close to having what I need, but being new to PHP, I'd rather get a suggestion to be more efficient before I start coding. Also, I’m aware all of my “code” below is wrong. It’s just written in “realspeak.”

Here's a little back ground info: viewtopic.php?f=1&t=126284

Here's what I want...

Poll the MySQL database and pull a table of games that have been postponed. This could be 1 game or (feasibly) 20 - 30 games, depending on the weather and how fast they get rescheduled. These will be displayed in a table. Within the table, I'll allow certain users to make updates to the schedule. These updates will then be sent back to the MySQL database, and the "postponed" flag will be turned off.

I’ve gotten as far as having all of the updates available in a PHP array, ready to be sent back to the MySQL database. Here's the part my brain is having a little trouble with..... The "original" data is pulled as a "MySQL array (which isn’t saved as an array)." The update from the website is a PHP array. Both are handled a little differently by PHP...

So I've seen a few methods for updating multiple records, but what makes mine a little unique is that I'm updating the primary keys. As it's a schedule, I don't want to allow two games with the same ballfield, date, and time, which are all the records that need to be updated if the game was postponed.

What I want to be really careful to avoid is sending the wrong update to the wrong game, which is where the MySQL / PHP difference worries me a bit....

My thoughts were to take the original SQL query and dump it into a PHP array, and add a second set of fields, so it would be

Code: Select all

Update NewDate[i] NewTime[i] NewField[i] where Date[i] Time[i] Field[i]
then increment . In this way, it seems that I could be certain to keep the updates with the original info.

As I have my data sitting now, it will be

Code: Select all

Update (3 fields from a PHP array) where (3 fields from a MySQL query)
then to the next 3 from the PHP array based on the next 3 from the database, etc... It seems like it would be easy to get numbers crossed, and update the wrong game.

I'm also a little hesitant to add a single primary key to the database as I'm not sure that would be very helpful... The schedule will be imported in the spring, and then updated from the website. So how do I say "update game #240 based on PHP array #3?" Doesn't seem that it would be much more efficient... Also, as I said, the combination of date, time, and ballfield have to be unique...


I hope that was clear enough to be understood, I’m not sure if the caffeine is kicking in yet…
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Best way to query SQL then update data

Post by Jade »

My solution would be that when a game is postponed it's removed from the main "games" table and instead sent to a "postponed" table. Then when the game is updated it can be put back onto the games table and check against the existing game keys to make sure there are no duplicates. If the insert works properly then the update succeeds, otherwise it fails and you don't have to worry about trying to keep track of keys as you're updating them.
JustPlainJef
Forum Commoner
Posts: 42
Joined: Tue Jan 04, 2011 5:04 am
Location: McHenry, IL

Re: Best way to query SQL then update data

Post by JustPlainJef »

Thanks for the reply, I had given up on seeing any replies...

I'm still pulling my hair out on this one, and every which way I go, I run into a road block.

I dumped the MySQL query into a PHP array, but then failed on adding additional fields to the PHP array, because I wanted to have "OldDate" and "NewDate" so I could set up something like
"The Junior Division Game between Red and Blue will be rescheduled from 8 PM, May 3 at Fox Ridge 2 to 6 PM, May 8, at Petersen Park 4."
I could have as many games listed as I wanted, using OR statements to list the games where at least one of the three values had changed. Then allow the person to review the updates, and either submit or cancel them.

I set up some sample data today, but it won't import into MySQL. So now that's aggrivating me. :banghead:

I'm not sure that pulling it out of the table would be the best way to go, but at this point, I'm happy to listen to anything.


I'm just happy it's Friday!
JustPlainJef
Forum Commoner
Posts: 42
Joined: Tue Jan 04, 2011 5:04 am
Location: McHenry, IL

Re: Best way to query SQL then update data

Post by JustPlainJef »

Thanks for the reply, I had given up on seeing any replies...

I'm still pulling my hair out on this one, and every which way I go, I run into a road block.

I dumped the MySQL query into a PHP array, but then failed on adding additional fields to the PHP array, because I wanted to have "OldDate" and "NewDate" so I could set up something like
"The Junior Division Game between Red and Blue will be rescheduled from 8 PM, May 3 at Fox Ridge 2 to 6 PM, May 8, at Petersen Park 4."
I could have as many games listed as I wanted, using OR statements to list the games where at least one of the three values had changed. Then allow the person to review the updates, and either submit or cancel them.

I set up some sample data today, but it won't import into MySQL. So now that's aggrivating me. :banghead:

I'm not sure that pulling it out of the table would be the best way to go, but at this point, I'm happy to listen to anything.


I'm just happy it's Friday!
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Best way to query SQL then update data

Post by Jade »

Okay, so from what you've described it sounds like the problem has to do with your table structure more than anything else. Have you considered something like:

Teams - this contains a list of all teams
Fields - this contains a list of all fields
Field_Dates - this contains a Field ID and the dates/times the field is available
Game - this contains two teams IDs a field_date ID, and a game_status id

If you need to postpone a game all you would do is change the game_status and then add a new row to the games table with a status of On Schedule. To find all the fields_dates available you would do a query for all fields_dates that don't have a game or have a game with the status of postponed. This would give you a list of all the fields_dates available to reschedule your game. It would also give you a history of every game that's been postponed (you'd have a record in the games table with a status of postponed) and you'd still know what field/date/time the original game was scheduled for. In fact this structure will even allow the game to be postponed several times and keep a history of every time the game between those two teams was re-scheduled.
JustPlainJef
Forum Commoner
Posts: 42
Joined: Tue Jan 04, 2011 5:04 am
Location: McHenry, IL

Re: Best way to query SQL then update data

Post by JustPlainJef »

I have not considered those options, but I certainly can take a look at it.


And I got the file to import...

Thanks Jade, have a great weekend, I'll check in next week!
Post Reply