distal-attribute
distal-attribute
distal-attribute
distal-attribute

problem inserting into database by extracting from another

gilgal posted 10 years ago in General
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:
<!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";
require_once('mysql.php');
mysql_select_db("kjv", $con);
$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());
}
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.
ansgar posted 10 years ago
You should check what $row2['text_cell'] gave you while this script runs and genereates the INSERT. The best would be to write these INSERTs to a .SQL-file, then load this file in HeidiSQL or another editor and analyze it. Could be that you had some wrong escaping or just empty variables, don't know.
gilgal posted 10 years ago
I noticed that I didn't close mysql and that I had forgotten to name one $con into $con2 to distinguish from the previous connection $con. But I still got:
ansgar posted 10 years ago
The screenshot is not helpful for debugging this - just check the values in your $row2 array, echo them before you generate the INSERT, or as already said, generate a .SQL file and analyze the contents afterwards.
gilgal posted 10 years ago
$row2? You mean $row because I use $row2 for ...

I corrected to $row and it worked! Because $row was used with $con but not for $con2.
gilgal posted 10 years ago

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's name Eve; because she was the mother of all living.')' at line 1



Do I have to put a \ before ' or something to ignore the syntax?
ansgar posted 10 years ago
Yes, definitely. You should use mysql_escape_string($row['something']) . That will add slashes where they're needed.

Please login to leave a reply, or register at first.