SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unknown column 'Data_Distance' in 'field list'

    hello there...

    only know some basic php and I'm not sure why this error is occurring, but I accidentally deleted an insert file that inserted data into my database then displayed it into a table so I had to rebuild the script and I may of screwed something up so maybe someone here who knows more php can help me....

    here's the php file:
    insert.php
    Code:
    <?PHP
    
    foreach ($_POST as $key => $value){
        $$key = $value;
    }
    
    if (!$submit) {
    
    ?>
    
    <HTML>
    <HEAD>
    <TITLE>Insert stuff</TITLE>
    <style type="text/css">
    ul, li
    {
    list-style: none;
    text-align: left;
    }
    </style>
    </HEAD>
    <BODY>
    <H1>Insert stuff...</H1>
    <FORM ACTION="insert.php" METHOD=POST>
    <ul>
    <li>Caliber: <INPUT TYPE=TEXT NAME=Caliber></li><BR>
    <li>Bullet: <INPUT TYPE=TEXT NAME=Bullet></li><BR>
    <li>Powder: <INPUT TYPE=TEXT NAME=Powder></li><BR>
    <li>Primer: <INPUT TYPE=TEXT NAME=Primer></li><BR>
    <li>OAL: <INPUT TYPE=TEXT NAME=OAL></li><BR>
    <li>Distance: <INPUT TYPE=TEXT NAME=Data_Distance></li><BR>
    <li>Grouping: <INPUT TYPE=TEXT NAME=Grouping></li><BR>
    <li>Bench: <INPUT TYPE=TEXT NAME=Bench></li><BR>
    <BR>
    <li><INPUT TYPE=HIDDEN NAME=submit VALUE="yes">
    <INPUT TYPE=SUBMIT VALUE="Submit your stuff..."></li>
    </ul>
    </FORM>
    </BODY>
    </HTML>
    
    <?PHP
    exit;
    }
    
    $username = removed
    $password = removed
    $database = removed
    $hostname = removed
    
    mysql_connect("$hostname", "$username", "$password") or die(mysql_error());
    mysql_select_db("$database") or die(mysql_error());
    
    # this stuff is just for reference (copied from your post) so you know what the database limits are when doing the insert...
    # for example, varchar(10) tells us that Ammo_Caliber can be pretty much any character, but not more than 10 characters long...
    # and NOT NULL means exactly that... the database won't let you leave it blank (null)
    
    #`Ammo_Caliber` varchar(10) collate latin1_german2_ci NOT NULL,
    #`Bullet` varchar(20) collate latin1_german2_ci NOT NULL,
    #`Powder` varchar(20) collate latin1_german2_ci NOT NULL,
    #`Primer` varchar(20) collate latin1_german2_ci NOT NULL,
    #`OAL` varchar(20) collate latin1_german2_ci NOT NULL,
    #`Data_Distance` varchar(20) collate latin1_german2_ci NOT NULL,
    #`Grouping` varchar(20) collate latin1_german2_ci NOT NULL,
    #`Bench` varchar(20) collate latin1_german2_ci NOT NULL,
    
    // Get all the data from the "example" table
    $result = mysql_query("INSERT into NewGuns (Caliber,Bullet,Powder,Primer,OAL,Data_Distance,Grouping,Bench) VALUES ('$Caliber','$Bullet','$Powder','$Primer','$OAL','$Data_Distance','$Grouping','$Bench')") or die(mysql_error());  
    
    print "Stuff was inserted.  Click <A HREF=\"select.php\">here</A> to see if it worked...\n";
    
    ?>
    here's my database
    Code:
    CREATE TABLE IF NOT EXISTS `Guns` (
      `ID` int(5) NOT NULL AUTO_INCREMENT,
      `Ammo_Caliber` varchar(10) COLLATE latin1_german2_ci NOT NULL,
      `Bullet` varchar(20) COLLATE latin1_german2_ci NOT NULL,
      `Powder` varchar(20) COLLATE latin1_german2_ci NOT NULL,
      `Primer` varchar(20) COLLATE latin1_german2_ci NOT NULL,
      `OAL` varchar(20) COLLATE latin1_german2_ci NOT NULL,
      `Data_Distance` varchar(20) COLLATE latin1_german2_ci NOT NULL,
      `Grouping` varchar(20) COLLATE latin1_german2_ci NOT NULL,
      `Bench` varchar(20) COLLATE latin1_german2_ci NOT NULL,
      PRIMARY KEY (`ID`)



    Last edited by kohoutek; Apr 6, 2010 at 18:23. Reason: Removed URL as requested by OP

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    First thing i'm going to tell you is to go back to your HTML and enquote all of your values.
    Then i'm going to tell you to go down to your SQL query and enquote the field names too.

    type='text' name='Caliber', for example.
    INSERT into NewGuns ('Caliber','Bullet',

    This error however, is a SQL error, not a PHP error.

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    First thing i'm going to tell you is to go back to your HTML and enquote all of your values.
    Then i'm going to tell you to go down to your SQL query and enquote the field names too.

    type='text' name='Caliber', for example.
    INSERT into NewGuns ('Caliber','Bullet',

    This error however, is a SQL error, not a PHP error.
    I'll work on that tonight, but will that fix the problem?

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    I cant see any GLARING errors... if this is the only place the field name is used specifically... consider stripping the underscore out and renaming the field. Underscores can be tricky things with certain versions of mySQL...

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    your query, as posted, attempts to insert into a table called NewGuns

    the table you posted, with the column Data_Distance, is called Guns

    is that the problem?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Would have thought that it'd throw an error a lot sooner than that if the table didnt exist....

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    Would have thought that it'd throw an error a lot sooner than that if the table didnt exist....
    oh, it's pretty obvious that both tables exist... he must've been changing the design, you know, hitting a moving target with his query

    notice the INSERT statement refers to Caliber, but the column is Ammo_Caliber

    he's chnging his columns on the fly from one table design to the next

    hence the "unknown column" error messages...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    your query, as posted, attempts to insert into a table called NewGuns

    the table you posted, with the column Data_Distance, is called Guns

    is that the problem?
    thanks..that fixed the problem.

    I was trying to add stuff into the wrong database..that and my 'caliber' should of been ammo_caliber.

    do have another question...is there a query that I can run to recover stuff that had been deleted today? apparently me posting a link to my site was a bad idea..someone thought it would be funny to deleted everything from 1 of my tables.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by andrewjs18 View Post
    is there a query that I can run to recover stuff that had been deleted today?
    nope, not unless you took a backup
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,716
    Mentioned
    103 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by andrewjs18 View Post
    do have another question...is there a query that I can run to recover stuff that had been deleted today? apparently me posting a link to my site was a bad idea..someone thought it would be funny to deleted everything from 1 of my tables.
    Once it's deleted it's gone. You can only recover it if you've made a backup, or keep transaction logs.

    I suspect that someone was checking your security (see XKCD: Exploits of a Mom)

    The following recent thread goes through the important security techniques you need to apply, so that you can protect yourself and your database from future occurances.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  11. #11
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pmw57 View Post
    Once it's deleted it's gone. You can only recover it if you've made a backup, or keep transaction logs.

    I suspect that someone was checking your security (see XKCD: Exploits of a Mom)

    The following recent thread goes through the important security techniques you need to apply, so that you can protect yourself and your database from future occurances.
    I was able to recover the deleted data from a prior backup. thank God for backups!

  12. #12
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:


    Quote Originally Posted by pmw57 View Post
    I suspect that someone was checking your security (see XKCD: Exploits of a Mom)
    That is genius Reminds me of something that was going round on Twitter a few weeks ago; SQL injection for speed cameras.

    Sam Hastings

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SJH View Post
    Off Topic:




    That is genius Reminds me of something that was going round on Twitter a few weeks ago; SQL injection for speed cameras.

    lol


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
  •