Solved

PHP Select Loop Insert

Posted on 2011-09-06
23
213 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
  • 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
 

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 108

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 108

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 108

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
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!

 

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 108

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 108

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 108

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 108

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 108

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

747 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

14 Experts available now in Live!

Get 1:1 Help Now