Dave Teu Dave Teu - 5 months ago 16
MySQL Question

PDO Mysql Not returning results properly

Hi I'm stuck with this really weird situation.

I a mysql server and a app server. App connects to mysql via private network and i grant . privileges to both users.

This is my code from app server.

$db2 = new PDO('mysql:host=' . $host . ';dbname=' .$database. ';charset=utf8mb4', $userid, $db_password);
$db2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db2->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);


$somequery = $db2->query('SELECT mail,message_id from mc_mails');
$somequery->execute();

$somedata = $somequery->fetchAll(\PDO::FETCH_ASSOC);

pretty_print($somedata); //my own <pre>print_r()</pre> function

var_dump($somedata); //shows raw dump


//so below i try to do again in a try loop to get Exception, and there's no exception.
try {
$somequery->execute();

while ($row = $somequery->fetch(PDO::FETCH_ORI_NEXT)) {
pretty_print($row);
}

} catch (PDOException $e) {
echo $e->getMessage();
}


The above yields me the following results

**This is from pretty_print() **
Array
(
[0] => Array
(
[mail] => 20
[message_id] => <2061A721-82BE-48EC-BA44-D2CD6D6DAC00@gmail.com>
)

[1] => Array
(
[mail] => 21
[message_id] => <8064AD4C-7C30-499B-8D83-E7DBABADCF36@gmail.com>
)

[2] => Array
(
[mail] => 22
[message_id] =>
)

[3] => Array
(
[mail] => 23
[message_id] =>
)

[4] => Array
(
[mail] => 24
[message_id] =>
)

[5] => Array
(
[mail] => 25
[message_id] =>
)

[6] => Array
(
[mail] => 26
[message_id] =>
)

[7] => Array
(
[mail] => 27
[message_id] => <850E2143-F797-4B6B-8F29-1004EEBCC0D2@gmail.com>
)

[8] => Array
(
[mail] => 28
[message_id] =>
)

[9] => Array
(
[mail] => 29
[message_id] =>
)

[10] => Array
(
[mail] => 30
[message_id] => <59DED427-255B-45EB-AC10-172FD49D529D@gmail.com>
)

[11] => Array
(
[mail] => 31
[message_id] => <50360840-52AD-44FE-9257-B51610F36171@gmail.com>
)

[12] => Array
(
[mail] => 34
[message_id] =>
)

[13] => Array
(
[mail] => 35
[message_id] =>
)

)

** This is from var_dump() **
array(14) { [0]=> array(2) { ["mail"]=> int(20) ["message_id"]=> string(48) "<2061A721-82BE-48EC-BA44-D2CD6D6DAC00@gmail.com>" } [1]=> array(2) { ["mail"]=> int(21) ["message_id"]=> string(48) "<8064AD4C-7C30-499B-8D83-E7DBABADCF36@gmail.com>" } [2]=> array(2) { ["mail"]=> int(22) ["message_id"]=> string(48) "" } [3]=> array(2) { ["mail"]=> int(23) ["message_id"]=> string(48) "" } [4]=> array(2) { ["mail"]=> int(24) ["message_id"]=> string(48) "" } [5]=> array(2) { ["mail"]=> int(25) ["message_id"]=> string(48) "" } [6]=> array(2) { ["mail"]=> int(26) ["message_id"]=> string(48) "" } [7]=> array(2) { ["mail"]=> int(27) ["message_id"]=> string(48) "<850E2143-F797-4B6B-8F29-1004EEBCC0D2@gmail.com>" } [8]=> array(2) { ["mail"]=> int(28) ["message_id"]=> string(48) "" } [9]=> array(2) { ["mail"]=> int(29) ["message_id"]=> string(48) "" } [10]=> array(2) { ["mail"]=> int(30) ["message_id"]=> string(48) "<59DED427-255B-45EB-AC10-172FD49D529D@gmail.com>" } [11]=> array(2) { ["mail"]=> int(31) ["message_id"]=> string(48) "<50360840-52AD-44FE-9257-B51610F36171@gmail.com>" } [12]=> array(2) { ["mail"]=> int(34) ["message_id"]=> string(48) "" } [13]=> array(2) { ["mail"]=> int(35) ["message_id"]=> string(48) "" } }


** This is from fetch loop**
Array
(
[mail] => 20
[0] => 20
[message_id] => <2061A721-82BE-48EC-BA44-D2CD6D6DAC00@gmail.com>
[1] => <2061A721-82BE-48EC-BA44-D2CD6D6DAC00@gmail.com>
)
Array
(
[mail] => 21
[0] => 21
[message_id] => <8064AD4C-7C30-499B-8D83-E7DBABADCF36@gmail.com>
[1] => <8064AD4C-7C30-499B-8D83-E7DBABADCF36@gmail.com>
)
Array
(
[mail] => 22
[0] => 22
[message_id] =>
[1] =>
)
Array
(
[mail] => 23
[0] => 23
[message_id] =>
[1] =>
)
Array
(
[mail] => 24
[0] => 24
[message_id] =>
[1] =>
)
Array
(
[mail] => 25
[0] => 25
[message_id] =>
[1] =>
)
Array
(
[mail] => 26
[0] => 26
[message_id] =>
[1] =>
)
Array
(
[mail] => 27
[0] => 27
[message_id] => <850E2143-F797-4B6B-8F29-1004EEBCC0D2@gmail.com>
[1] => <850E2143-F797-4B6B-8F29-1004EEBCC0D2@gmail.com>
)
Array
(
[mail] => 28
[0] => 28
[message_id] =>
[1] =>
)
Array
(
[mail] => 29
[0] => 29
[message_id] =>
[1] =>
)
Array
(
[mail] => 30
[0] => 30
[message_id] => <59DED427-255B-45EB-AC10-172FD49D529D@gmail.com>
[1] => <59DED427-255B-45EB-AC10-172FD49D529D@gmail.com>
)
Array
(
[mail] => 31
[0] => 31
[message_id] => <50360840-52AD-44FE-9257-B51610F36171@gmail.com>
[1] => <50360840-52AD-44FE-9257-B51610F36171@gmail.com>
)
Array
(
[mail] => 34
[0] => 34
[message_id] =>
[1] =>
)
Array
(
[mail] => 35
[0] => 35
[message_id] =>
[1] =>
)


I've no idea why some of the arrays are empty. Notice the var_dump results shows there's string of 48 but it's still empty.

The loop also shows something in the array but it never gets printed. However, the exact same query I used directly in ssh gives me the following. All the fields are set.

enter image description here

I'm not sure whether that's the problem but my message_id is varchar(1000) - yes it's very big, but i'm just starting to learn about email headers and i read rfc is about 1000 chars for message-id though i doubt it ever go past that.

Need help please. I'm stocked.

EDIT: This happens in many fields too, i'm just choosing one field for clarity.

Answer

You're dumping into a browser, and those message IDs have <>, which means they're being rendered as unknown/illegal html tags. Do a view-source of the page, and you'll see they're there

I'm guessing the first two that DO show up were encoded with &lt;...&gt;, so they show as text, not rendered as html.