line breaks appearing in sourcecode after mysql query

Question

I am doing a simple query to retrieve car manufacturers from my db:

$result = mysql_query("SELECT DISTINCT model_make_display_name FROM car_query_models_full ORDER By model_make_display_name ASC") or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
echo "<p>".$row['model_make_display_name']."</p>\n";
}

The data is appearing as normal in HTML view, however when I view source some of the results are appearing like so:

<p>Ariel
</p>
<p>Armstrong </p>
<p>Ascari
</p>
<p>Aston Mart</p>
<p>Audi
</p>
<p>Austin
</p>
<p>Austin Hea</p>
<p>Autobianch</p>
<p>Auverland
</p>
<p>Avanti
</p>
<p>Beijing
</p>
<p>Bentley
</p>

Notice some of the names are adding a break after the text forcing the closing p tag to go onto next line, what could be causing this?

Cheers

score 1 · Answer 1 · edited May 25 '19 at 14:30

The way it's stored in the database is causing this.

You can try:

echo "<p>" . htmlspecialchars( $row['model_make_display_name'] ) . "</p>\n";

Or replace \n and \r from the value:

SELECT ... REPLACE(model_make_display_name, '\n', '')

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

score 1 · Accepted Answer · answered Jan 11 '13 at 01:07

1

Have you checked for newline characters on the ends of those manufacturers? Try

echo "<p>".trim($row['model_make_display_name'])."</p>\n";

to see if they're present. If so, simply cleanse the database (on entry or one-time) and you'll be good to go.

answered Jan 11 '13 at 01:07

jessevondoom

68
9

there are no newline characters on the ends of any manufacturers :S but that trim function worked and the output looks fine in the source code, does that indicate that I still need to cleanse the db? if so how?, there are 55,000+ rows – AJFMEDIA Jan 11 '13 at 01:15
I'd guess you got some kind of funky/obscure whitespace character on data entry somehow. You could go through and do an UPDATE query for each one with the trim($row['model_make_display_name']) for the new value, or if it's not too intense on your app you could just leave it with the trim. Play it by ear depending on where and how you're using it... – jessevondoom Jan 11 '13 at 01:29
FYI this worked a treat: UPDATE car_query_models_full SET model_make_display_name = TRIM( "\r" FROM model_make_display_name ); – AJFMEDIA Jan 12 '13 at 17:18

line breaks appearing in sourcecode after mysql query

2 Answers2