PHP Language - mysql_fetch_assoc($result) Question

Author

mysql_fetch_assoc($result) Question

Chris Grossbe

2006-01-26, 6:57 pm

Hi all

I have this problem:

Ive made a select which selected "id" and "name" from my database and i
get the right result(i used mysql_fetch_assoc).

now i want to get only the name with the id=4 for example.

I tried, thinking of the mysql_fetch_assoc array as a normal array, to
get this like that:

mysql_select_db($database_h, $h);
$query_partner = "SELECT id, name FROM waagenfirma";
$partner = mysql_query($query_partner, $h) or die(mysql_error());
$row_partner = mysql_fetch_assoc($partner);
$totalRows_partner = mysql_num_rows($partner);

echo $row_partner["4"]["name"];

but that only returns the 4th char from the first partner, not the name
of the partner with the id 4.

how can i do this? okay i can change the select to a "where id=4" but i
use that select for something else too(later on, so there is no pointer
prob).

any help or explanation to the mysql_fetch_assoc array appricated

ZeldorBlat

2006-01-26, 6:57 pm

Chris Grossbe wrote:
> Hi all
>
> I have this problem:
>
> Ive made a select which selected "id" and "name" from my database and i
> get the right result(i used mysql_fetch_assoc).
>
> now i want to get only the name with the id=4 for example.
>
> I tried, thinking of the mysql_fetch_assoc array as a normal array, to
> get this like that:
>
> mysql_select_db($database_h, $h);
> $query_partner = "SELECT id, name FROM waagenfirma";
> $partner = mysql_query($query_partner, $h) or die(mysql_error());
> $row_partner = mysql_fetch_assoc($partner);
> $totalRows_partner = mysql_num_rows($partner);
>
> echo $row_partner["4"]["name"];
>
> but that only returns the 4th char from the first partner, not the name
> of the partner with the id 4.
>
> how can i do this? okay i can change the select to a "where id=4" but i
> use that select for something else too(later on, so there is no pointer
> prob).
>
> any help or explanation to the mysql_fetch_assoc array appricated

mysql_fetch_assoc() will grab the "current" row and then advance the
"row pointer" to the next row so that the next call to
mysql_fetch_assoc() will grab the next row. In other words, you only
get one row at a time. While the best way would be to add something to
the where clause of the query, you can alternatively do something like
this:

mysql_select_db($database_h, $h);
$query_partner = "SELECT id, name FROM waagenfirma";
$partner = mysql_query($query_partner, $h) or die(mysql_error());

while(($row = mysql_fetch_assoc($partner)) !== false) {
if($row['id'] == 4) {
echo $row['name'];
break;
}
}

Which basically starts at the beginning of the result set, walking
through each row until it finds one where id = 4. Then it echos and
breaks out of the loop. It's worth noting that you may end up looking
through the whole result this way, though.

Michael Austin

2006-01-26, 6:57 pm

ZeldorBlat wrote:

> Chris Grossbe wrote:
>
>
>
> mysql_fetch_assoc() will grab the "current" row and then advance the
> "row pointer" to the next row so that the next call to
> mysql_fetch_assoc() will grab the next row. In other words, you only
> get one row at a time. While the best way would be to add something to
> the where clause of the query, you can alternatively do something like
> this:
>
> mysql_select_db($database_h, $h);
> $query_partner = "SELECT id, name FROM waagenfirma";
> $partner = mysql_query($query_partner, $h) or die(mysql_error());
>
> while(($row = mysql_fetch_assoc($partner)) !== false) {
> if($row['id'] == 4) {
> echo $row['name'];
> break;
> }
> }
>
> Which basically starts at the beginning of the result set, walking
> through each row until it finds one where id = 4. Then it echos and
> breaks out of the loop. It's worth noting that you may end up looking
> through the whole result this way, though.
>

$id = 4;
$query_partner = "SELECT id, name FROM waagenfirma where id=".$id;

if the id field is a char/varchar then it needs to look like:

$id = "4";
$query_partner = "SELECT id, name FROM waagenfirma where id='".$id"'";

Use the database to select the row - don't return all rows and parse
through them. What happens when you have 100K names/id's... the
returned data will be significant.

Learn SQL and it will serve you well.

Michael Austin.

Chris Grossbe

2006-01-27, 7:56 am

Thx for the answers and the explanation. Seems like creating the select
with the where is the only thing that makes sense.

I thought at it as a normal array, so i figured that a new select
wouldnt make lots of sense.

thx and greez

Sponsored Links