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

[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".

Within the form, before posting, I want to combine the three first fields and populate the "fullname" field....

Sounds simple..

I attach my pathetic code tryout to capture it during post - but to no avail!!

[Newsgroup_User]

.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

[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 (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!

[RichardODreamweaver]

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.

[Newsgroup_User]

.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

[RichardODreamweaver]

quote:

---

Originally posted by: Newsgroup User

It's correct to store firstname and lastname in separate columns in the
database.

---

Cool!

quote:

---

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

---

Also very useful...Thanks Micha

I'll be more specific about the search.

I have one textfield to search for names. Ideally you should be able to enter a full name to return the correct recordset but at present, I have to search "where searchfield like firstname or where searchfield like lastname " etc. etc. which isn't an ideal solution as if you just search by firstname, you'll get a huge recordset.

This is why I wanted a field "fullname" so all I'd have to do would be "SELECT * FROM CONTACTS WHERE FULLNAME LIKE %searchfieldvariable%"

This would allow the user to put in a full or part name.

Is this a better description?

[Newsgroup_User]

RichardODreamweaver wrote:
> This is why I wanted a field "fullname" so all I'd have to do would be "SELECT
> * FROM CONTACTS WHERE FULLNAME LIKE %searchfieldvariable%"

Micha's answer is still right.

SELECT
firstname,
lastname,
CONCAT_WS(' ', firstname, lastname) AS fullname
FROM CONTACTS
WHERE fullname LIKE %searchfieldvariable%

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[Newsgroup_User · Accepted solution]

.oO(David Powers)

>RichardODreamweaver wrote:
>> This is why I wanted a field "fullname" so all I'd have to do would be "SELECT
>> * FROM CONTACTS WHERE FULLNAME LIKE %searchfieldvariable%"
>
>Micha's answer is still right.
>
>SELECT
> firstname,
> lastname,
> CONCAT_WS(' ', firstname, lastname) AS fullname
> FROM CONTACTS
> WHERE fullname LIKE %searchfieldvariable%

Just a little thing: The WHERE wouldn't work here. At the time when it
is executed the actual column values may not yet be calculated, hence
you can't refer to column aliases in a WHERE clause. But with a HAVING
clause you can do it, so it should either be

SELECT ..
FROM ...
WHERE CONCAT_WS(' ', firstname, lastname) LIKE ...

or

SELECT ..
FROM ...
HAVING fullname LIKE ...

Micha

[Newsgroup_User]

Michael Fesser wrote:
> Just a little thing: The WHERE wouldn't work here.

Ah, the dangers of writing answers without first testing them. Thanks
for pointing that out, Micha.

> SELECT ..
> FROM ...
> WHERE CONCAT_WS(' ', firstname, lastname) LIKE ...

Actually, my first instinct was to do it that way. Maybe I should stick
with my instincts. ;-)

> SELECT ..
> FROM ...
> HAVING fullname LIKE ...

Not familiar with HAVING. Time to get my MySQL books out again to brush
up on less frequently used parts of the syntax.

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[Newsgroup_User]

.oO(David Powers)

>Michael Fesser wrote:
>> Just a little thing: The WHERE wouldn't work here.
>
>Ah, the dangers of writing answers without first testing them. Thanks
>for pointing that out, Micha.
>
>> SELECT ..
>> FROM ...
>> WHERE CONCAT_WS(' ', firstname, lastname) LIKE ...
>
>Actually, my first instinct was to do it that way. Maybe I should stick
>with my instincts. ;-)

Good idea. ;)

>> SELECT ..
>> FROM ...
>> HAVING fullname LIKE ...
>
>Not familiar with HAVING. Time to get my MySQL books out again to brush
>up on less frequently used parts of the syntax.

There's a short note about this issue in the manual:

| Standard SQL doesn't allow you to refer to a column alias in a WHERE
| clause. This restriction is imposed because when the WHERE code is
| executed, the column value may not yet be determined. For example, the
| following query is illegal:
|
| SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;
|
| The WHERE statement is executed to determine which rows should be
| included in the GROUP BY part, whereas HAVING is used to decide which
| rows from the result set should be used.

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

There are also some important hints and notes about HAVING in the SELECT
chapter, because some of its behaviour is MySQL-specific.

http://dev.mysql.com/doc/refman/5.0/en/select.html

Micha

[RichardODreamweaver]

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

[Newsgroup_User]

.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