-3
SELECT FROM solarbricklight, solarcharger, solarlantern, solarled, solarlightfan, solarlightingkits
WHERE ="productid AND productname" 
ORDER BY productid
Rajeev Ranjan
  • 4,152
  • 3
  • 28
  • 41
user3336480
  • 1
  • 1
    I am `solar-ed`. And how are we supposed to know what's in `solarbricklight`, `solarcharger`, `solarlantern`.. etc. etc.? – AyB Mar 03 '14 at 12:14
  • 2
    Why do you have a separate table for every product type anyway? That's just bad database design – Mark Baker Mar 03 '14 at 12:15
  • @OP, following what Mark Baker said, you should probably just have **1** table with all your products and a `type` column that says what type of product it is or you're going to have loads of trouble later. – AyB Mar 03 '14 at 12:18
  • hi mr. Can Has Cheezburger, solarbricklight, solarcharger, solarlantern.. etc. etc.? this is table – user3336480 Mar 03 '14 at 12:21
  • Put all your products in one table and add one column for product type. Her you can choose to use either product type name or product type ID. Prod.type.ID then you will need one more table for product types. – Steven Mar 03 '14 at 12:23
  • hi mr. Mark Baker, how to database deisign for like that product? – user3336480 Mar 03 '14 at 12:25
  • ok but i have many products in all so how can call in one product in one page? – user3336480 Mar 03 '14 at 12:27
  • Rephrase your question so we can understand it a bit better. – Steven Mar 03 '14 at 12:31
  • See columns of your all these solar* tables, And think about them, What common column they have, and what different(logically) columns they have. Then think that can you have just one table instead of all those solar*** – Rohit Awasthi Mar 03 '14 at 12:31
  • Show the table structures for your different tables, then we might be better able to tell you how to combine them all into a single table – Mark Baker Mar 03 '14 at 12:31

4 Answers4

0

Your question is not very clear.

But still let me try. Assuming that theres productid and productname in each of your tables (for purpose of an example I am assuming that there are three tables t1, t2 and t3). Heres what you will do...

$query = "(SELECT productid, productname, 't1' as type FROM t1 WHERE productid = 1 OR productname LIKE '%" . $keyword ."%') UNION (SELECT productid, productname, 't2' as type FROM t2 WHERE productid = 1 OR productname LIKE '%" . $keyword ."%') UNION (SELECT productid, productname, 't3' as type FROM t3 WHERE productid = 1 OR productname LIKE '%" . $keyword ."%')";

mysql_query($query);

So, you are getting result from all of the three tables, and you can identify which row came from which table by looking at its type value.

  • Dear mayur my table name is solarbricklight, solarcharger, solarlantern, solarled, solarlightfan, solarlightingkits and in table productname and productid – user3336480 Mar 03 '14 at 12:33
  • @user3336480 so use the table names in lieu of t1, t2 and t3 above and continue your query for all the 6 tables. –  Mar 04 '14 at 06:45
0

Looks like you should thing about a database design like this:

TABLE PRODUCTS

  • product_id (int autoincrament)
  • product_name (varchar(50))

Then you SQL can be like :

SELECT product_id, product_name FROM products WHERE product_name LIKE "%search_string%" ORDER BY product_id ASC

Jake Mac
  • 31
  • 1
0

If you want to search from six tables independently, then use union all:

SELECT
FROM solarbricklight
WHERE ="productid AND productname" 
UNION ALL
SELECT 
FROM solarcharger
WHERE ="productid AND productname" 
UNION ALL
SELECT
FROM solarlantern
WHERE ="productid AND productname" 
UNION ALL
SELECT solarled
FROM olarlightfan
WHERE ="productid AND productname" 
UNION ALL
SELECT
FROM solarlightingkits
WHERE ="productid AND productname" 
ORDER BY productid;

You need to be sure the columns in the select clause are all compatible (and all have the same number). The final ORDER BY refers to the results of the entire query.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Here are some tutorials on database designs - read this, then start designing your database :)

http://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html
http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html

A beginner's guide to SQL database design

Community
  • 1
  • 1
Steven
  • 19,224
  • 47
  • 152
  • 257
  • Just remember to describe your question a bit better so people can better understand your problem. – Steven Mar 03 '14 at 12:38