Solved

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

Posted on 2015-02-20
6
182 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 30

Expert Comment

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

Open in new window

0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
While I was hoping FetchAll, would work, it returned no data.

Chris, Please advise how to code that.   Thanks!
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now