CODEWITHSUNDEEP
phpmysqlvariables
Jesse Figueroa
Jesse Figueroa

Reputation: 83

Passing PHP variables into MySQL

I have a function in PHP which inserts values into MYSQL tables.

function insertRow($db, $new_table, $ID, $Partner, $Merchant)
{
    $insert = "INSERT INTO " .$new_table. " VALUES(number, "string", "string")"
    $q = mysqli_query($db, $insert);
}

I am struggling customizing the VALUES part. I need the number, string, and string to be ID, Partner, and Merchant variables from PHP respectively.

I've tried

function insertRow($db, $new_table, $ID, $Partner, $Merchant)
{
    $insert = "INSERT INTO " .$new_table. " VALUES(" .$ID . $Partner . $Merchant . ")";
    $q = mysqli_query($db, $insert);
}

as well. But it doesn't seem to work because for SQL the string values must be surrounded with quotes. But if I change the code so that its ."$ID" . "$Partner" . "$Merchant" . ")"; and thus the variables are in quotes as needed, they are no longer PHP variables. How do I get my PHP variables to be included in quotes so that I can execute the SQL correctly?

Upvotes: 3

Views: 7771

Answers (7)

alpham8
alpham8

Reputation: 1362

Well, there are many methods to possibilities to do this:

String concatenation

be aware of the data type that the database except. In your question, you missed out the comma to seperate the values:

$insert = 'INSERT INTO ' . $new_table . ' VALUES(' . $ID . ', \'' . $Partner . '\', \'' . $Merchant '\')';

There are many ways to modify the string concatenation example. Consider the php documentation about single and double quotes. In you example you used double quotes in the meaning of single quotes. With double quotes you can put variables directly into it. So I changed it to single quotes.

sprintf

$insert = sprintf('INSERT INTO %s VALUES(%s, \'%s\', \'%s\');', $new_table, $ID, $Partner, $Merchant);

see the documentation for more info about this handy function: http://www.php.net/sprintf

Prepared Statements

I would rather advice you to use this method because of SQL Injection. In the example above you need to take care about string escaping. May work with mysql_real_escape. But it may not work in all scenarios.

So, prepared statements are a lot better:

$insert = 'INSERT INTO ' .$new_table. ' VALUES(?, ?, ?)';
$q = mysqli_prepare($db, $insert);
mysqli_stmt_bind_param($q, "iss", $ID, $Partner, $Merchant);
mysqli_stmt_execute($q);

PS: Using the object oriented way would be also better for prepared statement, because you got more opportunities to do this. See the documentation for MySQLi class style and for PDO.

Upvotes: 1

lonesomeday
lonesomeday

Reputation: 237817

The other answers, using concatenation, are the simple ones. The best one is to use prepared statements, which will make your code significantly more secure.

$insert = "INSERT INTO " .$new_table. " VALUES(?, ?, ?)";
$q = mysqli_prepare($db, $insert);
mysqli_stmt_bind_param($q, "iss", $ID, $Partner, $Merchant);
mysqli_stmt_execute($q);

Doing parameterised queries means your query and the data are sent separately. This means that the structure of the query already exists, and so cannot be altered by anything else inserted in the data, which means you are safe from SQL injection.

See the PHP manual:

Upvotes: 5

Fahim Parkar
Fahim Parkar

Reputation: 31627

$insert = "INSERT INTO $new_table VALUES($ID,'$Partner','$Merchant')";

Upvotes: 0

icanc
icanc

Reputation: 3577

Try this:

$insert = "INSERT INTO $new_table VALUES(number, '{$string}', '{$string}')";

Upvotes: 0

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324610

Um... it being a query makes no difference to the fact that it's just a string like any other. You were perfectly capable of adding in the $new_table value, so what's stopping you from doing the others?

$insert = "INSERT INTO ".$new_table." VALUES (".$ID.", '".$Partner."', '".$Merchant.")";

This assumes you already took the necessary steps to sanitise the values.

Upvotes: 0

jcho360
jcho360

Reputation: 3759

Try this:

function insertRow($db, $new_table, $ID, $Partner, $Merchant)
{
    $insert = "INSERT INTO " .$new_table. " VALUES( '" .$ID."','" . $Partner."','". $Merchant . "')";
    $q = mysqli_query($db, $insert);
}

Or you can use 

function insertRow($db, $new_table, $ID, $Partner, $Merchant)
{
    $insert = "INSERT INTO " .$new_table. " VALUES( '$ID','$Partner','$Merchant')";
    $q = mysqli_query($db, $insert);
}

Upvotes: 0

Mike
Mike

Reputation: 781

Concatenation

$insert = "INSERT INTO " .$new_table. " VALUES(".$ID.", '".$Partner."', "."'"$Merchant"')";

Upvotes: 0

Related Questions