Select statement tweaking

[stevew]

(Checkboxes)

 

apples

oranges

bananas

 

I have imploded the form input into a string ($fruits) so whichever boxes are checked will return those results in a

string. The SELECT FROM table has a 'fruits' column with various combinations of the $fruits strings.

 

Everything works properly except for the following query:

 

apples,bananas

 

Ideally, this should return any rows that contain apples,bananas, & apples,oranges,bananas in the 'fruits' column...just as apples,oranges will also return rows with apples,oranges,bananas.

 

 

 

 

 

[cpd]

SELECT * FROM `fruits` WHERE `fruit` LIKE '%apples%bananas%'

 

With a large amount of records this will probably get quite intensive though as it searches every record in the table.

[stevew]

SELECT * FROM `fruits` WHERE `fruit` LIKE '%apples%bananas%'

 

With a large amount of records this will probably get quite intensive though as it searches every record in the table.

 

Thanks but I am looking to use LIKE $fruits in the SELECT as the values are going to be different every time.  Anyway, I am going to create a separate column for each fruit.

[PFMaBiSmAd]

If your intent is to be able to easily manipulate (insert, update, delete) or find any piece of data, then each piece of data should be stored in its own record/row.

[stevew]

If your intent is to be able to easily manipulate (insert, update, delete) or find any piece of data, then each piece of data should be stored in its own record/row.

 

Yes...that is the intention. The problem I am having is how to create a select statement that will query multiple columns in the same table.

 

SELECT * FROM fruits WHERE apples, oranges, bananas like '%$fruits%'");

[maxudaskin]

I know that this will be a stretch, as it would likely require a major rewrite of your script, but I'd suggest having two tables with a many to many relationship.

 

Your example seemed very... flat... so, here's a more in-depth example.

 

An company/employee database.

 

Table: Company

Columns: id, name, address, ect.

Description: The list of companies

 

Table Employee

Columns: id, name, sin, ect.

Description: The list of employees

 

Table: Company_has_Employee

Columns: id, Company_id, Employee_id

Description: The link between companies and employees

 

The reasoning behind this is that each company has many employees. Just because an employee works at one company, does not mean that they do not work at another. Having the third table, you have one row per relationship.

 

Company

-------

  1 | Company Inc. | 123 Anywhere

  2 | Incorp Ltd.  | 456 Somewhere

 

Employee

--------

1 | Jon | 123456

2 | Max | 654321

3 | Laura | 532673

4 | Hank | 639256

 

Company_has_Employee

------------------

1 | 1 | 1 // Company Inc has Jon

2 | 1 | 2 // Company Inc has Max

3 | 2 | 3 // Incorp Ltd has Laura

4 | 1 | 4 // Company Inc has Hank

4 | 2 | 4 // Incorp Ltd has Hank

 

You can see that Jon and Max are employees of Company Inc, Laura is an employee of Incorp Ltd, and Hank is an employee of both.

[stevew]

Ok thanks...a rewrite is no problem as just trying different things out. I will familiarize myself more on the different relationships and joining tables.

[stevew]

Ok I have switched to dropdown select for now. This basic example working properly.

 

<FORM METHOD=POST ACTION="">

         

<select name="Check1">

<option value="all">all</option> 

<option value="yes">yes</option> 

<option value="no">no</option></select>

 

<select name="Check2">

<option value="all">all</option> 

<option value="yes">yes</option>

<option value="no">no</option></select>

 

<select name="Check3">

<option value="all">all</option> 

<option value="yes">yes</option>

<option value="no">no</option></select>

 

<INPUT TYPE=SUBMIT VALUE="Submit">

 

 

$apples = $_POST["Check1"];

$oranges = $_POST["Check2"];

$bananas = $_POST["Check3"];

 

 

$result = mysql_query("SELECT * FROM fruits WHERE apples like '$apples' && oranges like '$oranges'  && bananas like '$bananas'"); 

 

How do I tweak this to make use of the "all" value?

 

ID*********apples*********oranges**********bananas

1***********yes************yes*************yes

2***********no*************yes**************yes

3***********no*************yes**************no

 

$apples "all"

$oranges "yes"

$bananas "yes"

the query should return rows 1,2.

 

 

 

thanks

 

 

[stevew]

<option value="%">all</option>

 

Got it working with the % value.

 

ID*********apples*********oranges**********bananas

1***********yes************yes*************yes

2***********no*************yes*************yes

3***********no*************yes**************no

 

$apples "all"

$oranges "yes"

$bananas "yes"

result: rows 1,2.