SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2004
    Location
    Boston
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Array from query

    Is there a simple way to create an array from the resutls of a mysql query?

    somethign like the following:

    PHP Code:
    $sql "select distinct(value) from table;"
    $result = (sql);

    $value_list = array($row_sql); 

    or do I have to use

    PHP Code:
    do{

    while(); 

  2. #2
    SitePoint Zealot Dachande663's Avatar
    Join Date
    Feb 2005
    Location
    Birmingham, UK
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    function SQL2array($query) {
        
    $return = array();
        
    $i 0;
        
    $result mysql_query($query);
        while (
    $row mysql_fetch_assoc($result)) {
            foreach (
    $row as $key=$value) {
                
    $return[$i][$key] = $value;
            }
            
    $i++;
        }
        return 
    $return;

    Pass the query to the function and it will return an array.
    Web Developer & Geek: hybridlogic.co.uk ~ lukelanchester.com

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2004
    Location
    Boston
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dachande663 View Post
    PHP Code:
    function SQL2array($query) {
        
    $return = array();
        
    $i 0;
        
    $result mysql_query($query);
        while (
    $row mysql_fetch_assoc($result)) {
            foreach (
    $row as $key=$value) {
                
    $return[$i][$key] = $value;
            }
            
    $i++;
        }
        return 
    $return;

    Pass the query to the function and it will return an array.
    Thanks, I was trying that method as well. I am having trouble getting it to work to build a dynamic chart using jpgraph.

  4. #4
    SitePoint Zealot Dachande663's Avatar
    Join Date
    Feb 2005
    Location
    Birmingham, UK
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you mean by trouble, what happens if you try print_r on the output?
    Web Developer & Geek: hybridlogic.co.uk ~ lukelanchester.com

  5. #5
    SitePoint Evangelist
    Join Date
    Apr 2004
    Location
    Boston
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dachande663 View Post
    What do you mean by trouble, what happens if you try print_r on the output?

    I just get Array displayed on the screen

  6. #6
    SitePoint Zealot Dachande663's Avatar
    Join Date
    Feb 2005
    Location
    Birmingham, UK
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you just echo out the result it will show array as it is one, try something like this:
    PHP Code:
    print_r(SQL2array("yourquery")); 
    Web Developer & Geek: hybridlogic.co.uk ~ lukelanchester.com

  7. #7
    SitePoint Zealot
    Join Date
    Jun 2007
    Location
    Regina, SK, Canada
    Posts
    129
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Rmazin View Post
    Is there a simple way to create an array from the resutls of a mysql query?

    somethign like the following:

    PHP Code:
    $sql "select distinct(value) from table;"
    $result = (sql);

    $value_list = array($row_sql); 
    Try this...

    Code PHP:
    $sql = mysql_query("SELECT DISTINCT(value) FROM table");
    while($data = mysql_fetch_assoc($sql)){
           $stored[] = $data['value'];
    }

    Then to break it out do..

    Code PHP:
    foreach($stored as $val){
      echo $val.'<br>';
    }

  8. #8
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Compared to the other function this is faster. Notice the no foreach?

    PHP Code:
    function SQL2array($query) {
        
    $r = array();

        
    $result mysql_query($query);
        while (
    $row mysql_fetch_assoc($result)) {
            
    $r[] = $row;
        }
        
        return 
    $r;

    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  9. #9
    SitePoint Evangelist
    Join Date
    Apr 2004
    Location
    Boston
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by logic_earth View Post
    Compared to the other function this is faster. Notice the no foreach?

    PHP Code:
    function SQL2array($query) {
        
    $r = array();

        
    $result mysql_query($query);
        while (
    $row mysql_fetch_assoc($result)) {
            
    $r[] = $row;
        }
        
        return 
    $r;

    Yes but this only returns one value. I need it to return all 20 distinct values from the DB column.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i just thought i would add a tangiential comment here...

    DISTINCT is not a function

    please don't write this --
    Code:
    SELECT DISTINCT(value) FROM table
    please write this instead --
    Code:
    SELECT DISTINCT value FROM table
    putting stuff in brackets after DISTINCT will surely mislead someone
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, this is a really dumb question. but how the hell do you create a new thread? i have some questions and im not finding the link anywhere...

  12. #12
    SitePoint Evangelist
    Join Date
    Apr 2004
    Location
    Boston
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i just thought i would add a tangiential comment here...

    DISTINCT is not a function

    please don't write this --
    Code:
    SELECT DISTINCT(value) FROM table
    please write this instead --
    Code:
    SELECT DISTINCT value FROM table
    putting stuff in brackets after DISTINCT will surely mislead someone
    thats my query and i'm sticking to it.. it works that way.

  13. #13
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Rmazin View Post
    Yes but this only returns one value. I need it to return all 20 distinct values from the DB column.
    If the Query returns 20 results there will be 20 items in the arary.

    print_r() the result do not use print.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by immrbaldwin View Post
    ok, this is a really dumb question. but how the hell do you create a new thread? i have some questions and im not finding the link anywhere...
    go to the forum list (i.e. not a thread like this one) and look for the following button on the left, near the top:

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Rmazin View Post
    thats my query and i'm sticking to it.. it works that way.
    yes, it works, but you will run into trouble when you want the query to return distinct combinations of more than one column

    forewarned is forearmed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Evangelist
    Join Date
    Apr 2004
    Location
    Boston
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, it works, but you will run into trouble when you want the query to return distinct combinations of more than one column

    forewarned is forearmed
    I see, funny I know exactly what youre saying, i've run into it before and wasn't sure how to fix it. Thanks.

  17. #17
    SitePoint Evangelist
    Join Date
    Apr 2004
    Location
    Boston
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dachande663 View Post
    PHP Code:
    function SQL2array($query) {
        
    $return = array();
        
    $i 0;
        
    $result mysql_query($query);
        while (
    $row mysql_fetch_assoc($result)) {
            foreach (
    $row as $key=$value) {
                
    $return[$i][$key] = $value;
            }
            
    $i++;
        }
        return 
    $return;

    Pass the query to the function and it will return an array.
    I got this to work but how do i get it into the correct format for calling back just a single variable or array string by name?

    such as
    PHP Code:
    $arr = array('val 1''val2''etc'); 
    it currently displays like this:

    Array ( [0] => Array ( [Standards of Performance] => Standards of Performance ) [1] => Array ( [Individual Accountability] => Individual Accountability ) [2] => Array ( [Place to Invest Your Career] => Place to Invest Your Career ) [3] => Array ( [Growth Potential] => Growth Potential ) [4] => Array ( [Planning and Visibility] => Planning and Visibility ) [5] => Array ( [Associates' Commitment] => Associates' Commitment ) [6] => Array ( [Company Teamwork] => Company Teamwork ) [7] => Array ( [Performance Evaluation] => Performance Evaluation ) [8] => Array ( [Net: Future Outlook] => Net: Future Outlook ) [9] => Array ( [Career Opportunites] => Career Opportunites ) [10] => Array ( [Business Concept] => Business Concept ) [11] => Array ( [Productivity of Our People] => Productivity of Our People ) [12] => Array ( [Structure] => Structure ) [13] => Array ( [Profit Potential] => Profit Potential ) [14] => Array ( [Supervisor's relationship to Subordinate] => Supervisor's relationship to Subordinate ) [15] => Array ( [Economic Strength] => Economic Strength ) [16] => Array ( [Fairness to Associates] => Fairness to Associates ) [17] => Array ( [Leadership Syle] => Leadership Syle ) [18] => Array ( [Strategy - Thought Leadership] => Strategy - Thought Leadership ) [19] => Array ( [Management Talent (in relation to plan)] => Management Talent (in relation to plan) ) )

    -----------------------------------------------------
    UPDATE: Got it working, all I had to do is set the array variable name like this:

    $arr = SQL2array("");
    Last edited by Rmazin; Aug 29, 2007 at 11:47.

  18. #18
    SitePoint Evangelist
    Join Date
    Apr 2004
    Location
    Boston
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now that I got arrats working from a query, I have that is a little more advanced that I need to get working. This one I need alternating query results to populate the array.

    For example I have my query below and then what the array should look like:

    PHP Code:
    $sql "select MIN(score), MAX(score) from table where id ='$id'";
    $results = ($sql);

    $arr= array(MIN(score), MAX(score), MIN(score), MAX(score), etc..); 
    I'm going to play with the code you guys have so graciously provided for the other arrays but any other help would be appreciated.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •