smada
smada

Reputation: 237

MySQL Insert from PHP

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:

  1. Are those 10k, 14k, etc, okay mysql table row names?
  2. 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).

Upvotes: 2

Views: 235

Answers (4)

Pebbl
Pebbl

Reputation: 36005

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 ) .  "')";

Upvotes: 0

gowri
gowri

Reputation: 681

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..

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838276

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.

Upvotes: 6

ppeterka
ppeterka

Reputation: 20726

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...

Upvotes: 1

Related Questions