MySQL Insert from PHP

Question

I'm having a little trouble with my insert statement this morning. Yes, I am using the deprecated mysql_query function. My insert statement looks as follows:

$query3 = "INSERT INTO ".$db_prefix ." offer_det 
          (fname, lname, 10k, 14k, 18k, 21k, 22k, 24k, 925, coins, bars) 
           VALUES '".$fname."', '".$lname."', '".$_10k."', '".$_14k."', 
                  '".$_18k."', '".$_21k."', '".$_22k."', '".$_24k."', 
                  '".$_925."', '".$coins."', '".$bars."')";

$result3 = mysql_query($query3);

My PHP form values are all the variables listed in the first part of the insert statement, 'fname', etc.

My variables are set to pull from the post and are listed as the values going into the insert.

I had to change the variables to underscore before they started, I guess PHP didn't like that.

My questions:

Are those 10k, 14k, etc, okay mysql table row names?
Is there an issue I'm missing here?

The datatype for fname and lname are varchar and for the 10k through bars are decimal (7,3).

try to print the result sentence with "echo $query3;" Maybe it'll become more easy to find a mistake. — Arnaud Bessems
– Arnaud Bessems, Commented Oct 19, 2012 at 11:22
dev.mysql.com/doc/refman/5.0/en/identifiers.html MySQL naming rules — Bogdan Burym
– Bogdan Burym, Commented Oct 19, 2012 at 11:23

Mark Byers · Accepted Answer · 2012-10-19 11:26:27Z

6

The column name 925 must be quoted using backticks.

(`fname`, `lname`, `10k`, `14k`, `18k`, `21k`, `22k`, `24k`, `925`, `coins`, `bars`)

You may also want to consider changing the column names to something else to avoid further similar problems in the future.

edited Oct 19, 2012 at 11:26

answered Oct 19, 2012 at 11:21

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ppeterka Over a year ago

the 10k and similar column names are OK, according to the schema object naming guide of MySQL, as they are not consisted solely of numbers...

Mark Byers Over a year ago

Yes, I just read the link that pebbl posted in the comments and have updated my answer accordingly.

smada Over a year ago

Thanks Mark, I'm new to PHP / MySQL in general.

Pebbl Over a year ago

I always go with back-ticking anyway - it's safer in my eyes as you avoid the possibility of problems, and I can't think that it adds that much overhead to query parsing. So this answer still gets +1 from me due to my biased opinion :)

smada Over a year ago

My updated query is as follows: $query3 = "insert into ".$db_prefix."offer_det (fname, lname, 10k, 14k, 18k, 21k, 22k, 24k, 925k, coins, bars) values ('$fname', '$lname', '$_10k', '$_14k', '$_18k', '$_21k', '$_22k','$_24k', '$_925', '$coins', '$bars')"; echo $query3; $query_exec=mysql_query($query3) or die(mysql_error()); I'm still not getting any action on this -- Those values are backticked

|

ppeterka · Accepted Answer · 2012-10-19 11:23:52Z

1

You should quote the 925 column name, as per MySQL Schema Object names

So correctly:

$query3 = "insert into ".$db_prefix."offer_det (fname, lname, 10k, 14k, 18k, 21k, 22k, 24k, `925`, coins, bars)
values 
('".$fname."', '".$lname."', '".$_10k."', '".$_14k."', '".$_18k."', '".$_21k."', 
'".$_22k."','".$_24k."', '".$_925."', '".$coins."', '".$bars."')";

Another recommendation: you should escape the incoming strings, because SQL injection is a nasty thing to experience...

answered Oct 19, 2012 at 11:23

ppeterka

20.8k6 gold badges67 silver badges80 bronze badges

Comments

gowri · Accepted Answer · 2012-10-19 11:26:49Z

0

Use the QUERY as like follow..

$query3 = "insert into ".$db_prefix."offer_det (fname, lname, 10k, 14k, 18k, 21k, 22k, 24k, 925, coins, bars) 
       values ('$fname', '$lname', '$_10k', '$_14k', '$_18k', '$_21k', '$_22k', 
       '$_24k', '$_925', '$coins', '$bars')";
$query_exec=mysql_query($query3) or die(mysql_error());

And for inserting a variable you need to use single codes only..

answered Oct 19, 2012 at 11:26

gowri

6819 silver badges27 bronze badges

Comments

Pebbl · Accepted Answer · 2012-10-19 11:27:08Z

0

Can I be bold and suggest a change in your implementation?

/// put your vars in an easier to use format
$insert = array(
  'fname' => $fname,
  'lname' => $lname,
  '10k' => $_10k,
  /* and so on ...*/
);

/// considering you are using mysql_query, use it's escape function
foreach ( $insert as $field => $value ) {
  $insert[$field] = mysql_real_escape_string($value);
}

/// pull out the keys as fields and the values as values
$keys = array_keys($insert);
$vals = array_values($insert);

/// the following should auto backtick everything... however it should be
/// noted all the values will be treated like strings as you were doing anyway
$query = "INSERT INTO `" . $db_prefix . "offer_det` " . 
         "(`" . implode('`,`', $keys) . "`) " . 
         "VALUES ('" . implode("','", $vals ) .  "')";

answered Oct 19, 2012 at 11:27

Pebbl

36.1k6 gold badges66 silver badges65 bronze badges

1 Comment

smada Over a year ago

Pebbl, I appreciate this. I'm kind of following a structure that I've seen previously. I'm new to PHP and MySQL and I should change to MySQLi early on to learn the new structure and habits. Unfortunately I'm on a deadline right now, so I need to produce some results fairly quickly. All of these functions are on an encrypted and credential required page, but nonetheless I agree that it needs to be safer in case the credentials get compromised and or the employees get too tricky and or stupid.

Collectives™ on Stack Overflow

MySQL Insert from PHP

4 Answers 4

6 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related