a couple of questions (fairly simple ones)

[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)

[someonewhois]

<?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
$sql = mysql_query("SELECT * FROM crap"); // say there are 8 rows in here
$crap = mysql_result($sql, 1);
   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().

[Nova]

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
$query = mysql_query(your query);

while($row = mysql_fetch_assoc($query)) {
  echo $row['fieldname'];
}
?>

or

<?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

[someonewhois]

Originally posted by Nova

edit* someonewhois was first

Sorry!

[plattopus]

This is how I do it (and it's generally what I see around the place):

$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:

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

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

[xyuri]

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:

$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 ?

[plattopus]

First, you should really give your variables unique names...

This is how your code could be improved:

$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).

[Mincer]

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

$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.

[someonewhois]

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

[xyuri]

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) ?

[Mincer]

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:

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

$startrow = 10 ;
$endrow = 15 ;

mysql_data_seek( $result, $startrow - 1 ) ;

for( $i = 1 ; $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.

[plattopus]

It would be far easier to use MySQL's LIMIT here:

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

[Mincer]

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.

[xyuri]

thank you very much people helped me a lot.