mysql_fetch_assoc initiating a new query?

[sorenchr]

Hi, since i don't know how to log mysql queries (which would help greatly), im gonna throw this question out to you guys.

 

Suppose i have a mysql query and an output:

 

$query = mysql_query("SELECT * FROM users WHERE username = '$username'");
while($row = mysql_fetch_assoc($query)) {

echo "Your name is: ";
echo $row['name'];
echo ", unlucky you.";

}

 

Will the mysql_fetch_assoc($query) initiate a new mysql query for everytime $row gets used, or will it reuse the one stored in $query? Im asking this because im concerned about the uneccesary use of database communication, if it initiates a new query every time.

 

[kenrbnsn]

No, the query gets done once and produces a pointer to the results. The mysql_fetch function returns each record of the results pointed to by the pointer.  BTW, if you're only going to use one field in the table, it's more efficient to just get that field:

 

<?php
$q = "SELECT name FROM users WHERE username = '$username'";
$rs = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
while ($row = mysql_fetch_assoc($rs)) 
    echo "Your name is {$row['name']}, unlucky you.";
?>

 

Also, since it's unlikely that there is more than one record in the database with the same username, you don't need the while statement, but you should check that a record was returned before fetching the results:

<?php
$q = "SELECT name FROM users WHERE username = '$username'";
$rs = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
if (mysql_num_rows($rs) > 0) {
   $row = mysql_fetch_assoc($rs);
   echo "Your name is {$row['name']}, unlucky you.";
}
?>

 

Ken

[inaba]

hi sorenchr

As far i know it will reuse the one stored in query.

[waynew]

It's working off your queries result. Username should be a unique field in the database.

 

username varchar(30) unique

[sorenchr]

Thanks kenrbnsn, perhaps i made a poor example script, I have multiple rows and multiple records i need to output.

[waynew]

Ex: Multiple rows

 

$result = mysql_query("Select * from contacts where contact_country = 'Ireland'") or die(mysql_error);

while($row = mysql_fetch_array($result)){

echo "Name: ".$row['name'];
echo "Phone: ".$row['phone'];
echo "Email: ".$row['email'];

}

 

Imaginary table of course.