| View Question |
|
|
 | ||
|
| Subject:
php, mySQL, and HTML Entering an SQL query directly into an HTML form
Category: Computers > Programming Asked by: hawstom-ga List Price: $12.00 |
Posted:
25 Aug 2005 20:07 PDT
Expires: 24 Sep 2005 20:07 PDT Question ID: 560639 |
QUESTION:
Why can't I paste the following SQL into the form I have created and
have it work when it works in phpMyADMIN?
INSERT INTO foods VALUES ("REFRIED BEANS","CANS",30)
The successful answer will explain to me why taking the variable
$_POST['query']
from my HTML form and supplying it to
mysql_query($_POST['query']);
does not work for these INSERT INTO queries. If pertinent to the
question and time permitting, I would like an explanation or pertinent
links to explanations of the nuances ( " ' ` ) involved in making this
work.
Thank you,
Tom Haws
DETAIL:
I am trying to learn the MP in LAMP (mySQL and php). At
http://happyhavengraphics.com/lamp/ I have placed what I am attempting
to start with as a raw sql query input form. See Appendix A below for
the source. That form calls mysql.php. See Appendix B below for the
source.
If I paste either of the following queries into the form I get a nice
result (contacts or foods are the only tables currently in the
database):
SELECT * FROM foods
SELECT * FROM contacts
Why can't I paste the following SQL queries into the form I have
created and have them work?
INSERT INTO foods VALUES ("REFRIED BEANS","CANS",30)
INSERT INTO contacts VALUES ('','Jacob','Smith','01234 567890','00112
334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')
I think it has something to do with quotation marks in php and mySQL.
==Appendix A==
<html>
<head>
<title></title>
</head>
<body>
<h1>Raw mySQL entry</h1>
<form action="mysql.php" method="post">
<p>mySQL query: </p>
<textarea name="query" cols=72 rows=25></textarea>
<p><input type="submit" /></p>
</form>
</body>
</html>
==Appendix B==
<p>This was your query</p>
<p><?php echo $_POST['query']; ?></p>
<?
$user="happyhav_lamp";
$password="lamp";
$database="happyhav_lamp";
mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$result=mysql_query($_POST['query']);
// Connecting, selecting database
$link = mysql_connect(localhost, 'happyhav_lamp', 'lamp')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('happyhav_lamp') or die('Could not select database');
// Performing SQL query
$query = $_POST['query'];
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// Printing results in HTML
echo "<b><center>Database Output</center></b><br><br>";
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
mysql_free_result($result);
// Closing connection
mysql_close($link);
?> | |
| |
| |
|
| ||
|
| Subject:
Re: php, mySQL, and HTML Entering an SQL query directly into an HTML form
Answered By: palitoy-ga on 27 Aug 2005 03:11 PDT Rated:  |
Hello Tom
I'm glad I could help solve your problem.
The problem was indeed caused by the use of "escape characters" in
your string. An escape character causes the MySQL statement to become
nonsense when passed to MySQL for execution.
When a form is submitted these characters are automatically escaped by
the use of a \ character. The use of the php function "stripslashes"
removes these characters from the POST'ed string and therefore allows
MySQL to process the function correctly.
In the solution I have provided if any of the field values you wished
to pass to your MySQL database had an escape character in it, it
should be "escaped" using three back-slash characters.
For example:
INSERT INTO things VALUES ("tom's name")
Should be entered:
INSERT INTO things VALUES ("tom\\\'s name")
After POST and stripslashes this would become:
INSERT INTO things VALUES ("tom\'s name")
This is safe for MySQL as the field "tom's name" is NOT \"tom\'s
name\" as was happening previously.
I hope this is clear, if not please ask for clarification and I will
try and explain it a little more. Basically any escape character that
you wish to remain escaped needs to be proceeded by \\\.
The MySQL reference manual has an excellent page that details which
characters need to be escaped. These are most notably " ' but does
also include linefeeds, return characters and other entities. The
page can be found here:
http://dev.mysql.com/doc/mysql/en/string-syntax.html
PHP also includes several other useful functions for dealing with
MySQL and strings in general.
mysql_real_escape_string -- Escapes special characters in a string for
use in a SQL statement
http://www.php.net/manual/en/function.mysql-real-escape-string.php
addslashes -- Quote string with slashes
http://uk.php.net/manual/en/function.addslashes.php
stripslashes -- Un-quote string quoted with addslashes()
http://uk.php.net/stripslashes
get_magic_quotes_gpc -- Gets the current configuration setting of magic quotes gpc
http://uk.php.net/manual/en/function.get-magic-quotes-gpc.php
Example 3 on the mysql_real_escape_string page gives you an example of
"Best Practice". This includes a small function you can use to ensure
your MySQL statement will execute.
I hope this has answered your query fully, if not please ask for
clarification and I will try to address your additional queries. |
hawstom-ga
rated this answer:
This researcher answered exactly what I asked in the way I requested it. I asked for explanation/links and he/she gave me explanation AND links as well as solving my immediate programming problem. I hope that palitoy-ga is around the next time I have a MP within LAMP question. |
| ||
|
| Subject:
Re: php, mySQL, and HTML Entering an SQL query directly into an HTML form
From: meridius-ga on 26 Aug 2005 10:53 PDT |
The query:
---
INSERT INTO contacts VALUES ('','Jacob','Smith','01234 567890','00112
334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')
---
is invalid, I believe, because you do not have the table column
definitions following "contacts". I'm pretty sure (working from memory
here) that those are required.
My query, new and improved, would be:
---
INSERT INTO contacts(`id`,`firstname`,`lastname`,`somenumber1`,`somenumber2`,`somenumber3`,`email`,`website`)
VALUES ('','Jacob','Smith','01234 567890','00112
334455','01234 567891','johnsmith@gowansnet.com','http://www.gowansnet.com')
--
You will need to ensure that the column definitions (`id`,
`firstname`, etc) match your table (namely, I assume that you did not
name a column 'somenumber1'.
I will let the friendly GA staff finish your answer for you :) Hope I helped. |
| Subject:
Re: php, mySQL, and HTML Entering an SQL query directly into an HTML form
From: hawstom-ga on 26 Aug 2005 21:02 PDT |
Dear meridius-ga, I am sorry, but the query as I gave it was correct. INSERT INTO table VALUES (column1value, ... , columnivalue) is valid, as is the "correction" you gave. See http://www.w3schools.com/sql/sql_insert.asp Thanks for taking the time to opine. |
If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you. |
| Search Google Answers for |
| Google Home - Answers FAQ - Terms of Service - Privacy Policy |