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.
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.
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”.
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.
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.
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
