Unknown column 'Data_Distance' in 'field list'

PHP
1

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


<?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...\
";

?>

here’s my database


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`)

[URL=“http://www.andrewshemo.com/reloading/insert.php”]

2

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

I’ll work on that tonight, but will that fix the problem?

4

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

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?

6

Would have thought that it’d throw an error a lot sooner than that if the table didnt exist…

7

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…

8

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

nope, not unless you took a backup

10

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.

11

I was able to recover the deleted data from a prior backup. thank God for backups!

12
Off Topic:

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

13

lol