sponsors gamedev.net search:

INSERT something into the database As the heading suggests revealed, when inserting (adding) something to the database, we're likely to use the INSERT command through SQL. Remember, a query is only a string command, you have to set up the database connection and objects in the code for yourself, but I'll post two examples. This is the basic syntax for inserting a member into the database through a single SQL query: INSERT INTO member(memberName, memberPassword, memberEmail, memberWeb)             VALUES('Albert Sandberg', 'secret', 'thec@home.se', 'www.thec.org') (All the text in above example should be on one row, I just cut it down for nicer page layout) It's pretty self explanatory: first we have the command, INSERT, which tells the database we're about to add something, next is the INTO statement followed up by the table name which we are supposed to store the data in. Inside the parentheses we just tell the database which entities we want to store, and inside the VALUES comes the data, in the same order as specified inside the first set of parentheses. No problem? Didn't think so! It's really that simple. The hardest part is to program a web page that contains forms and links, corrections for incorrectly entered data, etc. The SQL should be the easy part when you've gotten used to it! Here's an example of entering some data into the news table: INSERT INTO news(memberID, newsSubject, newsBody)             VALUES(1, 'I know something about you.', 'You're really impressed by SQL too!') Right here, we're using some of the magic of SQL. First, we have the memberID field, now referring to the first entry of the member table, which is 1. The member with id=1 in the member table is Mr "Albert Sandberg" since we've just entered him above. Note: If you have entered some test info and then deleted it when you created your tables, the first id will be the the one after the last one you entered in the test, because, as we discussed before, the id field should contain NO DUPLICATES! This is a feature you know! The date is covered by the database. We never need to manually set it through the query, since the entities we're not entering will be filled in with their default values. If we hadn't entered Now() for the newsDate field, it would probably have been an empty field instead. UPDATE changed data Once again, you might have an idea of what is going down. The UPDATE command changes things in the database. To move on forward let's skip the talk and get down to business: UPDATE member set memberPassword='wünderbaum' where memberName='Albert Sandberg' This should be fairly self-explanatory. The above will work fine if there is only one Albert Sandberg in the database, but since there is one more guy with that name which I know of, chances are we might some day enter the same website that you have coded, and I wouldn't like to not be able to log in when the other guy changes his password. You see, the where statement in the sql query returns ALL rows which match the execution argument. So if there had been two Albert Sandberg's, both passwords would have been changed. So we need to write something smarter, like: UPDATE member set memberPassword='wünderbaum' where id=1 Now there's no doubt. Since you keep my login in a cookie on the web server (or session variable, still a cookie though) you will know my id, and when I choose to change my password, you will enter it at the end of the line. In practice you never use static foreign/primary keys, as they are always dynamic. SELECT (view) our data All right, you want to present your news on the first page of your web site. Well, wait no further! SELECT * FROM news That's the basics. SELECT just picks every row in the database and since we don't have a WHERE statement on this one, it will simply select every row in the table. That's not as interesting though, since there could be 200 news items when we just want to see 20 of them, and perhaps in reversed order, or at least ordered by date (note, SQL has the power to choose in which order the items will be shown if we don't pass directions, so look ahead): SELECT TOP 20 * FROM news ORDER BY newsDate DESC Now we're talking. The "TOP 20" tells the database we want the 20 first occurrences of news items, sorted by newsDate! DESC just tells the order to be reversed. With dates, that means newest first, oldest last. In some databases you would use the LIMIT command instead of TOP, but TOP is used in Access. Try to read the database documentation for further hints and usage descriptions. All right, I have one more thing to show you before I move on to the next command. It's called joins and it is a little bit tricky at first, but I will try to make this as clear as I possibly can. Think about this: when you have SELECTed each news item, how do you fetch the member's name and id? The bad way. You take each row of the returned news data and create a second query to the database where you do: SELECT * from member where id=1 (of course you know the id=1 from the news.memberID field, right?). Anyway, this is not the correct way of doing it. When you are forced to make a second SQL query for fetching information you know belongs to the first query to start with, you know you need to update your SQL skills. This will make a lot more sense after looking at the next option. You do it the good way, like this: SELECT TOP 20 news.*, member.memberName, member.id FROM news,member        WHERE news.memberID=member.id ORDER BY newsDate DESC Felt like punch in the belly? No? Great, open up the champagne (or just fill up your glass). Here we do a join between two tables, plus we use the basics we already used. We want the TOP 20 results, we want to SELECT the info FROM both the news and the member table (which we select by using [table].entity; * is a wildcard, telling the database that we want everything), we tell the query compiler we only want entities WHERE news.memberID matches member.id (this one actually tells the database to bind up two tables into one result, but let's just stay there for a while) and we want to ORDER it by the newsDate, but in reverse order, that is, DESCending. Got it? Thank god, I succeeded. No? Read it again, you will not miss the fun the second time. DELETE something All right, let's face it. Albert Sandberg is the most boring guy on the planet, so you want to get rid of him and destroy all evidence of his existence. You will have to start with removing all rows in the database where the id matches Albert Sandberg's, so you do the following: DELETE * FROM news where memberID=1 That's how you remove the row matching the where clause in Microsoft Access. In any decent database you do it without the wildcard (why would you try to delete 50% of the entities anyway?). To remove the user from the member table in, for example, a mySql database, you'd use the following: DELETE FROM member where id=1 Done. Albert Sandberg ceases to exist. Finally, the hero knows his powers and is ready to save the world! Hurray! FAQ

Contents   Introduction   Commands   FAQ   Printable version   Discuss this article