0

I'm working on a project that deals with text files that are to be stored in a database.

So i'm using the blob data type, that converts the files into binary and then uploads to MySQL.

My problem is how to convert the entire binary text into char while retrieving it? I tried the php command below but it doesn't seem to work well. Please help.

SELECT cast(blob-col_name as char(1000000)) from `table_name`

The output retrieves garbage text as shown in the image below.

Mariam
  • 13
  • 8
  • 1
    Possible duplicate of [How do I convert from BLOB to TEXT in MySQL?](http://stackoverflow.com/questions/948174/how-do-i-convert-from-blob-to-text-in-mysql) – Nytrix Dec 07 '16 at 20:28
  • if it's only text files, you should consider using a TEXT-field instead. – Franz Gleichmann Dec 07 '16 at 20:58
  • @FranzGleichman the TEXT-field option works well but i have to upload documents in the database as part of the requirements which doesn't go with this option.. – Mariam Dec 07 '16 at 21:06

1 Answers1

0

Try this:

SELECT CAST(blob-col_name AS CHAR(1000000) CHARACTER SET utf8) As BLOBText FROM table_name;

UPDATE

$sql = "SELECT Legislation_ID, Legislation_Name, Category, 
        CAST(data AS CHAR(1000000) CHARACTER SET utf8) As BLOBText FROM legislations";
$query = mysqli_query($sql);

while($rows = mysqli_fetch_array($query)) {
    file_put_contents($rows["Legislation_Name"].".zip", $rows["BLOBText"]);
    echo "<textarea style='height:500px;width:300px'>".$rows["BLOBText"]."</textarea><br/>";

}
Jis Maxi
  • 226
  • 1
  • 4
  • 16
  • How you retreive datas ? what is the code your are using with PHP ? – Jis Maxi Dec 07 '16 at 21:05
  • $sql = "SELECT `Legislation_ID`, `Legislation_Name`, `Category`, CAST(`data` AS CHAR(1000000) CHARACTER SET utf8) As BLOBText FROM `legislations`"; – Mariam Dec 07 '16 at 21:07
  • I used the above query and made a table to print all the results. But the data field comes out blank in the output. – Mariam Dec 07 '16 at 21:08
  • I updated the code, try it like that, retreive it like this: `$row["BLOBText"]` – Jis Maxi Dec 07 '16 at 21:10
  • Yes the query works. But now its printing garbage text in the output field :| – Mariam Dec 07 '16 at 21:23
  • Nope, still the same thing. – Mariam Dec 07 '16 at 21:56
  • I have edited the post with an image link that displays the query results. – Mariam Dec 07 '16 at 22:06
  • Add this line to your code: `file_put_contents($rows["Legislation_Name"].".zip", $rows["BLOBText"]);` the contents shown is in zip file and not in text file (This mean you saved a zip file in the blob column). – Jis Maxi Dec 07 '16 at 22:10
  • No, i converted my text file to .sql before uploading into the blob column. It wasn't a zip file – Mariam Dec 07 '16 at 22:23
  • You store your file in doc/docx format no ? if yes then make what i told to you, change only extension to : `.doc` / `.docx` and not with `.zip`. – Jis Maxi Dec 07 '16 at 22:45
  • If you want return directly a TEXT from your BLOB Column, you have to store files created with block note / notepad etc ... in `.txt` format. – Jis Maxi Dec 07 '16 at 22:46
  • And i needed to know one more thing if i could do any formatting and encoding to the text because it dumps all the text directly in the output field. – Mariam Dec 08 '16 at 07:02
  • Please make another question about your problem, this post not related to this. – Jis Maxi Dec 08 '16 at 14:17