Retrieve Texts from MySQL database

Question

Hi I would like to know the answers of the following queries,

What is the maximum size of data a TEXT data type can have in mysql table?
If I would like to store 6000 to 7000 lines of texts (say, LARGE Text) in a particular field what should be the datatype of that field?
What is the process to retrieve the LARGE TEXT data from mysql table in the following two cases using PHP,

Case-1 : Retrieve data from a particular field "Messages"

---------------------------
id   |   Messages
---------------------------
1    |  "here LARGE Text...

Case-2 : Retrieve all "Messages" data from a the table.

---------------------------
id   |   Messages
---------------------------
1    |  "here LARGE Text... 
---------------------------
2    | "here another LARGE Text...

http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html — aynber, Feb 17 '16 at 13:50
Case 1: `SELECT Messages FROM tableName WHERE id=1`; Case 2: `SELECT Messages FROM tableName` ; **You have to replace** `tableName` with your own table name — fusion3k, Feb 17 '16 at 13:52
@Nordenheim Properly, I mean, I wish google would make it more simple to use their service. — Epodax, Feb 17 '16 at 13:53
Okey, 1 and 2 can be answered from the "TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes". — user1328007, Feb 17 '16 at 13:56
@fusion3k , that's the column of the fild will displayed, but how to get the data in .txt or .doc format? — user1328007, Feb 17 '16 at 13:56

score 0 · Answer 1 · answered Feb 17 '16 at 13:52

0

Maximum size of TEXT data type is 65,535 bytes - reference
6000-7000 lines of texts can be in TEXT or, if too long, MEDIUMTEXT (16,777,215 bytes ~16MB) - reference
Same as any other MySQL retrieval format. For example, using PDO:

// Create connection
$dbh = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

//Create and execute query
$stmt = $dbh->query("SELECT `messages` FROM `table`");
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $row){
    echo $row["messages"];
}

answered Feb 17 '16 at 13:52

Ben

8,894
7
44
80

I mean to say , is it possible to get the LARGE texts in .txt of .doc file from the database? – user1328007 Feb 17 '16 at 13:57
MEDIUMTEXT could be appropriate for me – user1328007 Feb 17 '16 at 13:58
@user1328007 That question doesn't make sense... If it's in your database, it's not in a `txt` or `.doc` file, it's in the database! – Ben Feb 17 '16 at 13:58
yes, but is it possible to retrieve the data in those form – user1328007 Feb 17 '16 at 13:59
So the above query can fetch all the messages (of different rows) of case 2? – user1328007 Feb 17 '16 at 14:00

Retrieve Texts from MySQL database

1 Answers1