Solved

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

Posted on 2015-02-20
6
212 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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 …

749 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