SQL INSERT

I followed this post but it is still not inserting into the table. I have been struggling to get the correct syntax and would appreciate any help.

$db = &JFactory::getDBO();
    $query = "INSERT INTO table_name (id, name, start, end, publish, discount_type, amount, code, limit, events_enable) VALUES (NULL,'$name', '$start', '$end', '1', '2', '100.00', '$code', '0', '0');"; 
    $db->setQuery($query);
    $db->query(); 

I have also tried to skip inserting a NULL into the key as follows:

$db = &JFactory::getDBO();
    $query = "INSERT INTO table_name (name, start, end, publish, discount_type, amount, code, limit, events_enable) VALUES ('$name', '$start', '$end', '1', '2', '100.00', '$code', '0', '0');"; 
    $db->setQuery($query);
    $db->query(); 

Also, I have to insert array values into one of the fields in another table. How do I place the following into the sytax for SQL:

{"range":"all","range2":"first","amount_left":0,"msc_ids":"2","currencies":"USD"}

?

Hi,

Why are you trying to do this manually ? the DB Save action can already insert the data for you.

Regards,
Max

Hi iibanew,

Juts as a PS to Max's post it looks as though the final snippet in your post is JSON encoded (or possibly serialised). To save it like that you'd need to add a Custom Code action to encode the array before the DB Save action.

Bob

The reason I am not using the DB Save event is because I need to use a loop to insert multiple records into the same table. I am not sure how or if I can accomplish that with the DB Save event. Here is the code. It inserts the values produced in the loop just fine, but the first insert is not working. Do I need to place the function right after the first insert instead of at the bottom?

<?php

// Create sponsor representative event registration code and insert into table
$length = 8;
$form->data['sponsor_rep_code'] = createMembershipDiscountCode($length);
$sponsor_code = $form->data['sponsor_rep_code'];
$name = preg_replace('/[^0-9a-zA-Z]+/', '', $form->data['display_name']);
$start = $form->data['publish_up'];
$end = $form->data['publish_down'];
$db = &JFactory::getDBO();
$query = "INSERT INTO table1 (name, start, end, publish, discount_type, amount, code, limit, events_enable) VALUES ('$name', '$start', '$end', '1', '2', '100.00', '$sponsor_code', '0', '1')";
$db->setQuery($query);
$db->query();


// Set the number of free sponsor membership codes
$length = 5;
if ($form->data['level'] == "Platinum" ) {
$num_codes = 6;
} elseif ($form->data['level'] == "Gold") {
$num_codes = 4;
} else {
$num_codes = 2;
}

// Create free chapter membership codes and insert into table
for ($i = 1; $i <= $num_codes; $i++ ) {
$form->data['sponsor_membership_code_'.$i] = createMembershipDiscountCode($length);
$code = $form->data['sponsor_membership_code_'.$i];
$title = preg_replace('/[^0-9a-zA-Z]+/', '', $form->data['display_name'])." ".$i;
$params = '{"range":"all","range2":"first","amount_left":0,"msc_ids":"2","currencies":"USD"}';
$db = &JFactory::getDBO();
$query = "INSERT INTO table2 (title, code, type, amount, amount_infinity, discount, discount_type, params, data) VALUES ('$title', '$code', '', '1', '0', '100', 'rate', '$params', NULL)";
$db->setQuery($query);
$db->query();
}

// Function that generates the event and membership codes
function createMembershipDiscountCode($length){
$keyset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789abcdefghijklmnopqrstuvwxyz";
$randkey = "";
for ($i=1; $i<=$length; $i++)
$randkey .= substr($keyset, rand(0, strlen($keyset)-1), 1);
return $randkey;
}


?>

Hi,

Then try to echo the first insert query and test it in phpMyAdmin to find why its not working.

Regards,
Max