Create table bug in creation of SQL statement

I've been evaluating this forms plugin for a new website I've been building for a client (I will be licensing😛 ) but I've hit a very minor bug in the create table process.

From what I can see of the SQL statement that's displayed in the error its simply a loop problem in that a trailing comma is being left on the field creation clause before definition of the primary indexes.

i.e. [create field], [create field], [index clause]

It's a simple oversight that's easy to fix this end, but as I'm just evaluating and lots of things may change with the site (i.e. this is the least of my worries :whistle: ), I'd rather not do it this end.

Any chance of a quick fix in the downloadable version for when I get around to updating this?

regards,
Sphynx.<br><br>Post edited by: Sphynx, at: 2007/07/26 17:35

Hi Sphynx,

Of course thousands of users have used this without a problem so there must not be a bug at this piece or its a "big" bug🙂, I bet its just a special case with your form code which generated this error, can you show us the error and the form code please ?

Cheers

Max

Well, I'll need to edit it slightly before pasting it as the form deals with certain client related multi-choice options that are technically commercial in confidence at present. It's really just a set of <input> tags within a table - nothing more special than that.

The error is coming up from the [create table] option in the admin part of ChronoEngine - all of the fields are being displayed correctly (including no 'additional' or empty fields), and other than this extra comma the SQl statement looks fine.

Other than pasting in what is a pretty standard set of HTML elements, I've not coded anything into the component at all - I've not added anything additional to any of the other fields (such as onSubmit etc.)

This is a screen shot of the error as reported on selection of the [Create table] button, along with the set of fields that were identified by the component.

<br><br>Post edited by: Sphynx, at: 2007/07/26 18:49

Error as reported:

Thanks Sphynx,

I think that this can be solved by going into the components > chronoforms > configuration > change mysql type to "TYPE" instead of "ENGINE".

Please let me know if this will fix it for you!

Cheers

Max

The only option that I have in that area is : "In which format to send the results email". That's it.

Having now looked at the PHP code in question however, that part of the SQL statement is hard coded as below, it does not seem to be based on a variable.

if ( count($chrono_fields) > 0){
$table_sql = "CREATE TABLE `#__chronoforms_".$id."` (";
$table_sql .= $table_sql_body ." , PRIMARY KEY ( `cf_id` )";
$table_sql .= ") ENGINE = MYISAM ;";

Looking at the loop in question, the loop also seems to be catering for adding a ',' only if there is more than 1 field with the following code:

if($i > 1) $table_sql_body .= " , ";

but there does not appear to be any equivilent test to ensure that it is not added for any final field.<br><br>Post edited by: Sphynx, at: 2007/07/26 19:13

While I've not got to that stage yet, it looks like the same problem is present on the INSERT statement as well.

Yes, because the ',' is only added before new fields not after them so this shouldn't be a trouble.

Which ChronoForms version are you using ? in V2.2 you should have an option to change the MYSQL type, Iam not sure about your version but you can change the last line of the code block above to be TYPE = MYISAM instead of ENGINE = MYISAM

btw, v2.2 is not a BETA, because some users think that only v2.0 is stable!

Please let us know!

Cheers

Max

Sphynx, Iam sure there is no problems at this piece of code because I use this component at too many live websites and thousands of users do too and I have never got any complains or problems unless about the mysql type as I pointed before, please try to follow instructions at my post and above and let me know.

Thank you!

Max

Looks like I've been supplied with 2.1.4 - I'll uninstall and try the 2.2.

Great, and there you should have the mysql config variable!

Max

Thanks Max - switching to 2.2 does seem to have cured the problem.

I did not actually download 2.1.4 myself, but inherited it from a previous webdeveloper on this project who claimed to have just downloaded for this purposes. Fool me for trusting him...😛

No worries! Glad to know all is fine now🙂

Thanks!

Max

hi,

i was having same problem,
it occurs because the code don't consider that user may not select first few fields to insert to database.

well here is fixed code

function finalizetableChronoContact( $option ) {
  global $database;
  $id = $_POST['formid'];
  $row = new mosChronoContact( $database );
  $row->load( $id );
  $htmlstring = $row->html;
  preg_match_all('/name=("|\').*?("|\')/i', $htmlstring, $matches);
  $i = 1;
  $found == false;  //this will be true once it find first insert field
  $chrono_fields = array();
  $chrono_posts = "";
  $col_names = array();
  $table_sql_body = "`cf_id` INT( 11 ) NOT NULL AUTO_INCREMENT , `recordtime` TEXT NOT NULL ,";
  foreach ($matches[0] as $match) {
    $new_match = preg_replace('/name=("|\')/i', '', $match);
    $new_match2 = preg_replace('/("|\')/', '', $new_match);
    $name = preg_replace('/name=("|\')/', '', $new_match2);
    if(in_array($name,$col_names)){
      continue;
    }else{
      $col_names[] = $name;
    }
    if(($_POST['cf_'.$name] == '1')&&trim($name)){
      $chrono_fields[] = $name;
      if($i > 1 && $found == true) {
        $table_sql_body .= " , "; // don't insert before first field
      }
      $found = true;
      $table_sql_body .= "`".$name."` ".$_POST['fieldtype_'.$name]. " NOT NULL";
      if($i > 1) $chrono_posts .= " , "; {
        $chrono_posts .= "'\".$"."_POST['$name'].\"'";
      }
    }
    $i++;
  }
  if ( count($chrono_fields) > 0){
    $table_sql = "CREATE TABLE `#__chronoforms_".$id."` (";
    $table_sql .= $table_sql_body ." , PRIMARY KEY ( `cf_id` )";
    $table_sql .= "«») ENGINE = MYISAM ;";
  }
  $database->setQuery( $table_sql );
  if (!$database->query()) {
    echo "<script> alert('".$database->getErrorMsg()."'); window.history.go(-1); </script>\n";
    exit();
  }
  $chrono_onsubmit = "<?php global $"."database; 
$"."database->setQuery( \"INSERT INTO #__chronoforms_".$id." VALUES  ('' , '\". date('Y-m-d').\" - \".date(\"H:i:«»s\"«»).\"' , ".$chrono_posts."«»);\" );
  if (!$"."database->query()) {
  echo \"<script> alert('\".$"."database->getErrorMsg().\"'); window.history.go(-1); </script>\n\";
}
?>";
  $row = new mosChronoContact( $database );
  $row->load( $id );
  $row->autogenerated = $chrono_onsubmit;
  if (!$row->store()) {
    echo "<script> alert('".$row->getError()."'); window.history.go(-1); </script>\n";
    exit();
  }
  mosRedirect( 'index2.php?option='.$option );
}

cheers

-sameer

http://possible.in
http://plantspedia.org

+++++
Code section edited to remove blank lines and commented out code. GreyHead<br><br>Post edited by: GreyHead, at: 2007/08/04 16:38