How to get MySQL query return correct value in PHP?

[Dima]

I am putting checkbox values into an array. For certain selections, DB has value of 1, but query below is returning 0. What am I doing wrong? TY! also attached DB schema image.

$box=$_POST['box'];

if ($box != 0) {

    while (list ($key,$val) = each ($box)) {
        $val1 = "$val, ";
        echo "$val1";
        $a = $a + 1 ;
    }

}

for ($j = 0; $j <= $a-1; $j++) {

    for ($i = 0; $i <= $a-1; $i++) {
        $box1 = $box["$i"] ;
        $box2 = $box["$j"] ;

        $queryziptrack2 = mysql_query("SELECT $box1 FROM paint WHERE color=$box2") or die ('Error: ' . mysql_error ());

        $queryziptrack1 = mysql_fetch_array($queryziptrack2);
        
        if (mysql_num_rows($queryziptrack2) == 0) {
            echo "No rows found, nothing to print so am exiting";
            exit;
        }

        if ($queryziptrack1["$box1"] == 1) {
            $masking = "<font color=red>Extra masking is required.</font>" ;
            echo "<font color=red>Extra masking is required.</font>" ;
            exit;
        } else {
            $masking1 = "<font color=red>Extra masking is not required.</font>" ;
        }

    }
}


if (!$masking) {
    echo $masking1 . "<br>" ;
} else {
    echo $masking . "<br>" ;
}

echo "<br>Select colors and press 'tell me' button to see if extra masking is needed.<br><br> " ;
echo "<form method=post action=''>";
echo "<table border='0' cellspacing='0' style='border-collapse: collapse' width='200' >

<tr bgcolor='#ffffff'>
<td width='25%'><input type=checkbox name=box[] value='black' $t1v/></td>
<td width='25%'>&nbsp;Black</td>
<td width='25%'><input type=checkbox name=box[] value='white' $t2v/></td>
<td width='25%'>&nbsp;White</td>
<td width='25%'><input type=checkbox name=box[] value='red' /></td>
<td width='25%'>&nbsp;Red</td>
<td width='25%'><input type=checkbox name=box[] value='blue' /></td>
<td width='25%'>&nbsp;Blue</td>
<td width='25%'><input type=checkbox name=box[] value='yellow' /></td>
<td width='25%'>&nbsp;Yellow</td>
</tr>
<tr bgcolor='#f1f1f1'>
<td width='25%'><input type=checkbox name=box[] value='green' /></td>
<td width='25%'>&nbsp;Green</td>
<td width='25%'><input type=checkbox name=box[] value='orange' /></td>
<td width='25%'>&nbsp;Orange</td>
<td width='25%'><input type=checkbox name=box[] value='silver' /></td>
<td width='25%'>&nbsp;Silver</td>
<td width='25%'><input type=checkbox name=box[] value='ivory' /></td>
<td width='25%'>&nbsp;Ivory</td>
<td width='25%'><input type=checkbox name=box[] value='magenta' /></td>
<td width='25%'>&nbsp;Magenta</td>
</tr>
<tr bgcolor='#ffffff'>
<td width='25%'><input type=checkbox name=box[] value='burgundy' /></td>
<td width='25%'>&nbsp;Burgundy</td>
<td width='25%'><input type=checkbox name=box[] value='plum' /></td>
<td width='25%'>&nbsp;Plum</td>
<td width='25%'><input type=checkbox name=box[] value='brown' /></td>
<td width='25%'>&nbsp;Brown</td>
<td width='25%'><input type=checkbox name=box[] value='pink' /></td>
<td width='25%'>&nbsp;Pink</td>
<td width='25%'><input type=checkbox name=box[] value='purple' /></td>
<td width='25%'>&nbsp;Purple</td>
</tr>
<tr><td colspan =6>&nbsp;</td></tr>
<tr><td colspan =6><input type=submit value='Tell Me'></form></td></tr>
</table>";
?> 

db.png

[JeremyC]

Would it be possible to upload the photo to a service like photobucket or similar so that it will be available quicker?

Also one other thing in the query. The condition should be in single quotes and any variables inside double quotes should be wrapped in curly braces.

"SELECT {$box1} FROM paint WHERE color='{$box2}'"; 

[Dima]

http://i1154.photobucket.com/albums/p537/dsaidov/db.png

[JeremyC]

I have been playing with the script and I am having a hard time figuring out what it is you are trying to accomplish. I set up a small sample db based on the picture that you posted and I can't replicate your problem. It seems to be pulling the values from the database just fine.

What it looks like is that you are looking for any instance where a returned value is equal to 1 and then breaking out of the loop correct?

I modified it just slightly to display the query and the returned values and it seems to be working fine.

$query = "SELECT $box1 FROM paint WHERE color=$box2";
        $queryziptrack2 = mysql_query($query) or die ('Error: ' . mysql_error ()); 

        $queryziptrack1 = mysql_fetch_array($queryziptrack2); 
        echo $query; 

displays the output:

black, white, red, 

SELECT black FROM paint WHERE color=black
array
  0 => string '0' (length=1)
  'black' => string '0' (length=1)
