Solved

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

Posted on 2015-02-20
6
188 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This article discusses four methods for overlaying images in a container on a web page
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.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

947 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

21 Experts available now in Live!

Get 1:1 Help Now