Auto generate a column's data...

[Jim R]

I searched, using my subject line, and didn't find what I was looking for. 

 

In my custom table, I have columns nameFirst and nameLast.  As I'm entering names directly in the database, I'd like a column to auto generate data that equals:

 

nameFirst-nameLast

 

 

Basically, I'm trying to create data so my WordPress slugs can check for an equal, then post a Tag's ID to my custom table.

 

[Jim R]

[corbin]

There's no way to automatically generate something SQL side.

 

You could of course do it with PHP though (but I don't think you want to do it with PHP).

 

 

You could do it after inserting all of the names with something like:

 

UPDATE tablename SET fullName = CONCAT(CONCAT(firstname,'-'), lastname)

[Jim R]

I've studied up some on this since my first post on it.  I'm assuming I would have to do a bulk Update, but for the future, I could input my raw data, so to speak, via a PHP form.  Would the PHP form be able to produce the results I want? 

 

In other words, I imagine I would $_POST to individual columns in my custom table, but then could I also post to my wpSlug (nameFirst-nameLast) column in the custom table?

[Jim R]

BTW...I have 449 rows of data in my custom table.

[corbin]

With PHP it would be quite easy.

 

 

Assume in the following that $firstName and $lastName contain what you would expect them to contain, and also pretend that they've been passed through mysql_real_escape_string.

 

 

$q = mysql_query("INSERT INTO someTable (firstName, lastName, bothNames) VALUES ('{$firstName}', '{$firstName}', '{$firstName}-{$lastName}');");

[Jim R]

I'll play with that and get back to you.  Thanks.

[corbin]

No problem .

[Jim R]

One more question before I go, my custom table has other columns, I assume I need to format all of those too?

 

$sql="INSERT INTO fallLeague09reg(nameFirst,nameLast,email,addressHome,stateHome,zipHome,phoneHome,phoneMobile,school,grade,coachSchool,feet,inches)
VALUES
('$_POST[nameFirst]','$_POST[nameLast]','$_POST[email]','$_POST[addressHome]','$_POST[stateHome]','$_POST[zipHome]','$_POST[phoneHome]','$_POST[phoneMobile]','$_POST[school]','$_POST[grade]','$_POST[coachSchool]','$_POST[feet]','$_POST[inches]')";

[Jim R]

For anyone who might know, I have to retrofit, so to speak, the column data I want for my previous 449 entries.  How do I do that?

[PFMaBiSmAd]

An UPDATE query without an WHERE clause will operate on all the rows in your table. You can execute such a query through your favorite database management utility program, no php code is necessary.

[Jim R]

Ok...I was using another post and just about to try that when this reply notification came in. 

 

I'm looking at this from another post:

 

UPDATE wp_playerRank SET wpSlug = CONCAT(???????????)

 

wpSlug is the column I want to input/retrofit the information I need.

 

It needs to read:

 

nameFirst-nameLast

 

 

[Jim R]

I'm trying this and getting an error:

 

UPDATE wp_playerRank SET wpSlug = CONCAT ( 'nameFirst' . '-' . 'nameLast')

 

 

[PFMaBiSmAd]

Perhaps if you read the documentation - http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat

 

And column names are not enclosed in single-quotes as that would make them strings.

[Jim R]

I've already looked at that. That's why I came here.

 

[PFMaBiSmAd]

If you missed seeing that commas are used between the parameters, you would need to schedule an appointment with your eye doctor. That's not a programming issue.

[Jim R]

Well, the documentation you showed me uses single quotes.  So learning by reading documentation, to quote you, I should be using single quotes, but like I said, I didn't understand what was there to begin with so I came here for assistance. 

 

BTW...I used commas, still didn't work.

[Jim R]

Tried this:

 

UPDATE wp_playerRank SET wpSlug = CONCAT (nameFirst,-,nameLast)

 

Still getting a syntax error:

 

[PFMaBiSmAd]

The examples in the documentation use literal string data for each parameter, which are enclosed in single-quotes, since they have no idea how you would use the function in your query. If you are instead referencing a column value as a parameter, you replace that corresponding parameter with the column name you want that parameter to take on the value of. Parameters that are literal strings are still enclosed in single quotes, such as the literal '-' string that you want between the two column values.

[Jim R]

Is there any chance you could take what I have presented above and show me what the syntax should be?  Because I know a string to be a variable with a $.  Every example I have read shows something in single quotes.  Some of the examples appear to be column headers.  I have tried it with single quotes, without, with commas, with periods, with backticks, and now what I have is:

 

UPDATE wp_playerRank SET wpSlug = CONCAT (nameFirst,'-',nameLast)

 

Still getting a syntax error. 

 

[fenway]

That's because you have a space between CONCAT and the open parenthesis.