mysql statement

I am looking for some advice on how to achieve something and so far have
been unable to do what I am looking to do.

Here is the query I am using:
mysql> SELECT *
-> FROM `orders`
-> WHERE `ordernum` LIKE "35132"
-> OR `price` LIKE "35132"
-> OR `partnum` LIKE "35132"
-> OR `vendor` LIKE "35132"
-> OR `purpose` LIKE "35132"
-> OR `tracking` LIKE "35132"
-> OR `contact` LIKE "35132"
-> AND `group` LIKE 'mac'
-> ORDER BY `ordernum`
-> LIMIT 0 , 30;

First here is the table structure:
mysql> describe orders;
+-------------+--------------+------+-----+---------+------- ---------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+------- ---------+
| id | int(255) | NO | PRI | | auto_increment |
| ordernum | int(10) | NO | | | |
| date | varchar(60) | NO | | | |
| time | varchar(20) | NO | | | |
| group | varchar(20) | NO | | | |
| quantity | int(10) | NO | | | |
| description | varchar(255) | NO | | | |
| price | decimal(3,0) | NO | | | |
| partnum | varchar(40) | NO | | | |
| vendor | varchar(65) | NO | | | |
| purpose | varchar(255) | NO | | | |
| tracking | varchar(120) | NO | | | |
| contact | varchar(255) | NO | | | |
| eta | varchar(50) | NO | | | |
| department | varchar(125) | NO | | | |
| notes | varchar(255) | NO | | | |
+-------------+--------------+------+-----+---------+------- ---------+
16 rows in set (0.00 sec)

I am trying to essentially LIMIT all records returned to be limited by
the `group` field so I can search for records and limit the rows
returned by that one field.

Any tips? TIA.
jas

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: mysql statement [SOLVED]

Got if figured out, needed a sub-select type of query:

mysql> SELECT *
-> FROM ( SELECT * FROM `orders`
-> WHERE `group` = "groupname" )
-> AS orders UNION SELECT * FROM `orders`
-> WHERE `ordernum` LIKE "35132"
-> OR `price` LIKE "35132"
-> OR `partnum` LIKE "35132"
-> OR `vendor` LIKE "35132"
-> OR `purpose` LIKE "35132"
-> OR `tracking` LIKE "35132"
-> OR `contact` LIKE "35132"
-> AND `group` LIKE 'mac'
-> ORDER BY `ordernum`
-> LIMIT 0 , 30;

Jas wrote:
> I am looking for some advice on how to achieve something and so far have
> been unable to do what I am looking to do.
>
> Here is the query I am using:
> mysql> SELECT *
> -> FROM `orders`
> -> WHERE `ordernum` LIKE "35132"
> -> OR `price` LIKE "35132"
> -> OR `partnum` LIKE "35132"
> -> OR `vendor` LIKE "35132"
> -> OR `purpose` LIKE "35132"
> -> OR `tracking` LIKE "35132"
> -> OR `contact` LIKE "35132"
> -> AND `group` LIKE 'mac'
> -> ORDER BY `ordernum`
> -> LIMIT 0 , 30;
>
> First here is the table structure:
> mysql> describe orders;
> +-------------+--------------+------+-----+---------+------- ---------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+--------------+------+-----+---------+------- ---------+
> | id | int(255) | NO | PRI | | auto_increment |
> | ordernum | int(10) | NO | | | |
> | date | varchar(60) | NO | | | |
> | time | varchar(20) | NO | | | |
> | group | varchar(20) | NO | | | |
> | quantity | int(10) | NO | | | |
> | description | varchar(255) | NO | | | |
> | price | decimal(3,0) | NO | | | |
> | partnum | varchar(40) | NO | | | |
> | vendor | varchar(65) | NO | | | |
> | purpose | varchar(255) | NO | | | |
> | tracking | varchar(120) | NO | | | |
> | contact | varchar(255) | NO | | | |
> | eta | varchar(50) | NO | | | |
> | department | varchar(125) | NO | | | |
> | notes | varchar(255) | NO | | | |
> +-------------+--------------+------+-----+---------+------- ---------+
> 16 rows in set (0.00 sec)
>
> I am trying to essentially LIMIT all records returned to be limited by
> the `group` field so I can search for records and limit the rows
> returned by that one field.
>
> Any tips? TIA.
> jas

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: mysql statement [SOLVED]

