Find solution
How to write a query for PHP and MySQL?

php, mySQL, and HTML Entering an SQL query directly into an HTML form

  • 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','[email protected]','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); ?>

  • Answer:

    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 at Google Answers Visit the source

Was this solution helpful to you?

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.