Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006

    Question Unanswered: Script stores data in wrong MySQL columns

    I'm creating my first web application in PHP/MySQL, however, when I go to view the database contents, the data has been stored in the wrong columns. I've gone through my insertion code with a fine toothcomb and can see no errors which might cause this. I will post the code below, and you will see that I have some diagnostics built in, which show that the information (at least at that point) appears to be about to be stored in the correct columns.

    I have 'checked', 'repaired' and 'flushed' the database using PHPMyAdmin and tried again. I have also restarted my PC several times too (although so far, it has always been very stable).

    These are a list of some of the columns in my table called 'products' and along side them is the name of the columns data that is wrongly stored there:

    NameModel - correct
    Manufacturer - correct
    Tagline - CONTAINS Ed_Main
    Ed_Summary - CONTAINS Tagline
    Ed_main - CONTAINS Ed_Summary

    The rest of the columns are store correctly.

    This is the diagnostic text that was output on the screen because of the diagnostic code in insert.php

    submit = Submit Query
    namemodel = 1Name/Model
    manufacturer = Manufacturer
    tagline = Tagline
    ed_summary = Ed Summary
    ed_main = Main Editorial
    features = Features
    specs = Specs
    options = Options
    pic_thumb_100x100 = Thumbnail Picture
    pic_1 = Picture 1
    pic_2 = Picture 2
    costprice = 1
    retailprice = 2
    supplierid = 3
    curr_stock = 4
    minstock = 5
    maxstock = 6
    The text to the left of the '=' sign is the Post HTTP header field name and to the right of the '=' is the contents of the form.

    Looking at that, it is all good so far, as I typed in each text field some text to identify which field it should be in. EG. I typed 'Main Editorial' in the field called 'Ed_main' in the form that collects the data to insert.

    However, when I check the actual contents of the database the data is stored in the wrong columns still.

    Here is the full code of the page holding the form:

    HTML Code:
              <form action="insert.php" method="post">
                   <input type="Submit" name="submit" />
                   Name/Model: <input type="text" name="namemodel" /><br />
                   Manufacturer: <input type="text" name="manufacturer" /><br />
                   Tagline: <input type="text" name="tagline" /><br />
                   Ed Summary: <textarea cols="20" rows="10" name="ed_summary"></textarea><br />
                   Main Editorial: <textarea cols="60" rows="20" name="ed_main"></textarea><br />
                   Features: <textarea cols="20" rows="20" name="features"></textarea><br />
                   Specs: <textarea cols="20" rows="10" name="specs"></textarea><br />
                   Options: <textarea cols="20" rows="10" name="options"></textarea><br />
                   Thumbnail Picture: <input type="text" name="pic_thumb_100x100" /><br />
                   Picture 1: <input type="text" name="pic_1" /><br />
                   Picture 2: <input type="text" name="pic_2" /><br />
                   Cost Price: <input type="text" name="costprice" /><br />
                   Retail Price: <input type="text" name="retailprice" /><br />
                   Supplier ID: <input type="text" name="supplierid" /><br />
                   Current Stock: <input type="text" name="curr_stock" /><br />
                   Minimum Stock: <input type="text" name="minstock" /><br />
                   Maximum Stock: <input type="text" name="maxstock" /><br />
                   <input type="Submit" name="submit" />

    Here is the code for insert.php

    PHP Code:

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

    ="removed for forum";
    $password="removed for forum";

                   if (
    $username="removed for forum") {

    // process form
    echo "Values <br>";
                   while (list(
    $name$value) = each($HTTP_POST_VARS)) {

    "$name = $value<br>\n";



    $query "INSERT INTO products VALUES ('$ProdID', '$NameModel', '$Manufacturer', '$Tagline', '$Ed_Summary', '$Ed_Main', '$Features', '$Specs', '$Options', '$Pic_Thumb_100x100', '$Pic_1', '$Pic_2', '$CostPrice', '$RetailPrice', '$SupplierID', '$Curr_Stock', '$MinStock', '$MaxStock')";

    $resultset=odbc_exec($conn,$query) or die ("Error inserting data<BR>$query");

    /* <!-- where:-
    $sql is the variable containing the SQL to be applied
    $cnn is the variable containing a valid MySQL resource
    $resultset is the return from the function
     --> */

    I would really appreciate your help!

    Thanks, pete

  2. #2
    Join Date
    Jan 2004
    Can you please post the table structure for "products".
    I would like to see that before commenting on this furthure.

    Freelance and Technology Consultant
    Dreams are for ever

  3. #3
    Join Date
    Apr 2005
    Baltimore, MD
    Try explicitly declaring the column names in your insert statement.

    INSERT INTO tbl (col1, col2, col3) VALUES ('val1', 'val2', 'val3')

  4. #4
    Join Date
    Apr 2005
    Baltimore, MD
    Also as a side note, use double '==' for comparison.
    PHP Code:
                   if ($username="removed for forum") { 
    PHP Code:
                   if ($username=="removed for forum") { 

  5. #5
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    ...all good ideas so far.... jus a thought:
    is ProdID an autonumber field?
    if so then its shouldn't be in the insert statement
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2006
    Hi Chaps, thanks for all those ideas, I took all of your advice and now it is working... amazing what a difference it makes when you do it right!!

    My problem was a combination of things from "wrong kind of quote marks", databose order of columns was not as I understood it (that made a pretty big difference!).

    Thanks everyone.


Posting Permissions

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