SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Hybrid View

  1. #1
    SitePoint Wizard xyuri's Avatar
    Join Date
    Jul 2002
    Location
    Brisbane
    Posts
    1,487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    a couple of questions (fairly simple ones)

    could someone please explain / show how to retrieve query results in an array and then how to loop through them all and display all the fields ?

    and which method is better ? arrays or mysql_result() ?

    EDIT: (for displaying more than one row of data that is)

  2. #2
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    $sql 
    mysql_query("SELECT * FROM crap"); // say there are 8 rows in here
    while($row mysql_fetch_array($sql)
    {
       echo 
    $row['crap'];
    }
    ?>
    Produces:


    crap
    crap
    crap
    crap
    crap
    crap
    crap
    crap

    while:

    PHP Code:
    <?php
    $sql 
    mysql_query("SELECT * FROM crap"); // say there are 8 rows in here
    $crap mysql_result($sql1);
       echo 
    $crap;

    ?>

    produces:

    crap


    Since it isn't looped... under mysql_result() in the php manual:


    Recommended high-performance alternatives: mysql_fetch_row(), mysql_fetch_array(), and mysql_fetch_object().

  3. #3
    SitePoint Zealot Nova's Avatar
    Join Date
    Sep 2002
    Location
    Netherlands
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: a couple of questions (fairly simple ones)

    Originally posted by xyuri
    could someone please explain / show how to retrieve query results in an array and then how to loop through them all and display all the fields ?

    and which method is better ? arrays or mysql_result() ?

    EDIT: (for displaying more than one row of data that is)
    i preffer *_assoc
    next examples can also be *_array
    PHP Code:
    <?php
    $query 
    mysql_query(your query);

    while(
    $row mysql_fetch_assoc($query)) {
      echo 
    $row['fieldname'];
    }
    ?>
    or
    PHP Code:
    <?php
    $query 
    mysql_query(your query);
    $total mysql_num_rows($query);

    for(
    $i 0$i $total$i++){
      
    $row mysql_fetch_assoc($query)) 
      echo 
    $row['fieldname'];
    }
    ?>
    edit* someonewhois was first
    if you can't beat them call them cheaters

    www.phppatterns.nl

  4. #4
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Re: a couple of questions (fairly simple ones)

    Originally posted by Nova

    edit* someonewhois was first

    Sorry!

  5. #5
    SitePoint Enthusiast plattopus's Avatar
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is how I do it (and it's generally what I see around the place):

    PHP Code:
    $someSql mysql_query("SELECT * FROM tbl_name WHERE condition = 'expression'");

    while (
    $var mysql_fetch_array($someSql)) {
      echo 
    $var['field'];

    You can also use mysql_fetch_object, which works much the same way:

    PHP Code:
    $someSql mysql_query("SELECT * FROM tbl_name WHERE condition = 'expression'");

    while (
    $var mysql_fetch_object($someSql)) {
      echo 
    $var->field;


  6. #6
    SitePoint Wizard xyuri's Avatar
    Join Date
    Jul 2002
    Location
    Brisbane
    Posts
    1,487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whats the difference between mysql_fetch_object() and mysql_fetch_array() ? cos I notice ths object method uses like 'class' notation

    well, i was just wondering about arrays cos at the moment i use something like this:

    PHP Code:
    $data mysql_query("SELECT name, pwd FROM users");

    $x = (int)0;
    WHILE (
    $x mysql_num_rows($data))
      {
      ECHO 
    mysql_result($data$x"name");
      ECHO 
    mysql_result($data$x"pwd");
      
    $x++;
      } unset(
    $x//cos i use it a lot in my scripts 
    this is bad practice, right ?

  7. #7
    SitePoint Enthusiast plattopus's Avatar
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First, you should really give your variables unique names...

    This is how your code could be improved:

    PHP Code:
    $userSql mysql_query("SELECT name, pwd FROM users"); 

    while(
    $user mysql_fetch_array($userSql)) {
      echo 
    'name is ' $user['name'] . ' and pwd is ' $user['pwd'] . '<br />';

    The while() loop will keep going until there's no more database rows left (much the same as your very long workaround).

  8. #8
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by xyuri
    well, i was just wondering about arrays cos at the moment i use something like this:

    PHP Code:
    $data mysql_query("SELECT name, pwd FROM users");

    $x = (int)0;
    WHILE (
    $x mysql_num_rows($data))
      {
      ECHO 
    mysql_result($data$x"name");
      ECHO 
    mysql_result($data$x"pwd");
      
    $x++;
      } unset(
    $x//cos i use it a lot in my scripts 
    this is bad practice, right ?
    Yes, it's bad practice.

  9. #9
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by xyuri
    Whats the difference between mysql_fetch_object() and mysql_fetch_array() ?

    mysql_fetch_array, puts it into an array.
    It's supposed to be used like this:
    $row['0'] (ie ID)
    $row['1'] (ie Userid)
    $row['2'] (ie Password)
    $row['3'] (ie Fullname)
    I however use it like this:
    $row['id']
    $row['userid']
    $row['password']
    $row['fullname']

    They have written a function, mysql_fetch_assoc(), to do exactly how I (and a lot of people) use fetch_array.

    Mysql_fetch_object(), just gives you a class instead of an array:

    $row->id
    $row->userid
    $row->password
    $row->fullname



    You understand?

    Regards,
    Someonewhois

  10. #10
    SitePoint Wizard xyuri's Avatar
    Join Date
    Jul 2002
    Location
    Brisbane
    Posts
    1,487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by someonewhois
    You understand?
    yes thank you.

    another: can this method be used to fetch rows only, say, between 10 and 15 ? (for displaying a certain portion of the query result) ?

  11. #11
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by xyuri

    yes thank you.

    another: can this method be used to fetch rows only, say, between 10 and 15 ? (for displaying a certain portion of the query result) ?
    Yes:

    PHP Code:

    $sql 
    "blah blah" ;
    $result mysql_query$sql )

    $startrow 10 ;
    $endrow 15 ;

    mysql_data_seek$result$startrow ) ;

    for( 
    $i $i <= ( $endrow $startrow ) ; $i++ )
    {
        
    $row mysql_fetch_array$result ) ;

        echo( 
    $row['foo'] ) ;
        echo( 
    $row['bar'] ) ;

    You should do checking that $endrow isn't bigger than mysql_num_rows( $result )

    Matt.

  12. #12
    SitePoint Enthusiast plattopus's Avatar
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It would be far easier to use MySQL's LIMIT here:

    PHP Code:
    $sql mysql_query("SELECT * FROM tbl_name ORDER BY id DESC LIMIT 10, 5"); 

  13. #13
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by plattopus
    It would be far easier to use MySQL's LIMIT here:
    Well, yes and no.

    If he's not doing anything else with the result other than displaying those specifies rows, then yes. But LIMIT isn't ANSI SQL, and you wouldn't be able to port it to other databases. And if you did want to do something else with other parts of the result set, you wouldn't have to do another db query.

    There are pros and cons for each way.

  14. #14
    SitePoint Wizard xyuri's Avatar
    Join Date
    Jul 2002
    Location
    Brisbane
    Posts
    1,487
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you very much people helped me a lot.


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
  •