CODEWITHSUNDEEP
phpsqlselectrow
Vidarious
Vidarious

Reputation: 826

How do I select entire table of data one row at a time?

I have a table (customer) with 10,000 records. I would like to modify every single record one at a time and modify some data then update it back into the database. It doesn't make sense to SELECT * FROM customer then fetchAll since there are 10,000 records.

I'd like to create a while loop in PHP which does a incrementing SELECT statement but what would a select statment like this look like if there is no stable primary key that increments nicely? Logically the while loop would look like:

$row = 1;
while(Number_Rows_In_Table)
{
  $record = $this->db->exec("SELECT * FROM customer WHERE ROW = $row");
  $row++;
  //Do something with the data then update it
}

Is there a way to do this?

Upvotes: 0

Views: 303

Answers (4)

Mike B
Mike B

Reputation: 32155

You could use offset, limit syntax available in MySQL... but it seems like a strange thing to do

SELECT * FROM customer LIMIT $row, 1

Assuming $row starts at 0 and is auto-incremented (like in your example). You'd probably have to order it as well.

Upvotes: 2

smilly92
smilly92

Reputation: 2443

Depending on what you need to update you can probably just use SQL. For example, to find and replace a sting:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);

Upvotes: 0

Madara's Ghost
Madara's Ghost

Reputation: 174977

You don't have to use fetchAll you know:

$stmt = $pdo->query("SELECT * FROM `table`");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    //Do stuff with specific rows one a time
}

This will be much faster than having 10,000 select queries. I can guarantee you that.

Upvotes: 2

nickb
nickb

Reputation: 59699

You don't have to call fetchAll() and grab all of the rows, just fetch one row at a time from a SELECT * FROM customer query with fetch().

Upvotes: 0

Related Questions