Solved

How do I loop through all of the results?  PHP, PDO

Posted on 2015-02-20
6
197 Views
Last Modified: 2015-02-21
This script works perfectly, however, it only returns 1 row of data.  I have 11000+ rows of data.

How do I create a while loop or foreach to get through the query?


$query3 = $conn->query('SELECT DISTINCT CompatiblePartNumber, ERPartNumber FROM `ErepCrossReference1`');
    
// LOOP HERE???  		
$partNumbers = array();   
$erPartNumber = '';
while ($row3 = $query3->fetch(PDO::FETCH_ASSOC)) {
     // build an array of the part numbers
     $partNumbers[] = $row3["CompatiblePartNumber"];
     $erPartNumber = $row3["ERPartNumber"];
}

// convert the array into a comma delimited string
$partNumberList = implode(",", $partNumbers);

// prepare the query
$stmt = $conn->prepare('INSERT INTO `testtable` (ERPartNumber, CompatiblePartNumber) VALUES(:ERPartNumber, :CompatiblePartNumber)');

// bind the parameters
$params = array(':ERPartNumber' => $erPartNumber, ':CompatiblePartNumber' => isset($partNumberList) ? $partNumberList : '');

// execute the query
$stmt->execute($params); 

Open in new window


THE RESULTS OF THIS QUERY:
ID     ERPartNumber           CompatiblePartNumber      
4             WT870                     02K6513,02K6513,02K6513,02K6513,02K6513,02K6513,02..
0
Comment
Question by:lawrence_dev
  • 3
  • 2
