• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

INSERT or UPDATE if contents exist

BatAlf has been helping me with a form for displaying conference registration info and updating that info. One of the problems I've come across is that there are input fields generated in which a room number gets  written and logged to a db. The problem is this: the idea is that the room number gets written to the database, the page reloads with the room number still displayed in the <iinput field>. If it needs to be changed, it is easy enough to change it there. Initially the room number has a NULL value, so I can't use an UPDATE statement when the form is submitted, and if there is already a room number listed that I want to change, an INSERT statement won't work, but will just write another row to the table.

I've searched both here and on the MySQL site for a solution for this problem, but without success in finding one.

Thanks to any who can suggest how to deal with this problem.

RWW
0
PstWood
Asked:
PstWood
  • 7
  • 3
  • 2
  • +1
1 Solution
 
BatalfCommented:
The most easy solution is to

* Delete the room info related to this conference before looping through the new form variables from the page.

example:

mysql_query("delete from assignments  where ....");

then loop through the records POST variables and insert the records again

Something like:

if(isset($_POST['GuestID'])){
    for($no=0;$no<count($_POST['GuestID']);$no++){
        echo "GuestID: ".$_POST['GuestID']."(Debuggin)<br>";
        echo "Room number: ".$_POST['RoomNumber']."(Debuggin)<br>";
        // Here you can insert data into the database
    }
}
0
 
PstWoodAuthor Commented:
But what would you put in the DELETE...WHERE clause?

That's what I"ve been trying to do, even to the point of just "DELETE FROM room_assignments"  Since all the rows that have rooms already assigned still have those values listed in the <input field>, they get re-written to the table, however all the rest of the rows that formerly had a NULL value now have a '0'. As I said, this is a livable situation, however I'd like to figure out how to get rid of the zeros.

This is probabaly complicated, as I indicated before by the fact that I'm trying to hack an existing piece of software that already has some db capabilities. I'm not nearly at the point where I could write this whole thing from scratch, but so far it works, even if it's jury rigged with bubble gum an bailing twine. 8^D

Thanks again
RWW
0
 
BatalfCommented:
Before you insert new values into your database, you can make a check that $_POST['RoomNumber'] is set

e.g.

for($no=0;$no<count($_POST['GuestID']);$no++){
    if(!empty($_POST['RoomNumber'][$no])){
             // Insert new record

     }

}
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
_Marcel_Commented:
Can't you use REPLACE INTO ?

Also, it would be easier to see some of the tables/columns that you have to update including primary keys. In this way it would be easier to get to the answer.
0
 
PstWoodAuthor Commented:
To: Batalf
Understood, but I still don't see what the WHERE clause would contain. ...WHERE GuestID = $GuestID[$no] ? Furthermore, the if (!empty)... routine essentially says "for a loop between 0 and the currently available to be posted row's Guest ID, if RoomNumber is not empty, insert a new RoomNumber" Is that right or not? That doesn't seem right to me And then how do I handle UPDATES? elseif? Sorry if I'm being dense here.

To: Marcel
REPLACE INTO
I can't get it to work though I've tried. REPLACE  INTO  room_assignments ( GuestID, RoomNumber ) VALUES (  '1',  '105' ) WHERE ( GuestID =  '1' ) returns a syntax error. The only other column is RoomAssignID, which is an auto number field.

Thanks
RWW
0
 
Marcus BointonCommented:
REPLACE can be dangerous if you're using InnoDB as it does a delete (which may cause an unwanted cascade deletion) followed by an insert. If you have MySQL 4.0, you can safely use: INSERT ... ON DUPLICATE KEY UPDATE, and you'll need to make sure that your unique key will identify the duplicate record correctly. In general, don't write stuff to the DB until you have valid data - keep it in the session until it's all ready to save.
0
 
_Marcel_Commented:
Hmmm... this RoomAssignID doesn't happen to be (part of) your primary key now does it? Then you are really going to need it for a REPLACE or UPDATE statement. Otherwise, what is this RoomAssignID for? Also, are there any other tables that are related to this RoomAssignID? If so, you cannot have it changing it's number...