SELECT white FROM paint WHERE color=black
array
  0 => string '0' (length=1)
  'white' => string '0' (length=1)
SELECT red FROM paint WHERE color=black
array
  0 => string '1' (length=1)
  'red' => string '1' (length=1)
Extra masking is required.

[StarLion]

What defines whether a paint requires extra masking? I dont understand your table.

[Dima]

StarLion:

Basically, whatever colors you choose, it has to look each pair up in the DB, if it is 1, it has to require extra masking. As soon as it finds the first pair of colors requiring extra masking, it has to quit and say "required". If it does not find the pair returning "1", it has to go through the whole loop, and return "not required".

[Dima]

I have been playing with the script and I am having a hard time figuring out what it is you are trying to accomplish. I set up a small sample db based on the picture that you posted and I can't replicate your problem. It seems to be pulling the values from the database just fine.

What it looks like is that you are looking for any instance where a returned value is equal to 1 and then breaking out of the loop correct?

I modified it just slightly to display the query and the returned values and it seems to be working fine.

$query = "SELECT $box1 FROM paint WHERE color=$box2";
        $queryziptrack2 = mysql_query($query) or die ('Error: ' . mysql_error ()); 

        $queryziptrack1 = mysql_fetch_array($queryziptrack2); 
        echo $query; 

displays the output:

black, white, red, 

SELECT black FROM paint WHERE color=black
array
  0 => string '0' (length=1)
  'black' => string '0' (length=1)
SELECT white FROM paint WHERE color=black
array
  0 => string '0' (length=1)
  'white' => string '0' (length=1)
SELECT red FROM paint WHERE color=black
array
  0 => string '1' (length=1)
  'red' => string '1' (length=1)
Extra masking is required.

correct. I am reviewing what you posted. thanks. I will get back to you shortly.

[StarLion]

Well, we can simplify this code a bit by using a better query. I'm more inclined to say that a redesign of the table would be better, but will work with what we've got.

$query = 'SELECT SUM(total) FROM (SELECT SUM('.implode(',',$_POST['box']).') AS total FROM paint WHERE color IN("'.implode('","', $_POST['box']).'"))'; 

(untested and unsanitized)

if the single value returned is greater than 0, you have your answer.

[Dima]

Well, we can simplify this code a bit by using a better query. I'm more inclined to say that a redesign of the table would be better, but will work with what we've got.

$query = 'SELECT SUM(total) FROM (SELECT SUM('.implode(',',$_POST['box']).') AS total FROM paint WHERE color IN("'.implode('","', $_POST['box']).'"))'; 

(untested and unsanitized)

if the single value returned is greater than 0, you have your answer.

This project was done 3 years ago with old PHP and MySQL. I have not touched PHP/MySQL since then. Client said the script has stopped working after they updated PHP from 4.7 to 5.3.

When I added your $query, it produced an error: Warning: implode() [function.implode]: Invalid arguments passed in ...

I welcome your ideas on redesign of the table and script also. Thanks a lot.

[StarLion]

Simplest answer is to redesign the table to hold this:

color1 VARCHAR(30)
color2 VARCHAR(30)

(this also makes it easier to add colors, because you're not pre-defining colors with fields.)

And then add rows for each pair that requires masking.
Then make your script:

if(isset($_POST['box'][0])) { //If there is at least one box checked...
$string =  '"'.implode('","',$_POST['box']).'"'; //Implode it into a string.
$res = mysql_query("SELECT color1 FROM paint WHERE color1 IN(".$string.") AND color2 IN(".$string.")");
if(mysql_num_rows($res)) {
  //Masking to be done
} else {
  //No masking to be done.
}
} 

As far as the error you're seeing, most likely it means $_POST['box'] wasnt set.

[Dima]

Simplest answer is to redesign the table to hold this:

color1 VARCHAR(30)
color2 VARCHAR(30)

(this also makes it easier to add colors, because you're not pre-defining colors with fields.)

And then add rows for each pair that requires masking.
Then make your script:

if(isset($_POST['box'][0])) { //If there is at least one box checked...
$string =  '"'.implode('","',$_POST['box']).'"'; //Implode it into a string.
$res = mysql_query("SELECT color1 FROM paint WHERE color1 IN(".$string.") AND color2 IN(".$string.")");
if(mysql_num_rows($res)) {
  //Masking to be done
} else {
  //No masking to be done.
}
} 

As far as the error you're seeing, most likely it means $_POST['box'] wasnt set.

WORKED! Thanks a lot! Modified paint table per your instructions. And script is so much shorter. Brilliant!

[Dima]

WORKED! Thanks a lot! Modified paint table per your instructions. And script is so much shorter. Brilliant!

Spoke too soon. When I said above, I have created only 1 row in the "new" paint table (color1: blue, color2: red). But when I tried to create 2nd pair (color1: blue, color2: yellow), MySQL says: #1062 - Duplicate entry 'blue' for key 1

[Dima]

Spoke too soon. When I said above, I have created only 1 row in the "new" paint table (color1: blue, color2: red). But when I tried to create 2nd pair (color1: blue, color2: yellow), MySQL says: #1062 - Duplicate entry 'blue' for key 1

Never mind. color1 was primary key. removed. fixed.