6 Comments
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 40622679
Use fetchAll intead of fetch:
while ($row3 = $query3->fetchAll(PDO::FETCH_ASSOC)) {

Open in new window

0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40622842
Hey Lawrence,

You don't need to fetchAll().

Using fetch() in a loop will work it's way through all of the records. The problem you have here is one of data. In a previous question you asked, you indicated that you would only have one ERPartNumber, so there is only one INSERT Query.

You now say that's not the case, so what happens is that the while loop will loop through all 11000 records to build the array, and then insert all of that into your database with the LAST ERPartNumber it comes across. One INSERT query!

What you'll probably need to do here is select all the DISTINCT ERPartNumbers from your database in one query, loop through that, selecting all the compatible part numbers for the given ERPartNumber, and run the INSERT query in that loop. This way you'll get an INSERT for each ERPartNumber.

Let us know if you need help coding that
0
 

Author Comment

by:lawrence_dev
ID: 40623097
While I was hoping FetchAll, would work, it returned no data.

Chris, Please advise how to code that.   Thanks!
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 40623131
Hey Lawrence,

As I said - fetchAll() wouldn't have worked - all that does it fetch all your records as an array, so you'd then need to loop through the array, and you'd have exactly the same problem as you had before.

Have a look at this, and if there's something you don't understand, feel free to ask. I've added a few comments to help:

// Prepare the SELECT Query to select the compatible parts
$selectParts = $conn->prepare("SELECT CompatiblePartNumber FROM ErepCrossReference1 WHERE ERPartNumber = :erPartNumber");
$selectParts->bindParam(":erPartNumber", $erPartNumber, PDO::PARAM_STR);

// Prepare the INSERT Query to insert the new records
$insertParts = $conn->prepare("INSERT INTO testtable (ERPartNumber, CompatiblePartNumber) VALUES (:erPartNumber, :compPartNumbers)");
$insertParts->bindParam(":erPartNumber", $erPartNumber, PDO::PARAM_STR);
$insertParts->bindParam(":compPartNumbers", $compPartNumbers, PDO::PARAM_STR);

// Loop through the ERPartNumbers
foreach ($conn->query("SELECT DISTINCT ERPartNumber FROM ErepCrossReference1", PDO::FETCH_COLUMN, 0) as $erPartNumber):
	// Select the Compatible Parts
	$selectParts->execute();
	// Implode the Compatible Parts into a pipe delimited string
	$compPartNumbers = implode("|", $selectParts->fetchAll(PDO::FETCH_COLUMN, 0));
	// Insert the parts into the new table
	$insertParts->execute();
endforeach;

Open in new window

0
 

Author Comment

by:lawrence_dev
ID: 40623171
That worked great.  Starting to figure this out...

If I want to add another column of data, where do I do that?   I have attempted it but it is not working correctly...  I added "Model".

// Prepare the SELECT Query to select the compatible parts
$selectParts = $conn->prepare("SELECT DISTINCT CompatiblePartNumber, Model FROM ErepCrossReference1 WHERE ERPartNumber = :erPartNumber");
$selectParts->bindParam(":erPartNumber", $erPartNumber, PDO::PARAM_STR);

// Prepare the INSERT Query to insert the new records
$insertParts = $conn->prepare("INSERT INTO testtable (ERPartNumber, CompatiblePartNumber, Model) VALUES (:erPartNumber, :compPartNumbers, :Model)");
$insertParts->bindParam(":erPartNumber", $erPartNumber, PDO::PARAM_STR);
$insertParts->bindParam(":compPartNumbers", $compPartNumbers, PDO::PARAM_STR);
$insertParts->bindParam(":Model", $compPartNumbers, PDO::PARAM_STR);
// Loop through the ERPartNumbers
foreach ($conn->query("SELECT DISTINCT ERPartNumber FROM ErepCrossReference1", PDO::FETCH_COLUMN, 0) as $erPartNumber):
	// Select the Compatible Parts
	$selectParts->execute();
	// Implode the Compatible Parts into a pipe delimited string
	$compPartNumbers = implode("|", $selectParts->fetchAll(PDO::FETCH_COLUMN, 0));
	// Insert the parts into the new table
	$insertParts->execute();
endforeach;

Open in new window

0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40623221
Right. You're going to need to think this through a little. Now you've added a new column (model) to the select query, the records you get back aren't going to have the same relationship.

For example, do you want to concatenate all the Model values into a delimited string, similar to how you did with CompatiblePartNumbers. Bear in mind that when you run your select query, you will likely have duplicate CompatiblePartNumbers and duplicate Models. This may make your delimited strings somewhat meaningless.

Because you're adding another column, you'll need to do it slightly differently. You only had one column in your original select, so we could build the delimited string by fetching the column. Now you've added another column, you can't do that. You'll need to loop through the returned records and manually build the array.

Also, in your code above, you've added the parameter binding for Model, but you're still binding it to $compPartNumbers.

Have a look at this and see if it makes sense:

// Prepare the SELECT Query to select the compatible parts
$selectParts = $conn->prepare("SELECT DISTINCT CompatiblePartNumber, Model FROM ErepCrossReference1 WHERE ERPartNumber = :erPartNumber");
$selectParts->bindParam(":erPartNumber", $erPartNumber, PDO::PARAM_STR);

// Prepare the INSERT Query to insert the new records
$insertParts = $conn->prepare("INSERT INTO testtable (ERPartNumber, CompatiblePartNumber, Model) VALUES (:erPartNumber, :compPartNumbers, :model)");
$insertParts->bindParam(":erPartNumber", $erPartNumber, PDO::PARAM_STR);
$insertParts->bindParam(":compPartNumbers", $compPartNumbers, PDO::PARAM_STR);
$insertParts->bindParam(":model", $model, PDO::PARAM_STR);

// Loop through the ERPartNumbers
foreach ($conn->query("SELECT DISTINCT ERPartNumber FROM ErepCrossReference1", PDO::FETCH_COLUMN, 0) as $erPartNumber):
	// Select the Compatible Parts and Models
	$selectParts->execute();

	// We need to manually build the delimited strings
	$partsArray = array();
	$modelArray = array();
	while ($row = $selectParts->fetch()):
		$partsArray[] = $row->CompatiblePartNumber;
		$modelArray[] = $row->Model; 
	endwhile;

	// You may want to filter your arrays for unique values
	$partsArrayUnique = array_unique($partsArray);
	$modelArrayUnique = array_unique($modelArray);

	// Implode the Compatible Parts into a pipe delimited string
	$compPartNumbers = implode("|", $partsArrayUnique);
	$model = implode("|", $modelArrayUnique);

	// Insert the parts into the new table
	$insertParts->execute();
endforeach;

Open in new window

0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question