SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wink Count the number of scripts with reference number

    Hi,


    Currently I am dealing with a sys that track manuscripts. For every manuscript submitted, an auto increment id will assigned. If manuscript is accepted to be published, a reference number will be given.

    How do I count the the number of manuscripts(in the database) already with reference number(accepted scripts) and then add one to this number.


    $sql_max = "SELECT COUNT(*) FROM MANUSCRIPT_TABLE where ref_no != '' ";

    $result = mysql_query($sql_max) or die("SELECT Error: ".mysql_error());



    How do I get the count number in this case? I would want to add one to this number.

    Any advise is most appreciated.

    Thanks and good day!
    Regards,
    Junk

    I am never more keen to learn...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    why do you want to add 1 to the reference number?

    what's the difference between the auto_increment id and the reference number?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    $sql_max = "
    SELECT 
            (COUNT(*)  + 1) AS total
    FROM 
            MANUSCRIPT_TABLE 
    WHERE
            ref_no <> '' 
    ";
    
    $result = mysql_fetch_array(mysql_query($sql_max)) or die("SELECT Error: ".mysql_error());
    
    echo $result['total'];
    Should do the job for you

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    but why????
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Dean C,

    Thanks for your fast reply. I have tried your codes, but

    echo $result['total'];

    output nothing.

    May I know what is wrong?

    What if I would like to choose the max reference number and add one to this number.

    How can it be done?

    Thanks a million!
    Regards,
    Junk

    I am never more keen to learn...

  6. #6
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,

    I need to get the current largest reference number, so that when I insert the next manuscript, I can add one to the maximum reference. Increment in reference number, basically. But reference number starting at 10000.

    Id and reference number not the same because id is for all submitted manuscripts while reference numbers are for the manuscripts selected to be published.

    Please advise.

    Thanks!
    Regards,
    Junk

    I am never more keen to learn...

  7. #7
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT 
            (MAX(ref_no) + 1) AS nextid
    FROM
            manuscript_table
    That'll give you the next ID to insert. How you get it with your script is up to you However, by the sounds of things your tables are very poorly designed. You might want to think about having a little reshuffle and normalize them.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    junk, i could get into a big long discussion, but you may not have the time (nor the interest)

    let me just caution you that having a separate auto_incrementing id together with a reference number that you assign by adding 1 to the highest existing number is a strategy with more processing overhead than necessary and a huge risk of losing data integrity (which risk might, however, be acceptable if you add only a manuscript or two a year, because then chances are you will not get what's called a "race condition")

    here's a suggestion: start your auto_increment at 10050 and increment it in steps of 175

    this will generate a nice-looking number, and then when you decide a particular submitted manuscript is going to be published, you update a field (called, perhaps, isPublished or Accepted) for that manuscript

    kills both birds with one stone, and trust me, any solution which avoids code altogether is faster than a solution which involves code, no matter how efficient (and code to avoid a race condition is never efficient)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, I think your advice is sound, but for a couple of points:

    1) The extra overhead of running a refernce id and an auto incrementing id shouldn't add *considerably* to the processing overhead of the site.

    2) I think there is a golden rule in db design that you have broken in suggesting that the auto-inc_id and the reference_id be combined. That rule is that the auto-inc_id should not be part of the business data. In the present application, I suspect the reference_id may be used for more than just linking tables in the database, it may well end up on the inside cover of a printed copy, or have other uses. I have been thinking of it like an ISBN number on books. One shouldn't use the ISBN of a book as the ID field in a database.

    Your comment on the "race condition" is well made, though.

    Without knowing the full details of the application, I would be tempted to use two tables to keep track of the manuscript. One for manuscript submissions and one to keep data about the published manuscripts. When a manuscript gets published the data can simply be transferred from one table to the other with some PHP to create the new reference id based on whatever business rules are applicable.

    Food for thought?

    Mike

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by MikeBigg
    The extra overhead of running a refernce id and an auto incrementing id shouldn't add *considerably* to the processing overhead of the site.
    true, but why add processing if the zero-processing solution is as good or better?

    One shouldn't use the ISBN of a book as the ID field in a database.
    joe celko and i both disagree -- do a search for surrogate or natural key and you will find a wide range of opinions, with mainly data modellers on one side and DBAs on the other

    When a manuscript gets published the data can simply be transferred from one table to the other with some PHP to create the new reference id based on whatever business rules are applicable.
    i don't mind the two tables (although one is even better) but the manipulation of ids makes me feel ill

    natural keys are wonderful, even if they are auto_increments!!!
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937,

    I don't think the zero-processing solution is good or better I come from an embedded engineering background where every processor cycle must be accounted for. Code for the sake of code is not what I'm suggesting.

    I'm aware of the discussions on keys. I think the problem comes when (usually) some marketing bod decides that the reference ids are now going to be alpha-numeric or based on the phases of the 3rd moon of saturn or something else unrelated. Then having to change all the ids in that table and all related tables is a pain.

    Re: one table is better ... I'm not sure. I'm thinking in terms of a large database with lots of scripts submitted and a very small percentage published. (I understand this is represents real-life in the book world.) To get data on published books means trawling through a large table with the vast majority of rows being completely irrelevant.

    I think if you change your mindset from the reference number being an id to being some other string related to data outside of the database, then it becomes acceptable. As we don't know the business logic behind this application we are somewhat guessing at the best way forward, however I would suggest that my solution is less rigid and therefore offers more flexibilty in the future

    I'm yet to be convinced of natural keys

    Kind regards,

    Mike

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by MikeBigg
    ... reference ids are now going to be alpha-numeric or based on the phases of the 3rd moon of saturn or something else unrelated. Then having to change all the ids in that table and all related tables is a pain.
    that's not a design problem, that's a management problem

    if the public identifier datatype has to change, it's easy to change

    but the guy who suggests it is on shaky ground, e.g. suggesting a different reference id instead of the ISBN

    ... means trawling through a large table with the vast majority of rows being completely irrelevant.
    no index = trawling; index on isPublished = instantaneous retrieval


    I'm yet to be convinced of natural keys
    do you do any css? it's like css classes versus inline FONT tags

    you will one day see the light
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's not a design problem, that's a management problem
    Indeed ... and happy is the man who keeps the management happy

    Seriously, though. It's like using someones name as the foriegn key. They get married or divorced and you have to update all the tables that key is used in. I think that relationships between the tables should not be done using application/business data. Only data that makes sense inside the database, like autoinc fields, should be used.

    do you do any css? it's like css classes versus inline FONT tags
    I guess you mean that it is a similar mindset change to ...?

    you will one day see the light
    Maybe, but not without a good demonstration of the benefits first

    Mike
    Last edited by MikeBigg; Jan 4, 2005 at 12:24. Reason: Tidying quotes

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    comparing this example's auto id versus incremented reference number design question to the idea of using people's names as primary key is an apples and oranges comparison, and the oranges are sour

    the main problem with people's names is that they're not unique

    both the auto id and the reference number are unique, but the onus for this in the MAX()+1 strategy is clearly on the coder

    both identify the same row, so both are acceptable candidate keys

    my point is that it's better to have just one key here instead of two

    mine's neater-looking that yours, and mine is an auto_increment id, i'm just saying it's also a natural key in exactly the same way as the inventor of the ISBN can feel a sense of pride in the widespread acceptance of the ISBN as a natural key

    does that help?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    both the auto id and the reference number are unique, but the onus for this in the MAX()+1 strategy is clearly on the coder
    This is true and admittedly this is a big but.

    mine's neater-looking that yours, and mine is an auto_increment id, i'm just saying it's also a natural key in exactly the same way as the inventor of the ISBN can feel a sense of pride in the widespread acceptance of the ISBN as a natural key
    Your solution has a lot going for it: neatness, reference number generation within the insert (so no concurancy issues), easy external code so less bugs and less maintenance.

    I'm trying hard to think why I am resisting it

    The only thing in my favour is that there is more flexibility in the form that the reference number could take. The original post indicated an incrementing number was required, which your solution satifies (albeit with big jumps). I added a suggestion that alternatives to incrementing numbers may be required in the future which was not even hinted previously. If they want it differnt they can pay to have it changed!

    So, I admit your solution comes out on top.

    I don't think the natural key argument swung it for me, though. Being clear on this - it is not that I'm against natural data as indexes, it's just natural data used as a primary link between multiple tables ... specifically where that natural data may be subject to change.

    Regards,

    Mike
    ps I have enjoyed this exchange of views.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i have enjoyed this too, thanks for helping me carry this along (i sometimes have these types of conversations only with myself, so it's nice to bounce ideas around with someone who understands)

    regarding primary keys that change -- if it's individual values that must occasionally change, this is what ON UPDATE CASCADE in the foreign key is for

    if it's a wholesale domain change, well, that would be as disruptive as any domain change -- with or without surrogate keys -- so the conversion costs would be clearly need to be made known to management

    more specifically, no manager (or, how did you phrase it earlier, "marketing bod") would decide that the reference ids are going to change without a pretty good reason, and my comment on this issue is that management should invest the few minutes now to fully understand the purpose of a reference number outside the database
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I for one also enjoyed the exchange of ideas. Glad I read the thread. We need more of these and less repetitive questions where folks couldn't be bothered to search for similar question/answers if you ask me. ;-)

  18. #18
    SitePoint Zealot Junk's Avatar
    Join Date
    Dec 2004
    Location
    Asia
    Posts
    193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi all,

    really appreciate all your help and explanations. As a newbie, I benefit lots!

    Think what I do now is to review all the tables in the database and improve the structure.

    Thanks again!
    Regards,
    Junk

    I am never more keen to learn...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •