Welcome Guest, Not a member yet? Register   Sign In
how to serch data from two tables
#1

[eluser]dhananjay[/eluser]
lets user typed a key word now i have to search that keyword from two tables

how i can do that these two tables don't have anything common they both have a column named title and i have to search and get result from both with pagination


Thanks a lot
#2

[eluser]mallix[/eluser]
If a keyword matches two different tables then those 2 tables should be connected in your database design.

What is your database design ?
#3

[eluser]dhananjay[/eluser]
book table have columns
book_id,slug, title,user_id,contact_name,number,email,role, isbn,author,edition,condition, schools, suburb,price,ad_type,status,availability,remove_from_sale,payment,pic,created_at

uniform table have columns

uniform_id,slug,title, user_id ,contact_name,number,email,role,description,size,condition, schools, suburb, price, ad_type status, availability ,remove_from_sale, payment ,pic

,created_at

now i have to match title to that keyword and get data from both with pagination.......
#4

[eluser]mallix[/eluser]
Now from what I see the only thing that changes between books and uniforms are:

Books have: isbn, author, edition
Uniforms have: description, size

So for example you can create ONE table called ITEM

Take all the common fields in this table and ad another column with name "type" (refers to either book or uniform to keep the type of each entry in the db).

So now create one table with name ITEM_BOOK and one table with name ITEM_UNIFORM:
ITEM_BOOK (item_book_id, item_id, isbn, author, edition)
ITEM_UNIFORM (item_uniform_id, item_id, description, size)

Now you have connected all these items with "item_id" and you can create "join" queries to search on both per type or per title or both like:

$this->db->select('ITEM.*, ITEM_BOOK.*', ITEM_UNIFORM.*');
$this->db->from('ITEM');
$this->db->join('ITEM_BOOK', 'ITEM_BOOK.ITEM_ID = ITEM.ITEM_ID');
$this->db->join('ITEM_UNIFORM', 'ITEM_UNIFORM.ITEM_ID = ITEM.ITEM_ID');
$this->db->where(title like %$keyword%);

If this query does not give a match for you then switch to:

$this->db->select('ITEM.*, ITEM_BOOK.*');
$this->db->from('ITEM');
$this->db->join('ITEM_BOOK', 'ITEM_BOOK.ITEM_ID = ITEM.ITEM_ID');
$this->db->where(title like %$keyword%);

If this query does not give a match then again switch to the other table:

$this->db->select('ITEM.*, ITEM_UNIFORM.*');
$this->db->from('ITEM');
$this->db->join('ITEM_UNIFORM', 'ITEM_UNIFORM.ITEM_ID = ITEM.ITEM_ID');
$this->db->where(title like %$keyword%);

That s how I would do it anyway.
#5

[eluser]dhananjay[/eluser]
hi,

thanks a lot for your help , but what is the problem that i have already gone with the a lot of implementation and , if now i will change whole structure of database then i will need to recode these functionalists again


what if i create a table ITEMS
with columns id and ITEM_ID

and in both books and uniforms add a new column named ITEM_ID


then also i can join these three tables or not ?




Theme © iAndrew 2016 - Forum software by © MyBB