Solved

PHP Select Loop Insert

Posted on 2011-09-06
23
223 Views
Last Modified: 2012-05-12
I am trying to set an array using the entire column groupindex from my table wps_groups.  I want to then use the $test variable to make calls to a vendor api see code below.

 
$groupProductResult = $wps->api(array(
						"method"=>"product.getGroupProducts",	//(required) The name of the method to call
						"responseType"=>"HTML_OPTIONS", 		//(optional) could be PHP_ARRAY (default), HTML_OPTIONS, XML
						"group"=>"$test",   					//(required) Single group to retrieve products from.
						//"selectedProduct"=>"690-33092",   		//(optional) The part number to be selected.
						//"productSort"=>"ALPHA_ASC",   		//(optional) could be ALPHA_ASC, ALPHA_DESC, DEFAULT (if not provided DEFAULT)
					  ));

Open in new window


I am using a simple select statement to get the groupindex column from the table and have spent the entire day writing while and for loops to try to use this information as shown above.  Nothing has worked out does anyone have any suggestions.
0
Comment
Question by:cmaddrix
[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
  • 8
  • 7
  • 3
  • +2
23 Comments
 
LVL 5

Expert Comment

by:wmadrid1
ID: 36492301
Please post more info,

What SQL query are you trying, over what database engine?
already do you have the correct value on the var $test?
you need call the api with all the rows wich the sql query return?
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 36492711
the best way to get a reasonable answer with database queries (and with most other questions) is to provide as much information as possible

1. show us the database structure with some sample data
2. show us what you have done so far
3. show us what your desired output would look like
0
 

Author Comment

by:cmaddrix
ID: 36494730
I am using a mysql database.  Here is the code structure.

 
require("config.php");

//select entire groupindex column from table
$result = mysql_query("SELECT groupindex FROM wps_groups");

while($row = mysqli_fetch_array($result))

//this does not work
foreach($result as $row)
	{
	//make api call for each groupindex number in database $test needs to be set somehow
	  	$groupProductResult = $wps->api(array(
						"method"=>"product.getGroupProducts",	//(required) The name of the method to call
						"responseType"=>"HTML_OPTIONS", 		//(optional) could be PHP_ARRAY (default), HTML_OPTIONS, XML
						"group"=>"$test",   					//(required) Single group to retrieve products from.
						//"selectedProduct"=>"690-33092",   		//(optional) The part number to be selected.
						//"productSort"=>"ALPHA_ASC",   		//(optional) could be ALPHA_ASC, ALPHA_DESC, DEFAULT (if not provided DEFAULT)
					  ));
	
	
	}
mysql_close($dbC);

Open in new window

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!

 

Author Comment

by:cmaddrix
ID: 36494738
I will be inserting the data that is returned into a separate table
0
 
LVL 27

Assisted Solution

by:Lukasz Chmielewski
Lukasz Chmielewski earned 200 total points
ID: 36494803
Try like this, you have double loop in there,
require("config.php");

//select entire groupindex column from table
$result = mysql_query("SELECT groupindex FROM wps_groups");

while($row = mysqli_fetch_array($result))
{
 	$groupProductResult = $wps->api(array(
				"method"=>"product.getGroupProducts",	//(required) The name of the method to call
				"responseType"=>"HTML_OPTIONS", 		//(optional) could be PHP_ARRAY (default), HTML_OPTIONS, XML
				"group"=>$row[groupindex],   					//(required) Single group to retrieve products from.
				//"selectedProduct"=>"690-33092",   		//(optional) The part number to be selected.
				//"productSort"=>"ALPHA_ASC",   		//(optional) could be ALPHA_ASC, ALPHA_DESC, DEFAULT (if not provided DEFAULT)
					  ));
	
	
	}
mysql_close($dbC);

Open in new window

0
 
LVL 24

Accepted Solution

by:
mankowitz earned 200 total points
ID: 36496198
I agree with above, but you shouldn't use the bareword groupindex, and you should free the result, and you should be careful mixing the mysql_ and mysqli_ commands.

require("config.php");

//select entire groupindex column from table
$result = mysql_query("SELECT groupindex FROM wps_groups");

while($row = mysqli_fetch_array($result))
{
        $groupProductResult = $wps->api(array(
                                "method"=>"product.getGroupProducts",   //(required) The name of the method to call
                                "responseType"=>"HTML_OPTIONS",                 //(optional) could be PHP_ARRAY (default), HTML_OPTIONS, XML
                                "group"=>$row['groupindex'],                                      //(required) Single group to retrieve products from.
                                //"selectedProduct"=>"690-33092",               //(optional) The part number to be selected.
                                //"productSort"=>"ALPHA_ASC",                   //(optional) could be ALPHA_ASC, ALPHA_DESC, DEFAULT (if not provided DEFAULT)
                                          ));
        
        
        }
mysqli_free_result($result);
mysql_close($dbC);

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36497590
Going forward, you may find that var_dump() is your friend.  It will let you see the contents of variables including queries and responses.  Very useful when debugging.
http://php.net/manual/en/function.var-dump.php
0
 

Author Comment

by:cmaddrix
ID: 36502835
I tried mankowitz's suggestion and it returned the following errors.

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, resource given in /home/idiotcyc/public_html/wps/group-products.php on line 27

Notice: Undefined variable: groupProductResult in /home/idiotcyc/public_html/wps/group-products.php on line 39
NULL
Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, resource given in /home/idiotcyc/public_html/wps/group-products.php on line 121

I then got rid of mysqli and tried the following code and the page just hangs.  I also added var_dump so I can see the output, thanks ray
 
require("config.php");


//select entire groupindex column from table
$result = mysql_query("SELECT groupindex FROM wps_groups");

while($row = mysql_fetch_array($result))
{
        $groupProductResult = $wps->api(array(
                                "method"=>"product.getGroupProducts",   //(required) The name of the method to call
                                "responseType"=>"HTML_OPTIONS",                 //(optional) could be PHP_ARRAY (default), HTML_OPTIONS, XML
                                "group"=>$row['groupindex'],                                      //(required) Single group to retrieve products from.
                                //"selectedProduct"=>"690-33092",               //(optional) The part number to be selected.
                                //"productSort"=>"ALPHA_ASC",                   //(optional) could be ALPHA_ASC, ALPHA_DESC, DEFAULT (if not provided DEFAULT)
                                          ));
        
        
        }
	var_dump($groupProductResult);
mysql_free_result($result);
mysql_close($dbC);

Open in new window

0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 36502961
The correct way to run a query includes error checking and visualization.  Instead of this:

$result = mysql_query("SELECT groupindex FROM wps_groups");

Something more like this:

$string = "SELECT groupindex FROM wps_groups";
$result = mysql_query($string);
if (!$result)
{
    $msg = 'FAIL: ' . $string . '<br/>' . mysql_errno() . ' ' . mysql_error();
    die($msg);
}
$num = mysql_num_rows($result);
if (!$num) echo "NOTHING FOUND BY $string";
0
 

Author Comment

by:cmaddrix
ID: 36503088
OK I change it and the page is still hanging.  Here is my code

 
require("config.php");


$string = "SELECT groupindex FROM wps_groups";
$result = mysql_query($string);
if (!$result)
{
    $msg = 'FAIL: ' . $string . '<br/>' . mysql_errno() . ' ' . mysql_error();
    die($msg);
}
$num = mysql_num_rows($result);
if (!$num) echo "NOTHING FOUND BY $string";

while($row = mysql_fetch_array($result))
{
        $groupProductResult = $wps->api(array(
                                "method"=>"product.getGroupProducts",   //(required) The name of the method to call
                                "responseType"=>"HTML_OPTIONS",                 //(optional) could be PHP_ARRAY (default), HTML_OPTIONS, XML
                                "group"=>$row['groupindex'],                                      //(required) Single group to retrieve products from.
                                //"selectedProduct"=>"690-33092",               //(optional) The part number to be selected.
                                //"productSort"=>"ALPHA_ASC",                   //(optional) could be ALPHA_ASC, ALPHA_DESC, DEFAULT (if not provided DEFAULT)
                                          ));
        
        
        }
	var_dump($groupProductResult);
mysql_free_result($result);
mysql_close($dbC);

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36503255
Right after line 13, please add this:

var_dump($num);

That should print out something like int(3)

If the page still hangs and produces nothing the issue is somewhere else - not in the query.
0
 

Author Comment

by:cmaddrix
ID: 36503362
I did and the page still hangs here is the full code for this page.  It works fine if I just make a standard call and use an actual number for group instead of $row['groupindex'] which will return the results for one group but not all of them

//Turn error reporting on for easier testing.
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 'On');
ini_set('display_startup_errors', 'On');


