Combine 2 (varchar) form fields (PHP, MySQL)

.oO(RichardODreamweaver)

>With both of you on the case, I couldn't lose!

You're welcome. :)

> My query was a little more complex than the question, but the formatting works
>great.

Nice.

> I have gone for the option as below. (workgroup is from a session variable to
>prevent users from seeing records in another workgroup)
>
> SELECT *
> FROM CONTACTS
> WHERE (GROUPCODE LIKE workgroup AND CONCAT_WS(' ',TITLE, FIRSTNAME, LASTNAME)
>LIKE %varsearch1%) AND (GROUPCODE LIKE workgroup AND COMPANY LIKE %varsearch2%)
> ORDER BY COMPANY, FIRSTNAME, LASTNAME

Just some additional notes:

* If you want to do an exact match without any wildcards (like in your
workgroup check), you should not use the 'LIKE' operator, but a simple
'='. It's much more efficient and sometimes even more secure.

* You should avoid SELECT *, except for debugging or quick tests. In
production code you should explicitly list all the columns you want.
This avoids ambiguities (when working with JOINs) and is usually more
efficient, since the DB only has to deliver the data you want, not all
and everything.

* You're checking the workgroup twice. The current logical structure of
your WHERE condition is like this:

(A AND B) AND (A AND C)

with A being the workgroup check. Since there are only ANDs, the paren-
theses have no semantic meaning, so the entire condition evaluates to

(A AND B) AND (A AND C) = A AND B AND A AND C = A AND B AND C

* Finally just a personal preference: For readability I use uppercase
only for SQL statements/functions and camelCase for my own identifiers.
Additionally I try to indent and insert linebreaks where appropriate.

So, given all the above, I would write your query as

SELECT ...
FROM contacts
WHERE groupCode = workgroup
AND CONCAT_WS(' ', title, firstname, lastname) LIKE %varsearch1%
AND company LIKE %varsearch2%
ORDER BY company, firstname, lastname

But as said, this kind of formatting is just personal preference.

HTH
Micha

With both of you on the case, I couldn't lose!

My query was a little more complex than the question, but the formatting works great.

I have gone for the option as below. (workgroup is from a session variable to prevent users from seeing records in another workgroup)

SELECT *
FROM CONTACTS
WHERE (GROUPCODE LIKE workgroup AND CONCAT_WS(' ',TITLE, FIRSTNAME, LASTNAME) LIKE %varsearch1%) AND (GROUPCODE LIKE workgroup AND COMPANY LIKE %varsearch2%)
ORDER BY COMPANY, FIRSTNAME, LASTNAME

Thanks Guys

.oO(RichardODreamweaver)

>I could have the fullname field as visible but read only. This is just an
>amagulmation of the title, firstname and lastname.
>
> I'll be using this "fullname field" for search purposes and to use in list
>drop downs... unless you can think of a way to have multiple fields in a list
>drop down which enters the id (not visible) only.
>
> I'm beginning to wonder why I separated these in the first place!

And I'm still wondering what the hidden form field has to do with it
all, so just a general note:

It's correct to store firstname and lastname in separate columns in the
database. Using a single column for both would be bad and not normalized
table design. Then if you need the full name printed out somewhere, you
can do it easily with SQL and have MySQL return a combination of the
firstname and lastname columns, e.g.

SELECT
firstname,
lastname,
CONCAT_WS(' ', firstname, lastname) AS fullname
FROM
...

You don't have to store the full name anywhere, since you can always get
it on-the-fly.

Micha

OK - cracked the dynamic multi-column drop down list but i could still do with some advice as to how to join these fields for search purposes.

I could have the fullname field as visible but read only. This is just an amagulmation of the title, firstname and lastname.

I'll be using this "fullname field" for search purposes (recordsets where fullname like '%searchfield%' yadayada...)and to use in list drop downs... unless you can think of a way to have multiple fields(columns) in a list drop down which returns the row id (not visible) only.

I'm beginning to wonder why I separated these in the first place!

.oO(RichardODreamweaver)

>This must be such a common question but i can't find an answer anywhere.
>
> On an insert or update form, I have three common fields.
>
> title
> firstname
> lastname
>
> I also have another hidden field "fullname".

Why a hidden field? What do you want to do with the fullname?

Micha