Storing data with line breaks

[davidannis]

I implemented a simple bit of code, to randomly pick 2 ads from a mysql database and display them in the left hand column of my pages. The filed in mysql where the HTML is stored is of Type VARCHAR. To date I've had no problems with it. Then I joined Google's adsense and when I try to put the Google HTML into the database it doesn't seem to work. I think that the problem is that I am taking the linefeeds out before I stuff the html into the database. I'm using phpMyAdmin to put records into the database. I assumed that VARCHAR would not support newlines because I get a single line to put the field data into. However, a friend calaims VARCHAR will support newline characters.

My questions are:

Will VARCHAR allow me to have newline characters in it or do I need a different datatype (if so which)?

Will phpMyAdmin allow me to input data into a field with newlines and if so how?

Can anybody think of why if I strip linefeeds out of google's html code and replace it with spaces the html wouldn't work? - I thought javascript treated all whitespace the same.

The script can be seen in action at http://guidetosellingabusiness.com

The code that displays the ads is here:

<? for ($i=1 ; $i<=$numads; $i++){
        if ($advs[$i-1]){
        echo ' <tr>
             <td class="btad">                           

            <div align="Center">'.$advs[$i-1].'
             </div>
            </td>
          </tr>';}
        else {
        $result = mysql_query("SELECT * FROM ads");
        $num_rows = mysql_num_rows($result);
        $whichone = rand(1,$num_rows);
        $row_pointer = $whichone - 1 ; // rows start at 0, not one
        if (!mysql_data_seek($result, $row_pointer)) {
                echo "Cannot seek to row $row_pointer: " . mysql_error() . "\n";
                continue;
                 }
        if (!($row = mysql_fetch_assoc($result))) {
                continue;
                 }
         echo ' <tr>
            <td class="btad"><div align="Center">'.$row['adhtml'].'
             </div>
            </td>
          </tr>';
        }
}
if ($specialbot){echo '<TR><TD>'.$specialbot.'</TD></TR>';}
?>   

Thanks,

David

http://ignoranceoffsets.com
The gift for those with everything, except a clue

[naskar]

Umm does /n work?

[bradgrafelman]

davidannis wrote:

Will VARCHAR allow me to have newline characters in it or do I need a different datatype (if so which)?

Yes, VARCHAR can hold line feed and carriage return characters.

davidannis wrote:

Will phpMyAdmin allow me to input data into a field with newlines and if so how?

You can't do it in PMA using the simple INSERT form. You can insert new line characters if you execute raw SQL code (either in PMA, on the SQL tab, or the CLI or any other MySQL interface). Example:

INSERT INTO myTable (html_data) VALUES ('this is line 1.\nthis is line 2')

naskar wrote:

Umm does /n work?

No, but \n does. :p

More importantly, does it matter if you can insert new line characters or not?

[ucffool]

Also, don't forget that line breaks stored in the database won't showup as \n in phpmyadmin, and when they are output to html (such as echo $string), will not make use of the space if displayed directly on the page.
You need to use nl2br();
http://www.php.net/manual/en/function.nl2br.php

[bradgrafelman]

ucffool wrote:

You need to use nl2br();

Which brings us back to the question I asked - is it even necessary to store line breaks in the first place?

[davidannis]

bradgrafelman wrote:

Which brings us back to the question I asked - is it even necessary to store line breaks in the first place?

I know that it shouldn't matter, but when I cut and paste the code from Google into the html on it's own it displays fine. When I put the same code into a text editor and change the carriage returns to spaces and put the result into the database and let it display from there I get nothing, though I can see the code in view source. I'll post a demonstration when I have a chance.

Thanks,
David

[laserlight]

When I put the same code into a text editor and change the carriage returns to spaces and put the result into the database and let it display from there I get nothing, though I can see the code in view source.

Eh, but the point is that nl2br() changes newlines to newlines with HTML breaks. If you change the newlines to spaces, nl2br() will not work on those removed newlines.

[jeremyphphaven]

I'd also suggest storing HTML in at least a tinyblob field. Varchar limit is 255

[davidannis]

OK, I've created a page to demonstrate the issue. The page can be found at http://guidetosellingabusiness.com/indextest.php I have also added a robots.txt file to the site so I can play with it and it won't be indexed. I pasted the code from google into the page twice, once with line breaks and once without. I cut the html from the version with no line breaks and used MyPHPAdmin to insert it into record 11. I then added something to the script posted posted above to select record 11 (I set $adid=11 before including this).

 $result = mysql_query("SELECT * FROM ads where id=$adid");       

 if (!($row = mysql_fetch_assoc($result))) {                continue;                 }         

echo ' <tr>            <td class="btad"><div align="Center">'.$row['adhtml'].'             </div>            </td>          </tr>';

If you view source, you can see where the code is on the page, but the ad doesn't show up.

So, it's not the lack of linefeeds, it's something in my code or in the database.

Thanks,
David

[naskar]

Have you tried removing the code you manually pasted on the left side? There might be a clash with how many ads of the same type you have on one page

[ucffool]

Yeah, I attached a partial screenshot after I turned off adblock plus.. I don't see an issue, there are 2 ads.

[davidannis]

ucffool wrote:

Yeah, I attached a partial screenshot after I turned off adblock plus.. I don't see an issue, there are 2 ads.

The problem is that if you view source there are 2 ads above the ones that I manually pasted in (the two that you CAN see in the screenshot) that just aren't showing.

It happens whether I display the ad alone or with others.

[davidannis]

OK. I gave up and just reskinned the site with a whole new look and feel. I just added a separate place for Google ads where the code can be static.

Thanks to all who tried to help.

David