105 users online (1 members and 104 guests)  

  Results 1 to 3 of 3


  1. Replies: 1
  2. perl and mysql-compare 2 columns    Forum: CGI Perl Forum
    Replies: 7
  3. Replies: 4
  4. Replies: 3
  5. Transferring Login Data to another script    Forum: CGI Perl Forum
    Replies: 3
  1. #1
    snowweb's Avatar
    New User

    Join Date
    Jan 2006

    Question Script stores data in wrong MySQL columns

    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
    vinyl-junkie's Avatar

    Join Date
    Sep 2005

    Re: Script stores data in wrong MySQL columns

    The way you've written your insert query, the database will insert those values into your database row in the same order that they appear in the database. If that isn't the same order, then change your insert query to match that and everything should be okay.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more

  3. #3
    snowweb's Avatar
    New User

    Join Date
    Jan 2006

    Re: Script stores data in wrong MySQL columns

    You're right! The database order of columns was different to what I thought! I won't make that mistake again (or at least, if I do, I know where to look).

Tags for this Thread