Is there a problem with this MySQL Query?

0
votes
public function create() {


        echo $this->equipment->getCatId() . "<br/>";
        echo $this->equipment->getName() . "<br/>";
        echo $this->equipment->getYear() . "<br/>";
        echo $this->equipment->getManufacturer() . "<br/>";
        echo $this->equipment->getModel() . "<br/>";
        echo $this->equipment->getPrice() . "<br/>";
        echo $this->equipment->getLocation() . "<br/>";
        echo $this->equipment->getCondition() . "<br/>";
        echo $this->equipment->getStockNum() . "<br/>";
        echo $this->equipment->getInformation() . "<br/>";
        echo $this->equipment->getDescription() . "<br/><br/>";


        $db = Connect::connect();
        $current_time = date('y M d');
        $query = "INSERT INTO equipment (cat_id, name, year, manufacturer, model, price, location, condition,
                                         stock_num, information, description, created, modified)

                                         VALUES

                                        ({$this->equipment->getCatId()}, {$this->equipment->getName()}, {$this->equipment->getYear()},
                                         {$this->equipment->getManufacturer()}, {$this->equipment->getModel()}, {$this->equipment->getPrice()},
                                         {$this->equipment->getLocation()}, {$this->equipment->getCondition()}, {$this->equipment->getStockNum()},
                                         {$this->equipment->getInformation()}, {$this->equipment->getDescription()}, '$current_time', '$current_time')";

        $result = $db->query($query);

        return $db->insert_id;

    }
  • The echos at the top all display valid data that fit the database schema.
  • There are no connection errors

Any ideas?

Thanks in advance!

Here is the echo'ed query

INSERT INTO equipment (cat_id, name, year, manufacturer, model, price, location, condition, stock_num, information, description, created, modified) VALUES (1, 'r', 1, 'sdf', 'sdf', '2', 'd', 'd', '3', 'asdfasdfdf', 'df', '10 May 10', '10 May 10')

MySQL is giving: #1064 - 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 'condition, stock_num, information, description, created, modified) VALUES (1, 'r' at line 1

id int(11) unsigned NO PRI NULL auto_increment Edit Delete cat_id int(11) unsigned NO NULL
Edit Delete prod_name varchar(255) YES NULL
Edit Delete prod_year varchar(10) YES NULL
Edit Delete manufacturer varchar(255) YES NULL
Edit Delete model varchar(255) YES NULL
Edit Delete price varchar(10) YES NULL
Edit Delete location varchar(255) YES NULL
Edit Delete condition varchar(25) YES NULL
Edit Delete stock_num varchar(128) YES NULL
Edit Delete information text YES NULL
Edit Delete description text YES NULL
Edit Delete created varchar(20) YES NULL
Edit Delete modified varchar(20) YES NULL

Query: INSERT INTO equipment (cat_id, prod_name, prod_year, manufacturer, model, price, location, condition, stock_num, information, description, created, modified) VALUES (1, 'asdf', '234', 'adf', 'asdf', '34', 'asdf', 'asdf', '234', 'asdf', 'asdf', '10 May 10', '10 May 10')

Here is the SQL export from PhpMyAdmin in case someone wants to try duplicating this issue: http://pastie.org/954206

BLEHBLEHSDFOHSE -- So apparently, 'condition' is also a reserved word... threw in some backticks, then it started working.

6
phpmysqlinsertobject
didn't knew about that one too.. nice catch. :-) - acm
Syntax errors happen way before MySQL would touch the database, so they cannot be related with anything not present in that single query (such as column types). The error message for syntax errors is ridiculously unhelpful (though the quoted string does start at the exact point of error); using something that can syntax highlight (such as the MySQL Query Browser) helps to avoid a lot of basic errors without wasting much time. - Tgr
That SQL/PHP mix is hideous, you need to use MySQL binding statements instead. - TravisO

6 Answers

1
votes

YEAR is a reserved word in MySQL. If you're going to use it, you're going to need to backtick (i.e. `year`)

0
votes

Assuming location, information and description are strings quotes are missing.

INSERT INTO equipment (cat_id, name, year, manufacturer, model, price, location, condition,
                                         stock_num, information, description, created, modified)

                                         VALUES

                                        ({$this->equipment->getCatId()}, {$this->equipment->getName()}, {$this->equipment->getYear()},
                                         '{$this->equipment->getManufacturer()}', {$this->equipment->getModel()}, {$this->equipment->getPrice()},
                                         '{$this->equipment->getLocation()}', {$this->equipment->getCondition()}, {$this->equipment->getStockNum()},
                                         '{$this->equipment->getInformation()}', '{$this->equipment->getDescription()}', '$current_time', '$current_time')";
0
votes

Varchar, char and text fields need quotes around them to insert. Echo out the SQL query and check this is happening.

0
votes

as far as i see it at this point, your problem was year field with no quotes...

INSERT INTO equipment (cat_id, name, year, manufacturer, model, price, location, condition, stock_num, information, description, created, modified) VALUES (1, 'r', 1, 'sdf', 'sdf', '2', 'd', 'd', '3', 'asdfasdfdf', 'df', '10 May 10', '10 May 10')

here, the problem is cat_id WITH quotes

Query: INSERT INTO equipment (cat_id, name, year, manufacturer, model, price, location, condition, stock_num, information, description, created, modified) VALUES ('1', 'asdf', '234', 'adf', 'asdf', '34', 'asdf', 'asdf', '234', 'asdf', 'asdf', '10 May 10', '10 May 10')

0
votes

BLEHBLEHSDFOHSE -- So apparently, 'condition' is also a reserved word... threw in some backticks, then it started working.

0
votes

Stop using mysql_query() and switch to mysqli and prepared statements, then you don't need all that obtuse OOP in your SQL string. It would look cleaner like this:

$query = 'INSERT INTO table (id,name,age) VALUES (?,?,?)';