1

I have a database with the first table, wherein the table comprises 3 fields like the example below, but I do not understand how to create a query by taking one of the data on the name field that has a few words and?

Table school

 id   |         name       |  class
--------------------------------------
 01   | smith, john, billy |   III
 02   | ana, jach, georgre |   IV

Controllers

//suppose I want to take the data 'smith' on the field name
    $name = $this->input->post('name');
    $data = $this->models->get_name($name);
    $this->load->view('layout/wrapper', $data);

example Query on Models

public function get_name($name){

    $sql = "SELECT * FROM school 
            WHERE name.name = '".$name."'";

    return $this->db->query($sql)->row_array();
}
irwan dwiyanto
  • 690
  • 1
  • 9
  • 28
  • U should use array. I think so! – Myat Htut Oct 19 '16 at 10:01
  • show your expected result please – ScaisEdge Oct 19 '16 at 10:03
  • 1
    Think you may need to alter the way you are storing your data. Have a row for each name so `01 - smith - III, 02 - john - III` and so on. That way you can select the row by name and class – Blinkydamo Oct 19 '16 at 10:03
  • Possible duplicate of [MySQL query finding values in a comma separated string](http://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string) – Shadow Oct 19 '16 at 10:03

2 Answers2

1

Use FIND_IN_SET() function to accomplish this task (Here's the CI way to do that instead raw code)

public function get_name($name){
    $query = $this->db->select('*')->from('school')->where('FIND_IN_SET(' . $name . ',name) > 0', NULL, FALSE)->get();
    return $query->row_array();
}

So now when you will send smith it will return first row.

Rejoanul Alam
  • 5,435
  • 3
  • 39
  • 68
-1

$sql = "SELECT * FROM school WHERE name LIKE ?";

$query = $this->db->query($sql, '%,'.$this->db->escape_like_str($name).',%');

This will return fields from school table, if name = ',name,';

Mayoori - gaja digital agency
  • 254
  • 1
  • 10
  • This is wrong: 1) No protection against SQL injection (`$name` comes straight from `$_POST`) 2) You get results that match partial names (`$name === 'org') 3) Please explain your code... – Jost Oct 19 '16 at 10:29