//Include the WPS platform
require "wps.php";

//Instantiate a WPS Object. 

$wps = new WPS(array( 
	"dealerId" => "",
	"username" => "",
	"password" => "",
	"baseDomain" => "", //IP address only
	"fileUpload" => true)
);

require("config.php");


$string = "SELECT groupindex FROM wps_groups";
$result = mysql_query($string);
if (!$result)
{
    $msg = 'FAIL: ' . $string . '<br/>' . mysql_errno() . ' ' . mysql_error();
    die($msg);
}
$num = mysql_num_rows($result);
if (!$num) echo "NOTHING FOUND BY $string";
var_dump($num);

while($row = mysql_fetch_array($result))
{
        $groupProductResult = $wps->api(array(
                                "method"=>"product.getGroupProducts",   //(required) The name of the method to call
                                "responseType"=>"HTML_OPTIONS",                 //(optional) could be PHP_ARRAY (default), HTML_OPTIONS, XML
                                "group"=>$row['groupindex'],                                      //(required) Single group to retrieve products from.
                                //"selectedProduct"=>"690-33092",               //(optional) The part number to be selected.
                                //"productSort"=>"ALPHA_ASC",                   //(optional) could be ALPHA_ASC, ALPHA_DESC, DEFAULT (if not provided DEFAULT)
                                          ));
        
        
        }
		
