artaylor

Converting legacy data to PW with foreign keys

Recommended Posts

Hi,

I have a project that, based on initial specs, I decided to build in another framework due to the client wanting the ability to regularly import data from an existing manufacturing system. I decided a platform that would allow me to match the imported data to the system would make sense. Since then, the project has morphed (I know, it never happens) and the regular import is unnecessary and so a comprehensive admin is needed. I want to switch to PW but don't want to have to manually link thousands of tables.

I have the import routine working for the main table but I am not sure how to import the support tables and automatically create the page fields for them.

Oh, in case it matter, the links are not autoincrement id fields, but character codes. Eg. Category table - code: TRN, description: Transportation

If someone can point me in the right direction it would be much appreciated.

Share this post


Link to post
Share on other sites

Literally thousands of tables or just thousands of rows/records?

  • Like 1

Share this post


Link to post
Share on other sites

Ah, that reads a lot more doable then :)

For a dozen tables, I'd probably create the templates and their fields by hand as it is a one-time thing. Perhaps you can name the templates like the old tables for easier coding later. Then create a page field on the main template for every linked table and limit it to the according support table template. Your import routine can focus on creating pages with the correct template and filling in the data. The old id can just go into a dedicated field in the support template, and once all your support table pages have been created, you can use simple selectors like $pages->get("template=oldtablename, keyfield=TRN") to get the correct page to link to when you import the main page/table. Depending on whether you need to display the link codes, you can hide or even drop the field in the support templates.

I'm assuming most support tables will be relatively simple and a template with a title and one extra field for the db id code should be sufficient.

  • Like 3

Share this post


Link to post
Share on other sites

@BitPoet Thanks for the info.

While I have built a few PW sites, I have not had to go much beyond installing some modules and doing basic PW things. I have not gotten to the level of using the API to add data, etc, so if you don't mind sharing a quick snippet of the "import" routine, I would appreciate it. I don't want to take too much of your time, so please ignore this request if it is a lot of time and energy.

