RE: [PHP] Mysql Rows [END of thread]
Hi gang: Miles said: Why are we still chasing this thread? No need to pursue this thread anymore -- I'll just address the statements put to me. Why does he even have to see gaps? Just present the info, unless he wants to see the ID. Miles, I think that's the best solution I've heard thus far -- thanks. --- JM said: If you don't care that a given record may have a different, unpredictable record number each time its queried, and if you're sure no one is going to inherit this application and be stymied by your unorthodox approach, and if you know that in the future you will not need to access this data by a static record number, it doesn't matter. Otherwise, my advice would be to add a timestamp column and sort by that instead. LOL -- I think you drove your point home -- thanks. Anthony Ettinger said: I think the main reason is fora more extensible design. Sure, you may only have the 1 table now, and think you never will enhance your functionality...but as soon as you do comes up with a new scenario, you'll have to change the current behavior...easier to plan for that ahead of time. Technically, it works the way you want it...there's no right or wrong way, just degrees of flexibility, and it so happens this method seems inflexible from what I gather. Very good -- thanks. --- Paul said: It's simply -- concretely -- inefficient & inelegant to modify on average half the records in a database simply in order to delete one record, when queries give us fast, simple, READ-ONLY methods for enumerating existing data. Okay, I got the idea -- thank you. You guys are great -- thanks for putting up with me. As Daniel Boone once wrote: "I have never been lost, but I will admit to being confused for several weeks." tedd -- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
Barry: I realize that relational dB's are out if one does this -- and -- I fully understand why. That's the reason when I started this thread I made it clear that I was NOT talking about a relational dB but rather a simple flat file. What I find interesting in all of this exchange -- however -- is that everyone agree's renumbering the "id" of a dB is something you don't do, but no one can come up with a concrete (other than relational) reason why. I haven't finished reading this thread but here's why. It's because those id's could be used as foreign keys. I'll set up an example: create table author (authorid int, authorname varchar(200)); insert into author(authorid, authorname) values(1, 'Chris 1'); insert into author(authorid, authorname) values(2, 'Chris 2'); create table news(newsid int, newstitle varchar(200), authorid int); insert into news(newsid, newstitle, authorid) values (1, 'News by first author', 1); insert into news(newsid, newstitle, authorid) values (1, 'News by second author', 2); If you delete author '1' and then renumber, you have to go through every other table in the database to see if anything relates to authorid '1' and update it. Extremely time consuming and extremely error prone. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
At 08:57 AM 3/6/2006, tedd wrote: What I find interesting in all of this exchange -- however -- is that everyone agree's renumbering the "id" of a dB is something you don't do, but no one can come up with a concrete (other than relational) reason why. It's simply -- concretely -- inefficient & inelegant to modify on average half the records in a database simply in order to delete one record, when queries give us fast, simple, READ-ONLY methods for enumerating existing data. Regards, Paul -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
On 3/6/06, Jim Moseby <[EMAIL PROTECTED]> wrote: > > > > What I find interesting in all of this exchange -- however -- is that > > everyone agree's renumbering the "id" of a dB is something you don't > > do, but no one can come up with a concrete (other than relational) > > reason why. > > > > If you don't care that a given record may have a different, unpredictable > record number each time its queried, and if you're sure no one is going to > inherit this application and be stymied by your unorthodox approach, and if > you know that in the future you will not need to access this data by a > static record number, it doesn't matter. Otherwise, my advice would be to > add a timestamp column and sort by that instead. > > JM > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > > I think the main reason is fora more extensible design. Sure, you may only have the 1 table now, and think you never will enhance your functionality...but as soon as you do comes up with a new scenario, you'll have to change the current behavior...easier to plan for that ahead of time. Technically, it works the way you want it...there's no right or wrong way, just degrees of flexibility, and it so happens this method seems inflexible from what I gather. -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql Rows
At 10:56 AM 3/6/2006, tedd wrote: As such, if you don't renumber, then the only thing left is to use a timestamp, I guess. [/snip] No, if you have gaps you can still step through sequentially, like 14, 15, 18, 19, 20... It's the gaps that are the problem. I have no problem understanding why there are gaps in a dB and dealing with them. After all I've been juggling memory and bit-twiddling longer than I want to admit, so I understand "holes" in stuff. But what I am trying to solve is the simple thing of presenting records to a civilian such that there are no gaps in his record keeping. I don't want to have to explain to my client why his dB has gaps in it. I don't want to listen to him asking why those gaps aren't filled. In fact, I don't want to raise the issue at all if I can get around it. One way I found to get around this problem was to simply renumber the "id" filed in the table -- but, I received considerable advice against that from this list. I'm still not certain as to why that shouldn't be considered a "good" solution, but the "feelings" of the group are "don't do it". So, I'm still trying to find a simple way around this problem. Either I renumber the "id" field OR provide an external counter to present to the user. I don't see any other solutions, does anyone? Thanks. tedd Tedd, Why does he even have to see gaps? Just present the info, unless he wants to see the ID. If wonders why there are gaps, then just tell him that happens when someone is deleted. If he wants the gaps "filled" - then keep a table of the "holes" and recycle those numbers when new members are added. But there will still be occasional gaps. But if you renumber the ID field, you have seamless crap, because the number is absolutely meaningless. Today I'm 2345, tomorrow 2344 because one member's been deleted, next day I'm 2322 because a lot of people were deleted but the day after that 2367 because a lot of people signed up. If the client wants the number of current people, then SELECT Count(*) ... gets it for you. Why are we still chasing this thread? Regards - Miles -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 3/3/2006 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql Rows
> > What I find interesting in all of this exchange -- however -- is that > everyone agree's renumbering the "id" of a dB is something you don't > do, but no one can come up with a concrete (other than relational) > reason why. If you don't care that a given record may have a different, unpredictable record number each time its queried, and if you're sure no one is going to inherit this application and be stymied by your unorthodox approach, and if you know that in the future you will not need to access this data by a static record number, it doesn't matter. Otherwise, my advice would be to add a timestamp column and sort by that instead. JM -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
On 3/6/06, Dusty Bin <[EMAIL PROTECTED]> wrote: > Another point to consider, is that Tedds method of renumbering the rows, > *may* not preserve the original sequence. I have not checked the mysql > source, but if some delete activity has occurred in the table, then > there will be holes in the data, in some circumstances, inserting > further records to the table, some may go on the end, and some may fill > the holes. I suspect that using Tedd's method of dropping the column, > and then re-adding the column, the auto increment, will either be added > in the physical sequence of the table, or added in the order of any > index that may be traversed in this process. As an extreme example, if > one had a table with 5 million rows, and then deleted the first 4.5 > million rows, where will the next insert go? One of the benefits of a > relational database, is that you do not need to consider how the data is > physically stored. > > Best regards... Dusty > > [EMAIL PROTECTED] wrote: > > [snip] > > That's the reason when I started this thread I made it clear that I > > was NOT talking about a relational dB but rather a simple flat file. > > > > What I find interesting in all of this exchange -- however -- is that > > everyone agree's renumbering the "id" of a dB is something you don't > > do, but no one can come up with a concrete (other than relational) > > reason why. > > > > [/snip] > > > > Tedd, several here, including me, have said that if you have only a > > single table database that renumbering is OK, just not a preferred > > practice. (BTW, you never answered my question about this being a flat > > file database or single table, although I have figured it out now.) > > Renumber to your heart's content. If your users are allowed delete > > privileges (ACK!) and you don't want to confuse them, go ahead and > > renumber. > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > > Not to mention adding other tables which references those numbers as a foreign key. Seems like the wrong way to do it in my opinion. Business logic should be abstracted from the database layer and handled in the source code, rather than relying on re-factoring the database everytime you want a count. -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
Another point to consider, is that Tedds method of renumbering the rows, *may* not preserve the original sequence. I have not checked the mysql source, but if some delete activity has occurred in the table, then there will be holes in the data, in some circumstances, inserting further records to the table, some may go on the end, and some may fill the holes. I suspect that using Tedd's method of dropping the column, and then re-adding the column, the auto increment, will either be added in the physical sequence of the table, or added in the order of any index that may be traversed in this process. As an extreme example, if one had a table with 5 million rows, and then deleted the first 4.5 million rows, where will the next insert go? One of the benefits of a relational database, is that you do not need to consider how the data is physically stored. Best regards... Dusty [EMAIL PROTECTED] wrote: > [snip] > That's the reason when I started this thread I made it clear that I > was NOT talking about a relational dB but rather a simple flat file. > > What I find interesting in all of this exchange -- however -- is that > everyone agree's renumbering the "id" of a dB is something you don't > do, but no one can come up with a concrete (other than relational) > reason why. > > [/snip] > > Tedd, several here, including me, have said that if you have only a > single table database that renumbering is OK, just not a preferred > practice. (BTW, you never answered my question about this being a flat > file database or single table, although I have figured it out now.) > Renumber to your heart's content. If your users are allowed delete > privileges (ACK!) and you don't want to confuse them, go ahead and > renumber. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
tedd wrote: Well it seems you output it via PHP so count it extern in PHP. And changing ID values is a "no-go!" You will never have any relation possibilities if you alter the ID fields. In short. You mess everthing up with it. There are count functions in MySQL that gives you the counted rows or output it counted. You can look at the manuals in mysql.com Good luck Barry Barry: I realize that relational dB's are out if one does this -- and -- I fully understand why. That's the reason when I started this thread I made it clear that I was NOT talking about a relational dB but rather a simple flat file. What I find interesting in all of this exchange -- however -- is that everyone agree's renumbering the "id" of a dB is something you don't do, but no one can come up with a concrete (other than relational) reason why. it's a unique identifier in time and space. changing it means potentially losing referential information (that may exist on seperate systems and/or outside of the DB). if 123 refers to product X today you should be able to rely on refering to product X next week. the the product 123 refers to changes over time the id is at the very least alot less value as an identifier (because identifier are used to refer to specific things and/or related 2 or more entities - that is only of value when ids are immutable) one of the few reasons you would want to renumber is because,as is the case here I believe, one would like to use the id values for something other than what they were designed for - i.e. using them as optential wincodes in a sweepstake type wotsit [primary]keyfields are meant to uniquely identify an entity (row) and nothing else. But your suggestion to look-up the MySQL count function, is a good one, thank you. tedd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql Rows
[snip] That's the reason when I started this thread I made it clear that I was NOT talking about a relational dB but rather a simple flat file. What I find interesting in all of this exchange -- however -- is that everyone agree's renumbering the "id" of a dB is something you don't do, but no one can come up with a concrete (other than relational) reason why. [/snip] Tedd, several here, including me, have said that if you have only a single table database that renumbering is OK, just not a preferred practice. (BTW, you never answered my question about this being a flat file database or single table, although I have figured it out now.) Renumber to your heart's content. If your users are allowed delete privileges (ACK!) and you don't want to confuse them, go ahead and renumber. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows [and whats the difference between Navigational and Relational DBs]
Miles Thompson wrote: I hope the following will be helpful, and it is a bit of a rant .. thank god someone ranted on this already :-) I wasn't feeling up to it but it's also one of those cases that you can't help but speak out. ;-) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
Well it seems you output it via PHP so count it extern in PHP. And changing ID values is a "no-go!" You will never have any relation possibilities if you alter the ID fields. In short. You mess everthing up with it. There are count functions in MySQL that gives you the counted rows or output it counted. You can look at the manuals in mysql.com Good luck Barry Barry: I realize that relational dB's are out if one does this -- and -- I fully understand why. That's the reason when I started this thread I made it clear that I was NOT talking about a relational dB but rather a simple flat file. What I find interesting in all of this exchange -- however -- is that everyone agree's renumbering the "id" of a dB is something you don't do, but no one can come up with a concrete (other than relational) reason why. But your suggestion to look-up the MySQL count function, is a good one, thank you. tedd -- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
tedd wrote: As such, if you don't renumber, then the only thing left is to use a timestamp, I guess. [/snip] No, if you have gaps you can still step through sequentially, like 14, 15, 18, 19, 20... It's the gaps that are the problem. I have no problem understanding why there are gaps in a dB and dealing with them. After all I've been juggling memory and bit-twiddling longer than I want to admit, so I understand "holes" in stuff. But what I am trying to solve is the simple thing of presenting records to a civilian such that there are no gaps in his record keeping. I don't want to have to explain to my client why his dB has gaps in it. I don't want to listen to him asking why those gaps aren't filled. In fact, I don't want to raise the issue at all if I can get around it. One way I found to get around this problem was to simply renumber the "id" filed in the table -- but, I received considerable advice against that from this list. I'm still not certain as to why that shouldn't be considered a "good" solution, but the "feelings" of the group are "don't do it". So, I'm still trying to find a simple way around this problem. Either I renumber the "id" field OR provide an external counter to present to the user. I don't see any other solutions, does anyone? Thanks. tedd Well it seems you output it via PHP so count it extern in PHP. And changing ID values is a "no-go!" You will never have any relation possibilities if you alter the ID fields. In short. You mess everthing up with it. There are count functions in MySQL that gives you the counted rows or output it counted. You can look at the manuals in mysql.com Good luck Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
On Monday 06 March 2006 07:56, tedd wrote: > So, I'm still trying to find a simple way around this problem. Either > I renumber the "id" field OR provide an external counter to present > to the user. I don't see any other solutions, does anyone? > > Thanks. > > tedd I haven't followed this thread very closely, so pardon me if I'm just repeating what's already been said. In order to have gaps in the DB, you have to delete records out of the DB. What if you do not allow people to actually remove records from the DB, but rather have a "deleted" or "removed" column. That way all the IDs are still valid, but you also have a way of knowing what's current and what is not. Just a thought. -- Ray Hauge Programmer/Systems Administrator American Student Loan Services http://www.americanstudentloan.com 1.800.575.1099 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql Rows
As such, if you don't renumber, then the only thing left is to use a timestamp, I guess. [/snip] No, if you have gaps you can still step through sequentially, like 14, 15, 18, 19, 20... It's the gaps that are the problem. I have no problem understanding why there are gaps in a dB and dealing with them. After all I've been juggling memory and bit-twiddling longer than I want to admit, so I understand "holes" in stuff. But what I am trying to solve is the simple thing of presenting records to a civilian such that there are no gaps in his record keeping. I don't want to have to explain to my client why his dB has gaps in it. I don't want to listen to him asking why those gaps aren't filled. In fact, I don't want to raise the issue at all if I can get around it. One way I found to get around this problem was to simply renumber the "id" filed in the table -- but, I received considerable advice against that from this list. I'm still not certain as to why that shouldn't be considered a "good" solution, but the "feelings" of the group are "don't do it". So, I'm still trying to find a simple way around this problem. Either I renumber the "id" field OR provide an external counter to present to the user. I don't see any other solutions, does anyone? Thanks. tedd -- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql Rows
[snip] >You must have a column that is sequential in some way. An >auto-incremented column, timestamp, or some other device that will allow >you to step through regardless of gaps in sequence. If you do not have >such a column then you could add one. You see, now that's the problem. If you have a field that has auto-increment and then delete a record, auto-increment does not go back and fill up the gaps. As such, if you don't renumber, then the only thing left is to use a timestamp, I guess. [/snip] No, if you have gaps you can still step through sequentially, like 14, 15, 18, 19, 20... [snip] >The simplest example (most recent to oldest): > >select * from table order by datefield desc; > > >To get them in the order they were entered: > >select * from table order by id asc; > > >To get them in reverse order: > >select * from table order by id desc; Which I could be used in such a way that you also provide a counter outside of mysql to show the user a number as they step through the records. However, the next time they want to review record xxx, it may not be in the same position because of deletions. [/snip] But if you never change the record number it will always have the same record number. If someone searches for a particular record and it is not there it has been deleted. Renumbering makes this far worse, because if I am searching for record xxx and the table has been renumbered it may now be record yyI'd have no way of knowing. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
Gustav: In any event, I just tested your claim on my host and I was able to renumber 50,000 records in less than 1/2 second. Even though I tried it several times, the results were never above 0.47 seconds. If I was dealing with a database that was accessible to others, then I would either lock tables or use transaction and then renumber -- but in either case the difference in time is less than additional 1/10 of a second. Ok, it wasn't really a claim, it was more a "feeling", but I couldn't say it for sure, so I applogize for misleading you there! :-) No need to apologize, this is an open discussion -- I'm the one who is asking questions. Aha... I missed the part ... "I was dealing with a database that was accessible to others" Only YOU are using the db? Ok, then it would not be a problem. Well both. If I was the only one working with the dB, then there is no need to lock it down. However, I did set up test where I did lock it down and another test where I set up a transaction and both added about a tenth of a second to the alteration of 50,000 records. Ok, I got your point! :-) But there is another side to it , and that is scalability. Ok, you don't have so many posts in your database. Ok, it does take less then a second... What if you had do make the db visible to other users? As I said I did a LOCK TABLE, that's where no one can access the records while I work on them (at least that's my understanding) and it added about a tenth of a second to the overall time (just over 1/2 second for 50,000 records). I also preformed a transaction (BEGIN/COMMIT), which my understanding is that it checks to make sure that the operation can be preformed and then if everything can be done, then it does it -- an interesting feature. Then this link that might intrest you: (it's about pitfalls with transactions as may see in the link) http://www.onlamp.com/pub/a/php/2003/12/18/transaction_pitfalls.htmlhttp://www.onlamp.com/pub/a/php/2003/12/18/transaction_pitfalls.html I'll read the link, thanks. But what If you suddenly hade to change host for some reason... The new host server maybe wouldn't be as fast as yours is today. And as someone mentioned, what about if your db was growing to include a couple of million posts. Both valid points -- but I know the limits of my customer. If they post more than 100,000 records in their lifetime, I would be very surprised. Of course, if you don't think your db will grow so much, and it works fine, then of course you could contiuning doing the ALTER-statement thing, but there's always two sides of the story... ;-) Well... what I am developing is something for a civilian (i.e., no programming experience at all). They want to be able to add, edit, and delete records at their command. I know that I could not explain why when they deleted a record that they would no longer have the index again, for I couldn't think of any reason myself -- other than "it's bad form". So, I needed a way for them to delete a record and make it easy for them to understand. However, I also wanted to make sure that I wasn't doing them a disservice -- and thus my questions to this group -- the other side of the story. If the ALTER TABLE statement is prone to error, then I would like to know that and why. However, I suspect that claim isn't true, it's just that it's misuse has generated an urban myth of "Don't do that! That's dangerous!" without any real substance other than for programer error. *LOL* I have to say, that I agree with you on this one. I've searched on issues/regarding ALTER TABLE, and could only find errors regarding this when Microsoft was involved ;-) Yeah, I feel sorry for those who rely on M$, but that's another topic. I have thought of this... ...but isn't there a function in MySQL or PHP to retrieve the absolute recordnr of a db??? ...if there is not, then your code IS good, if you absolutelty need the functionality! :-) This exchange has given some ideas. One way is to use a timestamp to step through the records with a counter that's external to the dB. That way the customer can see a sequential count, while the dB is just stepping through records via timestamp. I think I can sell that. In any event, this exchange has helped me -- so, thanks to all. tedd -- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
- Original Message - From: "tedd" <[EMAIL PROTECTED]> To: Cc: "Gustav Wiberg" <[EMAIL PROTECTED]>; "Robert Cummings" <[EMAIL PROTECTED]> Sent: Sunday, March 05, 2006 12:58 AM Subject: Re: [PHP] Mysql Rows Hi: Gustav said: No, maybe not when it's a small db, but when you try to delete 50.000 posts I have a strong feeling this would be very much slower then if you don't alter table after each deletion. First, I'm not deleting 50,000 records -- I dropping a table and renumbering it. In any event, I just tested your claim on my host and I was able to renumber 50,000 records in less than 1/2 second. Even though I tried it several times, the results were never above 0.47 seconds. If I was dealing with a database that was accessible to others, then I would either lock tables or use transaction and then renumber -- but in either case the difference in time is less than additional 1/10 of a second. Ok, it wasn't really a claim, it was more a "feeling", but I couldn't say it for sure, so I applogize for misleading you there! :-) Half a second or 0.47 seconds may not be a big issue, but as I see it this could be "unessescary time", if you could achieve the same functionality (without doing some alter-statements) faster and without have to lock the db. Have you tested without the alter-table statement? *just curious* Aha... I missed the part ... "I was dealing with a database that was accessible to others" Only YOU are using the db? Ok, then it would not be a problem. I don't know if 1/2 second is a big deal in your world, or not, but it seems a bit slow to me. I wrote splay binary tree search routine that would perform 100,000 searches in a two million record dB in less than one second on my Mac. And if you know what a splay algorithm is, then you also know that it not only preforms a search but then reorders the tree each time a search is successful and thus is very laborious. Yet the time it took to preform 100,000 searches and reorders was still less than one second. Perhaps my host is running something slower -- after all, I'm only paying $7.00 per year for the service. But with all things considered, a half second is not that significant with a small 50,000 record dB. That's probably less than the majority of web sites that use MySQL, don't you think? Ok, I got your point! :-) But there is another side to it , and that is scalability. Ok, you don't have so many posts in your database. Ok, it does take less then a second... What if you had do make the db visible to other users? Then this link that might intrest you: (it's about pitfalls with transactions as may see in the link) http://www.onlamp.com/pub/a/php/2003/12/18/transaction_pitfalls.htmlhttp://www.onlamp.com/pub/a/php/2003/12/18/transaction_pitfalls.html But what If you suddenly hade to change host for some reason... The new host server maybe wouldn't be as fast as yours is today. And as someone mentioned, what about if your db was growing to include a couple of million posts. Of course, if you don't think your db will grow so much, and it works fine, then of course you could contiuning doing the ALTER-statement thing, but there's always two sides of the story... ;-) "That's probably less than the majority of web sites that use MySQL, don't you think?" I really have no clue, I could only guess... :-) Rod said: *LOL* I knew those MySQL people shouldn't have made the ALTER TABLE syntax available to just anyone. Gun --> foot --> *BLAM*. I hope to God you never get your hands on a real database with millions of entries. I'm glad that you were amused. Considering that I was talking about a flat dB, then you have already shot yourself in the foot if your "real database" is in the millions of entries and is flat. I hope to God that normalization may be something you consider in your next database design. In any event, it's interesting that I posted a question here and I expected some ribbing, but I also expected something of value. If the ALTER TABLE statement is prone to error, then I would like to know that and why. However, I suspect that claim isn't true, it's just that it's misuse has generated an urban myth of "Don't do that! That's dangerous!" without any real substance other than for programer error. *LOL* I have to say, that I agree with you on this one. I've searched on issues/regarding ALTER TABLE, and could only find errors regarding this when Microsoft was involved ;-) Of course, I've run into windozes programmers who accept the occasional crash and burn as "it comes with the territory", but that's unfortunate to apply this "apprehension" to MySQL. :-) In my previous post I plea
Re: [PHP] Mysql Rows
On Sun, Mar 05, 2006 at 08:01:07AM -0500, tedd wrote: > R O B said: > > >That Rod guy, he's such a card! I'd add something, but Jay has already > >covered my list in a more recent email than this one to which I'm > >responding :) > > jblanchard (who I think is Rod) said: > > >A SQL question on a PHP mailing list usually gets more than ribbing. ;) So, Jay is Rod, who is this Rob person? Now I'm really confused. Curt. -- cat .signature: No such file or directory -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql Rows
[snip] R O B said: >That Rod guy, he's such a card! I'd add something, but Jay has already >covered my list in a more recent email than this one to which I'm >responding :) jblanchard (who I think is Rod) said: [/snip] I am definitely not Rod. [snip] For sake of argument, let's agree that renumbering dB's is not a good idea -- so if you want to sequentially step through the records, then how do you do it? [/snip] You must have a column that is sequential in some way. An auto-incremented column, timestamp, or some other device that will allow you to step through regardless of gaps in sequence. If you do not have such a column then you could add one. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
On 3/6/06, tedd <[EMAIL PROTECTED]> wrote: > R O B said: > > >That Rod guy, he's such a card! I'd add something, but Jay has already > >covered my list in a more recent email than this one to which I'm > >responding :) > > jblanchard (who I think is Rod) said: > > >A SQL question on a PHP mailing list usually gets more than ribbing. ;) > > No harm done -- I been hammered much worse and occasionally for good reason. > > But you're right, I should have directed my question to a mysql > group. I just joined and expanded an ongoing thread on this subject. > > However, my apologies to the group for going off-PHP just one more time. > > For sake of argument, let's agree that renumbering dB's is not a good > idea -- so if you want to sequentially step through the records, then > how do you do it? The simplest example (most recent to oldest): select * from table order by datefield desc; To get them in the order they were entered: select * from table order by id asc; To get them in reverse order: select * from table order by id desc; Depends what data you have and what you are trying to do exactly. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
R O B said: That Rod guy, he's such a card! I'd add something, but Jay has already covered my list in a more recent email than this one to which I'm responding :) jblanchard (who I think is Rod) said: A SQL question on a PHP mailing list usually gets more than ribbing. ;) No harm done -- I been hammered much worse and occasionally for good reason. But you're right, I should have directed my question to a mysql group. I just joined and expanded an ongoing thread on this subject. However, my apologies to the group for going off-PHP just one more time. For sake of argument, let's agree that renumbering dB's is not a good idea -- so if you want to sequentially step through the records, then how do you do it? Thanks -- I do appreciate the feedback (even ribbing). tedd -- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
On Sat, 2006-03-04 at 18:58, tedd wrote: > Hi: > > Rod said: > > >*LOL* I knew those MySQL people shouldn't have made the ALTER TABLE > >syntax available to just anyone. Gun --> foot --> *BLAM*. I hope to God > >you never get your hands on a real database with millions of entries. > > I'm glad that you were amused. Considering that I was talking about a > flat dB, then you have already shot yourself in the foot if your > "real database" is in the millions of entries and is flat. I hope to > God that normalization may be something you consider in your next > database design. That Rod guy, he's such a card! I'd add something, but Jay has already covered my list in a more recent email than this one to which I'm responding :) Cheers, R O B -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql Rows
[snip] I'm glad that you were amused. Considering that I was talking about a flat dB, then you have already shot yourself in the foot if your "real database" is in the millions of entries and is flat. I hope to God that normalization may be something you consider in your next database design. [/snip] Normalization? I have a call records database with 100's of millions of records in a single table. No further normalization is required or needed. The records cannot be broken down any further. I think you may be using normalization incorrectly here. And by "flat" database, do you mean flat file database or just single table database? [snip] In any event, it's interesting that I posted a question here and I expected some ribbing, but I also expected something of value. [/snip] A SQL question on a PHP mailing list usually gets more than ribbing. ;) [snip] If the ALTER TABLE statement is prone to error, then I would like to know that and why. However, I suspect that claim isn't true, it's just that it's misuse has generated an urban myth of "Don't do that! That's dangerous!" without any real substance other than for programer error. Of course, I've run into windozes programmers who accept the occasional crash and burn as "it comes with the territory", but that's unfortunate to apply this "apprehension" to MySQL. [/snip] Re-numbering of database tables has been an acknowledged "bad practice" for years for several reasons. Ask on any SQL list, including MySQL, and you'll get tons of reasons not to do it. [snip] In my previous post I pleaded for someone to point out the error of my ways and to give me an alternative, but that hasn't happened yet -- so, does anyone want to tell me why I should not renumber a flat database and give me an alternative? I'm all ears... [/snip] I think that my answer in previous e-mail demonstrates a couple of ways that this could be done without re-numbering, even in a single table database. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql Rows
[snip] my reasoning for needing the users number in a database is this... i am going to be doing a lottery type thing where i grab a random number between 1 and the result of mysql_num_rows($result)... that is the reason the gaps matter. the while loop didn't work for me so if anyone could help me out on how to get this number i would aprreaciate it. thank you in advance. [/snip] The one thing that no one has mentioned is that relational databases will returns rows in whatever order they please if no ORDER BY is specified. Generally, due to query caching, rows without an order by will be returned in the same order nearly every time. The better solution here is selecting the lowest ID and the highest ID and selecting a random number between the two, checking for its existence, and doing the operation again until it comes up with a valid ID. This way it matters not what ID's exist in what order and you will always get a valid ID to award the lottery to. Or you could do it all in the query itself, if you wanted to be a true RDBMS master. SELECT * FROM table ORDER BY RAND() LIMIT 1 See...no ID's or artificial ordering required. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
On Sat, 2006-03-04 at 17:34, benifactor wrote:
> - Original Message -
> From: "benifactor" <[EMAIL PROTECTED]>
> To: "Robert Cummings" <[EMAIL PROTECTED]>; "tedd" <[EMAIL PROTECTED]>
> Cc: "PHP-General" ; "Murray @ PlanetThoughtful"
> <[EMAIL PROTECTED]>; "Anthony Ettinger" <[EMAIL PROTECTED]>
> Sent: Saturday, March 04, 2006 2:29 PM
> Subject: Re: [PHP] Mysql Rows
>
>
> >
> > - Original Message -
> > From: "Robert Cummings" <[EMAIL PROTECTED]>
> > To: "tedd" <[EMAIL PROTECTED]>
> > Cc: "PHP-General" ; "benifactor"
> > <[EMAIL PROTECTED]>; "Murray @ PlanetThoughtful" <[EMAIL PROTECTED]>;
> > "Anthony Ettinger" <[EMAIL PROTECTED]>
> > Sent: Saturday, March 04, 2006 9:41 AM
> > Subject: Re: [PHP] Mysql Rows
> >
> >
> > > On Sat, 2006-03-04 at 09:14, tedd wrote:
> > > > planetthoughtful wrote:
> > > >
> > > > >But, too often I've seen people new to database design not liking
> > > > >'gaps' because 'user1' will have a unique id of '1', while 'user2'
> > > > >will have a unique id of '6' because the records associated with
> > > > >unique ids '2' through '5' were deleted during testing, and so on.
> > > > >So, they feel that 'user2' should have a unique id of '2', ignoring
> > > > >the fact that that's not a unique id at all, if you had id '2'
> > > > >associated with another record at some point.
> > > >
> > > > And, Anthony wrote:
> > > >
> > > > >I remember the days where i'd
> > > > >clear a database after testing to keep the auto_increment inline, but
> > > > >eventually, you will get out of sync on that, so it's not a reliable
> > way of
> > > > >keeping a numerical sequence.
> > > >
> > > > Well... I'm one of those people who don't like gaps. I understand
> > > > that if the dB is relational, then you shouldn't be concerned about
> > > > gaps. Gaps are only perceived from a perspective of an artificial
> > > > ordering system -- who knows where the data actually is in memory or
> > > > on disk.
> > > >
> > > > However, when I'm working with a flat dB and want to step through the
> > > > records to do editing, I like the records to be in order based upon
> > > > an "id" (i.e., Record 1, Record 2, Record 3, and so on). I use an
> > > > auto_increment unique "id" for this.
> > > >
> > > > It's not a big problem for me to keep the records in order either.
> > > > Whenever I delete a record, I simply follow with:
> > > >
> > > > $dbQuery = "ALTER TABLE $dbtable ";
> > > > $dbQuery .= "DROP id, ";
> > > > $dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
> > > > $dbQuery .= "AUTO_INCREMENT = 1";
> > > > $result = mysql_query($dbQuery) or die("2. Could not renumber dB
> > > > $dbQuery" . mysql_error());
> > >
> > > *LOL* I knew those MySQL people shouldn't have made the ALTER TABLE
> > > syntax available to just anyone. Gun --> foot --> *BLAM*. I hope to God
> > > you never get your hands on a real database with millions of entries.
> > >
> >
> > my reasoning for needing the users number in a database is this...
> >
> > i am going to be doing a lottery type thing where i grab a random number
> > between 1 and the result of mysql_num_rows($result)... that is the reason
> > the gaps matter. the while loop didn't work for me so if anyone could
> help
> > me out on how to get this number i would aprreaciate it. thank you in
> > advance.
>
> here is what i tried..
> while ($d = mysql_fetch_array($query)) {
> $i = 0;
> while ($d[username] != $user) {
> $i++
> }
> }
Now we can help you...
Cheers,
Rob.
--
..
| InterJinn Application Framework - http://www.interjinn.com |
::
| An application and templating framework for PHP. Boasting |
| a powerful, scalable system for accessing system services |
| such as forms, properties, sessions, and caches. InterJinn |
| also provides an extremely flexible architecture for |
| creating re-usable components quickly and easily. |
`'
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
Hi: Gustav said: No, maybe not when it's a small db, but when you try to delete 50.000 posts I have a strong feeling this would be very much slower then if you don't alter table after each deletion. First, I'm not deleting 50,000 records -- I dropping a table and renumbering it. In any event, I just tested your claim on my host and I was able to renumber 50,000 records in less than 1/2 second. Even though I tried it several times, the results were never above 0.47 seconds. If I was dealing with a database that was accessible to others, then I would either lock tables or use transaction and then renumber -- but in either case the difference in time is less than additional 1/10 of a second. I don't know if 1/2 second is a big deal in your world, or not, but it seems a bit slow to me. I wrote splay binary tree search routine that would perform 100,000 searches in a two million record dB in less than one second on my Mac. And if you know what a splay algorithm is, then you also know that it not only preforms a search but then reorders the tree each time a search is successful and thus is very laborious. Yet the time it took to preform 100,000 searches and reorders was still less than one second. Perhaps my host is running something slower -- after all, I'm only paying $7.00 per year for the service. But with all things considered, a half second is not that significant with a small 50,000 record dB. That's probably less than the majority of web sites that use MySQL, don't you think? Rod said: *LOL* I knew those MySQL people shouldn't have made the ALTER TABLE syntax available to just anyone. Gun --> foot --> *BLAM*. I hope to God you never get your hands on a real database with millions of entries. I'm glad that you were amused. Considering that I was talking about a flat dB, then you have already shot yourself in the foot if your "real database" is in the millions of entries and is flat. I hope to God that normalization may be something you consider in your next database design. In any event, it's interesting that I posted a question here and I expected some ribbing, but I also expected something of value. If the ALTER TABLE statement is prone to error, then I would like to know that and why. However, I suspect that claim isn't true, it's just that it's misuse has generated an urban myth of "Don't do that! That's dangerous!" without any real substance other than for programer error. Of course, I've run into windozes programmers who accept the occasional crash and burn as "it comes with the territory", but that's unfortunate to apply this "apprehension" to MySQL. In my previous post I pleaded for someone to point out the error of my ways and to give me an alternative, but that hasn't happened yet -- so, does anyone want to tell me why I should not renumber a flat database and give me an alternative? I'm all ears... Thanks in advance for any replies. tedd -- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
- Original Message -
From: "benifactor" <[EMAIL PROTECTED]>
To: "Robert Cummings" <[EMAIL PROTECTED]>; "tedd" <[EMAIL PROTECTED]>
Cc: "PHP-General" ; "Murray @ PlanetThoughtful"
<[EMAIL PROTECTED]>; "Anthony Ettinger" <[EMAIL PROTECTED]>
Sent: Saturday, March 04, 2006 2:29 PM
Subject: Re: [PHP] Mysql Rows
>
> - Original Message -
> From: "Robert Cummings" <[EMAIL PROTECTED]>
> To: "tedd" <[EMAIL PROTECTED]>
> Cc: "PHP-General" ; "benifactor"
> <[EMAIL PROTECTED]>; "Murray @ PlanetThoughtful" <[EMAIL PROTECTED]>;
> "Anthony Ettinger" <[EMAIL PROTECTED]>
> Sent: Saturday, March 04, 2006 9:41 AM
> Subject: Re: [PHP] Mysql Rows
>
>
> > On Sat, 2006-03-04 at 09:14, tedd wrote:
> > > planetthoughtful wrote:
> > >
> > > >But, too often I've seen people new to database design not liking
> > > >'gaps' because 'user1' will have a unique id of '1', while 'user2'
> > > >will have a unique id of '6' because the records associated with
> > > >unique ids '2' through '5' were deleted during testing, and so on.
> > > >So, they feel that 'user2' should have a unique id of '2', ignoring
> > > >the fact that that's not a unique id at all, if you had id '2'
> > > >associated with another record at some point.
> > >
> > > And, Anthony wrote:
> > >
> > > >I remember the days where i'd
> > > >clear a database after testing to keep the auto_increment inline, but
> > > >eventually, you will get out of sync on that, so it's not a reliable
> way of
> > > >keeping a numerical sequence.
> > >
> > > Well... I'm one of those people who don't like gaps. I understand
> > > that if the dB is relational, then you shouldn't be concerned about
> > > gaps. Gaps are only perceived from a perspective of an artificial
> > > ordering system -- who knows where the data actually is in memory or
> > > on disk.
> > >
> > > However, when I'm working with a flat dB and want to step through the
> > > records to do editing, I like the records to be in order based upon
> > > an "id" (i.e., Record 1, Record 2, Record 3, and so on). I use an
> > > auto_increment unique "id" for this.
> > >
> > > It's not a big problem for me to keep the records in order either.
> > > Whenever I delete a record, I simply follow with:
> > >
> > > $dbQuery = "ALTER TABLE $dbtable ";
> > > $dbQuery .= "DROP id, ";
> > > $dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
> > > $dbQuery .= "AUTO_INCREMENT = 1";
> > > $result = mysql_query($dbQuery) or die("2. Could not renumber dB
> > > $dbQuery" . mysql_error());
> >
> > *LOL* I knew those MySQL people shouldn't have made the ALTER TABLE
> > syntax available to just anyone. Gun --> foot --> *BLAM*. I hope to God
> > you never get your hands on a real database with millions of entries.
> >
> > Cheers,
> > Rob.
> > --
> > ..
> > | InterJinn Application Framework - http://www.interjinn.com |
> > ::
> > | An application and templating framework for PHP. Boasting |
> > | a powerful, scalable system for accessing system services |
> > | such as forms, properties, sessions, and caches. InterJinn |
> > | also provides an extremely flexible architecture for |
> > | creating re-usable components quickly and easily. |
> > `'
> >
>
> my reasoning for needing the users number in a database is this...
>
> i am going to be doing a lottery type thing where i grab a random number
> between 1 and the result of mysql_num_rows($result)... that is the reason
> the gaps matter. the while loop didn't work for me so if anyone could
help
> me out on how to get this number i would aprreaciate it. thank you in
> advance.
here is what i tried..
while ($d = mysql_fetch_array($query)) {
$i = 0;
while ($d[username] != $user) {
$i++
}
}
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
- Original Message -
From: "Robert Cummings" <[EMAIL PROTECTED]>
To: "tedd" <[EMAIL PROTECTED]>
Cc: "PHP-General" ; "benifactor"
<[EMAIL PROTECTED]>; "Murray @ PlanetThoughtful" <[EMAIL PROTECTED]>;
"Anthony Ettinger" <[EMAIL PROTECTED]>
Sent: Saturday, March 04, 2006 9:41 AM
Subject: Re: [PHP] Mysql Rows
> On Sat, 2006-03-04 at 09:14, tedd wrote:
> > planetthoughtful wrote:
> >
> > >But, too often I've seen people new to database design not liking
> > >'gaps' because 'user1' will have a unique id of '1', while 'user2'
> > >will have a unique id of '6' because the records associated with
> > >unique ids '2' through '5' were deleted during testing, and so on.
> > >So, they feel that 'user2' should have a unique id of '2', ignoring
> > >the fact that that's not a unique id at all, if you had id '2'
> > >associated with another record at some point.
> >
> > And, Anthony wrote:
> >
> > >I remember the days where i'd
> > >clear a database after testing to keep the auto_increment inline, but
> > >eventually, you will get out of sync on that, so it's not a reliable
way of
> > >keeping a numerical sequence.
> >
> > Well... I'm one of those people who don't like gaps. I understand
> > that if the dB is relational, then you shouldn't be concerned about
> > gaps. Gaps are only perceived from a perspective of an artificial
> > ordering system -- who knows where the data actually is in memory or
> > on disk.
> >
> > However, when I'm working with a flat dB and want to step through the
> > records to do editing, I like the records to be in order based upon
> > an "id" (i.e., Record 1, Record 2, Record 3, and so on). I use an
> > auto_increment unique "id" for this.
> >
> > It's not a big problem for me to keep the records in order either.
> > Whenever I delete a record, I simply follow with:
> >
> > $dbQuery = "ALTER TABLE $dbtable ";
> > $dbQuery .= "DROP id, ";
> > $dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
> > $dbQuery .= "AUTO_INCREMENT = 1";
> > $result = mysql_query($dbQuery) or die("2. Could not renumber dB
> > $dbQuery" . mysql_error());
>
> *LOL* I knew those MySQL people shouldn't have made the ALTER TABLE
> syntax available to just anyone. Gun --> foot --> *BLAM*. I hope to God
> you never get your hands on a real database with millions of entries.
>
> Cheers,
> Rob.
> --
> ..
> | InterJinn Application Framework - http://www.interjinn.com |
> ::
> | An application and templating framework for PHP. Boasting |
> | a powerful, scalable system for accessing system services |
> | such as forms, properties, sessions, and caches. InterJinn |
> | also provides an extremely flexible architecture for |
> | creating re-usable components quickly and easily. |
> `'
>
my reasoning for needing the users number in a database is this...
i am going to be doing a lottery type thing where i grab a random number
between 1 and the result of mysql_num_rows($result)... that is the reason
the gaps matter. the while loop didn't work for me so if anyone could help
me out on how to get this number i would aprreaciate it. thank you in
advance.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
On Sat, 2006-03-04 at 09:14, tedd wrote:
> planetthoughtful wrote:
>
> >But, too often I've seen people new to database design not liking
> >'gaps' because 'user1' will have a unique id of '1', while 'user2'
> >will have a unique id of '6' because the records associated with
> >unique ids '2' through '5' were deleted during testing, and so on.
> >So, they feel that 'user2' should have a unique id of '2', ignoring
> >the fact that that's not a unique id at all, if you had id '2'
> >associated with another record at some point.
>
> And, Anthony wrote:
>
> >I remember the days where i'd
> >clear a database after testing to keep the auto_increment inline, but
> >eventually, you will get out of sync on that, so it's not a reliable way of
> >keeping a numerical sequence.
>
> Well... I'm one of those people who don't like gaps. I understand
> that if the dB is relational, then you shouldn't be concerned about
> gaps. Gaps are only perceived from a perspective of an artificial
> ordering system -- who knows where the data actually is in memory or
> on disk.
>
> However, when I'm working with a flat dB and want to step through the
> records to do editing, I like the records to be in order based upon
> an "id" (i.e., Record 1, Record 2, Record 3, and so on). I use an
> auto_increment unique "id" for this.
>
> It's not a big problem for me to keep the records in order either.
> Whenever I delete a record, I simply follow with:
>
> $dbQuery = "ALTER TABLE $dbtable ";
> $dbQuery .= "DROP id, ";
> $dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
> $dbQuery .= "AUTO_INCREMENT = 1";
> $result = mysql_query($dbQuery) or die("2. Could not renumber dB
> $dbQuery" . mysql_error());
*LOL* I knew those MySQL people shouldn't have made the ALTER TABLE
syntax available to just anyone. Gun --> foot --> *BLAM*. I hope to God
you never get your hands on a real database with millions of entries.
Cheers,
Rob.
--
..
| InterJinn Application Framework - http://www.interjinn.com |
::
| An application and templating framework for PHP. Boasting |
| a powerful, scalable system for accessing system services |
| such as forms, properties, sessions, and caches. InterJinn |
| also provides an extremely flexible architecture for |
| creating re-usable components quickly and easily. |
`'
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
- Original Message -
From: "tedd" <[EMAIL PROTECTED]>
To:
Cc: "benifactor" <[EMAIL PROTECTED]>; "Murray @ PlanetThoughtful"
<[EMAIL PROTECTED]>; "Anthony Ettinger" <[EMAIL PROTECTED]>
Sent: Saturday, March 04, 2006 3:14 PM
Subject: Re: [PHP] Mysql Rows
planetthoughtful wrote:
But, too often I've seen people new to database design not liking 'gaps'
because 'user1' will have a unique id of '1', while 'user2' will have a
unique id of '6' because the records associated with unique ids '2'
through '5' were deleted during testing, and so on. So, they feel that
'user2' should have a unique id of '2', ignoring the fact that that's not
a unique id at all, if you had id '2' associated with another record at
some point.
And, Anthony wrote:
I remember the days where i'd
clear a database after testing to keep the auto_increment inline, but
eventually, you will get out of sync on that, so it's not a reliable way
of
keeping a numerical sequence.
Well... I'm one of those people who don't like gaps. I understand that if
the dB is relational, then you shouldn't be concerned about gaps. Gaps are
only perceived from a perspective of an artificial ordering system -- who
knows where the data actually is in memory or on disk.
However, when I'm working with a flat dB and want to step through the
records to do editing, I like the records to be in order based upon an
"id" (i.e., Record 1, Record 2, Record 3, and so on). I use an
auto_increment unique "id" for this.
It's not a big problem for me to keep the records in order either.
Whenever I delete a record, I simply follow with:
$dbQuery = "ALTER TABLE $dbtable ";
$dbQuery .= "DROP id, ";
$dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
$dbQuery .= "AUTO_INCREMENT = 1";
$result = mysql_query($dbQuery) or die("2. Could not renumber dB $dbQuery"
. mysql_error());
and my dB is in order and all things are right with the world again. I'm
simple-minded that way.
Now, I'm not allowing more one than one person (namely me) the ability to
delete and reorder things, so I don't think there are any problems. Of
course I could lock down the tables, delete, and then do the reorder if
the dB is online -- but I haven't encountered any problems thus far.
I've read numerous dB books about why it isn't necessary to reorder and
everyone deplores the action, which is only done by newbies. But I don't
really understand, with a flat dB, as to why it's a bad idea to do this?
Now, is there a problem with the way I'm doing this? If so, *please*
enlighten me. Please tell me why this isn't a reliable way of keeping a
numerical sequence AND what technique would be?
Many thanks.
tedd
--
http://sperling.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Hi Tedd!
Generally speaking about db's it's not a good pratice to do in that you
describe, but as I understand you've already figured that out...
But one thing not doing "your" way is lack of performance:
I'll quote you:
"not a big problem for me to keep the records in order either.
Whenever I delete a record, I simply follow with:
$dbQuery = "ALTER TABLE $dbtable ";
$dbQuery .= "DROP id, ";
$dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
$dbQuery .= "AUTO_INCREMENT = 1";
$result = mysql_query($dbQuery) or die("2. Could not renumber dB $dbQuery"
. mysql_error());"
No, maybe not when it's a small db, but when you try to delete 50.000 posts
I have a strong feeling this would be very much slower then if you don't
alter table after each deletion.
/G
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
planetthoughtful wrote:
But, too often I've seen people new to database design not liking
'gaps' because 'user1' will have a unique id of '1', while 'user2'
will have a unique id of '6' because the records associated with
unique ids '2' through '5' were deleted during testing, and so on.
So, they feel that 'user2' should have a unique id of '2', ignoring
the fact that that's not a unique id at all, if you had id '2'
associated with another record at some point.
And, Anthony wrote:
I remember the days where i'd
clear a database after testing to keep the auto_increment inline, but
eventually, you will get out of sync on that, so it's not a reliable way of
keeping a numerical sequence.
Well... I'm one of those people who don't like gaps. I understand
that if the dB is relational, then you shouldn't be concerned about
gaps. Gaps are only perceived from a perspective of an artificial
ordering system -- who knows where the data actually is in memory or
on disk.
However, when I'm working with a flat dB and want to step through the
records to do editing, I like the records to be in order based upon
an "id" (i.e., Record 1, Record 2, Record 3, and so on). I use an
auto_increment unique "id" for this.
It's not a big problem for me to keep the records in order either.
Whenever I delete a record, I simply follow with:
$dbQuery = "ALTER TABLE $dbtable ";
$dbQuery .= "DROP id, ";
$dbQuery .= "ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,";
$dbQuery .= "AUTO_INCREMENT = 1";
$result = mysql_query($dbQuery) or die("2. Could not renumber dB
$dbQuery" . mysql_error());
and my dB is in order and all things are right with the world again.
I'm simple-minded that way.
Now, I'm not allowing more one than one person (namely me) the
ability to delete and reorder things, so I don't think there are any
problems. Of course I could lock down the tables, delete, and then do
the reorder if the dB is online -- but I haven't encountered any
problems thus far.
I've read numerous dB books about why it isn't necessary to reorder
and everyone deplores the action, which is only done by newbies. But
I don't really understand, with a flat dB, as to why it's a bad idea
to do this?
Now, is there a problem with the way I'm doing this? If so, *please*
enlighten me. Please tell me why this isn't a reliable way of keeping
a numerical sequence AND what technique would be?
Many thanks.
tedd
--
http://sperling.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
I hope the following will be helpful, and it is a bit of a rant ..
The row number DOES NOT MATTER and is absolutely irrelevant. MySQL is a
relational database from which information is gathered by means of
comparing fields to key values. Even if you are using an auto-incremented
primary key it serves ONLY AS A LABEL to UNIQUELY identify each row.
If you need sequential numbers, as for membership, devise a script to
manage them and recycle them, if that's what you want.
For the number of returned rows, check mysql_row_count (or similar function).
Recognize that an ORDER BY condition will change the apparent row number.
I am v. curious as to why you need to know which row is used for jimmy.
Regards - Miles
At 07:51 PM 3/3/2006, benifactor wrote:
i need to find a way to find out what number of a row is in a database...
for example:
//this is the database
Username: Chuck Password: adsasa
Username: jimmy Password: adsf
Username: stewart Password: dfds
the information i need is what row jimmy resides on..
this is what i tried:
function i_gun ($user) {
global $username;
$gun = mysql_query("select * from users");
while ($d = mysql_fetch_array($gun)) {
while($d[username] != $user) {
$i = $i + 1;
}
}
}
but it always returns 1. can sombody tell me what i am doing wrong or
point me in the right direction in the manual? plase and thank you
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 3/3/2006
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
On 4/03/2006 5:36 PM, Anthony Ettinger wrote: Yep, that's one good reason among many for using unique ids. Thinking a little about the OP's question, I could understand row order being relevant in certain situations where you wanted to display something like, "You were the 432nd person to register at our site!", etc. I'd do this with a timestamp, and then sorting by date and doing a count() on the results. But then again that's just me. I remember the days where i'd clear a database after testing to keep the auto_increment inline, but eventually, you will get out of sync on that, so it's not a reliable way of keeping a numerical sequence. Right, or you could just as easily do the same sort and WHERE clause by your unique id, but even just doing a count, you're interested in the fact that the record is at position in that recordset, so 'row order' is relevant and, in that context, meaningful. Much warmth, planetthoughtful --- "Lost in thought" http://www.planetthoughtful.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
On 3/3/06, Murray @ PlanetThoughtful <[EMAIL PROTECTED]> wrote: > > > > >> I have to agree with Anthony - why are you using row order to determine > >> something relating to users? I couldn't follow your brief explanation > >> above, and the fact that you're doing it sets off some soft alarm bells > >> about the design of your application. Why is it important that there > >> shouldn't be any 'gaps' between users? Because you want to know how > many > >> users there are? If so, simply do a SELECT COUNT(*) on the table > >> whenever / wherever you need to know. > >> > >> If you're using it for IDs for the users, it's generally a bad idea to > >> reuse this type of information. If you have some other purpose, I'm > >> extremely curious about what it might be. > >> > > > > > > What I was getting at is you get the unique id for the username (if > > you allow username changes, then you want a unique key to do your > > joins on from other tables). > > > > > > Yep, that's one good reason among many for using unique ids. Thinking a > little about the OP's question, I could understand row order being > relevant in certain situations where you wanted to display something > like, "You were the 432nd person to register at our site!", etc. I'd do this with a timestamp, and then sorting by date and doing a count() on the results. But then again that's just me. I remember the days where i'd clear a database after testing to keep the auto_increment inline, but eventually, you will get out of sync on that, so it's not a reliable way of keeping a numerical sequence. But, too often I've seen people new to database design not liking 'gaps' > because 'user1' will have a unique id of '1', while 'user2' will have a > unique id of '6' because the records associated with unique ids '2' > through '5' were deleted during testing, and so on. So, they feel that > 'user2' should have a unique id of '2', ignoring the fact that that's > not a unique id at all, if you had id '2' associated with another record > at some point. > > I'm not suggesting this is what the OP is doing, just that that's why I > was curious about the purpose. > > Much warmth, > > planetthoughtful > --- > "Lost in thought" > http://www.planetthoughtful.org > > -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html
Re: [PHP] Mysql Rows
I have to agree with Anthony - why are you using row order to determine something relating to users? I couldn't follow your brief explanation above, and the fact that you're doing it sets off some soft alarm bells about the design of your application. Why is it important that there shouldn't be any 'gaps' between users? Because you want to know how many users there are? If so, simply do a SELECT COUNT(*) on the table whenever / wherever you need to know. If you're using it for IDs for the users, it's generally a bad idea to reuse this type of information. If you have some other purpose, I'm extremely curious about what it might be. What I was getting at is you get the unique id for the username (if you allow username changes, then you want a unique key to do your joins on from other tables). Yep, that's one good reason among many for using unique ids. Thinking a little about the OP's question, I could understand row order being relevant in certain situations where you wanted to display something like, "You were the 432nd person to register at our site!", etc. But, too often I've seen people new to database design not liking 'gaps' because 'user1' will have a unique id of '1', while 'user2' will have a unique id of '6' because the records associated with unique ids '2' through '5' were deleted during testing, and so on. So, they feel that 'user2' should have a unique id of '2', ignoring the fact that that's not a unique id at all, if you had id '2' associated with another record at some point. I'm not suggesting this is what the OP is doing, just that that's why I was curious about the purpose. Much warmth, planetthoughtful --- "Lost in thought" http://www.planetthoughtful.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
On 3/3/06, Murray @ PlanetThoughtful <[EMAIL PROTECTED]> wrote: > On 4/03/2006 2:49 PM, benifactor wrote: > > thank you. the table does have and id feild that auto increments, however if > > you delete a user there will be a gap between the users between which would > > not be what is not acurate enough. thank you for you help. simple fix. i > > should have caught it. > > - Original Message - > > From: "Anthony Ettinger" <[EMAIL PROTECTED]> > > To: "benifactor" <[EMAIL PROTECTED]> > > Cc: "php" > > Sent: Friday, March 03, 2006 3:52 PM > > Subject: Re: [PHP] Mysql Rows > > > > > > define $1 = 0 outside your loop. > > > > i'm curious why you are relying on row-order in the database? > > Typically you'd have a PRIMARY KEY auto_increment for something like > > this. > > > > > > I have to agree with Anthony - why are you using row order to determine > something relating to users? I couldn't follow your brief explanation > above, and the fact that you're doing it sets off some soft alarm bells > about the design of your application. Why is it important that there > shouldn't be any 'gaps' between users? Because you want to know how many > users there are? If so, simply do a SELECT COUNT(*) on the table > whenever / wherever you need to know. > > If you're using it for IDs for the users, it's generally a bad idea to > reuse this type of information. If you have some other purpose, I'm > extremely curious about what it might be. > > Much warmth, > > planetthoughtful > --- > "Lost in thought" > http://www.planetthoughtful.org > > What I was getting at is you get the unique id for the username (if you allow username changes, then you want a unique key to do your joins on from other tables). -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
On 4/03/2006 2:49 PM, benifactor wrote: thank you. the table does have and id feild that auto increments, however if you delete a user there will be a gap between the users between which would not be what is not acurate enough. thank you for you help. simple fix. i should have caught it. - Original Message - From: "Anthony Ettinger" <[EMAIL PROTECTED]> To: "benifactor" <[EMAIL PROTECTED]> Cc: "php" Sent: Friday, March 03, 2006 3:52 PM Subject: Re: [PHP] Mysql Rows define $1 = 0 outside your loop. i'm curious why you are relying on row-order in the database? Typically you'd have a PRIMARY KEY auto_increment for something like this. I have to agree with Anthony - why are you using row order to determine something relating to users? I couldn't follow your brief explanation above, and the fact that you're doing it sets off some soft alarm bells about the design of your application. Why is it important that there shouldn't be any 'gaps' between users? Because you want to know how many users there are? If so, simply do a SELECT COUNT(*) on the table whenever / wherever you need to know. If you're using it for IDs for the users, it's generally a bad idea to reuse this type of information. If you have some other purpose, I'm extremely curious about what it might be. Much warmth, planetthoughtful --- "Lost in thought" http://www.planetthoughtful.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
thank you. the table does have and id feild that auto increments, however if
you delete a user there will be a gap between the users between which would
not be what is not acurate enough. thank you for you help. simple fix. i
should have caught it.
- Original Message -
From: "Anthony Ettinger" <[EMAIL PROTECTED]>
To: "benifactor" <[EMAIL PROTECTED]>
Cc: "php"
Sent: Friday, March 03, 2006 3:52 PM
Subject: Re: [PHP] Mysql Rows
define $1 = 0 outside your loop.
i'm curious why you are relying on row-order in the database?
Typically you'd have a PRIMARY KEY auto_increment for something like
this.
On 3/3/06, benifactor <[EMAIL PROTECTED]> wrote:
> i need to find a way to find out what number of a row is in a database...
>
> for example:
>
> //this is the database
> Username: Chuck Password: adsasa
> Username: jimmy Password: adsf
> Username: stewart Password: dfds
>
> the information i need is what row jimmy resides on..
>
> this is what i tried:
>
> function i_gun ($user) {
> global $username;
> $gun = mysql_query("select * from users");
> while ($d = mysql_fetch_array($gun)) {
> while($d[username] != $user) {
> $i = $i + 1;
> }
> }
> }
>
> but it always returns 1. can sombody tell me what i am doing wrong or
point me in the right direction in the manual? plase and thank you
>
--
Anthony Ettinger
Signature: http://chovy.dyndns.org/hcard.html
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Rows
define $1 = 0 outside your loop.
i'm curious why you are relying on row-order in the database?
Typically you'd have a PRIMARY KEY auto_increment for something like
this.
On 3/3/06, benifactor <[EMAIL PROTECTED]> wrote:
> i need to find a way to find out what number of a row is in a database...
>
> for example:
>
> //this is the database
> Username: Chuck Password: adsasa
> Username: jimmy Password: adsf
> Username: stewart Password: dfds
>
> the information i need is what row jimmy resides on..
>
> this is what i tried:
>
> function i_gun ($user) {
> global $username;
> $gun = mysql_query("select * from users");
> while ($d = mysql_fetch_array($gun)) {
> while($d[username] != $user) {
> $i = $i + 1;
> }
> }
> }
>
> but it always returns 1. can sombody tell me what i am doing wrong or point
> me in the right direction in the manual? plase and thank you
>
--
Anthony Ettinger
Signature: http://chovy.dyndns.org/hcard.html
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL: Rows and columns
On Wed, 18 Jun 2003, zavaboy wrote: > I know this is more of a mySQL question but, how do I delete and add rows > and columns? Before you touch MySQL or attempt to access it through PHP, you really should learn basic SQL: http://www.sqlcourse.com/ http://www.w3schools.com/sql/ http://linux.oreillynet.com/pub/ct/19 Spend some time learning basic SQL syntax and your life will drastically improve. Also, perhaps the most popular tool on this subject is phpmyadmin, it shows the actual queries as you go which also helps with learning: http://www.phpmyadmin.net/ Be sure to keep it secure though as it's a powerful tool. Regards, Philip -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mySQL: Rows and columns
how about : Start Using MySQL http://articles.weberdev.com/ViewArticle.php3?ArticleID=247 Beginners guide to PHP/MySQL - Creating a simple guest book http://articles.weberdev.com/ViewArticle.php3?ArticleID=26 Sincerely berber Visit http://www.weberdev.com/ Today!!! To see where PHP might take you tomorrow. -Original Message- From: zavaboy [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 7:03 PM To: [EMAIL PROTECTED] Subject: [PHP] mySQL: Rows and columns I know this is more of a mySQL question but, how do I delete and add rows and columns? -- - Zavaboy [EMAIL PROTECTED] www.zavaboy.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL: Rows and columns
On Wed, 2003-06-18 at 10:03, zavaboy wrote: > I know this is more of a mySQL question but, how do I delete and add rows > and columns? > > -- > > - Zavaboy > [EMAIL PROTECTED] > www.zavaboy.com Using the mysql_*() function and the SQL language. The mysql_*() functions are documented here: http://www.php.net/mysql You can find a PHP/SQL tutorial here: http://phpdev.gold.ac.uk/tutorial/sql/ There are likely a bunch more on Google. Good luck, Torben -- Torben Wilson <[EMAIL PROTECTED]>+1.604.709.0506 http://www.thebuttlesschaps.com http://www.inflatableeye.com http://www.hybrid17.com http://www.themainonmain.com - Boycott Starbucks! http://www.haidabuckscafe.com - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL: Rows and columns
http://www.mysql.com/doc/en/ALTER_TABLE.html > I know this is more of a mySQL question but, how do I delete and add rows > and columns? > > -- > > - Zavaboy > [EMAIL PROTECTED] > www.zavaboy.com > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mySQL: Rows and columns
A search on mysql.com reveals. http://www.mysql.com/search/index.php?q=DELETE&from=%2Fdocumentation%2Findex .html -- Aaron Gould Web Developer Parts Canada -Original Message- From: zavaboy [mailto:[EMAIL PROTECTED] Sent: June 18, 2003 1:03 PM To: [EMAIL PROTECTED] Subject: [PHP] mySQL: Rows and columns I know this is more of a mySQL question but, how do I delete and add rows and columns? -- - Zavaboy [EMAIL PROTECTED] www.zavaboy.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
