Professional Web Applications Themes

Four questions - MySQL

After using SQL for a few months I am very satisfied. I have a few teething problems that are still causing me headaches. 1 I have a table in which I need to change a value in a field (from null to "done") for the first 765 posts. I tried selecting the posts in phpadmin, but the edit button only allows me to change one post at a time. Is there a simple way to do this using phpadmin or do I need to write code to do this? 2 What user interface for MySQL do most people use? So ...

  1. #1

    Default Four questions

    After using SQL for a few months I am very satisfied. I have a few teething
    problems that are still causing me headaches.

    1
    I have a table in which I need to change a value in a field (from null to
    "done") for the first 765 posts. I tried selecting the posts in phpadmin,
    but the edit button only allows me to change one post at a time. Is there a
    simple way to do this using phpadmin or do I need to write code to do this?

    2
    What user interface for MySQL do most people use? So far I have stuck with
    the phpadmin that is provided by my web hosting supplier. Maybe there is an
    interface that allows me to do the above block edit?

    3

    I would like to time and date stamp the user entries I collect from users on
    my website. Is there an internal function for this? If not then how I do I
    enter these along with the user data?

    4
    When a user clicks enter after entering data on the user form I would like
    to present him/her with a unique index numer which they can then quote to me
    when inquring about their entry. Obviously I need to enter this number into
    the table as well. However I can not read in the highest number from the
    field and "add 1" because two users may be entering data at the same time. I
    need to create this number at execution time, and show it to the user when
    the form has been processed on the "confirm" page I display. (I know I can
    fake this effect using a "page hit" counter when displaying the form but
    thats probably not the best way to do this.) Any tips on the best way to
    create a unique number?

    Thanks for any answers that you are able to help me with.

    Garry Jones
    Sweden


    Garry Jones Guest

  2. #2

    Default Re: Four questions

    > 1
    > I have a table in which I need to change a value in a field (from null to
    > "done") for the first 765 posts.
    UPDATE table SET field = 'Done' WHERE ISNULL(field) LIMIT 765;

    Leave off the LIMIT clause if you want to change all occurrences of
    'NULL'.

    Also, consider creating a new table of statuses(status*)
    > 2
    > What user interface for MySQL do most people use?
    I use PHPMyAdmin,textpad and the command prompt. Navicat's also
    popular.
    > 3
    >
    > I would like to time and date stamp the user entries I collect from users on
    > my website. Is there an internal function for this?
    Er, 'timestamp' !?!
    >
    > 4
    > When a user clicks enter after entering data on the user form I would like
    > to present him/her with a unique index numer which they can then quote to me
    > when inquring about their entry. Obviously I need to enter this number into
    > the table as well. However I can not read in the highest number from the
    > field and "add 1" because two users may be entering data at the same time. I
    > need to create this number at execution time, and show it to the user when
    > the form has been processed on the "confirm" page I display. (I know I can
    > fake this effect using a "page hit" counter when displaying the form but
    > thats probably not the best way to do this.) Any tips on the best way to
    > create a unique number?
    >
    Well, 3 out of 4's not bad

    strawberry Guest

  3. #3

    Default Re: Four questions

    strawberry wrote:
    >> 1
    >> I have a table in which I need to change a value in a field (from null to
    >> "done") for the first 765 posts.
    >
    > UPDATE table SET field = 'Done' WHERE ISNULL(field) LIMIT 765;
    >
    > Leave off the LIMIT clause if you want to change all occurrences of
    > 'NULL'.
    >
    > Also, consider creating a new table of statuses(status*)
    That was BAD ADVICE.

    Using LIMIT without a ORDER BY clause will result in unpredictable order. Ergo, 765 possibly random records will be updated.

    In relational databases there is no such thing as "the first X records". You must use a specific WHERE clause to
    identify your records. If there is no way of doing so, your data structure should probably need some review.

    Ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  4. #4

    Default Re: Four questions


    Giuseppe Maxia wrote:
    > strawberry wrote:
    > >> 1
    > >> I have a table in which I need to change a value in a field (from null to
    > >> "done") for the first 765 posts.
    > >
    > > UPDATE table SET field = 'Done' WHERE ISNULL(field) LIMIT 765;
    > >
    > > Leave off the LIMIT clause if you want to change all occurrences of
    > > 'NULL'.
    > >
    > > Also, consider creating a new table of statuses(status*)
    >
    > That was BAD ADVICE.
    >
    > Using LIMIT without a ORDER BY clause will result in unpredictable order. Ergo, 765 possibly random records will be updated.
    >
    > In relational databases there is no such thing as "the first X records". You must use a specific WHERE clause to
    > identify your records. If there is no way of doing so, your data structure should probably need some review.
    >
    > Ciao
    > gmax
    >
    > --
    > _ _ _ _
    > (_|| | |(_|>< The Data Charmer
    > _|
    > [url]http://datacharmer.org/[/url]
    Oops, that's right. There should be an ORDER BY clause!

    (although I'd a bet you a beer that the right records would get updated
    anyway!)

    strawberry Guest

  5. #5

    Default Re: Four questions

    strawberry wrote:
    [SNIP]
    >
    > Oops, that's right. There should be an ORDER BY clause!
    >
    > (although I'd a bet you a beer that the right records would get updated
    > anyway!)
    >
    I would gladly bet a beer. And I grant it that you may even win in most cases.
    But would you bet your job or your business on it?

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  6. #6

    Default Re: Four questions


    Giuseppe Maxia wrote:
    > strawberry wrote:
    > [SNIP]
    > >
    > > Oops, that's right. There should be an ORDER BY clause!
    > >
    > > (although I'd a bet you a beer that the right records would get updated
    > > anyway!)
    > >
    >
    > I would gladly bet a beer. And I grant it that you may even win in most cases.
    > But would you bet your job or your business on it?
    >
    > ciao
    > gmax
    >
    > --
    > _ _ _ _
    > (_|| | |(_|>< The Data Charmer
    > _|
    > [url]http://datacharmer.org/[/url]
    Fair enough! Now, I have a question for you Giuseppe!

    The other day I tried, without success, to implement (well copy/paste)
    your snippets for rapidly populating tables. What am I doing wrong?

    mysql Ver 14.12 Distrib 5.0.15, for Win32 (ia32)

    strawberry Guest

  7. #7

    Default Re: Four questions

    Garry Jones wrote:
    > After using SQL for a few months I am very satisfied. I have a few teething
    > problems that are still causing me headaches.
    >
    > 1
    > I have a table in which I need to change a value in a field (from null to
    > "done") for the first 765 posts. I tried selecting the posts in phpadmin,
    > but the edit button only allows me to change one post at a time. Is there a
    > simple way to do this using phpadmin or do I need to write code to do this?
    >
    No good way. As others have indicated, rows in a SQL database are
    unordered. Without an "ORDER BY" clause, there is no such thing as
    "first" or "last" rows.

    If I were in your position, I would write a quick PHP web page to
    display say 50 rows at a time with a checkbox for each one. Check the
    box (or even add some javascript to check all of them on the page) and
    submit the page. Those with the checkboxes checked get marked done.
    > 2
    > What user interface for MySQL do most people use? So far I have stuck with
    > the phpadmin that is provided by my web hosting supplier. Maybe there is an
    > interface that allows me to do the above block edit?
    >
    I mainly use phpMyAdmin for quick stuff. Fancy stuff I usually end up
    writing a quick page like above.
    > 3
    >
    > I would like to time and date stamp the user entries I collect from users on
    > my website. Is there an internal function for this? If not then how I do I
    > enter these along with the user data?
    >
    Create a filed with type TIMESTAMP with a default, i.e.

    ALTER TABLE mytable ADD addtime TIMESTAMP NOT NULL DEFAULT
    CURRENT_TIMESTAMP ;
    > 4
    > When a user clicks enter after entering data on the user form I would like
    > to present him/her with a unique index numer which they can then quote to me
    > when inquring about their entry. Obviously I need to enter this number into
    > the table as well. However I can not read in the highest number from the
    > field and "add 1" because two users may be entering data at the same time. I
    > need to create this number at execution time, and show it to the user when
    > the form has been processed on the "confirm" page I display. (I know I can
    > fake this effect using a "page hit" counter when displaying the form but
    > thats probably not the best way to do this.) Any tips on the best way to
    > create a unique number?
    >
    Create an autoincrement column in the table. When adding a new row, do
    NOT put anything in this column (don't specify it in your INSERT statement).

    After inserting, call mysql_insert_id() to fetch the value placed in the
    autoincrement column.
    > Thanks for any answers that you are able to help me with.
    >
    > Garry Jones
    > Sweden
    >
    >

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  8. #8

    Default Re: Four questions

    Jerry Stuckle wrote:
    > Garry Jones wrote:
    >> I have a table in which I need to change a value in a field (from null
    >> to "done") for the first 765 posts. I tried selecting the posts in
    >> phpadmin, but the edit button only allows me to change one post at a
    >> time. Is there a simple way to do this using phpadmin or do I need to
    >> write code to do this?
    Other people have alluded to this solution, but I thought it would be
    helpful to have it in one place:

    UPDATE tablename
    SET field = 'done'
    ORDER BY expression
    LIMIT 765;

    Where "expression" is some column or calculation whose order makes the
    first 765 rows be the ones you want to update.
    >> 2
    >> What user interface for MySQL do most people use? So far I have stuck
    >> with the phpadmin that is provided by my web hosting supplier. Maybe
    >> there is an interface that allows me to do the above block edit?
    I use MySQL Query Browser, Administrator, and Workbench (GUI tools
    provided by MySQL.com) or else the command-line mysql shell.

    You may be stuck with phpMyAdmin. If you're using MySQL on a hosted
    service, instead of within your own network, it's likely that the
    hosting company has blocked connections to MySQL from outside their
    firewall. So your web apps can connect to MySQL because they're living
    on the hosting service's machines. But you cannot connect to MySQL from
    MySQL Query Browser or Navicat or other clients running on your own PC.

    However, phpMyAdmin has a lot of features. When I use phpMyAdmin on my
    hosted sites, I often use the "SQL" tab, which allows me to enter any
    SQL statement that isn't easily achieved by using the GUI.
    > Create an autoincrement column in the table. When adding a new row, do
    > NOT put anything in this column (don't specify it in your INSERT
    > statement).
    FWIW, one can also specify a NULL or the value 0 for an auto-increment
    column; either of these causes it to generate a new id value. So the
    following two statements would have the same effect:

    INSERT INTO tablename (autoIncColumn, dataColumn) VALUES (0, 42);
    INSERT INTO tablename (autoIncColumn, dataColumn) VALUES (NULL, 42);
    INSERT INTO tablename (dataColumn) VALUES (42);

    It should be pointed out that auto-increment handles the case of
    multiple clients just fine. The last_insert_id() function only gives
    the last id created in the current session. So if some other client in
    another session inserts their record simultaneously, they will get a
    different value back from last_insert_id(), and your session will get
    the correct one.

    Read more about auto-increment here:
    [url]http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/create-table.html[/url]

    And last_insert_id() here:
    [url]http://dev.mysql.com/doc/refman/5.0/en/information-functions.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. Two questions, for those who may know
    By RichA in forum Photography
    Replies: 4
    Last Post: December 9th, 04:57 AM
  2. 2 questions.
    By usegobos webforumsuser@macromedia.com in forum Macromedia Fireworks
    Replies: 1
    Last Post: October 5th, 07:30 PM
  3. a few questions
    By Jaxx in forum Macromedia Director Lingo
    Replies: 4
    Last Post: October 3rd, 12:12 PM
  4. IIS Log Questions.
    By Shawn & Jo-an Mehaffie in forum ASP.NET General
    Replies: 0
    Last Post: August 5th, 04:13 AM
  5. 2 questions :) - portal questions
    By John Weinshel in forum FileMaker
    Replies: 3
    Last Post: July 14th, 04:37 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139