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

problem inserting into database by extracting from another

User, date Message
Written by gilgal
7 years ago
Category: General
32 posts since Wed, 12 Dec 07
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.
Written by ansgar
7 years ago
5023 posts since Fri, 07 Apr 06
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.
Written by gilgal
7 years ago
32 posts since Wed, 12 Dec 07
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:
Written by ansgar
7 years ago
5023 posts since Fri, 07 Apr 06
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.
Written by gilgal
7 years ago
32 posts since Wed, 12 Dec 07
$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.
Written by gilgal
7 years ago
32 posts since Wed, 12 Dec 07

wrote: 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?
Written by ansgar
7 years ago
5023 posts since Fri, 07 Apr 06
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.