SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How should I get an array of mysql results?

    I can't figure out how to do this!

    Basically, I want an array of all the entries in the database from a sql statement. Not just the first match, which is what I'm getting. I could get this by walking through mysql_fetch_row adding values but I'm sure there is a single function route.

    Here's the code:
    PHP Code:
        $sql "select super_ID from subcategory where sub_ID='$main_category'";
        echo(
    $sql);
        
    $result mysql_query($sql);
        
    $super_ID_row mysql_fetch_row($result);
        
    //the results
        
    echo('<p><pre>');
        
    var_dump($super_ID);
        echo(
    '</pre></p>'); 
    Here's the on-screen results:
    Code:
    select super_ID from subcategory where sub_ID='8'
    
    
    array(1) {
      [0]=>
      string(1) "9"
    }
    Here's the result of running the same sql on the mysql command line:
    Code:
    mysql> select super_ID from subcategory where sub_ID='8';
    +----------+
    | super_ID |
    +----------+
    |        9 |
    |        8 |
    |        8 |
    |        9 |
    +----------+
    In the situation posted at top, I want to end up with this array:
    PHP Code:
    $super_ID_row[0] = 9;
    $super_ID_row[1] = 8;
    $super_ID_row[2] = 8;
    $super_ID_row[3] = 9
    Basically this:
    Code:
    array(4) {
      [0]=>
      int(9)
      [1]=>
      int(8)
      [2]=>
      int(8)
      [3]=>
      int(9)
    }
    Surely there is a way to simply get an array directly from the database without stepping through the results.

    So that you are aware of my motives, later in the script I will be using in_array($super_ID_row) to determine if actions need to be taken on certain items.

    Thanks for reading, have a wonderful day!
    Sam
    Last edited by samsm; May 28, 2002 at 19:55.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  2. #2
    SitePoint Guru dragonhawk's Avatar
    Join Date
    Apr 2002
    Location
    Melbourne
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not really sure if this is what you're after, but i would do things like so:

    PHP Code:
    $sql="select super_ID from subcategory where sub_ID='$main_category'";
    $results=mysql_query($sql);

    while (
    $row=mysql_fetch_array($results)) {
      echo 
    "<p><pre>".$row[super_ID]."</pre></p>";


  3. #3
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I edited my post a little to clarify it...hopefully it makes sense now .

    Thanks, dragonhawk... that how I usually treat mysql results too.... all of a sudden I want to do something differently and I've realized I have little idea how to do it. I want to get all the data into one array without a while loop (or any other sort of loop).
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  4. #4
    SitePoint Guru dragonhawk's Avatar
    Join Date
    Apr 2002
    Location
    Melbourne
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    by using

    $row=mysql_fetch_array($results);

    you are getting

    $row[0]==9;
    $row[1]==8;
    $row[2]==8;
    $row[3]==9;

    $row is an array of the results.

    The while loop just makes it easier to display the values.

    Try it, after you run the code and place it into the $row, echo $row[0] up to echo $row[4] and you will see the values in each of them.

  5. #5
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Atlanta, GA
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    function getQueryResults($query)
    {
        
    $ret = array();
        
    $sql $query;
        
    $results mysql_query($sql) or die(mysql_error());
        if(!
    $results)
            {
                return 
    $ret;
            }
        while(
    $row mysql_fetch_array($results))
        {
            
    $ret[] = $row;
        }
        return 
    $ret;
     }

    $sql "SELECT * FROM table";
    $ar getQueryResults$sql ); 


  6. #6
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think phppete's right there... dragonhawk, i think you're a little confused

    by using

    $row=mysql_fetch_array($results);

    you are getting not
    $row[0]==9;
    $row[1]==8;
    $row[2]==8;
    $row[3]==9;

    instead, each element of $row would be the field in that particular row... think about it or read the mysql_fetch_array() function reference

  7. #7
    SitePoint Guru dragonhawk's Avatar
    Join Date
    Apr 2002
    Location
    Melbourne
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, you're right, i was a little confused...

    According to the functional reference:

    PHP Code:
    <?php
    mysql_connect
    ($host$user$password);
    mysql_select_db("database");
    $result mysql_query("select user_id, fullname from table");
    while (
    $row mysql_fetch_array($result)) {
        echo 
    "user_id: ".$row["user_id"]."<br>\n";
        echo 
    "user_id: ".$row[0]."<br>\n";
        echo 
    "fullname: ".$row["fullname"]."<br>\n";
        echo 
    "fullname: ".$row[1]."<br>\n";
    }
    mysql_free_result($result);
    ?>
    my mistake... heh... at least i learnt something

  8. #8
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, something like phpPete's function was my backup plan.... his function is a little more polished than what I was going to make so I am glad I asked ( thanks ).

    So is there isn't a built-in function that can pull the results into an array in one step? Wow, I'm sort of disillusioned, I would think that would be a common need. Anyway, that function should work fine so I really have no complaints.

    I'll admit that I initially assumed that the $row in $row=mysql_fetch_array($results);
    yeilded a full array of all the results.... I also assumed that handling the results indivisually was a special behavior of 'while'... not the case on both counts! I was editting the heck out of the completely wrong part of my script because of that misunderstanding.

    Anyway, as ever, thanks to all of you... and special thanks to phpPete for the function.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  9. #9
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Atlanta, GA
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Glad to help.

    and while the exact function you were looking for isn't built in as you hoped, the basic building blocks are there, which is all anyone can ask for.

    I do love arrays!!


  10. #10
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah all this array stuff messes your head... but PHP's multi-dimensional arrays (vanilla and associative) are so much easier to understand and very forgiving... i know cos i'm using a hash of arrays of arrays as a data structure in a Perl program i'm working on (right now! or at least as soon as i finish typing this )

    and actually it isn't that difficult if you want to place all the rows into an array

    borrowing from phppete's work:
    PHP Code:
    $results=array();  // this will store the entire resultset; an array of arrays (or hashes)

    while($row=mysql_fetch_array)
    {
      
    $results[]=$row;

    should do the trick...


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
  •