Solved

How to get MySQL query results (set of integers) into a numeric array as a comma separated list with PHP?

Posted on 2014-04-17
26
1,819 Views
Last Modified: 2014-04-17
How can we get MySQL query results (set of integers) into a numeric array as a comma sepatated list?

I mean, we need to use the query results inside multiGCD(array(???????)) and make it work as "multiGCD(array(40,100,20,100,20,40,1000,500,99,100));" in order to produce GCD of the integers.

<?php
$process = arg(1);
$scale_qtys = db_query("SELECT field FROM scale1 WHERE bundle = 'costs' AND deleted = '0' AND process_nid = {$process}");

function getGCDBetween($a, $b)
{
    while ($b != 0)
    {
        $m = $a % $b;
        $a = $b;
        $b = $m;
    }
    return $a;
}

function multiGCD($nums)
{
    $gcd = getGCDBetween($nums[0], $nums[1]);
    for ($i = 2; $i < count($nums); $i++) { $gcd = getGCDBetween($gcd, $nums[$i]); }
    return $gcd;
}

print multiGCD(array(???????));
?>

Open in new window


Further details;

*"db_query" is a just query format for Drupal and query is working.
*We can get an output as 40,100,20,100,20,40,1000,500,99,100, if we use foreach;

foreach($scale_qtys as $value) {
$scale_qty_value= $value->field;
print $scale_qty_value.",";
}

Open in new window


*multiGCD function is also working if we paste the list (40,100,20,100,20,40,1000,500,99,100) by hand.
*multiGCD is not working if we use SELECT GROUP_CONCAT(field) inside our MySQL query, even the form of query results seems correct.
0
Comment
Question by:sbayrak
  • 9
  • 9
  • 7
  • +1
