SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to get MySQL query return correct value in PHP?

    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.

    PHP Code:
    $box=$_POST['box'];

    if (
    $box != 0) {

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

    }

    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

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2011
    Posts
    70
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    "SELECT {$box1} FROM paint WHERE color='{$box2}'"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2011
    Posts
    70
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    $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:
    Code:
    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.

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    What defines whether a paint requires extra masking? I dont understand your table.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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".

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by JeremyC View Post
    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.

    PHP Code:
    $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:
    Code:
    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.

  8. #8
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    $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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    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.

    PHP Code:
    $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.

  10. #10
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  11. #11
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    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:
    PHP Code:
    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!

  12. #12
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dima View Post
    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

  13. #13
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dima View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •