current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
2  
@i_Conica What if I want to see all data in the "Food N' Things" category? –  Mike B Nov 16 '11 at 18:11
    
Mike B, I don't understand your question? My problem is that the new iteration of the loop is only overwriting the previous loop's data if it contains a new string, if it contains an empty string, it's still storing the previous iteration (database row)'s data. –  i-CONICA Nov 16 '11 at 18:14
    
@i-CONICA, you'd want to escape the category if it has a single quote. –  Marcus Adams Nov 16 '11 at 18:24
    
Hi, Marcus. I know the reason for escaping variables, I just don't know why Polynomial presumed I must be getting $category from the user side, and that I mustn't be escaping it, none of that information is present in my question or code. I appreciate his offering of advice, though. –  i-CONICA Nov 16 '11 at 18:26
    
@i-CONICA The source of the params used in queries has no bearing over whether or not they should be escaped. It's not a security issue. –  Mike B Nov 16 '11 at 20:09

4 Answers 4

up vote 2 down vote accepted

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 = "";
}
share|improve this answer
up vote 2 down vote

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"];
    }
}
share|improve this answer
1  
I think it's a better solution then if/else. Initializing variables is a good practice that should be followed. –  Second Rikudo Nov 16 '11 at 18:13
1  
@Truth, you've convinced me. Easier to read too. –  Marcus Adams Nov 16 '11 at 18:27
up vote 1 down vote

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 = '';
}
share|improve this answer
up vote 1 down vote

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"];
}
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.