[Resolved] Question on DB Select

[Ivains]

Hi Everyone,

Have a question and maybe someone can point me in the right direction.

I have 10 check boxes, each one is set as Enum('yes','no') in the db. Is there a way to write a statement to select only the ones that equal 'yes' to be displayed?

The way I have it now works, but it's alot of if($x='yes'). I'm pretty sure it can be written better. Any suggestions on which route I should take with this would be gratefully appreciated.

Thanks for all your help.

[bradgrafelman]

Are you talking about selecting rows from a MySQL database after they've been inserted?

Sure, that can easily be done!

$query = "SELECT * FROM `table` WHERE `field1`='no'";

Just replace table with your table name, and field1 with the name of the ENUM field in MySQL. Now you can use mysql_result(), mysql_fetch_assoc(), or whatever function you use to fetch results!

If you were talking about something different, clarify a bit on what you're trying to do.

[Ivains]

Hi bradgrafelman,

I'm trying to go a little bit beyond that. I still want all my records to display, but I only want the items checked 'yes' to be displayed with the record and the ones checked 'no' not to be displayed.

Lets say I have 3 check boxes (but I actually have 10 in my table):
field1 -> apples -> yes
field2 -> oranges -> no
field3 -> pears -> yes

My record display should be like:
Tom choose:
apples
pears

Right now I'm using the IF like:

if($apples == 'yes') {
echo "apples";
} else { };

and so on...

I have this 10 times. I'm just trying to find out if there is a better, cleaner way of doing this.

Thanks for the input.

[liquorvicar]

why not use a foreach loop?

