I am trying to put height into mysql database table through an entry made in php. All other fields, name, age, etc. work fine, but when it comes to putting height in the foot-inch format (5'6") there is a problem.
How do I make it so the characters ' and " can be inserted into mysql table?
If it helps, this is the message I get when the form is submitted: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6"')' at line 2
Mysql_real_escape_string will fix that issue as already mentioned. But this also means that you're not properly sanitizing your input variables. Just remember to always run any input through a sanitizing function like mysql_real_escape_string(), or else you will make yourself vulnerable to sql injection attacks.
And for future reference, if any character may be interpreted as anything besides data in mysql you can also manually escape that character using a \ and it will be ignored.
Great, thanks. I thought I had to use mysql_real_escape_string, but I wasn't sure how.
Thankfully this is all for test sites so I can become familiar with php/html/java before doing anything final, so filtering is not my TOP priority. Just as long as it works, for now. I have no hosts yet, just using localhost.
One more thing, what is
PHP Code:
$db_conn
? It all worked, but what does: mysql_real_escape_string() expects parameter 2 to be resource, null given in
mean?
Great, thanks. I thought I had to use mysql_real_escape_string, but I wasn't sure how.
Thankfully this is all for test sites so I can become familiar with php/html/java before doing anything final, so filtering is not my TOP priority. Just as long as it works, for now. I have no hosts yet, just using localhost.
One more thing, what is
PHP Code:
$db_conn
? It all worked, but what does: mysql_real_escape_string() expects parameter 2 to be resource, null given in
mean?
$dn_conn is the handle used for the database connection, and your error message is related to the fact that $db_conn is not the name of the handle you used for the database connection, hence its null.
However, i don't believe you have to specify the connection handle unless you have multiple database connections going on at once as it will automatically use the last database connection made.
Hm. its still not working. I'll give you the code. When I submit 5'6" for example, it only shows the 5. Also, whenever I refresh the page, 0's appear across the board as if that is what I submitted.
PHP Code:
<?php
//Takes the post from insert.php and sends it to mysql
$Name = $_POST['Name'];
$Age = (int) $_POST['Age'];
$Height = $_POST['Height'];
//connects to mysql with "server", "user", "password"
mysql_connect ("localhost", "root", "")
or die(mysql_error());
//select the database "sam"
mysql_select_db ("sam");
//sends a query to mysql with the "command"
$query="insert into text (Name, Age, Height) values
('" . mysql_real_escape_string($Name, mysql_connect ("localhost", "root", "")) ."',
'" . mysql_real_escape_string($Age, mysql_connect ("localhost", "root", "")) ."',
'" . mysql_real_escape_string($Height, mysql_connect ("localhost", "root", "")) ."')
";
//tells mysql to do the query
mysql_query($query)
or die (mysql_error());
echo "Updated to: " .$Name. " , " .$Age. " , " .$Height ;
?>
<?php
// Connects to your Database from mysql
// Collects data from "text" table
// Add a 'where' clause to only show a certain number of ppl on the table
$data = mysql_query("select * from text")
or die(mysql_error());
// Make the cells look good
Print "<table border=2 cellpadding=3 cellspacing=2 width=20%>";
/* Puts the "text" info into the $info array
"while" makes the retrieve command a loop so it collects all data*/
// Print out the contents of the entry
{
Print "<tr>";
Print "<th>Name:</th>";
Print "<th>Age:</th>";
Print "<th>Height:</th>";
Print "</tr>";
}
while($info = mysql_fetch_array( $data ))
{
Print "<tr>";
Print "<td>" .$info['Name'] . "</td>";
print "<td>" .$info['Age'] . "</td>";
print "<td>" .$info['Height'] . "</td>";
print "</tr>";
}
Print "</table>";
?>
//connects to mysql with "server", "user", "password"
$db_conn = mysql_connect("localhost", "root", "") or die(mysql_error());
//select the database "sam"
mysql_select_db("sam", $db_conn);
//sends a query to mysql with the "command"
$query = "insert into text (Name, Age, Height) values
('" . mysql_real_escape_string($Name, $db_conn) . "',
'" . mysql_real_escape_string($Age, $db_conn) . "',
'" . mysql_real_escape_string($Height, $db_conn) . "')
";
//tells mysql to do the query
mysql_query($query) or die (mysql_error());
Don't add mysql_connect ("localhost", "root", "") into every mysql_real_escape_string(). Instead create a single handle from the original connection and use that:
PHP Code:
<?php
//Takes the post from insert.php and sends it to mysql
$Name = $_POST['Name'];
$Age = (int) $_POST['Age'];
$Height = $_POST['Height'];
//connects to mysql with "server", "user", "password"
$dbconn=mysql_connect ("localhost", "root", "")
or die(mysql_error());
//select the database "sam"
mysql_select_db ("sam");
//sends a query to mysql with the "command"
$query="insert into text (Name, Age, Height) values
('" . mysql_real_escape_string($Name, $dbconn) ."',
'" . mysql_real_escape_string($Age, $dbconn) ."',
'" . mysql_real_escape_string($Height, $dbconn) ."')
";
//tells mysql to do the query
mysql_query($query)
or die (mysql_error());
echo "Updated to: " .$Name. " , " .$Age. " , " .$Height ;
?>
<?php
// Connects to your Database from mysql
// Collects data from "text" table
// Add a 'where' clause to only show a certain number of ppl on the table
$data = mysql_query("select * from text")
or die(mysql_error());
// Make the cells look good
Print "<table border=2 cellpadding=3 cellspacing=2 width=20%>";
/* Puts the "text" info into the $info array
"while" makes the retrieve command a loop so it collects all data*/
// Print out the contents of the entry
{
Print "<tr>";
Print "<th>Name:</th>";
Print "<th>Age:</th>";
Print "<th>Height:</th>";
Print "</tr>";
}
while($info = mysql_fetch_array( $data ))
{
Print "<tr>";
Print "<td>" .$info['Name'] . "</td>";
print "<td>" .$info['Age'] . "</td>";
print "<td>" .$info['Height'] . "</td>";
print "</tr>";
}
Print "</table>";
?>
Guys, thanks again for your time.
But is there some kind of variable that needs to be places in mysql_real_escape_string?
The same thing keeps happening. 5'5" becomes in the display table. But the "updated to" text says 5'5". I don't know what I'm doing wrong, but there's something. I followed ur direction to a T.
I cleaned up your code the way I like it, I really hope it works :P
Just make sure you have the correct data types in your text table, and enough room for the strings...
// Connects to your Database from mysql
// Collects data from "text" table
// Add a 'where' clause to only show a certain number of ppl on the table
$data = mysql_query('SELECT * FROM `text`', $db_conn) or die(mysql_error());
// Make the cells look good
echo '<table border="2" cellpadding="3" cellspacing="2" width="20%">';
/* Puts the "text" info into the $info array
"while" makes the retrieve command a loop so it collects all data*/
// Print out the contents of the entry
echo
'<tr>' .
'<th>Name:</th>' .
'<th>Age:</th>' .
'<th>Height:</th>' .
'</tr>';
Yes! that's what it was. I guess I had the "height" column as (int) in my database. When I changed it to varchar it worked.
Also, I changed the "Print" to "echo" cuz it looks a lot better.
Haha, guy after my own heart. Samus is boss too. Gonna go in the the Varia suit next anime expo, tell me ppl won't think I'M weird.
Link is my absolute favorite though.
Reply With Quote
Originally Posted by SamE4u
Bookmarks