CODEWITHSUNDEEP
phpmysql
Reid
Reid

Reputation: 4524

PHP database getting variables

I have the following line of code that executes properly except for one hitch. It will print the variable name into the database.

I use this code in one of my scrips:

$con = mysql_connect("MyServer","MyDB","myPwd");
mysql_select_db("MyDB", $con);
$sql = "INSERT INTO `MyDB`.`Shipping` (`ID`, `FIRSTNAME`, `LASTNAME`, `ADDRESS1`, `ADDRESS2`, `CITY`, `STATE`, `ZIP`, `ORDERNUMBER`, `SHIPPINGTYPE`, `item1`, `item2`, `item3`, `item4`, `item5`, `item6`, `item7`, `item8`, `item9`, `item10`) VALUES (NULL, \'$first_name\', \'lname\', \'addr\', \'\', \'city\', \'state\', \'zip\', \'ordernum\', \'\', \'0\', \'0\', \'0\', \'0\', \'0\', \'0\', \'0\', \'0\', \'0\', \'0\');";
mysql_query("$sql");
mysql_close($con);

This code prints the followin to the database:

$first_name lname   addr        city    state   zip 0       0   0   0   0   0   0   0   0   0   0

Notice that the variable name, and not the contents of the variable are printed. How can I get it to print out the variable contents?

Upvotes: 1

Views: 161

Answers (5)

aquasan
aquasan

Reputation: 374

Rather than using \'$first_name\' replace it with \"$first_name\" and the same follows with other variables too.

Upvotes: 0

Mike Purcell
Mike Purcell

Reputation: 19979

Notice that the variable name, and not the contents of the variable are printed

This is because you are not passing variables to the insert statement, rather you are passing undefined(?) constants; lname, addr, etc. You should pass in the variables you assigned values to: (assuming) $lname, $addr, etc.

Also, you don't need to pass in a NULL for the ID column. If it's set to auto_increment, the database will create this value for you.

You should also take advantage of the default column values defined by your table schema, so you don't end up passing in a huge list of variables with default values, for example, if you have a DEFAULT 0 as part of the schema for item1, item2, item3 etc, you can simply make the following insert:

$sql =
"INSERT INTO `MyDB`.`Shipping` (`FIRSTNAME`, `LASTNAME`, `ADDRESS1`, `CITY`, `STATE`, `ZIP`, `ORDERNUMBER`) VALUES ('$first_name', '$lname', '$addr', '$city', '$state', '$zip', '$ordernum');";

The database will populate item1, item2, etc columns with 0 for you automatically.

As cambraca mentioned, if you pass variables directly into your query like this, you run the risk of SQL injection. You should read up on how to prepare your queries, which will also force you to use mysqli (vs outdated mysql) api. Or even better, read up on PDO.

Upvotes: 2

Philipp
Philipp

Reputation: 1445

I suppose not to use inline variables in strings in PHP, and you are escaping ' when you do not need to. Try this:

$con = mysql_connect("MyServer","MyDB","myPwd");
mysql_select_db("MyDB", $con);
$sql = "INSERT INTO `MyDB`.`Shipping` (`ID`, `FIRSTNAME`, `LASTNAME`, `ADDRESS1`, `ADDRESS2`, `CITY`, `STATE`, `ZIP`, `ORDERNUMBER`, `SHIPPINGTYPE`, `item1`, `item2`, `item3`, `item4`, `item5`, `item6`, `item7`, `item8`, `item9`, `item10`) VALUES (NULL, '".$first_name."', 'lname', 'addr', '', 'city', 'state', 'zip', 'ordernum', '', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');";
mysql_query($sql);
mysql_close($con);

Upvotes: 0

Cfreak
Cfreak

Reputation: 19309

For one you have a quoting issue. You seem to have double quotes and then escaped single quotes. don't escape your single quotes if you string is double quoted.

It seems the value of $first_name should work. Is this the exact code? Because if you have single quotes around the whole thing it would create the output you're describing.

All your other columns aren't variables so it's hard to know what you're trying to do. If you aren't going to use them why reference them at all?

In your mysql_query call don't quote $sql again.

Finally check mysql_error() for errors. It could be you are seeing something old in the database and your code isn't working at all.

Consider using PDO. It has support for placeholders, better security and better debugging than the native mysql_* interface.

Upvotes: 2

hjpotter92
hjpotter92

Reputation: 80639

Change the insertion statement to:

$sql = "INSERT INTO `MyDB`.`Shipping` 
  (`ID`, `FIRSTNAME`, `LASTNAME`, `ADDRESS1`, `ADDRESS2`, `CITY`, 
    `STATE`, `ZIP`, `ORDERNUMBER`, `SHIPPINGTYPE`, `item1`, `item2`, 
    `item3`, `item4`, `item5`, `item6`, `item7`, `item8`, `item9`, `item10`)
  VALUES (NULL, '{$first_name}', 'lname', 'addr', '', 'city', 'state',
    'zip', 'ordernum', '', '0', '0', '0', '0', '0', '0', '0',
    '0', '0', '0');";

Also, do check that your $first_name is properly assigned a value.

Upvotes: 0

Related Questions