CODEWITHSUNDEEP
phpmysqlsql
i-CONICA
i-CONICA

Reputation: 2379

Strange results using PHP while loop running over mysql_fetch_array()

I've got a set of database results where some of the columns have empty fields. I'm running this query:

SELECT * FROM `data` WHERE category = '$category' ORDER BY name

The PHP that's outputting the results is this (much simplified version):

while ($qValues = mysql_fetch_array($result)) {
    if ($qValues["licence"] != "") {
        $lice = 'Licence: ' . $qValues["licence"];
    }
    if ($qValues["publisher"] != "") {
       $pub = 'Publisher: ' . $qValues["publisher"];
    }
}

What's happening is, if the previous row had a publisher, but the current row has no publisher (not null, empty string) it is outputting the previous row's data.

I've no idea how this is happening because in my mind, mysql_fetch_array has the data in a grid, and the while loop advances it's internal counter one row after another. I don't understand how one row's data could bleed over into the next row if that row contained an empty string.

What's happening here?

Upvotes: 1

Views: 220

Answers (4)

Marcus Adams
Marcus Adams

Reputation: 53830

Since you're only setting the variables if the columns have values, the variables don't get reset when the columns are blank. This causes the issue where you're seeing the values from the previous record.

You should set the variables every time, even if the column is blank. You can use else statements:

while ($qValues = mysql_fetch_array($result)) {
if ($qValues["licence"] != "") {
    $lice = 'Licence: ' . $qValues["licence"];
} else {
    // Set to blank so as not to keep value from last iteration
    $lice = "";
}
if ($qValues["publisher"] != "") {
    $pub = 'Publisher: ' . $qValues["publisher"];
} else {
    // Set to blank so as not to keep value from last iteration
    $pub = "";
}

Upvotes: 2

DRiFTy
DRiFTy

Reputation: 11369

Reset $pub to $pub = "" each time through the loop...

while ($qValues = mysql_fetch_array($result)) {
    $lice = "";
    $pub = "";

    if ($qValues["licence"] != "") {
        $lice = 'Licence: ' . $qValues["licence"];
    }
    if ($qValues["publisher"] != "") {
        $pub = 'Publisher: ' . $qValues["publisher"];
    }
}

Upvotes: 2

clem
clem

Reputation: 3366

you are assigning the value of publisher and licence to a php variable. if the mysql result contains an empty string it will still keep the content of the previous row unset the 2 variables each time you loop through the result:

while ($qValues = mysql_fetch_array($result)) {
$lice = $pub = "";
if ($qValues["licence"] != "") {
    $lice = 'Licence: ' . $qValues["licence"];
}
if ($qValues["publisher"] != "") {
   $pub = 'Publisher: ' . $qValues["publisher"];
}
}

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838226

You aren't resetting the variables in each iteration, so you see the old values. To fix it you could do for example do this:

if ($qValues["licence"] != "") {
    $lice = 'Licence: ' . $qValues["licence"];
} else {
    $lice = '';
}

Upvotes: 1

Related Questions