Couple of little pointers.

If you're doing the sub-select, then you don't need the "group like 'mac'"
because you've already limited your query in the subselect to a specific
groupname.

The subselect is probably unnecessary since what you're doing is relatively
simple and uses the same table. It probably just adds overhead. But in
relation to that, you need to segregate your OR statements from your final
AND statement or it won't limit by group properly.

WHERE ( `ordernum` LIKE "35132"
OR `price` LIKE "35132"
OR `partnum` LIKE "35132"
OR `vendor` LIKE "35132"
OR `purpose` LIKE "35132"
OR `tracking` LIKE "35132"
OR `contact` LIKE "35132" )
AND `group` LIKE 'mac'

Notice the ( and ).

Next pointer.. LIKE is used when you're doing a non-exact search, but you
haven't used any wildcards to indicate a partial search. So what you're
essentially doing is the same as "price = 35132". If you're doing a
multi-field search and want to use LIKE, you'd do this on each:

`price` LIKE "%35132%"

The % is sorta like * in other systems. Any quantity of any characters can
match that space. Or you can do `price` LIKE "35132%" if you want to
search the beginning of the field (note the % at the end, but not the
beginning of the search string this time).

One last thing... I don't know if it increases speed or not, but since you're
using so many fields this MAY speed up the query. Depends on how much
data you're searching through and if you're doing %search% or need to find
start/end strings.

Anyway, here's the test.. what's faster, what you're trying to do:

WHERE ( `ordernum` LIKE "35132" OR `price` LIKE "35132" OR `partnum` LIKE
"35132"
OR `vendor` LIKE "35132" OR `purpose` LIKE "35132" OR
`tracking` LIKE "35132"
OR `contact` LIKE "35132" )
AND `group` LIKE 'mac'

or something like this....

WHERE CONCAT(`ordernum`, `price`, `partnum`, `vendor`, `purpose`, `tracking`,
`contact`) LIKE "%35132%"
AND `group` LIKE '%mac%'


All the LIKE comparisons against a ton of data MAY be more taxing on the
server than doing a CONCAT of all the fields then doing a single LIKE. If
you're doing LIKE %search% where it can appear anywhere in any of the
fields, then CONCAT + LIKE would work just as good as LIKE OR LIKE OR ...
results-wise. I don't know if it's faster/less intensive or not though.
You'd have to do some tests.

Also, comparisons like "=" should (if I recall) be faster than LIKE
comparisons. So if you really meant to use "=", do that instead.

Let me know if any of that's unclear. I know I get some kooky ideas
sometimes. Also, anyone see anything I screwed up or have thoughts on this
matter?

Good luck!

-TG






----- Original Message -----
From: Jas
To: php-db@lists.php.net
Date: Wed, 26 Sep 2007 12:08:53 -0600
Subject: [PHP-DB] Re: mysql statement [SOLVED]

> Got if figured out, needed a sub-select type of query:
>
> mysql> SELECT *
> -> FROM ( SELECT * FROM `orders`
> -> WHERE `group` = "groupname" )
> -> AS orders UNION SELECT * FROM `orders`
> -> WHERE `ordernum` LIKE "35132"
> -> OR `price` LIKE "35132"
> -> OR `partnum` LIKE "35132"
> -> OR `vendor` LIKE "35132"
> -> OR `purpose` LIKE "35132"
> -> OR `tracking` LIKE "35132"
> -> OR `contact` LIKE "35132"
> -> AND `group` LIKE 'mac'
> -> ORDER BY `ordernum`
> -> LIMIT 0 , 30;

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: mysql statement [SOLVED]

