SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am doing a sql query that gets back one row of results. In this scenario i do not know what the column names are but lets say they are in this case:

    Email Website Name
    and the results
    chris@domain.com http://www.ascifi.com/ Chris Padfield

    what i want to form is a named array.

    I am using phplib to get the results.

    I assume it will be something like

    $array[$db-f(???)] = $db->f(???);

    but i am not sure what, in fact am lost as to what to do here.

    thanks everyone.

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Chris here is what I came up with, I am not sure if it is the best way, but its the first way I could think of:

    PHP Code:
    <?

    include("db.php");
    $db = new DB;
    $tablename "test";

    $tabledata $db->metadata($tablename);

    foreach(
    $tabledata as $key => $val) {
        
    $fields[$tabledata[$key]["name"]] = "";
        }

    $db->query("select * from $tablename where something = '$something'");
    while(
    $db->next_record()) {
        foreach(
    $fields as $key => $val) {    
            
    $data[$key] = $db->f($key);
            }
        }

    foreach(
    $data as $key => $val) {
        print 
    "$key = $val<br>";
        }
        
    ?>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    got the email notification and thought.. yipee. freddy

    and guess what, works perfectly! and i understand it as well.

    thanks again!

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think there is probably a way easier method for achieving this but this one works for now, I will post back if I come up with something a little more clever.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Chris, I did some testing and I think I have a much better way to do this. First put this function into your db_mysql.inc
    You can put it anywhere in ther eI stuck it right under next_record()

    PHP Code:
      /* public: get arrays for each record */
      
    function row_array() {
        if (!
    $this->Query_ID) {
          
    $this->halt("next_record called with no query pending.");
          return 
    0;
        }

        
    $this->Record = @mysql_fetch_assoc($this->Query_ID);
        
    $this->Row   += 1;
        
    $this->Errno  mysql_errno();
        
    $this->Error  mysql_error();

        
    $stat is_array($this->Record);
        if (!
    $stat && $this->Auto_Free) {
          
    $this->free();
          return 
    false;
        }
        else {
          return 
    $this->Record;
        } 
      } 

    Sample Usage:

    PHP Code:
    $db->query("select * from tablename where something = '$something'");
    $data $db->row_array();
    foreach(
    $data as $key => $val) {
        print 
    "$key = $val<br>";
        } 
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that is cool.

    can you explain what is goingon there, i am a bit lost as to how it works, will it simply return the name name indexed array (ie only 1 row) but now done as a function?

    all these extra functions should get sent to phplib although i hear they might drop db_mysql and "merge" it with that other archive... pear i think?

  7. #7
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    p.s. it worked perfectly. So much cleaner

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh well, I will just keep using my hacked up version of their original script. Here is an explanantion, I originally tried to do it by just returning $db->Record because that holds the current data as fetched using mysql_fetch_array() except that returns two array elements for each field one being the key using the field name and the other being the array indice or number so let's say you have three fields in your table
    name
    email
    phone

    and lets say the three values for those fields are

    Freddy
    freddy@mail.com
    8005551212

    doing
    $r = mysql_query(blah blah);
    $row = mysql_fetch_array($result);


    $row now contains

    $row[0] = "Freddy";
    $row[name] = "Freddy";
    $row[1] = "freddy@mail.com";
    $row[email] = "freddy@mail.com";
    $row[2] = "8005551212";
    $row[phone] = "8005551212";


    while


    $r = mysql_query(blah blah);
    $row = mysql_fetch_assoc($result);


    only contains
    $row[name] = "Freddy";
    $row[email] = "freddy@mail.com";
    $row[phone] = "8005551212";

    Which is what we want.



    So I made a copy of the next_record() function and changed it to be named row_array(). Now next_record doesn't return any data it just moves the pointer to the next record and then fetches the array using mysql_fetch_array(),
    PHP Code:
        $this->Record = @mysql_fetch_array($this->Query_ID); 
    then it checks for $this->Record being an array

    PHP Code:
        $stat is_array($this->Record);
        if (!
    $stat && $this->Auto_Free) {
          
    $this->free();
        }
        return 
    $stat
    If it isn't an array it stops and if it is it knows it can move to the next record and now $this->Record holds the array from mysql_fetch_array() so now when you call $db->f(fieldname); all it does is return $this->Record(fieldname);


    So now on to how my function works, it does basically the same thing but instead of just setting the array to $this->Record it actually returns the array to your script. And again it only returns the fieldname keys in the array since I used mysql_fetch_assoc() in my function.

    Does that make sense, normally I have a hard time explaining exactly what it is that I did.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yup, i see what is going on there, is cool

  10. #10
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And I just read that I could have just modified the next_record() funtion to make the mysql_fetch_array() only return the fieldname keys and not both, but its probably just as well to keep the two function separate
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  11. #11
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Chris I just couldn't resist. So here is a slightly modified version of the next_record() function in phplib. So you can scrap my last example if you so desire and use this:

    PHP Code:
      /* public: walk result set */
      
    function next_record($r_type "") {
        if (!
    $this->Query_ID) {
          
    $this->halt("next_record called with no query pending.");
          return 
    0;
        }
        
    $this->Record = ($r_type != "") ? @mysql_fetch_array($this->Query_ID$r_type) : @mysql_fetch_array($this->Query_ID);
        
    $this->Row   += 1;
        
    $this->Errno  mysql_errno();
        
    $this->Error  mysql_error();

        
    $stat is_array($this->Record);
        if (!
    $stat && $this->Auto_Free) {
          
    $this->free();
        }
        return 
    $stat;
      } 

    Sample

    PHP Code:
    include("db.php");
    $db = new DB;

    $db->query("select * from ip LIMIT 1");
    $db->next_record(MYSQL_ASSOC);
    $data $db->Record;
    foreach(
    $data as $key => $val) {
        print 
    "$key = $val<br>";
        } 
    See how I pass MYSQL_ASSOC without quotes to the next_record function, and then in the function if the argument passed isn't blank use it has the result type to return in this case an associatve array instead of a numbered array.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  12. #12
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehe... will try it


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
  •