1

I have this in my words.txt :

ada
adanya
adalah
adapun

I want to insert all of them into mySQL table and make sure no whitespace inserted. My table should be like this :

id  |  word
1   |  ada
2   |  adanya
3   |  adalah
4   |  adapun

I've been searching in the forum, but can't find any case like mine.
Thank you for help.

kurniawan26
  • 793
  • 4
  • 16
  • 35
  • Load the file, line by line, and run a query. Which API are you using? What have you tried so far? – Qirel Apr 06 '17 at 12:24
  • I've inserted all words to mySQL table, but it also inserted an enter `\n` I guess. Because when I try to `SELECT * FROM myTable where word='ada'` it shows `0` result. – kurniawan26 Apr 06 '17 at 12:27
  • You can strip whitespace from a string with PHP in a number of ways, for example: `$word = preg_replace('/\s+/', '', $word);`. Can you show us your code perhaps? – glaux Apr 06 '17 at 12:32
  • @kurniawan26 Without seeing how its inserted/what data is inserted, its hard to say. Could be a whitespace issue, could be you insert the entire file at once. Hard to say for us, we can't see whats on your screen ;-) – Qirel Apr 06 '17 at 12:34

1 Answers1

2

Create a file-handle with fopen(), check if its a valid handle, run a loop with fgets() as the argument to the loop - this will have each line individually stored as a variable inside the loop, in this case $line. Run your query to insert the line inside that while-loop. 

if ($file = fopen("words.txt", "r")) {
    while (($line = fgets($file)) !== false) {
        // Run the query
        // Perhaps $line = trim($line)  ?
        // Pseudo-query: INSERT INTO tablename (word) VALUES ('$line')
        // $line is the content of each line individually */
    }

    fclose($file); // Close the file at the end
} else {
    /* File could not be opened */
}

You can use trim() on each $line to remove any whitespaces (would be $line = trim($line);).

Also note that when inserting values into a database, you should use parameterized queries to ensure you avoid any security issues (SQL injection) and issues with quotes.

Qirel
  • 25,449
  • 7
  • 45
  • 62
  • Thank you very much. `trim()` did it. – kurniawan26 Apr 06 '17 at 13:05
  • Cheers, happy it helped you solve the issue. Please take the note about parameterized queries seriously, it's quite important. See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) – Qirel Apr 06 '17 at 13:06
  • Actually I'm new in programming. I've seen some articles about SQL injection, but I don't read about it more detail. My program is run locally on my Mac. Do I still need to prevent SQL injection ? – kurniawan26 Apr 06 '17 at 13:10
  • Yes, whatever operation system you're on doesn't matter. It's a technique that attackers can use to compromise your database if you don't protect yourself against it. Read more here https://en.wikipedia.org/wiki/SQL_injection and here http://php.net/manual/en/security.database.sql-injection.php, in addition to the link I gave in my comment above. – Qirel Apr 06 '17 at 14:10
  • Thank you so much for telling about this. You are so kind. – kurniawan26 Apr 07 '17 at 04:33