[RESOLVED] Select the first line of a mysql data record

rsmith

Hey all,
I have a record stored in my table that, on the first line of a column, has a specific piece of data I want to collect and separate from the rest of the column. I was thinking of doing the following but not sure if it will work. If anyone knows a better way, by all means help!

$query = "SELECT `columnA`, `columnB` FROM `table` WHERE `id` = mysql_real_escape_string($_GET['id'])";
$result = mysql_query($query);
$record = mysql_fetch_assoc($result);

$find = strpos($record['columnA'], "\n");
$wanted = substr($record['columnA'], 0, $find);

Basically this looks for the first line break and gets the character at which it occurs. It would then store everything up to the line break in the $wanted variable. Will this work?

bpat1434

You could do that... or you could do it in the query using the SUBSTR() and LOCATE() MySQL functions:

"SELECT SUBSTR(`columnA`, 0, LOCATE('\n', `columnA`)) AS `line1`, `columnA`, `columnB`
FROM `table`
WHERE `id`= " . mysql_real_escape_string($_GET['id'])

Either way should work as you expect it to.

rsmith

I tried both ways but neither worked. If I just echo the $find it prints nothing to the screen... like it didn't find the \n.

rsmith

Aha! I got it. Took some creative thinking but it turns out that the textarea, since being edited with tinyMCE, was stored with a <p> tag in front of the line and after the line. So I did a str_replace() on the <p> and replaced it with a blank space. Then I searched for the </p> and used the location of it as my offset so.

Thanks for the help!