MySQL output to string

How can I make the output into a string?

[iflyata]

Hello,

I have constructed a MySQL query that outputs a table (in phpMyAdmin) with the values I need. However, I would like to see them in a string, separated by a comma. The table in phpMyAdmin contains two columns (departure & arrival), and then rows with respective matches. For example:

Departure ¦ Arrival
-------------------
Boston ¦ San Francisco
Chicago ¦ New York
Dallas ¦ Chicago

I would like it to produce that example output to this:

Boston-San Francisco,Chicago-New York,Dallas-Chicago

Any help is greatly appreciated!

[volatilegx]

Welcome to WebmasterWorld, iflyata :)

Are you working in PHP?

[ZydoSEO]

Sorry... I've had VERY little exposure to MySQL, but have done a lot of work in SQL*Server (it's big brother) and Oracle. Unfortunately, I haven't done any work in PhP either (I can see I need to learn both MySQL and PhP).

But in general there are probably 2 approaches you can take.

1) Do it all in SQL
2) Do it using a mixture of SQL and some programming language like PhP or ASP.

Option1:

If you want to do it in SQL, most databases have what is called a CURSOR... You can usually do something similar to the following in SQL (as in a stored procedure) - note this is pseudo code, syntax differs from DB to DB although very similar:

Inside a stored procedure:

DECLARE @lvDeparture VARCHAR(64)
DECLARE @lvArrival VARCHAR(64)
DECLARE @lvReturnString VARCHAR(2048)
DECLARE @lvLoopCnt INT

SET @lvLoopCnt = 0

DECLARE CURSOR myCursor FOR
SELECT Departure, Arrival
FROM tablename
WHERE whatever_conditions_you_desire

OPEN myCursor

FETCH NEXT myCursor
INTO @llvDeparture, @lvArrival

WHILE NOT END OF CURSOR
BEGIN
IF @lvLoopCnt > 0 THEN @lvReturnString = @lvReturnString + ','

@lvReturnString = @lvReturnString + @lvDeparture + '-' + @lvArrival

SET @lvLoopCnt = @lvLoopCnt + 1

FETCH NEXT myCursor
INTO @llvDeparture, @lvArrival
END

CLOSE myCursor

RETURN (@lvReturnString)

Option 2:

I don't know about PhP but in ASP you can retrieve a recordset from the database (similar to a cursor but it's done in the ASP code) that is the result of executing a SELECT over some database connection you've created from ASP... You essentially do all of the same stuff but in the ASP or PhP code... The ASP/PhP code cycles through the recordset that is returned and using ASP/PhP you'd add the '-' and ',' where needed using the string functions native to that language...

Hope this helps. Late and need rest. :)

[iflyata]

Thanks very much for the help...ONE more question :-)

I'm trying to query something from a MySQL database, and want to select only rows that in a certain field begin with the letter "K" - how do I tell MySQL to do that in a query?

[ZydoSEO]

Look in the help for SELECT. It should support the use of LIKE in the WHERE clause and the ability to use a wildcard with LIKE.

In SQL*Server it would look something like:

SELECT *
FROM mytable
WHERE myfield LIKE 'K%'

Where '%' is a wildcard...

So if the table had rows like:

------------------------
TABLE: mytable ¦
------------------------
myfield ¦ ...¦
------------------------
ABBA ¦ ...¦
GENESIS ¦ ...¦
LED ZEPPELIN ¦ ...¦
KING CRIMSON ¦ ...¦
KISS ¦ ...¦
PINK ¦ ...¦
RUSH ¦ ...¦
YES ¦ ...¦
------------------------

The above query would return:
KING CRIMSON
KISS

In SQL*Server you can have multiple wildcards such as:

SELECT *
FROM mytable
WHERE myfield LIKE '%K%'

which should return any row where myfield's value has a 'K' in it or:

KING CRIMSON
KISS
PINK

Hope that helps.