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

Use fetchAll intead of fetch:

while ($row3 = $query3->fetchAll(PDO::FETCH_ASSOC)) {

Select allOpen in new window

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

While I was hoping FetchAll, would work, it returned no data.

Chris, Please advise how to code that.   Thanks!

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;

Select allOpen in new window

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;

Select allOpen in new window