HTML Form > SQL > Google Map Display - Syntax + Logic

Question

My idea is to fill out entries in an HTML form, then save the info to the database (phpmyadmin). Then display the info on google map (exporting).

But it's not working because of a syntax and a logical issue.

My HTML form:

<html>
<head></head>
<body>
    <form method="POST" action="../BackEnd/ShopSetup.php" name="Setup">
    <td>Name</td>
    <td>
        <input type="text" name="name"></td>
    </tr>
     <tr>
         <td>type</td>
         <td>
             <input type="varchar" name="type"></td>
     </tr>
    <tr>
        <td>Address</td>
        <td>
            <input type="text" name="address"></td>
    </tr>
    <tr>
        <td>Email</td>
        <td>
            <input type="email" name="email"></td>
    </tr>
    <tr>
        <td>Phone Number</td>
        <td>
            <input type="varchar" name="phone"></td>
    </tr>
    <tr>
        <td>longitude</td>
        <td>
            <input type="float" name="long"></td>
    </tr>
    <tr>
        <td>latitude</td>
        <td>
            <input type="float" name="lat"></td>
    </tr>
    <tr>
        <td>Opening Hour</td>
        <td>
            <input type="varchar" name="opening"></td>
    </tr>
    <tr>
        <td>Closing Hour</td>
        <td>
            <input type="varchar" name="closing"></td>
    </tr>
    <tr>
        <td>
            <input id="button" type="submit" name="submit" value="Setup"></td>

    </tr>
    <tr></tr>
    </form>
</body>
</html>

My PHP Page On the BackEnd:

ShopSetup.php

     <?php


     include ("../Connections/Connection.php");



     if (isset($_POST["submit"]))
  {
      $name = $_POST["name"];   
      $type = $_POST["type"];
      $address = $_POST["address"];
      $email = $_POST["email"];
      $phone = $_POST["phone"];
      $long = $_POST["long"];
      $lat = $_POST["lat"];
      $opening = $_POST["opening"];
      $closing = $_POST["closing"];

  $sql = "INSERT INTO locations (name, type, address, email, phone, long, lat, opening, closing)
          VALUES('$name', '$type', '$address', '$email', '$phone', '$long', '$lat', '$opening', '$closing')";

  $query = mysql_query($sql);

  if (!$query)
  {
      die ("Error : " .  mysql_error());
  }
  if(empty($name) || empty($type) || empty($address) || empty($email) || empty($phone) || empty($long) || empty($lat) || empty($opening) || empty($closing))
      {
      echo "You did not fill out the required fields.";
      die();  // Note this
      }
  echo "<center></center>";
  }

  ?>
         <h1> Your order is complete!</h1>
                <p class="intro-text">You will see your shop on the map soon<br></p>
                <center> 

  <h3> <a href="../index.php"> go to home page  </a></h3>
  </center>

But when I submit the form I get:

Error : 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 'long, lat, opening, closing) VALUES('', '', '', '', '', '', '', '', '')' at line 1

What is the best HTML attribute to save opening/closing times: weeks, days. hours, minutes, seconds?