26 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40006065
If you can use foreach($scale_qtys..., then $scale_qtys is already an array and you should be able to use it in...

print multiGCD($scale_qtys);
0
 
LVL 1

Author Comment

by:sbayrak
ID: 40006092
I tried it but I get white screen of death.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 40006096
Two options - if you use the first query then

$process = arg(1);
$scale_qtys = db_query("SELECT field FROM scale1 WHERE bundle = 'costs' AND deleted = '0' AND process_nid = {$process}");

$mgcd = array();
foreach($scale_qtys as $i)
{
  $mgcd[] = $i;
}
print_r($mgcd);

Open in new window

Option 2
$process = arg(1);
$scale_qtys = db_query("SELECT GROUP_CONCAT(field) FROM scale1 WHERE bundle = 'costs' AND deleted = '0' AND process_nid = {$process}");

$mgcd = explode(',', $scale_qtys[0]);
print_r($mgcd);

Open in new window

The GROUP_CONCAT returns a comma separated string - php will see this as a string - not as an array. To get it into an array use the explode() function http://www.php.net/manual/en/function.explode.php to turn the string into an array of integers using the ',' as a delimeter.
0
 
LVL 1

Author Comment

by:sbayrak
ID: 40006222
option 1 output:
Array ( [0] => stdClass Object ( [field] => 40 ) [1] => stdClass Object ( [field] => 100 ) [2] => stdClass Object ( [field] => 20 ) [3] => stdClass Object ( [field] => 100 ) [4] => stdClass Object ( [field] => 20 ) [5] => stdClass Object ( [field] => 40 ) [6] => stdClass Object ( [field] => 1000 ) [7] => stdClass Object ( [field] => 500 ) [8] => stdClass Object ( [field] => 99 ) [9] => stdClass Object ( [field] => 100 ) )

this is not as expected...
by the way, I need to use it through print multiGCD() function... and get the correct GCD result. I don't need to see the final set of integers with print_r($mgcd)

option 2 output:
4
which is wrong... when I use it as "print multiGCD($mgcd);"

GCD of 40,100,20,100,20,40,1000,500,99,100 has to be "1", not 4.

GCD result is wrong, because when you print_r($mgcd); you get Array ( [0] => 4 ) as an output...
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 80 total points
ID: 40006236
It seems like you have two issues here.  First issue is "how do I get the query results set?"

It appears that the db_query function returns a results set of objects with property names that match the columns requested in the query string.  So to get a useful array, you might do something like this:
$process = arg(1);
$scale_qtys = db_query("SELECT field FROM scale1 WHERE bundle = 'costs' AND deleted = '0' AND process_nid = {$process}");

$mgcd = array();
foreach($scale_qtys as $i)
{
  $mgcd[] = $i->field;
}
print_r($mgcd);

Open in new window

We put the print_r() statement into the code to be able to visualize the data, an essential step in debugging the code.  You can remove it after you know that the code works correctly, but not before.  So let's take this step first and see what the output array looks like.  If it is as expected, you can move on to the next part, which is the GCD thing.
0
 
LVL 1

Author Comment

by:sbayrak
ID: 40006247
ok. Ray, with $mgcd[] = $i->field; approach now our output is;

Array ( [0] => 40 [1] => 100 [2] => 20 [3] => 100 [4] => 20 [5] => 40 [6] => 1000 [7] => 500 [8] => 99 [9] => 100 )

it's assoc right?
we still need a comma separated numeric array I guess...
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 40006257
All php arrays are assoc - by default a key of 0, 1, 2 etc is created if you don't specify

In terms of the option 2 above this should work - it was provided as an illustration as you mentioned you had tried the GROUP_CONCAT - and it was not doing what you expected.
$process = arg(1);
$scale_qtys = db_query("SELECT GROUP_CONCAT(field) as field FROM scale1 WHERE bundle = 'costs' AND deleted = '0' AND process_nid = {$process}");

$mgcd = explode(',', $scale_qtys[0]->field);
print_r($mgcd);

Open in new window

In terms of option 1 - simple change
$mgcd[] = $i;

Open in new window

Becomes
$mgcd[] = $i->field;

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40006260
a comma separated numeric array
There is no such thing.  Arrays are grouped data elements that you can refer to with a single name (collectively) and with a name + index (individually).  A comma-separated string is created from an array by implode().  Associative arrays have named keys.  Numerically indexed arrays, like what you see here, have integer keys.

I don't know what the GCD process is all about, but looking at the original code snippet it looks like the code is designed to operate on an array and not on a string.  But if you want to see the query results set as a string of comma-separated values, this would probably do the trick:
// SHOW THE ARRAY
print_r($mgcd);

// MAKE A STRING WITH COMMAS TO SEPARATE THE VALUES
$mgcd_string = implode(',', $mgcd);
print_r($mgcd_string);

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40006271
Please see http://iconoun.com/demo/temp_sbayrak.php

<?php // demo/temp_sbayrak.php
error_reporting(E_ALL);

// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28414624.html

// TEST DATA FROM THE POST AT EE
$arr = Array
( 0 => 40
, 1 => 100
, 2 => 20
, 3 => 100
, 4 => 20
, 5 => 40
, 6 => 1000
, 7 => 500
, 8 => 99
, 9 => 100
)
;

// FUNCTIONS COPIED FROM THE POST AT EE
function getGCDBetween($a, $b)
{
    while ($b != 0)
    {
        $m = $a % $b;
        $a = $b;
        $b = $m;
    }
    return $a;
}

function multiGCD($nums)
{
    $gcd = getGCDBetween($nums[0], $nums[1]);
    for ($i = 2; $i < count($nums); $i++) { $gcd = getGCDBetween($gcd, $nums[$i]); }
    return $gcd;
}

// RUN THE FUNCTION USING THE TEST DATA
$x = multigcd($arr);
echo '<pre>';
print_r($arr);
var_dump($x);

Open in new window

HTH, ~Ray
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 40006291
To show that all methods work the same AND that a default array is what you are looking for - here some code that demonstrates each
<?php
$str = '40,100,20,100,20,40,1000,500,99,100';
$arr = array(40,100,20,100,20,40,1000,500,99,100);

// CREATE OBJECT FROM ARRAY TO EMULATE ARRAY OF OBJECTS 
// RETURNED FROM DB CALL
function & makeObject($inp)
{
  $ret = array();
  foreach($inp as $a) {
    $obj = new stdClass;
    $obj->field = $a;
    $ret[] = $obj;
  }
  return $ret;
}

// FROM QUESTION
function getGCDBetween($a, $b)
{
    while ($b != 0)
    {
        $m = $a % $b;
        $a = $b;
        $b = $m;
    }
    return $a;
}
// FROM QUESTION
function multiGCD($nums)
{
    $gcd = getGCDBetween($nums[0], $nums[1]);
    for ($i = 2; $i < count($nums); $i++) { $gcd = getGCDBetween($gcd, $nums[$i]); }
    return $gcd;
}

// RESULTS FROM A DEFAULT PHP ARRAY
echo "GCD: " . multiGCD($arr) . "<br/>";

// EMULATE RETURN FROM GROUP_CONCAT WHERE RESULT IS
// A COMMA SEPARATED STRING OF INTEGERS
echo "GCD: " . multiGCD(explode(',',$str)) . "<br/>";
// TO DEMONSTRATE SAME RESULT AS DB QUERY

// EMULATE THE DB CALL
$scale_qtys = makeObject($arr);

// VERIFY RETURN IS IDENTICAL TO RETURN FROM DB
// (AS DISPLAYED IN POST #40006222)  
echo "<pre>";
print_r($scale_qtys);
echo "</pre>";

// DEMONSTRATE CREATION OF ARRAY FROM RETURNED
// RESULTS ALSO RETURNS SAME RESULT
$mgcd = array();
foreach($scale_qtys as $i)
{
  $mgcd[] = $i->field;
}
echo "GCD: " . multiGCD($mgcd) . "<br/>";
?>

Open in new window

0
 
LVL 1

Author Comment

by:sbayrak
ID: 40006308
GCD (greatest common devisior) function is yes designed to operate on an "array", not a string. That's why it works when we paste array (40,100,20,100,20,40,1000,500,99,100) by hand, and doesn't work when we $... it. It needs this; array(a,b,c)

Regading your last comment, yes, print_r($mgcd_string) shows the result set as comma separated values right visually, but it produces wierd "4" as a result when we use it within GCD, which is wrong. I think again because it is a string...

Anyway now we have "Array ( [0] => 40 [1] => 100 [2] => 20 [3] => 100 [4] => 20 [5] => 40 [6] => 1000 [7] => 500 [8] => 99 [9] => 100 )" in our hand before imploding. Isn't it possible to convert and use it AS AN ARRAY within the function?
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 40006355
but it produces wierd "4" as a result when we use it within GCD, which is wrong. I think again because it is a string..
Implode is not something you need to consider in this context. Your function expects an array - Implode creates a string. Not sure how implode entered the fray but it should not be here.

Explode - yes if you are working on a string - it converts a string to an array.

Isn't it possible to convert and use it AS AN ARRAY within the function?
The array you have can be used as is on the mutliGCD function - the sample I posted earlier demonstrates this along with all the other options for retrieving data and passing it to the function.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40006394
Anyway now we have "Array ... use it AS AN ARRAY within the function?
Yes, of course.  That is exactly what is demonstrated in the code snippet I posted earlier.  
http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28414624.html#a40006271

Please see lines 7-19, where we define the array.  This would be the information obtained from the db_query() function call, after it is processed into an array of numbers (instead of an array of objects, which obviously did not work).

Next, please see lines 21-38.  These are copied from the original question, posted right at the top.

Finally please see line 41.  This is where we run the function(s) and retrieve the value from the function in the $x variable.  The next lines are simply data visualization so we can see if we are getting the information we want.  Once we are certain that the code is working correctly we can remove any unwanted visualization.

In PHP, arrays and strings are different data structures and they are not interchangeable as arguments to a function call (even if they can be converted back and forth by specialized programming).  So you must choose the array for input to the function call if the function expects an array.

If you're new to PHP and you want some good learning resources, this article may be helpful.  In addition to steering you in the right learning directions, it will help steer you away from the many simply terrible examples of bad PHP code that litter the internet!
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:sbayrak
ID: 40006395
Regarding Ray's working examples;

His example works because Ray uses this;
Array ( 0 => 40, 1 => 100, 2 => 20, 3 => 100, 4 => 20, 5 => 40, 6 => 1000, 7 => 500, 8 => 99, 9 => 100 )

But our MySQL query returns this (with square brackets and without commas);
Array ( [0] => 40 [1] => 100 [2] => 20 [3] => 100 [4] => 20 [5] => 40 [6] => 1000 [7] => 500 [8] => 99 [9] => 100 )
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40006416
The square brackets are used by print_r() to show you that it is an array!  Your MySQL query returns some kind of data, but from the nature of the question it was apparent that you were a bit unsure about exactly what the format of the data could be.  A professional programmer would use print_r() to make a visual, human-readable, representation of the data, so you can see what it looks like.  All of the built-in PHP functions are documented in the online man pages, so if you're not completely sure what something is doing, you can look it up.  Example:
http://php.net/manual/en/function.print-r.php
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40006437
If you wanted to choose only one book to learn PHP, I would recommend that you get the latest version of this one.  When the new version is released, give the old one to one of your enemies and buy the new version.  This is a great "getting started" book.  It will help you understand all of the basics of PHP.  A couple of months spent studying will save you from a couple of years of trial-and-error, I promise!
http://www.amazon.com/PHP-MySQL-Web-Development-4th/dp/0672329166/

This book is old, but you don't need all of the latest bells and whistles to get a foundation in the basics.  And a 5th edition is expected to be released this year.
0
 
LVL 1

Author Comment

by:sbayrak
ID: 40006454
I'm not a PHP guru. So what do you suggest? Studying PHP couple of years till I became at least a PHP expert in order to get this simple snippet work? Then why I'm subscribed here if I would leave my actual expertises apart and start to learn a new one everytime I face anything because of my job?

Anyway, I use option-1;

$mgcd = array();
foreach($scale_qtys as $i)
{
  $mgcd[] = $i->field;
}
print_r($mgcd);

Open in new window


it returns an array;
Array ( [0] => 40 [1] => 100 [2] => 20 [3] => 100 [4] => 20 [5] => 40 [6] => 1000 [7] => 500 [8] => 99 [9] => 100 )


but it does not work inside the function!
0
 
LVL 51

Accepted Solution

by:
Julian Hansen earned 420 total points
ID: 40006469
But our MySQL query returns this (with square brackets and without commas);
This is not really relevant. What is relevant is that the code works when given a standard array - as demonstrated here

The multiGCD function expects as input an array of numbers.

If you are concerned about the representation of the array then this version of multiGCD will process any array - regardless of the keys used
function multiGCD($nums)
{
    $gcd = getGCDBetween(array_shift($nums), array_shift($nums));
	while(count($nums) > 0) {
		$gcd = getGCDBetween($gcd, array_shift($nums)); 
	}
    return $gcd;
}

Open in new window

The code uses array_shift to get the next value from the array rather than iterating through the array.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40006478
I recommend learning resources when I see that someone is struggling with the basics.  It's not to take up more of your time, it's to get you to a level of understanding so that we can have a dialog about the problem and find a solution.  This is not intuitive simple stuff; it requires structured learning of a programming language that uses many different terms of art.
does not work inside the function!
I'm sure we must be looking at different pieces of code.  The code snippet I posted above is exactly what I installed here, and it works correctly.
http://iconoun.com/demo/temp_sbayrak.php

If you've got a new issue, please show us the code as you have it now so we can try to see what may be causing a problem, thanks.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 40006479
but it does not work inside the function!
Can you post your full code - the code does work - the sample I posted earlier demonstrates this. If it is not working for you then we need to find where the difference is.

Given the code you have posted - if you take the output from your code snippet above and pass it to the multiGCD function you defined in your opening post - it will work.

What is it doing that makes you suspect it is not working - does it give an error or the "wrong" answer.

If an error - can you post it.

If the wrong answer - post what answer you are expecting and what it is prenting.

Based on your input the output should be 1.
0
 
LVL 1

Author Closing Comment

by:sbayrak
ID: 40006543
julianH version of multiGCD worked like a charm.
I just replaced the older one with array_shift version and it gave the correct result "1".
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 40006567
Hmm - would be interesting to know why the other version does not work.

But glad you got sorted - thanks for the points.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40006572
For anyone coming upon this question in the future...

There was nothing at all wrong with the original Greatest-Common-Divisor algorithm as implemented by the two functions posted in the question.  It has a dependency on sequential array keys, but in the context of the query and results set, it gives the correct output and there is no reason to change it.  

The real issue is getting the data out of the data base into an array of integers.
0
 
LVL 1

Author Comment

by:sbayrak
ID: 40006633
I agree that knowing why the original version failed working would be really interesting.
Thank you both for your interest and efforts.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40006712
The original version of the math functions, as posted in the question, worked correctly.  

The only thing wrong was the format or content of the input given to the functions.  

I tested all this stuff already, but didn't see any point in posting the intermediate test results since the functions were correct.  Once you get the input data right, everything is OK.  You don't need to change your functions.

I'm unable to reproduce your results that found a GCD == 4.  My only conclusion is that there were other inputs that were not in evidence in the question.
0
 
LVL 1

Author Comment

by:sbayrak
ID: 40006863
Yes, you're absolutely right that math functions were working correctly. But I already told that it gave the correct output if you passed the indexed array into the function by hand.

However, if the result set returned by the sql quey was wrong or faulty or whatever..., I think it also wouldn't work within julianH's version. Because I didn't touch anything in the code, but just changed the older multiGCD function with the new one.

By the way, there're no other inputs. I shared the code just as it was. I only shortened orig. MySQL query not to confuse anyone with lots of WHERE and AND clauses. Result set is stable and it is as I shared.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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 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…

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

13 Experts available now in Live!

Get 1:1 Help Now