?
Solved

INSERT or UPDATE if contents exist

Posted on 2005-04-05
14
Medium Priority
?
213 Views
Last Modified: 2013-12-12
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
Comment
Question by:PstWood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +1
14 Comments
 
LVL 32

Expert Comment

by:Batalf
ID: 13705029
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
 

Author Comment

by:PstWood
ID: 13705144
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
 
LVL 32

Expert Comment

by:Batalf
ID: 13705163
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
7 Extremely Useful Linux Commands for Beginners

Just getting started with Linux? Here's a quick start guide that has 7 commands that we believe will come in handy.

 
LVL 8

Accepted Solution

by:
_Marcel_ earned 500 total points
ID: 13705531
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
 

Author Comment

by:PstWood
ID: 13707293
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
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 13707758
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
 
LVL 8

Expert Comment

by:_Marcel_
ID: 13709218
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
 

Author Comment

by:PstWood
ID: 13709998
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
 

Author Comment

by:PstWood
ID: 13710150
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
 

Author Comment

by:PstWood
ID: 13711405
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
 
LVL 8

Expert Comment

by:_Marcel_
ID: 13714558
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
 

Author Comment

by:PstWood
ID: 13714620
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
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 13714656
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
 

Author Comment

by:PstWood
ID: 13714797
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question