Sql query to filter data in HTML table

Question

0.00/5 (No votes)

See more:

I need to create a search form where users can search using any keyword. But right now when user search its does not work.

I try test my code at search part sql query by simple query like

SQL

SELECT * FROM `course` WHERE CONCAT(`id`, `name`, `department_id`, `course_desc`,`hidden_id`) LIKE '%".$valueToSearch."%'

and it work. so, i know my problem is at sql query for search part only. but still i don't know how to solve my problem. Can anyone help me.

What I have tried:

SQL

if(isset($_POST['search']))

    {
      $valueToSearch = $_POST['valueToSearch'];

      $sql = "select  	
			s1.id		as id,
			s1.name		as name,
			s2.name 	as department_id,
			s1.course_desc 	as course_desc,
			s3.name as hidden_id
	from 		
			course  as s1
	left join	department as s2
		on	s1.department_id=s2.id
	left join hidden as s3
		on	s1.hidden_id=s3.id
      WHERE CONCAT(`id`, `name`, `department_id`, `course_desc`,`hidden_id`) 
      LIKE '%".$valueToSearch."%' 
      LIMIT $start_from, $per_page";
      $result = $mydb->query($sql);
    }

Posted 2-Nov-16 15:42pm

Member 12827990

Updated 2-Nov-16 18:21pm

Add a Solution

Comments

Suvendu Shekhar Giri 2-Nov-16 21:53pm

"so, i know my problem is at sql query for search part only"
What is search part?
Are you referring to the WHERE clause ?

Member 12827990 2-Nov-16 22:07pm

sorry, i mean sql part-select not where clause. Because when i try select all,it work. but right now i have multiple table..

Member 12827990 2-Nov-16 22:26pm

this is 3 table in my database

table 'course'
| id | course | name | department_id |course_desc| hidden_id |
|----|--------------|--------|---------------|----- |-----------|
| 1 | sme | b | 10 | bbbbbb | 1 |
| 2 |smu | a | 11 | aaaaaa | 2

table 'department'
| id | name |jabatan_desc|
|----|-----------|----- |
| 10 | jabatan a | fka |
| 11 |jabatan b | fkb |

table 'hidden'
| id | name |
|----|-----------|
| 1 | show |
| 2 |hide |

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-11-02T18:21:00

When you have multiple tables in your query, you should use an alias for each table and use that alias as a prefix when referring to the columns. Otherwise the database does not know to which column you're referring to if a column with the same name exists in multiple tables.

Try the following:

PHP

$sql = "select  	
			s1.id		as id,
			s1.name		as name,
			s2.name 	as department_id,
			s1.course_desc 	as course_desc,
			s3.name as hidden_id
	from 		
			course  as s1
	left join	department as s2
		on	s1.department_id=s2.id
	left join hidden as s3
		on	s1.hidden_id=s3.id
      WHERE CONCAT(`s1.id`, `s1.name`, `s1.department_id`, `s1.course_desc`,`s1.hidden_id`) 
      LIKE '%".$valueToSearch."%' 
      LIMIT $start_from, $per_page";

Sql query to filter data in HTML table

1 solution

Solution 1

Add your solution here

Preview 0

Sql query to filter data in HTML table

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us