Inserting data into mysql DB with cakePHP

Question

I have what should be a simply problem to solve, I have data that needs to be inserted into my DB for exporting to CSV in the future however standard PHP code is not working for me, I have tried the following line with no luck:

$insertQuery = ("INSERT INTO `DBNAME`.`TABLENAME` (`1`, `2`, `3`, `4`, `5`, `6`, `7`) VALUES ('{$1}', '{$2}', '{$3}', '{$cityquery['city']}', '{$cityquery['regionName']}', '{$cityquery ['country']}', '{$lang}')");

Thanks in advance!

Answer 1

G'day Steve,

May I ask as to why you are using SQL in CakePHP?

CakePHP uses Models and an ORM approach to managing data. Which can be found here: http://book.cakephp.org/2.0/en/models.html

I'll assume you're using V2 because that is the one I'm proficient with.

CakePHP enforces you to call your database table names in plural form and your Model Classes in Singular (For example)

Database table name = users,
Model i.e (Class) name = User

Now when you want to save data to User, we simply use CakePHP's ORM

$this->User->save(); //Now, assume we want to add a new record, we simply do:
$this->User->save(array('first_name' => 'Melon', 'last_name' => 'Head'));

If we wanted to edit an existing record, there are a few ways to approach it. One example is:

$this->User->id = 1;
$this->User->save(array('email' => '[email protected]'));

OR the one I use

$this->User->save(array('id' => 1, 'email' => '[email protected]'));

I hope this answers your question :)

I'll extend it a little more. You mentioned the table name is 'assessments'. Therefore, in our 'app/Model' folder in CakePHP we'd create a php file called Assessment.php inside the file we have:

class Assessment extends AppModel{}

That is the first step, to connect to the DB, there is a file in 'app/Config/database.php'. When we open this file, we'll see an array:

public $default = array(
    'datasource' => 'Database/Mysql',
    'persistent' => false,
    'host' => 'localhost',
    'login' => 'mysql_user',
    'password' => 'da_password',
    'database' => 'the_db_name',
    'encoding' => 'utf8'
);

Setup this array so it can connect to the db.

Now we go to the Controller. I'll assume you have a Controller setup called AssessmentController.php if you don't thats ok we'll fix that shortly.

In the Controller, we setup a function that will save our data for example

public function index(){
      //Here we'll save the data
      //I'll assume also you know where $1 is coming from.
      //Data from the user comes through the array var $this->request->data
     $this->Assessment->save(
            array(
                 '1' => "{$1}"
            )
     );
}

If you have a different controller name setup, that's ok. CakePHP automatically connects Same Model and Controller names together in the above example we have Assessment Controller and Assessment Model

If we have a different controller for example the controller is called Student, well we simply add an array to the controller called $uses

public $uses = array('Assessment');

This will tell CakePHP 'Hey I need this Model'. CakePHP responds 'Here you go'.