Edit form still does not come up

[JohnKlassen]

Max,

Thanks for responding to this post. I have literally spent hours searching the forum and the internet and trying different things to get my tables set up and the data loaded. I tried auto-increment which does not support varchar. I tried creating my own alphanumeric primary keys but then had problems with add record and clone. I understand that NuBuilder handles the primary key values AFTER the data is loaded. I just wasn't sure how to load the data to make it all work right.

Do you have a sample PHP script that you could share with me that I can use to load the data that would automatically create the primary key?

I really appreciate your help.

Thanks,

John

[massiws]

John,

if you want use uniqid() native function you must declare the PK field VARCHAR(15); alternatively, if you want to use autoincrement PK you must declare the field as INTEGER.
In both cases, don't think to manage them: nuBuilder can do all the work!

To import your data you can:
- import data directly from exel file, using this class (well documented) http://code.google.com/p/php-excel-reader/
- create your own PHP script like this:

Code: Select all

<?php
	// open the file to read
	$handle = fopen("/path/to/your/file.csv", "r");

	// attention: on windows system may be different (see http://it2.php.net/manual/it/function.fopen.php)
	$handle = fopen("c:\\data\\info.txt", "r");

	// Connect to database
	require 'config.php';  // <-- here are your configuration parameters
	$DB = new mysqli($DBHost, $DBUser, $DBPassword, $DBName);
	if ($DB->connect_errno) {
			$msg = 'Connection error (' . $DB->connect_errno . ') '
				. $DB->connect_error;
	}

	// read rows one by one
	while (($row = fgetcsv($handle, 1000, ",")) !== FALSE) {
		// build the sql
		$sql = "INSERT INTO table (
			table_id, 
			table_first_field, 
			table_second_field, 
			...
		) VALUES (
			`" . uniqid(1) ."`, 
			`" . $row[0] . `,
			`" . $row[1] . `,
			...
		); ";

		// this is not necessary
		echo "<br>".$sql;

		// execute the sql
		$res = $DB->query($sql);
		if ($res != TRUE) {
			echo "<br>error on inserting data: $sql";
		}

	}

	/* Free result set and close connection */
	$res->close();
	$DB->close();

	//close the file csv
	fclose($handle);
?>

Hope this helps.
Max

EDIT: sorry, I made an error: now I have correct. Copy this script.php file in your webserver (where nuBuilder is) and run from a browser in http://yourdomin.com/script.php

[JohnKlassen]

Max,

Thank-you very much. This will save me a lot of time and frustration.

I will make another post when I am done.

John

[JohnKlassen]

Max,

Thanks for giving me the script to load a table from a CSV file. I had to make some minor changes to make it work. Since I am somewhat new to PHP and since I wasn’t sure why it didn’t work the first time, I changed a few commands to others that I found.

Here is my updated script:

Code: Select all

<?php

// This script is used to read a CSV file and load a table that has a primary key

// open csv file to read (windows format for path)
      
	$handle = fopen("C:/path/to/file/csv_file.csv", "r");

// connect to MySQL

   $con = mysql_connect("localhost", "<user>", "<password>");
   if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }

// select database
   
	mysql_select_db("<table name>", $con);
 
// read rows one by one

	while (($row = fgetcsv($handle, 1000, ",")) !== FALSE) {
// build the sql

   $str = ("INSERT INTO <table name> (
         table_id, 
         table_first_field, 
		 table_second_field,
         ....        
   ) VALUES (
         '" . uniqid() . "', 	/* creates the primary key value - do not change */
         '$row[0]' ,			/* create 1 row for each field in addition to primary key */
         '$row[1]' ,
         ...  
         );");

// The following statement is used to display data to be inserted
// into the table - useful for debugging
		 
    echo "<br>".$str;			
	echo "<br>";				/* forces a new line */
	
// execute the sql	
     $res = mysql_query($str);
     if ($res != TRUE) {
        echo "<br>error on inserting data: $str";
		}
	}
			 
// Free result set and close connection to 

	mysql_close($res); 
	mysql_close($con);

//close the csv file
   fclose($handle);	  
?>

For those newbies that could benefit from what I learned from Steven, Max, the forum, wiki and Google, here is some additional information:
- NuBuilder requires a primary key on all tables or you cannot select a record on the browse screen to edit
- To insert a row with a primary key value, you must use the uniqid() function
- To load the data with the primary key from a CSV file, use the script above.
- The PHP script must be placed in the ‘www’ directory under the nuBuilder directory
- To run the script, start your browser and enter the following URL: http://localhost/<script name>.php

Thanks,

John

[admin]

John,

If you create an Activity Procedure you don't need an extra php file.

Also you can use nuRunQuery() without making a connection and providing username and password.

Code: Select all

    // This script is used to read a CSV file and load a table that has a primary key

    // open csv file to read (windows format for path)
         
       $handle = fopen("C:/test/file.csv", "r");

    // read rows one by one

       while (($row = fgetcsv($handle, 1000, ",")) !== FALSE) {
    // build the sql

       $str = "INSERT INTO aaa (
			aaa_id,
			tra_number,
			tra_date,
			tra_type,
			cus_name
       ) VALUES (
			'" . uniqid() . "',
			'$row[0]' ,
			'$row[1]' ,
			'$row[2]' ,
			'$row[3]' 
		);";

    // The following statement is used to display data to be inserted
    // into the table - useful for debugging
          
        echo "<br>".$str;         
       echo "<br>";            /* forces a new line */
       
    // execute the sql   
         $res = nuRunQuery($str);
         if ($res != TRUE) {
            echo "<br>error on inserting data: $str";
          }
       }

    //close the csv file
       fclose($handle);    

Just watchout for single quotes in your data.

Steven

[JohnKlassen]

Steven,

Thanks for the alternative suggestion.

I had already run into the situation with the single quotes.

John

[admin]

.