Cheers

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By answersmode
      Please help me........
      DATABASE CONNECTION PROBLEM  - CREATE DATABASE IF NOT EXISTS `lwteswm664_ltingesjfo_itbd` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
      Error
      SQL query:
       
      -- -- Database: `lwteswm664_ltingesjfo_itbd` -- CREATE DATABASE IF NOT EXISTS `lwteswm664_ltingesjfo_itbd` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;  
      MySQL said:
      #1044 - Access denied for user 'newhost'@'localhost' to database 'lwteswm664_ltingesjfo_itbd' 
       
      I'm facing this peoblem in my website xxxxx and xxxxx When I wanted to upload my databaz backup file.
      Now what can I do?
      Please help me........
       
       

    • By thmsnhl
      Hi everybody,
      we started our first Processwire driven project in my new company and for the first time, I was working on one site with more than 2 colleagues on the same site.
      It didn't take long for us to stumble across some problems when multiple developers work at the same time, conflicts with updating the database on vagrant machines, like duplicate entries for page IDs, errors when setting up fields and stuff like this. We ended up working on a dedicated database server, that we linked to our vagrant machines and most of the problems were gone, but the performance of this constellation is really bad compared to our first approach with database running on vagrant machines.
      I already tried to find a solution in the forums but I couldn't find anyone with problems like this.
      So I was wondering: how do you manage projects with multiple developers on vagrant machines in a git-based workflow?
    • By hezmann
      Hi!  I've just started on ProcessWire, coming from Wordpress.  I've done several sites in WP but this new one is just to data driven for Wordpress to handle without so many plugins and custom code it just seemed like there must be a better option so here I am.  I used to do a lot of stuff in Access (many years ago) and have some experience with PHP/MySQL.
      I've had a look at a few tutorials and created the planets website etc.  I have a specific structure that I'm looking to create so thought I would ask for some expert advice before making every mistake in the book.
      I do a lot of long distance (inn to inn) walking and am always looking for new ones which involves searching many different sites.  I've yet to find a good repository for this type of thing so thought I would build my own.  The basic structure is (more info added later but trying to get basic idea of best practices):
       
      Walks -> Walk Variations <->Walk Segments <-> Walk Towns (go into Walk Segments as Start and End Towns).
      Walks <->Links
      Walk Towns <-> Accomodation, Walk Towns <-> Dining
      Should I be using ProFields?  How should I structure this as far as fields/templates?
      Thanks in advance for your help.
      Heather
    • By eangulo
      Hello everyone,
      Usually in many CMS database tables prefixed or suffixed with "cache" can be manually cleared without a problem because the system will populate them on the "next page request". Actually in Processwire I am expecting this behaviour:
      [On PW 3.x]
      Manually clear table "caches" in database Go to "client" side (not in the admin panel) All references to my "/site/modules" in my template files does not work : wire("modules")->get(""), $modules->get("") and modules()->get("") PHP error:  Fatal error: Uncaught TypeError: Return value... My _init.php file are not able to find the references to my /site/modules/  The client side not working because this PHP fatal error. If I go to the admin panel "Admin -> Modules" and I trigger the action "Check for New Modules" in the top-right corner in the page, it populates the caches table with the required information and them the client side works.
      It is normal? Or I am doing something wrong ?
      A solution could be to manually call the script that the button "Check for New Modules" calls, but I want to know if  I am doing something wrong here.
      Thank you in advance guys ! 
    • By flashmaster
      Hi, i need some help with a calendar script (Full Calendar)
      Issue 1
      Its not saving to the database. This function dont work either when the script is running alone outside Processwire.
      Issue 2 (Fixed)
      When i click to ad an event and click save it wont turn up in blue, it just disapear. This function works when the script is running alone outside Processwire.
      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      I have attached the script running alone so you can se how it works. I have also pasted the code below that im trying to work under the template folder inside Processwire. If you can look at the code and se whats wrong with it. Im not that good at PHP or Javascript so i need you help.

      demo
      http://demos.phplift.net/jquery-fullcalendar-integration-bootstrap-php-mysql/
       
      home.php
      <?php include( "database.php" ); if ( isset( $_POST[ 'action' ] )or isset( $_GET[ 'view' ] ) ) //show all events { if ( isset( $_GET[ 'view' ] ) ) { header( 'Content-Type: application/json' ); $start = mysqli_real_escape_string( $connection, $_GET[ "start" ] ); $end = mysqli_real_escape_string( $connection, $_GET[ "end" ] ); $result = mysqli_query( $connection, "SELECT id, start ,end ,title FROM events where (date(start) >= ‘$start’ AND date(start) <= ‘$end’)" ); while ( $row = mysqli_fetch_assoc( $result ) ) { $events[] = $row; } echo json_encode( $events ); exit; } elseif ( $_POST[ 'action' ] == "add" ) // add new event section { mysqli_query( $connection, "INSERT INTO events ( title , start , end ) VALUES ( '" . mysqli_real_escape_string( $connection, $_POST[ "title" ] ) . "', '" . mysqli_real_escape_string( $connection, date( 'Y-m-d H:i:s', strtotime( $_POST[ "start" ] ) ) ) . "‘, '" . mysqli_real_escape_string( $connection, date( 'Y-m-d H:i:s', strtotime( $_POST[ "end" ] ) ) ) . "‘ )" ); header( 'Content-Type: application/json' ); echo '{"id":"' . mysqli_insert_id( $connection ) . '"}'; exit; } elseif ( $_POST[ 'action' ] == "update" ) // update event { mysqli_query( $connection, "UPDATE events set start = '" . mysqli_real_escape_string( $connection, date( 'Y-m-d H:i:s', strtotime( $_POST[ "start" ] ) ) ) . "', end = '" . mysqli_real_escape_string( $connection, date( 'Y-m-d H:i:s', strtotime( $_POST[ "end" ] ) ) ) . "' where id = '" . mysqli_real_escape_string( $connection, $_POST[ "id" ] ) . "'" ); exit; } elseif ( $_POST[ 'action' ] == "delete" ) // remove event { mysqli_query( $connection, "DELETE from events where id = '" . mysqli_real_escape_string( $connection, $_POST[ "id" ] ) . "'" ); if ( mysqli_affected_rows( $connection ) > 0 ) { echo "1"; } exit; } } ?> <!doctype html> <html lang="sv-se"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8"/> <style type="text/css"> img { border-width: 0 } * { font-family: 'Lucida Grande', sans-serif; } </style> <style type="text/css"> .block a:hover { color: silver; } .block a { color: #fff; } .block { position: fixed; background: #2184cd; padding: 20px; z-index: 1; top: 240px; } </style> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script> <script src="<?=$config->urls->templates;?>js/script.js" type="text/javascript"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script> <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet"> <link href="<?=$config->urls->templates;?>css/fullcalendar.css" rel="stylesheet"/> <link href="<?=$config->urls->templates;?>css/fullcalendar.print.css" rel="stylesheet" media="print"/> <script src="<?=$config->urls->templates;?>js/moment.min.js"></script> <script src="<?=$config->urls->templates;?>js/fullcalendar.js"></script> </head> <body> <div class="container">fsefsefes <div class="row"> <div id="calendar"></div> </div> </div> <!-- Modal --> <div id="createEventModal" class="modal fade" role="dialog"> <div class="modal-dialog"> <!-- Modal content--> <div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal">&times;</button> <h4 class="modal-title">Add Event</h4> </div> <div class="modal-body"> <div class="control-group"> <label class="control-label" for="inputPatient">Event:</label> <div class="field desc"> <input class="form-control" id="title" name="title" placeholder="Event" type="text" value=""> </div> </div> <input type="hidden" id="startTime"/> <input type="hidden" id="endTime"/> <div class="control-group"> <label class="control-label" for="when">When:</label> <div class="controls controls-row" id="when" style="margin-top:5px;"> </div> </div> </div> <div class="modal-footer"> <button class="btn" data-dismiss="modal" aria-hidden="true">Cancel</button> <button type="submit" class="btn btn-primary" id="submitButton">Save</button> </div> </div> </div> </div> <div id="calendarModal" class="modal fade"> <div class="modal-dialog"> <div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal">&times;</button> <h4 class="modal-title">Event Details</h4> </div> <div id="modalBody" class="modal-body"> <h4 id="modalTitle" class="modal-title"></h4> <div id="modalWhen" style="margin-top:5px;"></div> </div> <input type="hidden" id="eventID"/> <div class="modal-footer"> <button class="btn" data-dismiss="modal" aria-hidden="true">Cancel</button> <button type="submit" class="btn btn-danger" id="deleteButton">Delete</button> </div> </div> </div> </div> <!--Modal--> <div style='margin-left: auto;margin-right: auto;text-align: center;'> </div> </body> </html>  
      database.php (i have configure this in my file at localhost) this is just an example.
      <?php $connection = mysqli_connect('host','username','password','database') or die(mysqli_error($connection)); ?>  
      js/script.js
      $(document).ready(function(){ var calendar = $('#calendar').fullCalendar({ header:{ left: 'prev,next today', center: 'title', right: 'agendaWeek,agendaDay' }, defaultView: 'agendaWeek', editable: true, selectable: true, allDaySlot: false, events: "home.php?view=1", eventClick: function(event, jsEvent, view) { endtime = $.fullCalendar.moment(event.end).format('h:mm'); starttime = $.fullCalendar.moment(event.start).format('dddd, MMMM Do YYYY, h:mm'); var mywhen = starttime + ' - ' + endtime; $('#modalTitle').html(event.title); $('#modalWhen').text(mywhen); $('#eventID').val(event.id); $('#calendarModal').modal(); }, //header and other values select: function(start, end, jsEvent) { endtime = $.fullCalendar.moment(end).format('h:mm'); starttime = $.fullCalendar.moment(start).format('dddd, MMMM Do YYYY, h:mm'); var mywhen = starttime + ' - ' + endtime; start = moment(start).format(); end = moment(end).format(); $('#createEventModal #startTime').val(start); $('#createEventModal #endTime').val(end); $('#createEventModal #when').text(mywhen); $('#createEventModal').modal('toggle'); }, eventDrop: function(event, delta){ $.ajax({ url: 'home.php', data: 'action=update&title='+event.title+'&start='+moment(event.start).format()+'&end='+moment(event.end).format()+'&id='+event.id , type: "POST", success: function(json) { //alert(json); } }); }, eventResize: function(event) { $.ajax({ url: 'home.php', data: 'action=update&title='+event.title+'&start='+moment(event.start).format()+'&end='+moment(event.end).format()+'&id='+event.id, type: "POST", success: function(json) { //alert(json); } }); } }); $('#submitButton').on('click', function(e){ // We don't want this to act as a link so cancel the link action e.preventDefault(); doSubmit(); }); $('#deleteButton').on('click', function(e){ // We don't want this to act as a link so cancel the link action e.preventDefault(); doDelete(); }); function doDelete(){ $("#calendarModal").modal('hide'); var eventID = $('#eventID').val(); $.ajax({ url: 'home.php', data: 'action=delete&id='+eventID, type: "POST", success: function(json) { if(json == 1) $("#calendar").fullCalendar('removeEvents',eventID); else return false; } }); } function doSubmit(){ $("#createEventModal").modal('hide'); var title = $('#title').val(); var startTime = $('#startTime').val(); var endTime = $('#endTime').val(); $.ajax({ url: 'home.php', data: 'action=add&title='+title+'&start='+startTime+'&end='+endTime, type: "POST", success: function(json) { $("#calendar").fullCalendar('renderEvent', { id: json.id, title: title, start: startTime, end: endTime, }, true); } }); } });  
      database table
      CREATE TABLE events ( id int(11) NOT NULL AUTO_INCREMENT, start datetime DEFAULT NULL, end datetime DEFAULT NULL, title text, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;  
      fullcalendar.rar