cannot import a table in a database/mysql

Question

baziili 0 Newbie Poster

15 Years Ago

I tried to import a table intro a database and MySQL give me the next error message:

"MySQL said:

#1064 - 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 ''table_ro2'

INSERT INTO 'table_ro2' VALUES ('text nr. 1', 'text nr. 2', 'text' at line 1 "

content of my file what I want to import is:
"CREATE TABLE 'table_ro2'

INSERT INTO 'table_ro2' VALUES ('text nr. 1', 'text nr. 2', 'text nr. 3', 'text nr. 4');"

What is the problem?????

mysql

3 Contributors
16 Replies
324 Views
2 Days Discussion Span
Latest Post 15 Years Ago Latest Post by mwasif

Dani AI

Generated 9 Months Ago

As and already pointed out, the import error came from two separate problems: identifiers and an invalid CREATE TABLE. The fix is to (a) define the table structure and (b) use string quotes only for values (or backticks for identifiers when needed). Beyond that, make the SQL file complete (column definitions, semicolons) and avoid invisible byte-order marks that some editors add.

A minimal, importable SQL file that is robust for this use case looks like this:

CREATE TABLE table_ro2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  phrase VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO table_ro2 (phrase) VALUES
  ('Hello world'),
  ('How are you?'),
  ('What do you do?'),
  ('Sample text 4');

For the PHP side, the reason you saw "Resource id #4" is that the query function returned a result handle, not the row contents. Rather than the old mysql_* functions used earlier in the thread, use a modern API (PDO or mysqli) and prepared statements to avoid injection and future compatibility problems. One efficient pattern for selecting a truly random row without ORDER BY RAND() on large tables is: get the row count, pick a random offset, then fetch one row with LIMIT 1 OFFSET N. Example (PDO):

<?php
$pdo = new PDO('mysql:host=localhost;dbname=database123;charset=utf8mb4','root','password123',[
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$total = (int)$pdo->query('SELECT COUNT(*) FROM table_ro2')->fetchColumn();
$offset = random_int(0, max(0, $total - 1));
$stmt = $pdo->prepare('SELECT phrase FROM table_ro2 LIMIT 1 OFFSET ?');
$stmt->bindValue(1, $offset, PDO::PARAM_INT);
$stmt->execute();
echo $stmt->fetchColumn();

Quick troubleshooting checklist: confirm the import file has semicolons and no BOM, verify the table schema with DESCRIBE or SHOW CREATE TABLE, enable error reporting / exceptions in PHP to see real errors, and ensure the PHP column name matches the schema (as discovered). For small tables ORDER BY RAND() is fine, but for production use the COUNT+OFFSET or id-based sampling shown above.

All 16 Replies

mwasif 10 Posting Whiz

15 Years Ago

You should use back ticks (`) instead of single quotes around table names e.g.

INSERT INTO `table_ro2` VALUES ('text nr. 1', 'text nr. 2', 'text nr. 3', 'text nr. 4');

Note the difference to back tick (`) and single quote (')

smantscheff 265 Veteran Poster

15 Years Ago

You have to separate statements by a semicolon.
Please show the file which does not upload.

smantscheff 265 Veteran Poster

15 Years Ago

Your CREATE TABLE syntax is wrong. The table definition is missing.

smantscheff 265 Veteran Poster

15 Years Ago

Study the CREATE TABLE syntax here: http://dev.mysql.com/doc/refman/5.1/en/create-table.html
Which fields do you want your table to have?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

baziili 0 Newbie Poster · Answer 1 · 2011-02-13T23:24:16+00:00

You should use back ticks (`) instead of single quotes around table names e.g.
INSERT INTO `table_ro2` VALUES ('text nr. 1', 'text nr. 2', 'text nr. 3', 'text nr. 4');
Note the difference to back tick (`) and single quote (')

thanks for answer, I tried and also don't work!

baziili 0 Newbie Poster · Answer 2 · 2011-02-14T00:20:13+00:00

You have to separate statements by a semicolon.
Please show the file which does not upload.

this are the file (this is all content of my file) :

CREATE TABLE `table_ro2`
INSERT INTO `table_ro2` VALUES ('text nr. 1', 'text nr. 2', 'text nr. 3', 'text nr. 4');

baziili 0 Newbie Poster · Answer 3 · 2011-02-14T14:03:12+00:00

You have to separate statements by a semicolon.

I tried and also doesn't work.

baziili 0 Newbie Poster · Answer 4 · 2011-02-14T15:39:40+00:00

Your CREATE TABLE syntax is wrong. The table definition is missing.

please, can you show me how need to be my file? :)

baziili 0 Newbie Poster · Answer 5 · 2011-02-14T18:32:13+00:00

Study the CREATE TABLE syntax here: http://dev.mysql.com/doc/refman/5.1/en/create-table.html
Which fields do you want your table to have?

I want from this MySQL database to take intro php a random text/sentence from these text for example(hello word or how are you or what do you do).

for this I need to import this file intro my database:

CREATE TABLE `table_ro2`
INSERT INTO `table_ro2` VALUES ('hello word', 'how are you?', 'what do you do?', 'text nr. 4');

and with this script in php every time when i will make refresh (F5) on my page, to from my table/database to take a random text/sentence.

this is code from php:

<?php

$mysql_host = 'localhost';
$mysql_db = 'database123';
$mysql_user = 'root';
$mysql_pass = 'password123';

mysql_connect($mysql_host, $mysql_user, $mysql_pass);
mysql_select_db($mysql_db);

$sel = SELECT * FROM table_ro2 ORDER BY RAND(), limit 1; 
printf($sel);

?>

what I make wrong?

smantscheff 265 Veteran Poster · Answer 6 · 2011-02-15T00:20:16+00:00

Your table definition needs at least one field. And in the insert query each record (row) has to be in its own brackets. Try this:

CREATE TABLE `table_ro2` (theText text);
INSERT INTO `table_ro2` VALUES ('hello word'), ('how are you?'), ('what do you do?'), ('text nr. 4');

baziili 0 Newbie Poster · Answer 7 · 2011-02-15T03:59:20+00:00

Your table definition needs at least one field. And in the insert query each record (row) has to be in its own brackets. Try this:
CREATE TABLE `table_ro2` (theText text);
INSERT INTO `table_ro2` VALUES ('hello word'), ('how are you?'), ('what do you do?'), ('text nr. 4');

Thanks, finally I imported this table in my database :).
Now, I have a problem with my php script (I think), because php don't want to "print" on my screen a random text/sentence from my table/database.

<?php
 $mysql_host = 'localhost';
$mysql_db = 'database123';
$mysql_user = 'root';
$mysql_pass = 'password123';
 
mysql_connect($mysql_host, $mysql_user, $mysql_pass);
mysql_select_db($mysql_db);
 
$result_db = mysql_query("SELECT * FROM table_ro2 ORDER BY RAND(), LIMIT 1");
echo $result_db;
?>

What can be the problem in this case?

mwasif 10 Posting Whiz · Answer 8 · 2011-02-15T04:06:48+00:00

Check the PHP manual how to print query results. And MySQL query should be

SELECT * FROM table_ro2 ORDER BY RAND() LIMIT 1

baziili 0 Newbie Poster · Answer 9 · 2011-02-16T02:26:10+00:00

Check the PHP manual how to print query results. And MySQL query should be
SELECT * FROM table_ro2 ORDER BY RAND() LIMIT 1

I looked in manual and also don't work,

my php script is the next:

<?php
 $mysql_host = 'localhost';
$mysql_db = 'database123';
$mysql_user = 'root';
$mysql_pass = 'password123';
 
mysql_connect($mysql_host, $mysql_user, $mysql_pass);
mysql_select_db($mysql_db);
 
$query="SELECT * FROM table_ro2 ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
echo $result;
?>

and my php code show me the next result:
Resource id #4

why my phph code don't display from my database a random text??

mwasif 10 Posting Whiz · Answer 10 · 2011-02-16T02:43:47+00:00

Try this and see the PHP manual again for mysql_fetch_assoc()

<?php
 $mysql_host = 'localhost';
$mysql_db = 'database123';
$mysql_user = 'root';
$mysql_pass = 'password123';
 
mysql_connect($mysql_host, $mysql_user, $mysql_pass);
mysql_select_db($mysql_db);
 
$query="SELECT * FROM table_ro2 ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
$row = mysql_fetch_assoc($result);
echo $row["column1"]."<br>"; // replace column1 with real column name you want to print
echo $row["column2"]; // replace column2 with real column name you want to print
?>

baziili 0 Newbie Poster · Answer 11 · 2011-02-16T03:18:53+00:00

Try this and see the PHP manual again for mysql_fetch_assoc()

<?php
 $mysql_host = 'localhost';
$mysql_db = 'database123';
$mysql_user = 'root';
$mysql_pass = 'password123';
 
mysql_connect($mysql_host, $mysql_user, $mysql_pass);
mysql_select_db($mysql_db);
 
$query="SELECT * FROM table_ro2 ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
$row = mysql_fetch_assoc($result);
echo $row["column1"]."<br>"; // replace column1 with real column name you want to print
echo $row["column2"]; // replace column2 with real column name you want to print
?>

thanks a lot!! now works, I just changed the "column1" with name of my field "theText".

mwasif 10 Posting Whiz · Answer 12 · 2011-02-16T03:22:01+00:00

mwasif 10 Posting Whiz

15 Years Ago

You are welcome.

cannot import a table in a database/mysql

Dani AI

Recommended Answers Collapse Answers

All 16 Replies

Recommended Answers