2. PHPRunner General questions
  3. CONCAT Fields
This topic is locked

CONCAT Fields

 		2/18/2010 6:31:34 AM
PHPRunner General questions
S
 swanside author

Hello.
On my database, I have a jobs table where we select a site address for the job to be carried out. The field for this is Order_Site_Address.

Under this I have SLine1,SLine2,SLine3,SLine4,SLine5,SLine6 and SLine7 where these are the full site address, so the job will display as follows
Glass House (Order_Site_Address)

123 Any Street (Line1)

Any Town (Line2)

Any City (Line3)

Any County (Line4)
This takes up a lot of screen space when adding a new job.
Is there a way I can have the following to display
Glass House (Order_Site_Address)

123 Any Street, Any Town, Any City, Any County as a new field (Postal_Address) so this will now only take up 2 lines where the previous would take up 5 lines?

Thanks

Paul.
admin 2/18/2010

You can use concat() function in your SQL query to concatenate fields.
More info:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat
S
 		swanside author 2/18/2010

So I could do this and by selecting the Order_Site_Address on my job_add page it would put he Line1,2,3,4,5,6 and 7 into the field of FullSiteAddress?
SELECT

Order_Date,

File_No,

Job_No,

Order_Time,

Contract,

Order_Site_Address,

Job_Description,

VAT,

Invoice_Printed,

Invoice_Printing_Date,

Invoice_Tax_Date,

Payment_Received,

Job_Finished,

CustomerRef,

Customer_Name

SELECT CONCAT_WS(',','Line1','Line2','Line3','Line4','Line5','Line6','Line7')AS FullSiteAddress;

FROM job

ORDER BY Job_No DESC
C
 		copper21 2/18/2010

Paul,
Try this; it worked for me:
In your table, create these fields: Job Name (var char 100), Address (var char 100), Town (var char 100), City (var char 100), County (var char 100), etc
In your edit SQL query, you should see something like this (along with all of the other fields in your table):
SELECT

Job Name,

Address,

Town,

City,

County,

REST OF YOUR FIELDS,

FROM job
Now you are going to place this right before "FROM job":
concat(Address, ',',Town, ',',City, ',',County) as fullsiteaddress
It should now look like this:
SELECT

Job Name,

Address,

Town,

City,

County,

REST OF YOUR FIELDS,

concat(Address, ',',Town, ',',City, ',',County) as fullsiteaddress <-----no comma

FROM job
The output from this should be: Address, Town, City, County in one field called fullsiteaddress. You do not need to make "fullsiteaddress" as a field in the table of your database. You will be able to chose which pages this outputs to in the "Choose Fields" section in PHP Runner.
Brian
S
 		swanside author 2/19/2010



Paul,
Try this; it worked for me:
In your table, create these fields: Job Name (var char 100), Address (var char 100), Town (var char 100), City (var char 100), County (var char 100), etc
In your edit SQL query, you should see something like this (along with all of the other fields in your table):
SELECT

Job Name,

Address,

Town,

City,

County,

REST OF YOUR FIELDS,

FROM job
Now you are going to place this right before "FROM job":
concat(Address, ',',Town, ',',City, ',',County) as fullsiteaddress
It should now look like this:
SELECT

Job Name,

Address,

Town,

City,

County,

REST OF YOUR FIELDS,

concat(Address, ',',Town, ',',City, ',',County) as fullsiteaddress <-----no comma

FROM job
The output from this should be: Address, Town, City, County in one field called fullsiteaddress. You do not need to make "fullsiteaddress" as a field in the table of your database. You will be able to chose which pages this outputs to in the "Choose Fields" section in PHP Runner.
Brian



Thanks Brian, Will give that a go.
S
 		swanside author 3/7/2010



Thanks Brian, Will give that a go.


Hi Brian.

Thankyou for your help.

I did whay you said and it works a treat.

Regards

Paul.