Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: problem inserting into database by extracting from another

    I decided to extract from my MS Access 2000 database and insert the values into a mysql database.

    I decided to write a script to perform this task:
    PHP Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
        <title>Database Transfer</title>
        <style type="text/css">
            .titles {
                font-weight: bold;
                }
        </style>
    </head>

    <body>
    <?php    
        
    require_once('search/cat/odbc.php');
        
        
    $sql "Select * FROM bible WHERE book=1 and chapter < 25";
        
        
    $query odbc_exec($odbc$sql) or die (odbc_errormsg());
        
        
    //echo "<form action='transferresult.php' method='post'>"."\n";
        
    echo "<input name='submit' type='submit' title='Transfer' value=' Transfer ' size='100' />"."\n";
        echo 
    "<table>"."\n";
        echo 
    "<tr>"."\n";    

        
    //book_title

        
    echo "<th>"."\n";
        
        echo 
    "<span class='titles'>book_title</span>";
        
        echo 
    "</th>"."\n";

            
        
    //book number

        
    echo "<th>"."\n";
        
        echo 
    "<span class='titles'>book</span>";
        
        echo 
    "</th>"."\n";
        
        
        
    //chapter number

        
    echo "<th>"."\n";
        
        echo 
    "<span class='titles'>chapter</span>";
        
        echo 
    "</th>"."\n";
        
        
        
    //verse number

        
    echo "<th>"."\n";
        
        echo 
    "<span class='titles'>verse</span>";
        
        echo 
    "</th>"."\n";

        
        
    //book spoke

        
    echo "<th>"."\n";
        
        echo 
    "<span class='titles'>book_spoke</span>";
        
        echo 
    "</th>"."\n";
        
        
        
    //chapter spoke

        
    echo "<th>"."\n";
        
        echo 
    "<span class='titles'>chapter_spoke</span>";
        
        echo 
    "</th>"."\n";
        
        
        
    //verse spoke

        
    echo "<th>"."\n";
        
        echo 
    "<span class='titles'>verse_spoke</span>";
        
        echo 
    "</th>"."\n";
        
        
        
    //text_data

        
    echo "<th>"."\n";
        
        echo 
    "<span class='titles'>text_data</span>";
        
        echo 
    "</th>"."\n";
        echo 
    "</tr>"."\n";    

        while(
    $row odbc_fetch_array($query))
            {    
                echo 
    "<tr>"."\n";    
                
    // db table
                //book_title
            
                
    echo "<td>"."\n";
                
                echo 
    "<textarea name='book_title'>";
                echo 
    $row['book_title'];
                echo 
    "</textarea>";
                
                echo 
    "</td>"."\n";

                    
                
    //book number
                
                
    echo "<td>"."\n";

                echo 
    "<textarea name='book'>";            
                echo 
    $row['book'];
                echo 
    "</textarea>";
                            
                echo 
    "</td>"."\n";
        
                
                
    //chapter number
                
                
    echo "<td>"."\n";

                echo 
    "<textarea name='chapter'>";            
                echo 
    $row['chapter'];
                echo 
    "</textarea>";
                            
                echo 
    "</td>"."\n";
        
                
                
    //verse number
                
                
    echo "<td>"."\n";

                echo 
    "<textarea name='verse'>";            
                echo 
    $row['verse'];
                echo 
    "</textarea>";
                
                echo 
    "</td>"."\n";

                
                
    //book spoke
                
                
    echo "<td>"."\n";
                
                echo 
    "<textarea name='book_spoke'>";            
                echo 
    $row['book_spoke'];
                echo 
    "</textarea>";
                
                echo 
    "</td>"."\n";
        
                
                
    //chapter spoke
                
                
    echo "<td>"."\n";

                echo 
    "<textarea name='chapter_spoke'>";            
                echo 
    $row['chapter_spoke'];
                echo 
    "</textarea>";
                
                echo 
    "</td>"."\n";
        
                
                
    //verse spoke
                
                
    echo "<td>"."\n";

                echo 
    "<textarea name='verse_spoke'>";            
                echo 
    $row['verse_spoke'];
                echo 
    "</textarea>";
                
                echo 
    "</td>"."\n";
        
                
                
    //text_data
                
                
    echo "<td>"."\n";

                echo 
    "<textarea name='text_data'>";            
                echo 
    $row['text_data'];
                echo 
    "</textarea>";
                
                echo 
    "</td>"."\n";
                echo 
    "</tr>"."\n";
                
                            
    $con2 mysql_connect("","root","");
    if (!
    $con2)
      {
      die(
    'Could not connect: ' mysql_error());
      }

                
                
    mysql_select_db("kjv"$con2);

                          
    $sql2 "INSERT INTO bible (book_title, book, chapter, verse, book_spoke, chapter_spoke, verse_spoke, text_data) VALUES ('" $row2['book_title'] . "', '" $row2['book'] . "', '" $row2['chapter'] . "', '" $row2['verse'] . "', '" $row2['book_spoke'] . "', '" $row2['chapter_spoke'] . "', '" $row2['verse_spoke'] . "', '" $row2['text_data'] . "')";
                          
    mysql_query($sql2) or die('Error: ' mysql_error());
                          
    mysql_close($con2);
                     
            }
        
    odbc_close($odbc);
        echo 
    '</table>'."\n";
        
    //echo '</form>'."\n";                
        
    ?>    
    </body>
    </html>
    But the result was empty cells in the memo sections and a value of 0 in the integer sections.
    Compare bible texts (and other tools):
    TheWheelofGod

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    The name of the variables in the outside loop is $row[....]. You are using $row2[...] in the mysql query statement.

    Also, that code will run about 10 times faster if you put the mysql - connect, select database, and close function calls on the outside of the while loop so that you are not repeatedly connecting to the database server, selecting a database, and closing the connection on each iteration through the loop.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why not download a tool such as dbtools that will do that for you for free?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •