[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

PHP Select Loop Insert

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
cmaddrix
Asked:
cmaddrix
  • 8
  • 7
  • 3
  • +2
3 Solutions
 
wmadrid1Commented:
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
 
mankowitzCommented:
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
 
cmaddrixAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
cmaddrixAuthor Commented:
I will be inserting the data that is returned into a separate table
0
 
Lukasz ChmielewskiCommented:
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
 
mankowitzCommented:
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
 
Ray PaseurCommented:
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
 
cmaddrixAuthor Commented:
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
 
Ray PaseurCommented:
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
 
cmaddrixAuthor Commented:
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
 
Ray PaseurCommented:
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
 
cmaddrixAuthor Commented:
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
 
Ray PaseurCommented:
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
 
Lukasz ChmielewskiCommented:
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
 
Ray PaseurCommented:
@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
 
Ray PaseurCommented:
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
 
Lukasz ChmielewskiCommented:
Nevertheless, I'd give a shot without it... :)
0
 
wmadrid1Commented:
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
 
cmaddrixAuthor Commented:
I tried removing the comma and and wmadrid1  solution with the $row[0] but the page is still hanging
0
 
mankowitzCommented:
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
 
Ray PaseurCommented:
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
 
cmaddrixAuthor Commented:
Turns out the vendor API is not functioning right now.  I just got off the phone with them.  Thanks for all your help
0
 
Ray PaseurCommented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 8
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now