There are no legitimate reasons to use the `mysql_*` API any more @Misunderstood — Jay Blanchard, May 01 '15 at 16:47
Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 01 '15 at 16:48
I still did not understand what is the problem? My map is exporting the info from phpmyadmin database and displaying it on the map.. but instead of filling it out on manually a user should fill it out as a form that stores info on the database... so should I change the columns names of "long, lat, closing, opening" to something else? @Misunderstood — Naz970, May 01 '15 at 16:53
also consult on this http://dev.mysql.com/doc/refman/5.6/en/reserved-words.html — angelo bodas, May 01 '15 at 17:00
@JayBlanchard Consider someone my not be using a current version of PHP and would rather use a version with no security vulnerabilities. mysli has had some very serious vulnerabilities recently. I understand your point of view, but if someone wants to use mysql it's their choice. I have to maintain HIPAA compliant site where I can be fined $50,000+ if patient data is compromised. mysqli is a work in progress and occasionally introduces vulnerabilities. mysql is only depreciated if I upgrade PHP which I have no reason to do. If it ain't broke why fix it? Is that not a legitimate reason? — Misunderstood, May 01 '15 at 17:05
@nevos spelled it out. Use grave accents (what nevos calls backticks) around column names. Consider renaming the column `long`. — Misunderstood, May 01 '15 at 17:06
@Misunderstood, didn't know they were called grave accents :P. Learning something new everyday :D — nevos, May 01 '15 at 17:11
@nevos backtick is correct as is backquote. In the unicode character set it is technically referred to as grave accent. I made the reference to your backtick because I had used grave accent in a previous comment. — Misunderstood, May 01 '15 at 17:24
I did what you told me @nevos but I still have the same problem... Error : 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 ''name', 'type', 'address', 'email', 'phone', 'lng', 'lat', 'opening', 'closing')' at line 1 and next to every variable of the above it says: "Do not access Superglobal $_Post Array Directly — Naz970, May 01 '15 at 17:36
@Misunderstood: what was the nature of the MySQLi vulnerabilities you refer to? (I imagine it is better that such vulns are uncovered in this supported library, than not finding vulns in the mysql_ library, where they may not be detected or fixed). — halfer, May 01 '15 at 17:47
Not in the US here, but does computer security legislation you are subject to not require you to upgrade from an unsupported version of PHP? — halfer, May 01 '15 at 17:51
Sure, that is legitimate, to a point. Many of the posters here do not have control of their servers @Misunderstood and we're already starting to see the effects [an example where `mysql_*` functions are removed.](http://stackoverflow.com/questions/26299564/php-version-upgraded-cannot-use-few-functions). PDO *has not* had the vulnerabilities the MySQLi API has suffered from though and is what is typically recommended. To call the folks "SQL grammar nazis" for bringing awareness to the issue is a little bit misguided though. — Jay Blanchard, May 01 '15 at 18:00
@nevos did you get your badge today? And no one is "arguing" as far as I can see. — Jay Blanchard, May 01 '15 at 18:03
My comment is not nearly as cynical as yours @nevos and discussion like this can be very germaine to the question. Comments != answers. And you answer is already accepted. ¯\\_(ツ)_/¯ — Jay Blanchard, May 01 '15 at 20:10
@nevos: I have reported two of your contributions above as "not constructive". They might be removed, depending on what the moderator thinks. The comments section is entirely an appropriate place to raise concerns about things like computer security (we try to discourage significant extended discussion, where chat is probably a better forum, but that is not always possible). — halfer, May 02 '15 at 10:06

score 0 · Accepted Answer · edited May 23 '17 at 10:24

0

You should use backticks (`) around your column names. Like so:

$sql = "INSERT INTO locations (`name`, `type`, `address`, `email`, `phone`, `long`, `lat`, `opening`, `closing`)
      VALUES('$name', '$type', '$address', '$email', '$phone', '$long', '$lat', '$opening', '$closing')";

Backticks are recommended for table and column names, but are mandatory when using reserved keywords such as long.

Check out this discussion for more information.

Regarding your second question, have a look at the HTML5 datetime input type. Just note that it is not supported by all browsers

edited May 23 '17 at 10:24

Community

1
1

answered May 01 '15 at 16:59

nevos

907
1
10
22

I did what you told me @nevos but I still have the same problem... Error : 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 ''name', 'type', 'address', 'email', 'phone', 'lng', 'lat', 'opening', 'closing')' at line 1 and next to every variable of the above it says: "Do not access Superglobal $_Post Array Directly – Naz970 May 01 '15 at 17:40
@Naz970: try echoing the SQL you have and then running it directly in your database (e.g. at the console). To debug it, remove all the columns but one, and see if it is successful (or at least if the error changes, as you might have violated constraints). Then add columns in one by one until you find the problem. Maybe you have an unescaped apostrophe in one of your strings? – halfer May 02 '15 at 10:14

HTML Form > SQL > Google Map Display - Syntax + Logic

1 Answers1