Category Checkboxes - One query and determine if is checked?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Category Checkboxes - One query and determine if is checked?

Post by JAB Creations »

I have an interesting challenge...I'm working on the edit blog page for my blog. I am trying to determine the best way to implement a checkbox list of categories. The catch is I'm not sure if I can in a single MySQL query fetch all the rows and also determine whether the checkbox should already be checked or not. It may end up being two separate MySQL queries (one executing each time the PHP loop is executed) so I'm looking for a little guidance in how others have approached it.

I've exported the category and relational category tables for my blog below in case any one is curious. I'm initially interested with various views of how to approach the challenge and once something clicks in my head I'll post whatever correlating code I can. Additionally I've been looking through the WordPress code without much luck...my goal pretty much with my blog is to have everything managable including my own code...WordPress is very difficult to work with though it does have the category checkbox feature I'm trying to figure out how I could build myself. Thoughts please?

Code: Select all

-- Database: jabcreat_members-- Table structure for table blog_categories CREATE TABLE IF NOT EXISTS blog_categories (  cat_id INT(6) NOT NULL AUTO_INCREMENT,  cat_name VARCHAR(128) NOT NULL,  cat_name_base VARCHAR(128) NOT NULL,  PRIMARY KEY  (cat_id)) ENGINE=MyISAM  DEFAULT CHARSET=hp8 AUTO_INCREMENT=8 ; INSERT INTO blog_categories (cat_id, cat_name, cat_name_base) VALUES(1, 'Web Design & Development', 'web_design_and_development'),(2, 'PC Gaming', 'pc_gaming'),(3, 'Philosophy', 'philosophy'),(4, 'Politics', 'politics'),(5, 'Science', 'science'),(6, 'Software', 'software'),(7, 'Hardware', 'hardware');  -- Database: jabcreat_members-- Table structure for table blog_xhref_categories CREATE TABLE IF NOT EXISTS blog_xhref_categories (  xhref_id INT(10) NOT NULL AUTO_INCREMENT,  xhref_category_id INT(6) NOT NULL,  xhref_thread_id INT(6) NOT NULL,  PRIMARY KEY  (xhref_id)) ENGINE=MyISAM  DEFAULT CHARSET=hp8 AUTO_INCREMENT=9 ; INSERT INTO blog_xhref_categories (xhref_id, xhref_category_id, xhref_thread_id) VALUES(1, 1, 1),(2, 1, 2),(3, 1, 3),(4, 1, 4),(5, 1, 5),(6, 1, 6),(7, 1, 7),(8, 6, 7);
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Category Checkboxes - One query and determine if is checked?

Post by alex.barylski »

I'm not sure what it is your trying to do...

You have multiple check boxes in a FORM and I assume you post the form to a PHP script which then iterates over each checkbox value and if it's checked you would update that category (or delete it -- whatever).

If the checkboxes are within the limit of a few dozen, I would simply construct an IN() by imploding the array of checkbox value's

Code: Select all

 
// NOTE: Make sure to secure the values from POST using array_map and a callback to cast to integers or similar technique
$in = implode(',', $_POST['checks']);
 
Then I would use a SQL statement like:

[sql]SELECT * FROM TABLE WHERE pkid IN($id)[/sql]

Do I understand the problem?

Cheers,
Alex
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Category Checkboxes - One query and determine if is checked?

Post by JAB Creations »

You're speaking about the second half however the thread is (at least starting) with the first half when the page is initially being generated...before the $_POST request. :P
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Category Checkboxes - One query and determine if is checked?

Post by John Cartwright »

Code: Select all

SELECT *, IF(blog_xhref_categories.xhref_id IS NULL, 'unchecked', 'checked') AS `checkstatus`
FROM blog_categories
LEFT JOIN blog_xhref_categories ON blog_categories.cat_id = blog_xhref_categories.xhref_category_id
WHERE ...
Not exactly sure this is what your after.. and untested.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Category Checkboxes - One query and determine if is checked?

Post by JAB Creations »

It's merely an editing page so I'm not worried about splitting it in to two queries...I'm not really sure what you code does...I've not yet encountered IF conditions in MySQL. Here is what I currently have and it seems to work fine though I wouldn't mind improving it...

Code: Select all

$mysql_query1 = mysql_query("SELECT cat_id, cat_name FROM blog_categories");
 
 while($row1 = mysql_fetch_array($mysql_query1))
 {
  $mysql_query2 = mysql_query("SELECT * FROM blog_xhref_categories WHERE xhref_thread_id = '1' AND xhref_category_id = '".$row1['cat_id']."' LIMIT 0 , 1");
  $count = mysql_num_rows($mysql_query2);
  echo '<div>';
  echo '<label for="cat_'.$row1['cat_id'].'">';
  echo '<input ';
  if ($count == '1') {echo 'checked="checked" ';}
  echo 'class="block checkbox left" id="cat_'.$row1['cat_id'].'" name="cat_'.$row1['cat_id'].'" type="checkbox" /><span>'.htmlspecialchars($row1['cat_name']).'</span>';
  echo '</label>';
  echo '</div>'."\n";
 }
Post Reply