Menu
Community /Pin to ProfileBookmark
@NogDogJun 19.2008 — #Depends on which database interface you want to use. If done correctly (e.g.: making sure there is no SQL injection problems and sufficient error-trapping), it will take more than a line of code (unless it's a fairly long line ? ).
For instance:
@NogDogJun 19.2008 — #Check these manual pages first, then get back to us with any still unresolved questions:
http://www.php.net/manual/en/mysqli-stmt.prepare.php
http://www.php.net/manual/en/mysqli-stmt.bind-param.php @NogDogJun 19.2008 — #The "WHERE" does not belong in your query where you have it.
You will want to use the mysql_real_escape_string() function to prevent SQL injection. (Seehttp://www.php.net/mysql_real_escape_string for an explanation, as well as http://xkcd.com/327/ .)
Also, there's no real need to copy the values from $_POST into new variables if you are not going to change the data in any manner. You could therefore simply construct your query as:
@NogDogJun 19.2008 — #
As PHP is loosely typed, you normally do not need to worry about such conversions. If you want to validate that the user entered an integer, you can use the ctype_digit() function to check it. If you just want to quietly convert whatever they entered to an integer, you can cast it either via the[b](int) [/b] [url=http://www.php.net/manual/en/language.types.type-juggling.php]type-casting[/url] mechanism, or you could use the [url=http://www.php.net/settype]settype() function[/url] .
Unless I made a typo somewhere (always a possibility), there should be no "extra" quotes. However, in order to use the mysql_real_escape_string() function I used concatenation while building the query string, so had to "interrupt" the string by closing it with a quote, add a concatenation operator (the dot), do the function call, another dot, then a quote to get back into the string. An alternative method would be to use sprintf():
@NogDogJun 19.2008 — #%d (think of "[b]d[/b]igit" to remember it)
PS: and in this case you won't need to quote it in the query, nor filter it through mysql_real_escape_string() (but always filter any such inputs that are not explicitly converted to integers or floats).@NogDogJun 20.2008 — #Each argument supplied to sprintf() after the first one (the pattern string) is correlated to each successive %<char> place-holder. So in the following, $string is applied to %s, $integer to %d, and $float to %f:
I doubt that the mysql functions will go away any time in the foreseeable future, as they are so widely used. But if creating new applications that do not need to be PHP4-compatible; then mysqli gives you more functionality, support of more of the newer MySQL 4.1/5.x features, and can be used in an object-oriented manner.@NogDogJun 20.2008 — #Each place-holder -- the "%s", "%d", etc. -- in the string that is the first argument to the sprintf() function requires an additional parameter in the sprintf() parameter list. The first such parameter's value is used for the first place-holder, the second for the second one, etc. (There is a special exception to this rule if you use numbered place-holders, but let's pretend that does not exist for now.) The following would therefore echo [b]<p>Hello, World!</p>[/b].
If we reversed the 2nd and 3rd arguments, it would instead print [b]<p>World, Hello!</p>[/b]:
(Note that we could just do [b]printf()[/b] instead of [b]echo sprintf()[/b], but I didn't want to change functions here.)
Inserting Data Into MySQL
What would be the correct way to write a line of PHP code that inserts data into your MySQL database?
Sign in
to post a comment24 Comments(s) ↴
0
@NogDogJun 19.2008 — #Depends on which database interface you want to use. If done correctly (e.g.: making sure there is no SQL injection problems and sufficient error-trapping), it will take more than a line of code (unless it's a fairly long line ? ).For instance:
[code=php]
$db = new mysqli('localhost', 'user', 'pwd', 'db_name') or die('DB connection error');
$stmt = $db->prepare('INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?)';
$stmt->bind_param('ssi', $var1, $var2, $var3);
if(!$stmt->execute())
{
error_log($stmt->error);
echo "Insert failed, error logged";
}
else
{
echo $stmt->affected_rows . " rows inserted.";
}
[/code]0
@NogDogJun 19.2008 — #Check these manual pages first, then get back to us with any still unresolved questions:0
@NogDogJun 19.2008 — #The "WHERE" does not belong in your query where you have it.You will want to use the mysql_real_escape_string() function to prevent SQL injection. (See
Also, there's no real need to copy the values from $_POST into new variables if you are not going to change the data in any manner. You could therefore simply construct your query as:
[code=php]
$query = "INSERT INTO users (user_id, user_name, user_pwd, user_email) VALUES ('" .
mysql_real_escape_string($_POST['userid']) . "', '" .
mysql_real_escape_string($_POST['username']) . "', '" .
mysql_real_escape_string($_POST['password']) . "', '" .
mysql_real_escape_string($_POST['useremail']) . "')";
[/code]0
@NogDogJun 19.2008 — #Well, I just realized that I need to parse the userid form field into an integer, since that's what that field in the database is set to. However, I've never done that in PHP, so could someone help me out?[/quote]
As PHP is loosely typed, you normally do not need to worry about such conversions. If you want to validate that the user entered an integer, you can use the ctype_digit() function to check it. If you just want to quietly convert whatever they entered to an integer, you can cast it either via the
Also, what are all the extra quote after the mysql_real_escape_string for?[/QUOTE]
Unless I made a typo somewhere (always a possibility), there should be no "extra" quotes. However, in order to use the mysql_real_escape_string() function I used concatenation while building the query string, so had to "interrupt" the string by closing it with a quote, add a concatenation operator (the dot), do the function call, another dot, then a quote to get back into the string. An alternative method would be to use sprintf():
[code=php]
$query = sprintf(
"INSERT INTO users (user_id, user_name, user_pwd, user_email) VALUES ('%s', %s', '%s', '%s')",
mysql_real_escape_string($_POST['userid']),
mysql_real_escape_string($_POST['username']),
mysql_real_escape_string($_POST['password']),
mysql_real_escape_string($_POST['useremail'])
);
[/code]0
@NogDogJun 19.2008 — #%d (think of "[b]d[/b]igit" to remember it)PS: and in this case you won't need to quote it in the query, nor filter it through mysql_real_escape_string() (but always filter any such inputs that are not explicitly converted to integers or floats).
0
@NogDogJun 20.2008 — #Each argument supplied to sprintf() after the first one (the pattern string) is correlated to each successive %<char> place-holder. So in the following, $string is applied to %s, $integer to %d, and $float to %f:[code=php]
$string = sprintf("The string: '%s', the integer: %d, the float: %f", $string, $integer, $float);
[/code]I doubt that the mysql functions will go away any time in the foreseeable future, as they are so widely used. But if creating new applications that do not need to be PHP4-compatible; then mysqli gives you more functionality, support of more of the newer MySQL 4.1/5.x features, and can be used in an object-oriented manner.
0
@NogDogJun 20.2008 — #Each place-holder -- the "%s", "%d", etc. -- in the string that is the first argument to the sprintf() function requires an additional parameter in the sprintf() parameter list. The first such parameter's value is used for the first place-holder, the second for the second one, etc. (There is a special exception to this rule if you use numbered place-holders, but let's pretend that does not exist for now.) The following would therefore echo [b]<p>Hello, World!</p>[/b].[code=php]
$var1 = 'Hello';
$var2 = 'World';
echo sprintf("<p>%s, %s!</p>", $var1, $var2);
[/code]If we reversed the 2nd and 3rd arguments, it would instead print [b]<p>World, Hello!</p>[/b]:
[code=php]
echo sprintf("</p>%s, %s!</p>", $var2, $var1);
[/code](Note that we could just do [b]printf()[/b] instead of [b]echo sprintf()[/b], but I didn't want to change functions here.)