CODEWITHSUNDEEP
phpmysqljoomla3.0
Jose Marques
Jose Marques

Reputation: 748

ERROR 1064 iYou have an error in your SQL syntax;

Hi I have an error when I am inserting data into my database:

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 'User,hello,2017-03-30 10:42:43,0)' at line 3 SQL=INSERT INTO #__registentrys (name,type_entry,date_regist,published) VALUES (Super User,hello,2017-03-30 10:42:43,0)

My code:

$greating1=$this->item->greeting;
                $datetime = new Jdate('now + 1 hour'); //GTM hour Lisbon
                $this->item->menssage1 = $datetime;
                //session portection
                $user = JFactory::getUser();
                if($user->guest)
                {
                    $this->item->menssage2 = "You are not allowed to enter this site";
                }
                else
                {
                    $username = $user->name;
                    $this->item->menssage2 = $username;
                    $db1 = JFactory::getDbo();
                    $query1 = $db1->getQuery(true);

                    // Insert columns.
                    $columns = array('name', 'type_entry', 'date_regist', 'published');


                    // Insert values.
                    $values = array($username, $greating1, $datetime, 0);

                    // Build the query
                    $query1
                    ->insert($db1->quoteName('#__registentrys'))
                    ->columns($db1->quoteName($columns))
                    ->values(implode(',', $values));

                    $db1->setQuery($query1);    
                    $db1->execute();

Thanks in advance for your answers.

Upvotes: 0

Views: 195

Answers (3)

Amit Ray
Amit Ray

Reputation: 3485

Joomla database uses $db->quote to save the strings, else it will assume the values as columns. So you need to change the query like this, as per joomla syntax. This line

 $values = array($username, $greating1, $datetime, 0);

Needs to be changed to

 $values = array($db1->quote($username), $db1->quote($greating1), $db1->quote($datetime), 0);

Upvotes: 0

Jose Marques
Jose Marques

Reputation: 748

It's easy to place quotes on values

->values(implode(',', $db1->quote($values)));

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

The literal value mus be inside quote 

 SQL=INSERT INTO `#__registentrys` (`name`,`type_entry`,`date_regist`,`published`) 
 VALUES ( 'Super User', 'hello', '2017-03-30 10:42:43',0)

otherwise are assumed as column name

Upvotes: 1

Related Questions