[gemoney]

I am getting this error: Invalid query: 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 '' at line 14. This is happening when I try to add the data, so I believe it most be from my commit file. I can't find where I am missing the quotes. Thank you.

<?php
     // COMMIT ADD
    include "dblogin.php";
    switch ($_GET['action']) {
        case "add":
            switch ($_GET['type']) {
                case "vacancy":
                    $sql = "INSERT INTO vacancy
                            (taxparcel,
                            address,
                            city,
                            state,
                            zip,                            
                            description,
                            latitude,
                            longtitude)
                        VALUES
                            ('" . $_POST['taxparcel'] . "',
                            '" . $_POST['address'] . "',
                            '" . $_POST['city'] . "',
`                           '" . $_POST['state'] . "',
                            '" . $_POST['zip'] . "',
                            '" . $_POST['description'] . "',
                            '" . $_POST['latitude'] . "',
                            '" . $_POST['longtitude'] . "')
                       ";
            break;
        }
        break;
    }
    if (isset($sql) && !empty($sql)) {
        echo "<!--" . $sql . "-->";
        $result = mysql_query($sql)
            or die("Invalid query: " . mysql_error());
        ?>
            <p align="center" style="color:#FF0000">
                Done. <a href="database_index.php">Database Index</a>
            </p>
      <?php
    }
?>

[JackOfAllTrades]

You're echoing out the SQL in your page source (commented out) here:

echo "<!--" . $sql . "-->";

So view your page source and take a look at the failing query. If you can't figure it out, post that query here.

Also, you are wide open to SQL injection attacks. Never EVER use unfiltered user input in a database query.

[gemoney]

JackOfAllTrades, on 17 May 2012 - 06:22 AM, said:

You're echoing out the SQL in your page source (commented out) here:

echo "<!--" . $sql . "-->";

So view your page source and take a look at the failing query. If you can't figure it out, post that query here.

Also, you are wide open to SQL injection attacks. Never EVER use unfiltered user input in a database query.

Thanks JackOfAllTrades, but this is my 3rd on php/mysql. I read and did a two week crash course on this, so I am still learning and I haven't validated user input yet until get the code working.
So my problem still exist and I can't find the source of the error. I posted my inserting query I believed had the error
in. Here is example of the query I have used to insert data using the sql command in MYSQL

INSERT INTO `properties`.`vacancy` ( 
`vacancy_id` ,
`taxparcel` ,
`address` ,
`city` ,
`state` ,
`zip` ,
`description` ,
`latitude` ,
`longtitude` 
)
VALUES ( 
NULL , '1002150000', '16 E WATER ST', 'INDIANA', 'PA', '15701', 'none yet', '40.303562', '-79.543918'
);

my php inserting code giving me the error:

<?php
     // COMMIT ADD and EDIT
    include "dblogin.php";
    switch ($_GET['action']) {
        //Edit data
        case "edit":
            switch ($_GET['type']) {
                case "vacancy":
                    $sql = "UPDATE vacancy SET
                            taxparcel = '" . $_POST['taxparcel'] . "',
                            address = '" . $_POST['address'] . "',
                            city = '" . $_POST['city'] . "',
                            state = '" . $_POST['state'] . "',
                            zip = '" . $_POST['zip'] . "',
                            description = '" . $_POST['description'] . "',
                            latitude = '" . $_POST['latitude'] . "',
                            longtitude = '" .$_POST['longtitude']. "',                            
                            WHERE vacancy_id = '" . $_GET['id'] . "'";
                    break;
                }
                break;
         //Inserting data   
        case "add":
            switch ($_GET['type']) {
                case "vacancy":
                    $sql = "INSERT INTO vacancy
                            (taxparcel,
                            address,
                            city,
                            state,
                            zip,                            
                            description,
                            latitude,
                            longtitude)
                        VALUES
                            ('" . $_POST['taxparcel'] . "',
                            '" . $_POST['address'] . "',
                            '" . $_POST['city'] . "',
`                           '" . $_POST['state'] . "',
                            '" . $_POST['zip'] . "',
                            '" . $_POST['description'] . "',
                            '" . $_POST['latitude'] . "',
                            '" . $_POST['longtitude'] . "')
                       ";
            break;
        }
        break;
    }
    if (isset($sql) && !empty($sql)) {
        //echo "<!--" . $sql . "-->";
        $result = mysql_query($sql)
            or die("Invalid query: " . mysql_error());
        ?>
            <p align="center" style="color:#FF0000">
                Done. <a href="database_index.php">Database Index</a>
            </p>
      <?php
    }
