problem inserting into database by extracting from another

32 posts

[expired user #3277] posted 16 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.

10136 posts

ansgar posted 16 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.

32 posts

[expired user #3277] posted 16 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:

10136 posts

ansgar posted 16 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.

32 posts

[expired user #3277] posted 16 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.

32 posts

[expired user #3277] posted 16 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?

10136 posts

ansgar posted 16 years ago

Yes, definitely. You should use mysql_escape_string($row['something']) . That will add slashes where they're needed.