while ($row=mysql_fetch_array($query) {
  echo($row['name'].' chose:');
  foreach ($row as $field=>$value) {
    if ($value=='yes') {
      echo($field);
    }
  }
}

if your fields are not called 'apples' etc in the database, you could create an array to store the field=>fruit correspondance and loop through that with the foreach.
you could also store the data in the DB as a boolean (1 or 0, or true or false) and then the if could just be:

if ($value) {

[Ivains]

Ok I tried it and Im getting an error. I must be not understanding correctly. Here is what I have so far:

$q2 = "SELECT * FROM log WHERE ID = '$_GET[ID]' ";
$r2 = mysql_query($q2) or die(mysql_error());

$Pick = array(
"Shoes"=> "Shoes", 
"Shirts"=> "Shirts", 
"Pants"=> "Pants", 
"Tie"=> "Tie"
);

while ($row=mysql_fetch_array($q2)) { 
  foreach ($row as $Pick=>$value) { 
    if ($value=='yes') { 
      print "$Pick"; 
    } 
  } 
}

The error I get is:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Which is refering to

while ($row=mysql_fetch_array($q2)) { 

I'm still chopping away at it, but if someome can point out where I made the wrong turn it would be gratefully appreciated.

Thanks.

[liquorvicar]

i'd guess your problem is:

$q2 = "SELECT * FROM log WHERE ID = '$_GET[ID]' ";

which should be

$q2 = "SELECT * FROM log WHERE ID = '{$_GET['id']}' ";

but i'd STRONGLY recommend you don't insert $GET or $POST data straight into an SQL statement. at least do a

$id = intval($_GET['id']);

first to clean the data (search for other references to cleaning alphanumeric data).

[bradgrafelman]

First of all, you're running the fetch command on your QUERY, when that is not the MySQL resource!! Run it on $r2, the MySQL result resource! 🙂

while ($row=mysql_fetch_array($r2)) {

For the ID stuff, you can get out of the quotes to insert the ID to be sure:

$q2 = "SELECT * FROM log WHERE ID = '" . intval($_GET['ID']) . "' ";

But also, realize that there are quotes inside $_GET[], and I'm not sure if it is case sensitive or not.

If you correct the quote and $r2 problem, and incorporate SOME kind of protection against SQL injections (but using intval() for instance) and are still having problems, try:

echo $q2;

and pasting us your outputed query. I'm betting that $r2 mixup will fix you right up though 😉

[Ivains]

Ok.

Thanks for the info on changing the '$GET[ID]' to '" . intval($GET['ID']) . "'. I'm not sure if it poses a security risk or not, Im gonna do some research on that later and fix my other files.

I got it part of it to work. I had to change the SELECT * to SELECT field1, field2 etc., because it was for some reason not only listing the check boxes that were identified in the array as equal to 'yes', but some others ones from the table that I did not want listed and are not in the array.

Also changed it from q2 to r2 like recommeded. But this is what I get as the results:

2 -Shoes -8 -Shirts - 

 versus Shoes - Shirts -

I don't know what the numbers are, Im just taking a guess it might have something to do with the $row???

Here is the code after making the corrections:

$q2 = "SELECT Shoes, Shirts, Pants, Tie FROM log WHERE ID = '" . intval($_GET['ID']) . "' "; 
$r2 = mysql_query($q2) or die(mysql_error()); 

$Pick = array( 
"Shoes"=> "Shoes",  

"Shirts"=> "Shirts",  

"Pants"=> "Pants",  

"Tie"=> "Tie" 
); 

while ($row=mysql_fetch_array($r2)) {  

  foreach ($row as $Pick=>$value) {  

    if ($value=='yes') {  

      print "$Pick";  

    }  

  }  

}

Once again Thanks for your time and input.

[bradgrafelman]

Humm. I'd have to see your MySQL table, I'm not exactly sure what you're supposed to output and where you're supposed to get it.

[Ivains]

Here is part of it:

CREATE TABLE log (
ID int(11) NOT NULL auto_increment,
RecordDate date NOT NULL default '0000-00-00',
Location varchar(100) NOT NULL default '',
Country varchar(100) NOT NULL default '',
Site varchar(100) NOT NULL default '',
Name varchar(100) NOT NULL default '',
Hood enum('yes','no') NOT NULL default 'no',
Booties enum('yes','no') NOT NULL default 'no',
Gloves enum('yes','no') NOT NULL default 'no',
Light enum('yes','no') NOT NULL default 'no',
Camera enum('yes','no') NOT NULL default 'no',
Computer enum('yes','no') NOT NULL default 'no',
Training enum('yes','no') NOT NULL default 'no',
Other enum('yes','no') NOT NULL default 'no',
IfOther mediumtext NOT NULL,
TimeIn datetime NOT NULL default '0000-00-00 00:00:00',
TimeOut datetime NOT NULL default '0000-00-00 00:00:00',
TotalTime int(10) NOT NULL default '0',
image text NOT NULL,
Notes mediumtext NOT NULL,
DateAdded int(10) NOT NULL default '0',
PRIMARY KEY (ID)
) TYPE=MyISAM AUTO_INCREMENT=33 ;

[bradgrafelman]

You said you wanted it to output "Shoes - Shirts -" ?

What is that supposed to be? Just the column names, no data? What are the "2" and the "8"?

Also, I didn't see "Shoes, Shirts, Pants, Tie" in your table structure.

[tekky]

Any particular reason you are reusing the same variable for multiple things?

.....
//assigned as array here
$Pick = array( 
"Shoes"=> "Shoes",  

"Shirts"=> "Shirts",  

"Pants"=> "Pants",  

"Tie"=> "Tie" 
); 

while ($row=mysql_fetch_array($r2)) {  

  foreach ($row as $Pick=>$value) {  //assigned as array key value here...
    if ($value=='yes') {  

      print "$Pick";  

    }  

  }  

}
.....

[Ivains]

Wow I feel a little hostility going on here! Im just trying to understand the array and foreach. To answer some questions above:

I didn't see "Shoes, Shirts, Pants, Tie" in your table structure

Response: Right before the table structure I had said "Here is part of it:". The table is actually quite big with alot of enum('yes','no') fields.

What is that supposed to be? Just the column names, no data?

Response: Yes. If you remember I had said in the beginning of the thread "each one is set as Enum('yes','no') in the db". They are yes & no check boxes, but I just wanted a select few to display the field name if they were selected yes.

What are the "2" and the "8"?

Response: I dont know? You said you wanted me to show you the output and that what is was. Look a few responses up and you will see where I said "I don't know what the numbers are, Im just taking a guess it might have something to do with the $row???".

Any particular reason you are reusing the same variable for multiple things?

Response: No idea. I thought that is the way it was suppose to be written. I never written any thing with arrays or the foreach. Im still a Rookie or Novice at this learning.

Thank you all for your time. I really mean that. I learned a few new things the past couple of days... Thanks again.

[tekky]

No hostility here, but if you are reassigning $pick in your foreach loop you are killing the array you previously set it to...

what are you expecting the outcom of your while($row.....) and foreach inside of that to do?

[Ivains]

Thanks for replying.

My form has numerous checkboxes. The db is set up as fieldname, enum('yes','no') for each checkbox.

What I was trying to do was instead of having numerous if statements (e.g. if($Shirts == 'yes') { echo "Shirts"}, etc.) was figure out if there was a better more cleaner code to use. All I want is a certain block of fieldnames to be displayed if it equals 'yes'.

For example in the db:
Shirts = 'yes'
Shoes = 'no'
Tie = 'yes'

Then when I run the query it should only display from above: Shirts - Tie
and any other information I query it to display.

It was suggested to use an array with the foreach. Which Im trying to figure out how exactly to write it. Trying to learn...

My coding was by the looks of it incorrect, coz of what Im getting from the output.

I hope I explained it a little better.

Thanks for your time.

[tekky]

judging by your last reposnce and your Schema above...

Hood enum('yes','no') NOT NULL default 'no',
Booties enum('yes','no') NOT NULL default 'no',
Gloves enum('yes','no') NOT NULL default 'no',
Light enum('yes','no') NOT NULL default 'no',
Camera enum('yes','no') NOT NULL default 'no',
Computer enum('yes','no') NOT NULL default 'no',
Training enum('yes','no') NOT NULL default 'no',
Other enum('yes','no') NOT NULL default 'no',

you want...

$sql = 'SELECT `hood`, `booties`, `gloves`, `light`, `camera`, `computer`, `training`, `other` FROM `log` WHERE id=' . $id;
$result = mysql_query($sql) or die('Error in sql: ' . $sql . ' --- ' . mysql_error());
while($row = mysql_fetch_assoc($result))
  foreach($row as $key=>$value)
    if ($row == 'yes')
      echo $key . '<br />';

this will give you a print like...
Shirt
Other
if they are the only 2 yeses in that

[liquorvicar]

what i would do is to have a master array of all your enum columns (either hard-coded if your data structure is fixed, or read in at the top of your script)

$columns = array('Hood', 'Booties', 'Gloves', 'Light', 'Camera', 'Computer', 'Training', 'Other'); 

then for each row in the db, you can loop through this array and display the right stuff

foreach ($columns as $column) {
  if ($row[$column]=='yes') {
    echo($column);
  }
}

[Ivains]

Hi tekky,
I tried it and got errors. I made a few changes from:

$sql = 'SELECT `hood`, `booties`, `gloves`, `light`, `camera`, `computer`, `training`, `other` FROM `log` WHERE id=' . $id;

$result = mysql_query($sql) or die('Error in sql: ' . $sql . ' --- ' . mysql_error()); 

while($row = mysql_fetch_assoc($result)) 
  foreach($row as $key=>$value) 
    if ($row == 'yes') 
      echo $key . '<br />';

to

$sql = "SELECT `hood`, `booties`, `gloves`, `light`, `camera`, `computer`, `training`, `other` FROM `log` WHERE id=' . $id'";

$result = mysql_query($sql) or die('Error in sql: ' . $sql . ' --- ' . mysql_error()); 

while($row = mysql_fetch_assoc($result)) 
  foreach($row as $key=>$value) 
    if ($value == 'yes') 
      echo $key . '<br />';

and now it works perfect!

---

Hi liquorvicar,

I tried your way also, but I get no results. It's just blank. I thought it might of been where I placed the array, but no matter where I move it too I get the same results. Im curious to see your code at work also.

This is what I got:

$q2 = "SELECT * FROM log WHERE id = '32' ";
$r2 = mysql_query($q2) or die(mysql_error());		

$columns = array('Hood', 'Booties', 'Gloves', 'Light', 'Camera', 'Computer', 'Training');

foreach ($columns as $column) { 
  if ($row[$column]=='yes') { 
    echo($column); 
  } 
}

Thanks for your advice!

---

I want to thank everyone who has been inputting advice. So far I learned quite a bit, probably more than what I anticipated.

Special Thanks Shout Out to:

liquorvicar
tekky
bradgrafelman

Thank you all!

[liquorvicar]

the reason my code wasn't working is because you weren't actually fetching the DB query.
try this:

$q2 = "SELECT * FROM log WHERE id = '32' ";
$r2 = mysql_query($q2) or die(mysql_error());        

$columns = array('Hood', 'Booties', 'Gloves', 'Light', 'Camera', 'Computer', 'Training');
while ($row=mysql_fetch_array($r2)) {
//output name or whatever else
foreach ($columns as $column) { 
  if ($row[$column]=='yes') { 
    echo($column); 
  } 
}
}

but yes, tekky's method works equally well. it depends if you want to loop through all the records or just the one.

[tekky]

Originally posted by liquorvicar
.....but yes, tekky's method works equally well. it depends if you want to loop through all the records or just the one.

simply changing the query will make mine loop through mutiple rows from the database

using yours it will loop through the database results + the array every time, which eventually make for extremely inefficient code (start multiplying that processing time by hundreds or thousands)