I have inherited a Joomla website that has used a guestbook component to record a petition. Whilst visitors have left their address, it is not readable from the component and I need to recover it from the mySQL database.

I only have a rudimentary understanding of php, and urgently need help with a bit of code that will read the tables back to an excel spreadsheet, or at least to a readable document of some sort.

This is the table and field headings:

`jos_easygb` (`id`, `date`, `content`, `name`, `email`, `homepage`, `rating`, `ip`, `browser`, `published`, `address`)

It would be easy to import it directly into excel because the fields are all nicely comma delimited. But the 'address' field is not. It is one field, and here are two records, 34 and 35, suitably anonymised with personal details changed:

(34, '2007-08-16 04:17:16', 'Worcestershire', 'Joe Bloggs', 'joebloggs@hotmail.com', '', 0, '81.77.182.131', 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.8.0.12) Gecko/20000578 Firefox/1.5.0.12', 1, '22 Bosley Common\r\nBosley Kings\r\nStourport-on-Severn\r\nWorcestershire\r\nBG24 !RT'),
(35, '2007-08-16 05:15:58', 'Street North Somerset', 'Sally Barton', 'bartonsb@tiscali.co.uk', '', 0, '188.06.14.188', 'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; FREESERVE_IE5; .NET CLR 1.1.4322)', 1, 'The Old Poorhouse\r\nLittle Pudding Road\r\nBarton\r\nBristol\r\nBR32 7GR'),

The address lines are separated by \r\n

I know its a big ask, but would someone be willing to write a bit of code that will read these back to an excel spreadsheet, or even a simple document, so that the address is readable?

Richard

I don't have time right now, but you can simply replace those characters.

\r means "carriage return"
\n means "new line"

It's the code that windows send when you hit the enter key.
If you are more comfortable in excel, you could do a macro that will remove them and replace them by a space.
Or in php you can use str_replace

PHP Code:


$q="SELECT * FROM jos_easygb";
$r=mysql_query($q);
while($o=mysql_fetch_object($r)){
  $adr=str_replace('\r',' ',$o->address);
  $adr=str_replace('\n','',$adr);
  //And now, $adr contains the address only, without the \r\n junk
  //You can do whatever you want with it...
} 


Do a google on "php csv" and you'll find lots of scriptlets for generating csv files from php. You might also just do a search on "csv mysql" and do the dump outside of php if you have direct access to the data.

In case I missed a step in the explanation: csv files are readable by Excel.

You can remove these while exporting the data itself. In your phpmyadmin, when you select a table for export, select CSV, and on the right you will see an Option " Lines terminated by". Remove the "\r\n" against it and replace it with a Space. This should achieve the effect of replacing all newlines with Space in stead of "\r\n".