Page 1 of 1

Category Checkboxes - One query and determine if is checked?

Posted: Sun Nov 16, 2008 3:34 am
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);

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

Posted: Sun Nov 16, 2008 6:31 pm
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

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

Posted: Sun Nov 16, 2008 6:52 pm
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

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

Posted: Sun Nov 16, 2008 8:32 pm
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.

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

Posted: Mon Nov 17, 2008 4:55 am
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";
 }