SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy design for stock items and checkin/checkout process (was "Query help")

    I'm currently in the process of trying to bodge together a simple(ish) stock system to be used on a company intranet but I've ground to a halt with a particular problem.

    Using a MySQL database I've got tables with 11 fields, including 'Quantity' (self explanatory) and BookOut. Now this BookOut field contains information on the user who has booked out an item including the users name, the date/time etc

    The problem I have is this. If an item has a quantity of 7 and a user books out 1, then his/her info is stored in BookOut but when another user books out another 1 of said item at a later stage then the previous BookOut info is overwritten.

    Is there a way in that everytime another item is booked out that a new line is created within a specific field?

    Code:
    }
    
         if(!sizeof($error)){
    
                $update_SQL = "Update joystick Set BookOut = '" . mysql_real_escape_string($BookOut) . "', PCserial = '" . mysql_real_escape_string($PCserial) . "', Quantity = Quantity - " . $Quantity . ", MachineNo = '" . mysql_real_escape_string($MachineNo) . "', Date =  now() WHERE ID = " . $ID; 
                if(! @mysql_query($update_SQL)){
    
                          $error[] = "Update SQL failed";
    
                }
    The above is the query I am currently using. I'm not totally proficient with .php and have already had a lot of help getting this far.

    Anything ideas or suggestions would be great. Thanks.

  2. #2
    SitePoint Zealot sajjad's Avatar
    Join Date
    May 2005
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think u need to ask this question in database forum

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    moved to MySQL forum

    yes, that's a right bodge there, innit

    you want a separate table for in/outs, with composite primary key of: (bookid,userid,dateout)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Haha, bodges are my speciality.

    I should have explained it a bit better. The db contains for example a table Joystick. Within this there are seperate fields, Date, BookOut, Quantity...
    Creating seperate tables each time would seem like quite a lot of additional work.


    Is it possible to use INSERT instead of UPDATE to solve it in anyway?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, not each time!! a permanent table for the items, and a permanent table for the checkouts

    primary key (itemid,userid,dateout)

    then INSERT into this 2nd table whenever an item is booked out, and UPDATE that row when it is booked back in -- this gives you a nice audit trail, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've got a seperate table for the items (BookIn) and then for the checkouts (BookOut).

    I guess I'm just having problems with my syntax at this stage regarding INSERT. If I replace UPDATE in the query posted in my first post with INSERT then it just fails to carry out the action and I'm presented with the $error.

    Sorry to sound like an idiot, this is all still new to me, thanks for your help.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    please show the CREATE TABLE statements for both tables

    and a few rows of sample data would go swimmingly...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure how to go about that, tbh.

    However here's a couple of scrncaps that might explain what I've got going on...

    Fields
    Data

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    looks like you have separate tables for each item

    what i would suggest is a common table for all items, and a common table for all bookouts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I'll see what I can come up with next...

    Thanks for all your help!

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    by the way, phpmyadmin generates the CREATE TABLE statement for you, there's an Export button at the bottom of those screencaps you posted

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •