MySQL selecting 20.000 rows

Question

i've got a database to work with where articles are saved. Unfortunately and for whatever reason, they stored the picture information in BLOB in the database. Now i need to create folders that contain the picture data - thats perfectly working but when i try to select a greater amount of data, the script fails or runs into errors (gives back false). Can you somehow help me out to query 16.000-20.000 lines and extract it to a file?

Here`s my code

    <?php

$dbuser = 'user';
$dbpass = 'pass';

try {
    $db = $dbh = new PDO('mysql:host=localhost;dbname=Tventas', $dbuser, $dbpass);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::NULL_EMPTY_STRING);
}

catch (Exception $e) {
    echo 'Caught exception: ',  $e->getMessage(), "\
";
}
try {

    $sql="SELECT id_imagen, imagen FROM imagen900";
    //$stmt = $db->prepare($sql);
    //$stmt->execute();
    //$products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($db->query($sql) as $row){

    $filename = $row['id_imagen'].".jpg";

        if (!empty($row['imagen'])) {
        $blob=$row['imagen'];

            if (file_exists($filename)){
                echo "<pre>Datei bereits vorhanden: ";
                print_r($filename);
                echo "</pre>";
            }
            else
            {
                file_put_contents($filename,$blob);
                echo "<pre>";
                print_r($filename);
                echo "</pre>";
            }
        }
    }
}

catch (PDOException $e) {
    echo"Whoops! Something went wrong!";
    echo" Query with error: ".$sql;
    echo" Reason given:".$e->getMessage()."\
";
    return false;
}

?>

There is a good solution here http://stackoverflow.com/questions/4646533/exporting-blob-from-mysql-database-to-file-with-only-sql — Bogdan Burym, Dec 11 '14 at 16:06
You should do your queries in scetions, that is do 0-200 lins first, then the next 200-400. — gskema, Dec 11 '14 at 16:37

score 3 · Answer 1 · answered Dec 11 '14 at 16:05

3

You are trying to load all the 20 000 images into memory.

$products = $stmt->fetchAll(PDO::FETCH_ASSOC);

Try looping through the result set instead.

$sql = "SELECT id_imagen, imagen FROM imagen900";
foreach ($db->query($sql) as $row) {
  ...
}

answered Dec 11 '14 at 16:05

Kouber Saparev

7,637
2
29
26

@Dreshar And `file_put_contents($file, $row, FILE_APPEND);` inside the foreach loop to store the data – Lex Dec 11 '14 at 16:08
Been trying that, now i get Waning: Invalid argument supplied for foreach() in index.php on line 23 - Updated code above – Dreshar Dec 12 '14 at 11:04
Funny thing is, if i limit the SQL for example "LIMIT 1000" it works perfectly otherwise - invalid argument – Dreshar Dec 12 '14 at 11:12
I think i know why i get the invalid argument. The huge data is too much to query so it returns false - php telling me i supplied an invalid argument. But i still dont get how i can query it properly. – Dreshar Dec 12 '14 at 11:24

MySQL selecting 20.000 rows

1 Answers1