Best way to query SQL then update data
Posted: Mon Jan 10, 2011 4:46 am
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 bethen 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 bethen 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…
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]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)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…