PHP / MYSQL: iterate over each record in the database

Question

I am new to all php / mysql work. I have magazine magazines for the week (about 300,000 items), and I need to do some analysis. I plan to read them all in mysql db and then parse them with php.

That I'm not sure how to get through them. Using java reading a file, I would do something like this:

Scanner s = new Scanner(myfile);
while(s.hasNext()){
    String line = s.nextLine();
    ~~ Do something with this record. 
}

How to iterate over all records in mysql db using php? I think something like this will take a dumb amount of memory.

    $query = "SELECT * FROM mytable";
    $result = mysql_query($query);
    $rows = mysql_num_rows($result);
    for($j = 0; $j < $rows; ++$j){
            $curIndex   = mysql_result($result,$j,"index");
            $curURL     = mysql_result($result,$j,"something");
            ~~ Do something with this record
    }

So, I added a restriction on the select statement, and repeat until all the records go through cyclically. Is there a more standard way to do this? Is there a built-in mechanism that will do this?

while($startIndex < $numberOfRows){

    $query = "SELECT * FROM mytable ORDERBY mytable.index LIMIT $startIndex,$endIndex";
    $result = mysql_query($query);
    $rows = mysql_num_rows($result);
    for($j = 0; $j < $rows; ++$j){
            $curIndex   = mysql_result($result,$j,"index");
            $curURL     = mysql_result($result,$j,"something");
            ~~ Do something with this record
    }
    $startIndex = $endIndex + 1;
    $endIndex = $endIndes + 10;
}

Answer 1

:

http://www.tizag.com/mysqlTutorial/

http://www.tizag.com/mysqlTutorial/mysqlfetcharray.php

<?php
// Make a MySQL Connection
$query = "SELECT * FROM example"; 

$result = mysql_query($query) or die(mysql_error());


while($row = mysql_fetch_array($result)){
    echo $row['name']. " - ". $row['age'];
    echo "<br />";
}
?>

, , , while ', ' x x x. "" .

Answer 2

SELECT * FROM MYTABLE, , . . :

SELECT MIN(ID) FROM MYTABLE;
SELECT MAX(ID) FROM MYTABLE;

minId maxId, , , 10 000 . :

for (int i = minId; i < maxId; i = i + 10000) {
   int x = i;
   int y = i + 10000;
   SELECT * FROM MYTABLE WHERE ID >= x AND ID < y;
}

Answer 3

mysql_fetch_*

$result = mysql_query(...);
while($row = mysql_fetch_assoc($result)) {
 $curIndex = $row['index'];
}

, "" , . , mysql_result.

: , mysql_ PDO mysqli.

Answer 4

PHP , MySQL . , :

SELECT COUNT(*), severity 
FROM logs
WHERE date < ? AND date > ?
GROUP BY severity

PHP . , SQL- (, , , ), Map-Reduce CouchDB.

Answer 5

, Doctrine MySQL (PDO mysqli) - .

@dimitri-k , . : " $query- > iterate()", . \Traversable .

, , Doctrine , :

echo 'Starting with memory usage: ' . memory_get_usage(true) / 1024 / 1024 . " MB \n";

$pdo  = new \PDO("mysql:dbname=DBNAME;host=HOST", "USER", "PW");
$stmt = $pdo->prepare('SELECT * FROM my_big_table LIMIT 100000');
$stmt->execute();

while ($rawCampaign = $stmt->fetch()) {
    // echo $rawCampaign['id'] . "\n";
}

echo 'Ending with memory usage: ' . memory_get_usage(true) / 1024 / 1024 . " MB \n";

:

Starting with memory usage: 6 MB 
Ending with memory usage: 109.46875 MB

getIterator():

namespace Doctrine\DBAL\Driver\Mysqli\MysqliStatement

/**
 * {@inheritdoc}
 */
public function getIterator()
{
    $data = $this->fetchAll();

    return new \ArrayIterator($data);
}

You can use my small library for actual heavy table flows using PHP Doctrine or DQL or just plain SQL. However, you see fit: https://github.com/EnchanterIO/remote-collection-stream