var_dump($groupProductResult);
	
mysql_free_result($result);
mysql_close($dbC);

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36503409
Maybe you should look at the value in $row?  I don't know what else to suggest, except detailed data visualization, sorry.  It's just a one-step-at-a-time kind of thing.
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 36503618
Isn't that "," after $row['groupindex']  ?

"group"=>$row['groupindex'],                                      //(required) Single group to retrieve products from.
//"selectedProduct"=>"690-33092",               //(optional) The part number to be selected.
//"productSort"=>"ALPHA_ASC",                   //(optional) could be ALPHA_ASC, ALPHA_DESC, DEFAULT (if not provided DEFAULT)
                                          ));
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36503707
@Roads_Roads: Good catch!  The framework is probably suppressing the parse error message, which should arise from the use of the comments on the next two lines.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36503736
On second thought, no.  This parses correctly.  Array elements are permitted to have trailing commas.
<?php // RAY_temp_cmaddrix.php
error_reporting(E_ALL);

// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_27294142.html#a36503362

        $groupProductResult = $wps->api(array(
                                "method"=>"product.getGroupProducts",   //(required) The name of the method to call
                                "responseType"=>"HTML_OPTIONS",                 //(optional) could be PHP_ARRAY (default), HTML_OPTIONS, XML
                                "group"=>$row['groupindex'],                                      //(required) Single group to retrieve products from.
                                //"selectedProduct"=>"690-33092",               //(optional) The part number to be selected.
                                //"productSort"=>"ALPHA_ASC",                   //(optional) could be ALPHA_ASC, ALPHA_DESC, DEFAULT (if not provided DEFAULT)
                                          ));

Open in new window

0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 36503751
Nevertheless, I'd give a shot without it... :)
0
 
LVL 5

Expert Comment

by:wmadrid1
ID: 36503862
Hi, when you use the function mysql_fetch_array
you must access the result by index

while($row = mysql_fetch_array($result))
{
echo $row[0];
}

Open in new window


so use in your code
$row[0]


    $groupProductResult = $wps->api(array(
                                "method"=>"product.getGroupProducts",   //(required) The name of the method to call
                                "responseType"=>"HTML_OPTIONS",                 //(optional) could be PHP_ARRAY (default), HTML_OPTIONS, XML
                                "group"=>$row[0],                                      //(required) Single group to retrieve products from.
                                //"selectedProduct"=>"690-33092",               //(optional) The part number to be selected.
                                //"productSort"=>"ALPHA_ASC",                   //(optional) could be ALPHA_ASC, ALPHA_DESC, DEFAULT (if not provided DEFAULT)
                                          ));

Open in new window

0
 

Author Comment

by:cmaddrix
ID: 36503921
I tried removing the comma and and wmadrid1  solution with the $row[0] but the page is still hanging
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 36505199
when you say hanging, do you mean that it is not printing any results or that it is just giving you errors. If the latter, what errors is it giving you. Also, can you post your current (revised, revised, revised) page?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36505354
Regarding this: when you use the function mysql_fetch_array you must access the result by index

That is factually inaccurate.  Please see the man page, where it shows the default behavior is governed by MYSQL_BOTH, meaning that you may access the result by index or by associative key.
http://php.net/manual/en/function.mysql-fetch-array.php
0
 

Author Comment

by:cmaddrix
ID: 36505408
Turns out the vendor API is not functioning right now.  I just got off the phone with them.  Thanks for all your help
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 36505466
Interesting.  I wonder if there is something that might be changed in the WPS class that could detect the "out of order" condition.  Perhaps using CURL with a timeout and some kind of return code that says, "it timed out."
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Title # Comments Views Activity
PHP: Filling Out/Creating a PDF 29 97
Multi line FPDF footer: 3 25
Special characters in a TCPDF 4 26
php hashing methods 3 12
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
This article discusses how to create an extensible mechanism for linked drop downs.
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 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…

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