A small correction to the remark Squinky made: INSERT .. ON DUPLICATE KEY UPDATE is available from MySQL4.1. If you are running this release that might be the best way to go (considering the rest of Squinky's remark)
0
 
PstWoodAuthor Commented:
OK. I'm extremely new to writing my own queries, etc. and don't have a lot of time for studying it all out, but I'm a fast learner, and I think that what I've figured out is that I don't need a primary key for this table. Correct me if I'm wrong, but if all it is storing is a RoomNumber and a GuestID, both of which are columns in other tables, what would be the point of a primary key? I don't pretend to understand the logic or theory behind db design, but I can think of no time when that key would be needed. The only time I will access this data is, for example, to list what room a guest has been assigned too or what guests are in what room, but in both cases another table (guests or room_assignments) will be used in conjunction with this table.

Having said that, I'll try INSERT ON DUP. KEY UPDATE

Thanks
RWW
0
 
PstWoodAuthor Commented:
After re-reading both REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE, I don't think either is going to work here since there is no primary key until after a row is inserted and, if I use a primary key, since it's an auto-increment field, it's not easy to get it for my purposes (I know you "can" get the next available number)

I thought this would be a relatively easy thing to do. Can I make one of the two other fields the primary or unique key? There should only be one row that has each GuestID, though not all will be present initially.

Thanks.
RWW
0
 
PstWoodAuthor Commented:
OK

I deleted the original primary key that I decided was unnecessary and mae GuestID the primary key. Then REPLACE INTO works fine except for when I assign a room, let's say 105, and then decide to delete that assignment. If I just delete 105 from the <input field>, submitting the form leaves 105 still in the box. I can replace it with "0" or "null", both of which leave "0" in the input field.

I wish that the field came up null when the room number is deleted, but it's not worth agonizing over. I can live with it the way it is and will include a note on the page to change to 0 if the user (on my staff so they can't complain ;^) needs to remove a room assignment.

Thanks for your help. I'm sure it won't be the last time.
RWW
0
 
_Marcel_Commented:
Maybe one last thing: from what I understand, probably both fields should belong to the primary key (so both GuestID and RoomNumber). This is basically because the combination of these two is unique, not just the GuestID (this would mean that a Guest can only be in one room at the time, never alowing the same Guest to be in another room ever. If this is the case, you could also store the RoomNumber inside the Guest table...
0
 
PstWoodAuthor Commented:
I don't really have a handle on the primary key issue at this point, however I'm running out of time to put this project to bed and get on to other things (I wear too many hats.) And while there may be some very good logic to what you are saying, in reality this is a one shot deal more than likely for a conference we will be hosting next January. If I can make everything functional and streamline some of the dirty work for my very limited staff, I'll be very happy. In reality, I've been able to figure out, with much help from EE, some things that I didn't think I'd be able to do with my limited knowledge of both php and mysql. At this point, everything is set up for the public side of the registration site with people able to log on and sign themselves up for the conference. I have a few more items on the back end for administrative purposes and I think I'll be done.

The thing I like is that everytime I undertake something like this I learn a bunch that will help the next time.

Thanks again for your help.
RWW
0
 
Marcus BointonCommented:
It sounds like this could use a bit more normalisation. From what I gather, you want to be able to accept a booking without having to assign a room to the guest. As Marcel says, if you make room + guest a unique key, the room will be booked forever, and your guest will never be able to visit again! The solution is to separate the two functions. At a guess I'd say you should introduce a 'stay' table that represents a guest for a period of time, then have an "assignment" table that links a stay to a room. This way you're not relying on leaving fields empty - until the assignment happens, the stay exists but the room assignment just doesn't exist yet, just like in real life... It would also be possible to handle more complicated arrangements like having a guest change rooms part way through a stay - there would just be multiple assignments for a given stay. With key constraints it could automatically delete all room assignments on cancellation of a stay. This is all much cleaner from a relational point of view.

The next problem is how to not accept more stays than you can make assignments for!
0
 
PstWoodAuthor Commented:
All good points I'm sure and for you guys "stuff" that could be easily implemented. For me, I'm elated that I figured out how to use sessions to carry the language for the bi-lingual site and write a few simple functions for getting the job done. It's a Ford at this point, and while I'm sure a Cadillac would be nice, the Ford will get me where I'm going. I've always got new projects going on and if I can manage to pick up a little new stuff every time, I'll be happy.

Thanks.
RWW
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now