MySQL query takes too long in codeigniter

I’ve made this query

SELECT id,name,iupac_name,inchi_key,molecular_weight,molecular_formula,exact_molecular_mass     FROM compound     WHERE name LIKE '%quer%'     OR iupac_name LIKE '%quer%'     OR inchi_key LIKE '%quer%'     OR inchi_key IN( SELECT inchi_key FROM compound_synonyms WHERE name LIKE '%quer%') 

that took 0.3563 seconds to show result through phpmyadmin in my local machine and took 0.1117 seconds in digital ocean vps.

But the same query took 24+ minutes in another vps.

Does anyone can help me to find out the problem?

How to setup REST API in Codeigniter 3.0

I am creating the REST API using codeigniter. I downloaded this library and installed in my application. It works great! https://github.com/chriskacerguis/codeigniter-restserver

But I have an doubt. As per this library. I have 4 functoins

  1. GET
  2. POST
  3. PUT
  4. DELETE

I understand the functionality of each POST method but what I concern is how to write the validation and other things in the REST API.

Example: I have created the controller User.php

class User extends REST_Controller {       public function __construct()       {         parent::__construct();                     }       // Get multile / individual users records and perform search also.      public function index_get()      {            $  this->response('get / search users', REST_Controller::HTTP_OK);      }       // User Registration / Login      public function index_post()      {             echo 'create';      }       // User Information Update      public function index_put()      {          echo 'update';      }       // User Delete      public function index_delete()      {          echo 'delete';      }     } 

I tested in postman post method works fine.

Here is my question. I have the following functionality for user.

  1. Register
  2. Login
  3. Forgot Password
  4. Change Password
  5. Update Profile

I think except Register other functions are posted with PUT method? In the PUT / POST method do I have to write all validations and database operation buy identity the post method variable using action likes register, login, forgot-password etc.,

In admin I want to get active or inactive users, find by email or name. this kind of operations where we have to write?

Thanks to all.

Codeigniter models optimization

I have two tables:

tableA

aid name age sex email password

tableB

bid idno aid

The steps should be

  • Enter, verify email and password

  • If email and password are correct, insert random number into tableB as idno

  • Select name, age, sex, email, idno from both tables and return an array as result.

  • If email and password are wrong, do nothing.

I wrote the following code in Codeigniter model

function login($  data) {     $  email = $  data["email"];     $  password = $  data["password"];     $  sql = "select aid from tableA where email='".$  email."' and password='".$  password."'";     $  query = $  this->db->query($  sql);     if ($  query->num_rows() > 0) {         $  ret = $  query->row();         $  idno = random();         $  aid = $  ret->aid;         $  sql = "insert into tableB (idno, aid) values('".$  idno."','".$  aid."')";         $  query = $  this->db->query($  sql);         if ($  query) {             $  sql = "select '".$  idno."' as idno, name, age, sex, email from tableA where aid='".$  aid."'";             $  query = $  this->db->query($  sql);             if ($  query->num_rows() > 0) {                 return $  query->result_array();             }             return $  query->num_rows();          } else {             return false;          }          return false;     }     return $  query->num_rows(); } 

The code works, but I think the code is so ugly and not efficient. How can I optimize the code?

How to route my web app address codeigniter in linux

i want ask question about routing web, i’m developing web app with codeigniter and linux is my server,

if i want call my web in browser i just call like this

http://169.69.9.69/my_webapp (example)

but i want just call

http://169.69.9.69

in linux server how to configure route for my web app just call the linux server address (remove backslash(/) and webapp path)?

Query between 2 dates in Codeigniter, Date only from Datetime

I want to query all data between two date fields. The problem I am having is my database has a Datetime field and I only want to query the dates. I normally can get this to work with a like statement but I need to add logic and active record for like does not seem to support this. I used multiple where statements and was able to get this to work by adding “+1days to the to_date field. It seems to work but I don’t like it and wonder if there is a better way to go about this. If I dont add the +1 days to my code it only return the < and > and does not return any rows where field = date. Hopefully this makes sense.

I have tried the below code and it does not return anything where date = myInput.

$  query = $  this->db->select('*')->from('t_clock_punch')                    ->where('clock_in >=',date("Y-m-d", strtotime($  fromDate)))                    ->where('clock_in <=', date("Y-m-d", strtotime($  toDate)))                    ->get();     return $  query->result_array(); } 

I just added a day to my ->where clause and it seems to work just not sure if this is correct and may be problematic later on.

 ->where('clock_in <=', date("Y-m-d", strtotime($  toDate . '+1 days'))) 

Codeigniter 3 micro-blogging application

I am working on a basic blog application in Codeigniter 3.1.8 and Bootstrap 4.

The application allows Registration (as an author), Login, adding Categories and Posts.

I have created an installation process for he application: after creating a database and providing its credentials to the application/config/database.php file, you can run the Install controller which will create all the necessary tables:

class Install extends CI_Controller {     public function __construct()     {         parent::__construct();     }      public function index(){         // Create all the database tables if there are none         // by redirecting to the Migrations controller         $  tables = $  this->db->list_tables();         if (count($  tables) == 0) {             redirect('migrate');         } else {             redirect('/');         }     } } 

After that, you can register as an author. Being the first registered author, you are also an admin, meaning that your author account does not require activation (the is_admin column has a value of 1 for you).

All the future authors will need their accounts activated by you in order to publish articles (posts).

It is a rather ambitious project, although I did not start it with great plans in mind. I just wanted to learn Codeigniter 3. You can see it on my GitHub account.

There are a few possible issues:

  1. The application does not use HMVC, but “classic” MVC.
  2. It does not have a clear separation between its front its back (admin)
  3. I want to add a theming feature and I kinda got stuck with it; I don’t know what approach to use.

Please help me with useful feedback and suggestions. Thanks!