INSERT loop with an array

I’ve developed a community calendar, and I’m trying to allow my users to add multiple categories to their events (such as Music, Art, Community…). Doing some research, it looks like a job for a loop in my INSERT query for the array of categories. My tables look like so (only showing relevant field names):


EVENTS           CATEGORIES       EVENT_CAT_REL    USERS
---------------  ---------------  ---------------  ---------------
event_id (PK)    cat_id (PK)      event_id (PK)    user_id (PK)
user_id (FK)     cat_name         cat_id (PK)      user_pass

The form will use an array of check boxes to allow the user to input multiple categories for the event listing.


if (isset($_POST['submitted'])) {
   if (empty($_POST['cat[]'])) {
	$errors[] = 'You forgot to enter a category.';
   } else {
	$category[] = mysqli_real_escape_string($dbc, trim($_POST['cat[]']));
   }

   if (empty($errors)) {

      $q="INSERT not sure what to do with the cat[] array!"
      $r = @mysqli_query ($dbc, $q);

      if ($r) { // the query worked
         echo 'success';
      } else { // it failed
         echo 'failure';
      }
   } else { // there is no category submitted
      foreach ($errors as $msg) 
      { // Print each error.
            echo "* $msg<br />";
      }
   } // end errors
} // end main IF

// create the form
   
echo '<form method="post">';
$category = array(1 => 'Music', 'Dance', 'Theater', 'Books', 'Film', 'Comedy', 'Art', 'Community');

foreach ($category as $key => $value){
     echo '<input type="checkbox" name="cat[]" value="' . $key .'">' . $value . '<br />';
}
echo '<p><input type="submit" name="submit" value="Submit Event" /></p>',
'<input type="hidden" name="submitted" value="TRUE" />',
'</form>';

This outputs the check boxes all right, and it checks for a $_POST[‘cat’] value but I’m not sure how to handle the cat array from the form. I also don’t know what will go in the INSERT query.
Essentially, the INSERT will update the EVENTS table with a new event_id (it’s auto-incr), update the EVENT_CAT_REL table with the same event_id and loop through the cat array assigning the the many cat_id’s to the same event_id.

Any help is much appreciated, of course.

the SQL you want to produce looks like this –

INSERT INTO EVENT_CAT_REL (event_id,cat_id) VALUES
 ( 23 , 1 )
,( 23 , 4 )
,( 23 , 5 ) ;

here, event 23 is being given categories 1, 4, and 5

hopefully you can see where the “looping” (see thread title :)) needs to take place

there is another way to do it –


INSERT INTO EVENT_CAT_REL (event_id,cat_id) VALUES ( 23 , 1 ) ;
INSERT INTO EVENT_CAT_REL (event_id,cat_id) VALUES ( 23 , 4 ) ;
INSERT INTO EVENT_CAT_REL (event_id,cat_id) VALUES ( 23 , 5 ) ;

here, the looping is performed over the entire SQL statement, but this is ~not~ the way you want to do it

Thanks, rudy. I would like to try it using a single query as you showed in your first example. I still don’t grasp the whole thing here. I know I won’t be manually entering values for event_id and cat_id into the query. How will I grab the selections from the user and explode the array to loop inside the INSERT query?

the event_id will come from mysql_insert_id and the cat_ids will come from the <SELECT MULTIPLE> tag in the form

as for the php, sorry, i don’t do php, i just wanted to point you in the right direction for the SQL to generate


$id = 1;
$cats = array(10, 20, 30);
$values = array();
foreach($cats as $c) {
    $values[] = '('.$id.','.$c.')';
}
print_r($values);

$query = "INSERT INTO table (event_id, category_id) VALUES ".implode(',', $values);
echo $query;

For inserting multiple rows, i have made some utility function:

function insertMultiple($table, $field_array, $values_array){
	$sql = "INSERT INTO `".$table."` (";
	$sql .= implode(', ',$field_array);		
	$sql .= ") VALUES ";
	foreach ($values_array as $v){
		$sql .= "(";
		foreach ($v as $value){
			$sql .= "'{mysql_real_escape_string($value) }',";
		}
		$sql = substr($sql, 0, -1) . "),";			
	}
	$sql = substr($sql, 0, -1);	
	mysql_query($sql);
}

usage:

insertMultiple('table', array('event_id', 'category_id'), $values_as_an_array);

Hash, your sample code brought up lots of issues, and I’ve been spending my time learning about how to handle arrays populated by the database.

PHPhyco, thank you for the function. It might even work but I have no idea how or why, so it’s not something I can incorporate into the project right now. I need to understand what’s happening with the code I write so I can fix it when trouble arises.

I’m looking into the code I’ve been generously provided here and am now trying to write out how to keep my cat_id’s together with my cat_names. I can output the cat_names in a list, but can’t figure how to output the names and id’s together.

$q = "SELECT categories.cat_name FROM categories ORDER BY categories.cat_id";		
	$r = @mysqli_query ($dbc, $q); // Run the query.
	if ($r) 
	{	
		while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
			$cat_names[] = $row['cat_name'];
		}
		foreach( $cat_names as $v ) {
			echo $v
		}
	}