?>

[the1corrupted]

Something that I think you should start with is NOT the old mysql_* functions. If you're new, you should really start off with PDO and learn the more secure methods in which to interact with the database. Could save your bacon one day.

Anyway, you might also be able to figure out your error if you switch to a PDO object. Or at the very least, the mysqli_* functions for better performance.

PDO Index

But when troubleshooting mysql queries, it's always best to figure out which line MySQL is talking about, and what your output query looks like. HTML will parse it as one line, but MySQL will parse several lines (as it appears in the PHP code). So the empty string on "Line 1" refers to the "INSERT INTO" or "UPDATE" lines of your queries (in this case).

Also, if you have vacancy_id not set as a NULL database value (such as Unique Auto-Increment), do NOT put NULL. Don't call the field at all. It will take care of itself. Otherwise, you might wind up with NULL ids.

So an insert query: "INSERT INTO `table` (`name`, `password`) VALUES ('myname', 'somepass')"

But on this table, I have an `id` field. It's an unsigned int not null, unique, auto-increment. The ID automatically fills itself. This might also be your error.

[JackOfAllTrades]

You don't understand.

Quote

Here is example of the query I have used to insert data using the sql command in MYSQL

We don't WANT what you know works in MySQL. We want what your PAGE is generating for the query. Like I told you, you're outputting the SQL query in an HTML comment with in the page. In your browser, run the failing query and view the HTML source of the page. If you don't understand this, then change this line:

$result = mysql_query($sql)
            or die("Invalid query: " . mysql_error());

to

$result = mysql_query($sql)
            or die("Invalid query $sql: " . mysql_error());

[gemoney]

Thanks for the idea the1corrupted, this is what I already learned about so I will make it due for this project for now, then after this get another Lynda.com learning video for PDO. Alright I got my error fix finally now my editing table is given me an error at my WHERE clause:
Invalid query: 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 'WHERE vacancy_id = '6'' at line 6
here is the editing setup code in my property.php

switch ($_GET['action']) {
        //Edit database
        case "edit":
        $vacantsql = "SELECT * FROM vacancy
                    WHERE vacancy_id = '" . $_GET['id'] . "'";
        $result = mysql_query($vacantsql)
            or die("Invalid query: " . mysql_error());
        $row = mysql_fetch_array($result);
        $taxparcel = $row['taxparcel'];
        $address = $row['address'];
        $city = $row['city'];
        $state = $row['state'];
        $zip = $row['zip'];        
        $description = $row['description'];
        $latitude = $row['latitude'];
        $longtitude = $row['longtitude'];
        break;
    default:
        $taxparcel = "";
        $address = "";
        $city = "";
        $state = "";
        $zip = "";        
        $description = "";
        $latitude = "";
        $longtitude = "";        
    break;
    }

and here is the edit commit code in commit.php

$taxparcel = $_POST['taxparcel'];
    $address = $_POST['address'];
    $city = $_POST['city'];
    $state = $_POST['state'];
    $zip = $_POST['zip'];        
    $description = $_POST['description'];
    $latitude = $_POST['latitude'];
    $longtitude = $_POST['longtitude'];
    
    switch ($_GET['action']) {
        //Edit data
        case "edit":
            switch ($_GET['type']) {
                case "vacancy":
                    $sql = "UPDATE vacancy SET
                                taxparcel = '$taxparcel',address = '$address',
                                city = '$city',state = '$state',zip = '$zip',
                                description = '$description',latitude = '$latitude',
                                longtitude = '$longtitude',                            
                        WHERE vacancy_id = '" . $_GET['id'] . "'"; //ERROR IS HAPPENING HERE
                    break;
                }
                break;

[gemoney]

here is the source code being output from my update error...
<!--UPDATE vacancy SET
taxparcel = '33445600',address = '80 Water st ',
city = 'Indiana',state = 'GA',zip = '15705',
description = 'Good AND functional ',latitude = '40.302998',
longtitude = '-79.541527',
WHERE vacancy_id = '6'-->Invalid query: 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 'WHERE vacancy_id = '6'' at line 6

[Dormilich]

there’s a comma before WHERE that does not belong there.