SitePoint Sponsor

User Tag List

Page 2 of 3 FirstFirst 123 LastLast
Results 26 to 50 of 52
  1. #26
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHPJOHN... yes your amazing code is working! 50% of my solution ready to go... just still needing it to enter the database. Not yet set but soon.
    PHP Code:
    mysql_query"INSERT INTO Cust_cart
                      SET
                        posted    = '
    $posted',
                        cart_id   = '
    $cart_id',
                        cust_id   = '
    $cust_id',
                        quanitity = "
    .$_REQUEST["quanitity".$i].",
                        number    = "
    .$_REQUEST["number".$i].",
                        item      = '"
    .$_REQUEST["item".$i]."',
                        price     = "
    .$_REQUEST["price".$i] );
      } 
    I asumming that I will grab the cust_id from the cust_order database to enter in cust_cart database.
    thanks

  2. #27
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would not grab a customer number from the order table, but from the customer table... If I remember right, you have one?

    Oops... nope, you are storing that information in the Cust_Order table...

    I would separate out the customer order information and place it in a customer table like this:

    Code:
    tblCustomers:
     
    fldCustId MEDIUMINT AUTO_INCREMENT ZEROFILL PRIMARY KEY ,
    fldFirstName VARCHAR 20,
    fldLastName VARCHAR 20,
    fldPassword VARCHAR 32,
    fldBillingAddress VARCHAR 40,
    fldBillingCity VARCHAR 20,
    fldBillingState VARCHAR 2,
    fldBillingZip MEDIUMINT(5),
    fldShippingAddress VARCHAR 40,
    fldShippingCity VARCHAR 20,
    fldShippingState VARCHAR 2,
    fldShippingZip MEDIUMINT(5)
    You could even store their prefered payment method...

    The customer id would be automatically generated by mySQL and would be unique for each customer.

    Then the orders would be held in a different table and referenced to the customer by the customer id.

    The whole idea is to reduce redundant information as much as possible in your database design, so storing all of the customer information with each order is not an efficient use of the database.
    Last edited by PHP John; May 12, 2003 at 20:23.
    John

  3. #28
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    PHP Code:
      // Build the query for each item and insert it into the database
        
       
    mysql_query"INSERT INTO cust_cart
                      SET
                        posted    = '
    $posted',
                        cart_id   = '
    $cart_id',
                        cust_id   = '
    $cust_id',
                        quanitity = "
    .$_REQUEST["quantity".$i].",
                        number    = "
    .$_REQUEST["number".$i].",
                        item      = '"
    .$_REQUEST["item".$i]."',
                        price     = "
    .$_REQUEST["price".$i] ); 

    Trying to get this code to enter the values into the database but unfortunetly nothing is entered for any of the fields.... would this indicate a syntax error? Any error correcting offerings? thanks.

  4. #29
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, Mac.

    The email part is working, right?

    Replace the above code with this and post back what the echo returns:
    PHP Code:
      //  Make sure that the table name is exactly the same. ie "Cust_cart" is not the same as "cust_cart"
        
    $query "INSERT INTO Cust_cart
                    SET
                      posted    = '
    $posted',
                      cart_id   = '
    $cart_id',
                      cust_id   = '
    $cust_id',
                      quanitity = "
    .$_REQUEST["quanitity".$i].",
                      number    = "
    .$_REQUEST["number".$i].",
                      item      = '"
    .$_REQUEST["item".$i]."',
                      price     = "
    .$_REQUEST["price".$i];
        echo 
    $query.'<br />';
        
    mysql_query$query ); 
    John

  5. #30
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the response from the echo:
    INSERT INTO cust_cart SET posted = '2003-05-16', cart_id = '030516103630', cust_id = '', quantity = 1 x , number = 025237, item = ' Fringe Alphabets', price = 49.95

    The table name is cust_cart. This information did not enter the database..... thanks so much for taking time to look at this.

  6. #31
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright... would you post the data types for your fields in "cust_cart"?
    John

  7. #32
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    posted|varchar|15|not null
    cart_id|varchar|15|not null
    cust_id|varchar|15|not null
    quantity|varchar|10|not null
    number|varchar|10|not null
    item|varchar|15|not null
    price|varchar|6|not null

    thank you!

  8. #33
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MAC#1
    posted|varchar|15|not null
    cart_id|varchar|15|not null
    cust_id|varchar|15|not null
    quantity|varchar|10|not null
    number|varchar|10|not null
    item|varchar|15|not null
    price|varchar|6|not null
    Alright, Mac.

    I would change any field that will only contain integer numbers from VARCHAR to INT, or MEDIUMINT, or SMALLINT, or TINYINT, depending upon the greatest expected length of the numbers you want to store. Then any field that you expect to store non-numeric only data in I would keep as VARCHAR like you have them.

    The database processes numbers faster, and they take less storage space.

    If you choose NOT to make any modifications, use the following code:
    PHP Code:
        $query "INSERT INTO Cust_cart
                    SET
                      posted    = '
    $posted',
                      cart_id   = '
    $cart_id',
                      cust_id   = '
    $cust_id',
                      quanitity = '"
    .$_REQUEST["quanitity".$i]."',
                      number    = '"
    .$_REQUEST["number".$i]."',
                      item      = '"
    .$_REQUEST["item".$i]."',
                      price     = '"
    .$_REQUEST["price".$i]."'";
        echo 
    $query.'<br />';
        
    mysql_query$query ); 
    John

  9. #34
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHP John
    I would change any field that will only contain integer numbers from VARCHAR to INT, or MEDIUMINT, or SMALLINT, or TINYINT, depending upon the greatest expected length of the numbers you want to store. Then any field that you expect to store non-numeric only data in I would keep as VARCHAR like you have them.

    Ok, I changed all but the item field to various sized integers. But still not able to insert into database.
    ------------------------------
    I am also working on retreiving the cust_id from the customer database that gets auto-incremented earlier on in the script.
    PHP Code:
    $query " select * from customer where firstname = '$firstname' and lastname = '$lastname'";
    $result mysql_query($cust_id);
    echo 
    $query.'<br />';
        
    mysql_query$cust_id ); 
    But not sure how to get the cust_id info turned into a useable variable... everything I've tried isn't working.

  10. #35
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Post your new data types for the "cust_cart" table.
    John

  11. #36
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHP John
    Post your new data types for the "cust_cart" table.

    posted | MEDIUMINT | 15
    cart_id | MEDIUMINT | 15
    cust_id | MEDIUMINT | 15
    quantity | TINYINT | 15
    number | SMALLINT | 10
    item | VARCHAR | 25
    price | MEDIUMINT | 6

    INSERT INTO cust_cart SET posted = '2003-05-16', cart_id = '030516112601', cust_id = '', quantity = 1 x , number = 025237, item = ' Fringe Alphabets', price = 49.95

    THANKS.

  12. #37
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Look at your quanitity. This is NOT an integer. It is a string. If you are going to store it in such a manner you need to keep the "quantity" field as a VARCHAR, or if you keep it as a TINYINT, you need to do the math first before insterting it into the database.

    Also, your field "posted" is NOT an integer. It is of type DATE.

    You need to review your data types in MySQL and then coordinate the definitions of your fields with the data types you are going to store.

    Remember also that 49.95, is NOT an integer. Integers have no decimal places. 49.95 would be a FLOAT type.
    John

  13. #38
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHP John
    Look at your quanitity. This is NOT an integer. It is a string. If you are going to store it in such a manner you need to keep the "quantity" field as a VARCHAR, or if you keep it as a TINYINT, you need to do the math first before insterting it into the database.

    Also, your field "posted" is NOT an integer. It is of type DATE.

    Remember also that 49.95, is NOT an integer. Integers have no decimal places. 49.95 would be a FLOAT type.
    SORRY. I'm trying.

    posted | date
    cart_id | mediumint | 15
    cust_id | mediumint | 15
    quantity | varchar | 10
    number | mediumint | 10
    item | varchar | 25
    price | decimal | 10,0

  14. #39
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didn't mean it as a reprimand, just some advice.

    Here is some information on the DECIMAL type:
    DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
    An unpacked floating-point number. Behaves like a CHAR column: "unpacked" means the number is stored as a string, using one character for each digit of the value. The decimal point and, for negative numbers, the - sign, are not counted in M (but space for these is reserved). If D is 0, values will have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. If UNSIGNED is specified, negative values are disallowed.
    If D is omitted, the default is 0. If M is omitted, the default is 10.
    MySQL Reference Manual (C) 2002 MySQL AB
    Here is what I would do, then:
    Code:
    posted | DATE
    cart_id | MEDIUMINT | 15
    cust_id | MEDIUMINT | 15
    quantity | VARCHAR | 15
    number | SMALLINT | 10
    item | VARCHAR | 25
    price | DECIMAL | 10, 2
    And use this code to do the insert:
    PHP Code:
        $query "INSERT INTO Cust_cart
                    SET
                      posted    = '
    $posted',
                      cart_id   = 
    $cart_id,
                      cust_id   = 
    $cust_id,
                      quanitity = '"
    .$_REQUEST["quanitity".$i]."',
                      number    = "
    .$_REQUEST["number".$i].",
                      item      = '"
    .$_REQUEST["item".$i]."',
                      price     = '"
    .$_REQUEST["price".$i]."'";
        echo 
    $query.'<br />';
        
    mysql_query$query ); 
    John

  15. #40
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, TRIED many many ways... still can't get it to enter into the database... would primary key have anything to do with this???

  16. #41
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Post your table fields and types, the query that is echoed out, and any error messages that are displayed (if any).

    Also tell me which field is your primary key.
    John

  17. #42
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    TABLE cust_cart
    posted date
    cart_id mediumint(15)
    cust_id mediumint(15)
    quantity varchar(15)
    number smallint(10)
    item varchar(25)
    price decimal(10,2)

    Primary key is cart_id
    Query echoed:
    INSERT INTO cust_cart SET posted = '2003-05-16', cart_id = 030516130336, cust_id = , quantity = ' 1 x ', number = ' 025237', item = ' Fringe Alphabets', price = ' 49.95'

    Very sorry to be bothering you with this but I greatly appreciate your helping.

  18. #43
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you already have a row with cart_id as "030516130336"?

    Is "cart_id" AUTO_INCREMENT, and do you need multiple rows with the SAME value in "cart_id"?

    And, are you getting ANY error messages?
    John

  19. #44
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No errors reported.... nothing gets sent to the cust_cart table. The table is blank.

    But the cart_id does successfully make it to card_payment table. (different mysqlquery insert)

  20. #45
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Reread my post #43. I may have edited it after you read it.
    John

  21. #46
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHP John
    Is "cart_id" AUTO_INCREMENT, and do you need multiple rows with the SAME value in "cart_id"?

    And, are you getting ANY error messages?

    Finally, success getting the values to the database... there were a few ' missing around cart_id and cust_id.
    ***But strange thing now is that the cart_id does not match the actual cart_id that is being placed into card_payment.

    The card_id code is generated through PHP on this page as
    PHP Code:
    // generates cart id number 
    $cart_id date("ymjHis" ); 
    The number that is in the cart_id (cust_cart) makes no sense. Since it has to be a unique number it will not allow any other inputs even though the other tables are still excepting the correct information. Sorry if this is confusing .. not sure how to explain it better.

  22. #47
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Strange. Integers should not require quotes around them in the query. Unless the field datatypes have not been changed from VARCHAR. You did change them, right?

    Anyway, post your new code. Somewhere you are generating a new cart_id value between queries.

    The cart_id value should be generated before ANY INSERT queries are executed, and then left alone if you want the same value for all queries.
    John

  23. #48
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OOOOPss.... your right! I would definetly need multiple rows for the cart_id as more than one item may be ordered at a time.... So I have taken off the Primary Key from cart_id.

    Since removing the Primary Key I am able to submit values to the database again but.... the cart_id row is always filled with 8388607... this number has no relevance to anything....

  24. #49
    SitePoint Enthusiast MAC#1's Avatar
    Join Date
    Mar 2003
    Location
    ontario
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHP John
    Strange. Integers should not require quotes around them in the query. Unless the field datatypes have not been changed from VARCHAR. You did change them, right?
    Anyway, post your new code.
    Yes, the cart_id and cust_id have earlier been changed to integers. heres the code: (cust_id is an auto-increment in customer table) but I'm not quite sure how to retrieve it to use within the other tables.
    PHP Code:
    <?php
    // required variables 
    require("config.php" ); 
    $amount     $_REQUEST["amount"]; 
    $cart_id    $_REQUEST["cart_id"]; 
    $company    $_REQUEST["company"]; 
    $firstname  $_REQUEST["firstname"]; 
    $lastname   $_REQUEST["lastname"];   
    $email      $_REQUEST["email"]; 
    $address    $_REQUEST["address"]; 
    $city       $_REQUEST["city"]; 
    $state      $_REQUEST["state"]; 
    $zip        $_REQUEST["zip"]; 
    $country    $_REQUEST["country"]; 
    $phone      $_REQUEST["phone"];
    $shaddress    $_REQUEST["shaddress"]; 
    $shcity       $_REQUEST["shcity"]; 
    $shstate      $_REQUEST["shstate"]; 
    $shzip        $_REQUEST["shzip"]; 
    $shcountry    $_REQUEST["shcountry"]; 
    $phone      $_REQUEST["phone"];  
    $comment    $_REQUEST["comment"]; 
    $total      $_REQUEST["total"]; 
    $order_id   $_REQUEST["cart_id"]; 

    // generates cart id number 
    $cart_id date("ymjHis" ); 
    // generates posted number 
    $posted date("Y-m-d" ); 
    $emailBody '';
    // database connection 
    mysql_connect("$host","$user","$pass); 
    // database selection 
    mysql_select_db($database); 

    //  Insert customer information
    mysql_query("INSERT INTO customer VALUES(
                  
                  '
    $cust_id',
                  '
    $posted',
                  '
    $firstname',
                  '
    $lastname',
                  '
    $company',
                  '
    $address',
                  '
    $city',
                  '
    $state',
                  '
    $country',
                  '
    $zip',
                  '
    $phone',
                  '
    $email',
                 '
    $shaddress',
                  '
    $shcity',
                  '
    $shstate',
                  '
    $shcountry',
                  '
    $shzip',
                  '
    $password')" ); 



    //  Insert payment
    mysql_query("INSERT INTO card_payment VALUES(
                  '
    $posted',
                  '
    $cust_id',
                  '
    $cart_id',
                  '
    $total')" ); 
                                
               
                  
    for( 
    $i 0$i 30$i++ )
    {
      if( 
    $_REQUEST["quantity".$i] )
      {
        
        
    // Build the body of the email:
        // The format is:
        // [quantity] [number] [item] [price]   
        // [quantity] [number] [item] [price]
        // [quantity] [number] [item] [price]
        // etc...
        
        
    $emailBody .= $_REQUEST["quantity".$i].', '.$_REQUEST["number".$i].', '.$_REQUEST["item".$i].', '.$_REQUEST["price".$i]."\r\n";     
        
    $query "INSERT INTO cust_cart
                    SET
                      posted    = '
    $posted',
                      cart_id   = '
    $cart_id',
                      cust_id   = '
    $cust_id',
                      quantity = '"
    .$_REQUEST["quantity".$i]."',
                      number    = '"
    .$_REQUEST["number".$i]."',
                      item      = '"
    .$_REQUEST["item".$i]."',
                      price     = '"
    .$_REQUEST["price".$i]."'";
       echo 
    $query.'<br />';
        
    mysql_query$query );
      }
    }
    $message ="IP: $REMOTE_ADDR Thank you for placing your order: ART-SEW-PERFECT'S ONLINE 
    Credit Card - Order Number 
    $cart_id 
    Customer ID - 
    $cust_id
    Email -
    $email 
    Company - 
    $company 
    Firstname - 
    $firstname 
    LastName - 
    $lastname 
    Address - 
    $address 
    City - 
    $city 
    State -
    $state 
    Zip - 
    $zip 
    Country - 
    $country 
    Shipping Address - 
    $shaddress 
    Shipping City - 
    $shcity 
    Shipping State -
    $shstate 
    Shipping Zip - 
    $shzip 
    Shipping Country - 
    $shcountry 
    Phone - 
    $phone 
    Comment: 
    $comment 
    Total 
    $txt_currency:  $total 
    Order: 
    $emailBody
    "
    ;        
    mail("$receipt2""Re: Online Order""$message""From:$receipt); 
    ?>

  25. #50
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've got to run, so you're going to have to figure out why you are getting that value.

    Go ahead and post your conclusions and your questions. When I get back, I'll see if I can help you more.
    John


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
  •