Thanks for understanding that this is a learning curve for me. I’m now trying to incorporate some of rudy’s GROUP CONCAT sql into the SELECT and see what happens…

Scratch that, you’ve already given it to me elsewhere:


$q = "SELECT 
        GROUP_CONCAT(CONCAT(categories.cat_id,'=',categories.cat_name)) 
        AS cat_group
        FROM categories 
        ORDER BY categories.cat_id";	
	
	$r = @mysqli_query ($dbc, $q); // Run the query.
	if ($r) 
	{	
		while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
			
			$categories = explode(',', $row['cat_group']);
			
			echo '<ul>';
			
			foreach ($categories as $category) {
				list($cat_id, $cat_name) = explode('=', $category);
				echo '<li><a href="category.php?cat_id=' . $cat_id . '" title="View all events in this category">' . $cat_name . '</a></li>';
			}
			echo '</ul>';
		}
	}

Still working on this…

I assume that’s part of the bigger query, but if not, gonna point out the obvious and say that code concats and then un concats for no reason :stuck_out_tongue:

I don’t understand. The code produces the right result. Can you explain?

What you’re doing is taking two columns, joining them together in the SQL, then separating them with php. So no need to join them in the first place.


$q = "
    SELECT cat_id, cat_name 
    FROM categories 
    ORDER BY categories.cat_id";    

$r = @mysqli_query ($dbc, $q); // Run the query.
if ($r) {    
    echo '<ul>';
    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
        echo '<li><a href="category.php?cat_id=' . $row['cat_id'] . '" title="View all events in this category">' . $row['cat_name'] . '</a></li>';
    }
    echo '</ul>';
} 

For the other query you did need to join them, because you had multiple rows that needed to be collapsed into single rows.

Now that you’ve cleared that up for me, I need to go back and look at why I thought I needed to do that. I don’t work at the dept. of redundancy redundancy dept.

Thanks for the help. again.
(I have a feeling I’ll be back)

Once I wrapped my head around hash’s code, and saw it not only handled the cat_id array, but kept it with the event_id in the INSERT query, I was in good shape. Some research on arrays and how to handle multiple form entries didn’t hurt at all.

This is what I’ve put together:

if (isset($_POST['submitted'])) {
	$id = 1;
	$cats = $_POST['category'];
	$values = array();
	foreach($cats as $c) {
		$values[] = '('.$id.','.$c.')';
	}
	$query = "INSERT INTO table (event_id, category_id) VALUES ".implode(',', $values);
}

$q = "
	SELECT cat_id, cat_name
	FROM categories 
	ORDER BY categories.cat_order"; 

$r = @mysqli_query ($dbc, $q); // Run the query.
if ($r) {

	echo '&lt;form action="page.php" method="post"&gt;',
			'&lt;label&gt;Category&lt;/label&gt;',
			'&lt;ul&gt;';
			
	while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
		
		echo '&lt;li&gt;&lt;input type="checkbox" name="category[]" value="'.$row['cat_id'].'" /&gt; '.$row['cat_name'].'&lt;/li&gt;';
	
	} // end WHILE
	
	echo '&lt;/ul&gt;',
		'&lt;input type="submit" name="submit" value="submit" /&gt;',
		'&lt;input type="hidden" name="submitted" value="TRUE" /&gt;',
		'&lt;/form&gt;';
}

I threw the cat_order field into the categories table in case the client wanted to change the order of appearance of their category list.

When I add an event to the calendar I'll have plenty of data to insert into the database along with the cat_id's. How do I combine implode(',', $values) with everything else, without screwing it up?

So categories can have a specific order per event? or Categories can have a specific order in general?

The cat_order column was added to the categories table so categories can have a specific order in general (not related to the event).

Then it has nothing to do with the implode, that is for the category_event_rel table, when entering a new event. That would be done in another form that lets an admin add/modify categories in the system.

Sorry for the confusion. I mentioned the cat_order column because it was part of the code I pasted there, and didn’t want it to be confusing (so much for that :)). I realize this does not affect the query in this case or have any relevance to my question.

What I will do is include all sorts of information into the INSERT query for the event, such as event_title, event_desc, event_date, and on and on. I like the way the cat_id and event_id are kept together in the query above. How will I add values for these other columns into the same query without separating my cat_id and event_id? Would it be simpler to perform two separate queries?

Yep, use two queries, you’ll need to get the event_id after inserting the event if using auto_increment.

I am using auto_increment for the event_id, so that makes perfect sense that I’d need to insert the other data first, which will generate an event_id, then grab that id, and add the event_id and cat_id’s to the event_cat_rel table.

I think this process will be helpful when I need to edit an event. Using checkboxes in the form, I’ll need to check for the event_id, remove any instance of it, and INSERT the newly selected cat_id’s for that event. While the other singular data columns will remain as an UPDATE for that event_id. Is that correct?