Thanks, most of that I knew but the grouping in your first example is
the one that allowed me to only use records matching any field but still
limited by the one db field. So thanks again. I was not aware of the
parenthesis for grouping objects like a mathematical equation where
anything inside the paren's gets executed first and still conditional on
the remainder of the equation.

TG wrote:
> Couple of little pointers.
>
> If you're doing the sub-select, then you don't need the "group like 'mac'"
> because you've already limited your query in the subselect to a specific
> groupname.
>
> The subselect is probably unnecessary since what you're doing is relatively
> simple and uses the same table. It probably just adds overhead. But in
> relation to that, you need to segregate your OR statements from your final
> AND statement or it won't limit by group properly.
>
> WHERE ( `ordernum` LIKE "35132"
> OR `price` LIKE "35132"
> OR `partnum` LIKE "35132"
> OR `vendor` LIKE "35132"
> OR `purpose` LIKE "35132"
> OR `tracking` LIKE "35132"
> OR `contact` LIKE "35132" )
> AND `group` LIKE 'mac'
>
> Notice the ( and ).
>
> Next pointer.. LIKE is used when you're doing a non-exact search, but you
> haven't used any wildcards to indicate a partial search. So what you're
> essentially doing is the same as "price = 35132". If you're doing a
> multi-field search and want to use LIKE, you'd do this on each:
>
> `price` LIKE "%35132%"
>
> The % is sorta like * in other systems. Any quantity of any characters can
> match that space. Or you can do `price` LIKE "35132%" if you want to
> search the beginning of the field (note the % at the end, but not the
> beginning of the search string this time).
>
> One last thing... I don't know if it increases speed or not, but since you're
> using so many fields this MAY speed up the query. Depends on how much
> data you're searching through and if you're doing %search% or need to find
> start/end strings.
>
> Anyway, here's the test.. what's faster, what you're trying to do:
>
> WHERE ( `ordernum` LIKE "35132" OR `price` LIKE "35132" OR `partnum` LIKE
> "35132"
> OR `vendor` LIKE "35132" OR `purpose` LIKE "35132" OR
> `tracking` LIKE "35132"
> OR `contact` LIKE "35132" )
> AND `group` LIKE 'mac'
>
> or something like this....
>
> WHERE CONCAT(`ordernum`, `price`, `partnum`, `vendor`, `purpose`, `tracking`,
> `contact`) LIKE "%35132%"
> AND `group` LIKE '%mac%'
>
>
> All the LIKE comparisons against a ton of data MAY be more taxing on the
> server than doing a CONCAT of all the fields then doing a single LIKE. If
> you're doing LIKE %search% where it can appear anywhere in any of the
> fields, then CONCAT + LIKE would work just as good as LIKE OR LIKE OR ...
> results-wise. I don't know if it's faster/less intensive or not though.
> You'd have to do some tests.
>
> Also, comparisons like "=" should (if I recall) be faster than LIKE
> comparisons. So if you really meant to use "=", do that instead.
>
> Let me know if any of that's unclear. I know I get some kooky ideas
> sometimes. Also, anyone see anything I screwed up or have thoughts on this
> matter?
>
> Good luck!
>
> -TG
>
>
>
>
>
>
> ----- Original Message -----
> From: Jas
> To: php-db@lists.php.net
> Date: Wed, 26 Sep 2007 12:08:53 -0600
> Subject: [PHP-DB] Re: mysql statement [SOLVED]
>
>> Got if figured out, needed a sub-select type of query:
>>
>> mysql> SELECT *
>> -> FROM ( SELECT * FROM `orders`
>> -> WHERE `group` = "groupname" )
>> -> AS orders UNION SELECT * FROM `orders`
>> -> WHERE `ordernum` LIKE "35132"
>> -> OR `price` LIKE "35132"
>> -> OR `partnum` LIKE "35132"
>> -> OR `vendor` LIKE "35132"
>> -> OR `purpose` LIKE "35132"
>> -> OR `tracking` LIKE "35132"
>> -> OR `contact` LIKE "35132"
>> -> AND `group` LIKE 'mac'
>> -> ORDER BY `ordernum`
>> -> LIMIT 0 , 30;

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php