Inserting Data into Table

[chrisburton]

Updated: see this gist for code (by @traq).

For some reason I’m getting the following 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 ‘read(title, url) VALUES (‘Kirby loves MySQL’, ‘http://getkirby.com/blog/database’ at line 1

Also, how can I get all the titles and URLs into the database, not just the first one?

[chrisburton]

`read` is a reserved word apparently. I found the info here: http://stackoverflow.com/a/14093979/938664

Enclosing `$table` with backticks successfully inserted the data into the database.

So, Issue 1 = Solved

However, how can I get all the data into the database, not just the first title and url? I’m hoping someone can guide me rather than just tell me the answer.

[Alen]

You should use PDO to connect.

$config = array(‘user’=>’USERNAME’,’pass’=>’PASSWORD’);
try{
$connect = new PDO(‘mysql:host=HOST;dbname=DBNAME’, $config, $config);
$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e){
echo ‘Error’ . $e->getMessage() . ‘
‘;
}
// Query
$results = $connect->query(‘SELECT `B`,`C` FROM `revision` ORDER BY `C` ASC’);

// Pass variable
$views = $connect->query(‘SELECT `C`,`E` FROM `revision` WHERE `C` =’ . $connect->quote($term));

You db query should be within foreach loop, since it needs to loop over all items and store each in the database. Or like @traq said (see below) pass the whole array. (code not tested)

$data = array(‘Cool Title’ => ‘http://#’);
$connect->query(‘INSERT INTO `read` (`title`, `url`) VALUES ($connect->quote($data))’);

[__]

you’re doing all that work in your loops, but you don’t try to do anything with the data until after the loops are done. At this point, you’re left with only the last set of values.

I have some suggestions about how to parse those URLs, too. Give me a few minutes.

*****
######_EDIT_
yes, that was the other thing: don’t use the `mysql_*` functions. They are deprecated. PDO or MySQL**i** should be used instead.

However, I disagree with putting the query _inside_ the loop: that’s a lot of queries, each requiring a new call to the DB, which takes a lot of time. Instead, you can use your loop to parse/organize all your data, and then do a single query to insert at the end.

BRB.

[chrisburton]

@AlenAbdula I believe @CrocoDillon mentioned this but since I’m not using a form, I don’t think it’s an issue, right? Plus, I have no idea what most of that means. Haha

@traq I can’t thank you enough.

P.S. Try to guide me so I can learn =P

[__]

check it out [on github](https://gist.github.com/customanything/5906840).

Tested. Commented; ask anything.

>Plus, I have no idea what most of that means. Haha

I’ll translate:

>However, I disagree with putting the query inside the loop: that’s a lot of queries, each requiring a new call to the DB, which takes a lot of time. Instead, you can use your loop to parse/organize all your data, and then do a single query to insert at the end.

“don’t put stuff in loops if you don’t have to.”

>You should use PDO to connect.

`ext/mysql` (all the `mysql_*()` functions) is deprecated and will be removed from PHP in the future. It’s been outdated for many years anyway. You should use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) instead.

(Where your data comes from is not relevant.)

[chrisburton]

@traq Damn, that’s awesome! Thanks so much for taking the time to comment every line so I can understand and learn from it. One thing I noticed is that when some of the content is added into the database, it strips out apostrophes and quotes.

See here: http://cloud.chrisburton.me/Q0i5

Update: I’ll study the code in the morning and see if I can fix it myself. Thanks again!

[__]

damn smartquotes.

IIRC, you _did_ make sure your DB table uses `DEFAULT CHARSET=utf8`, correct? This problem is probably being caused by the actual _connection_ to MySQL using a different charset.

##Charsets Are A Pain In The Butt.
Check:

… the character encoding readability.com returns the data in

… the character encoding used by your php script

… the character encoding used by your database table

… the character encoding used by your database connection

In all likelihood, your connection is using `latin1`. Add this right after you connect:

$DB->set_charset( ‘utf8’ );

I updated the gist.

[TheDoc]

@traq – I’m giving you a gold star:

[__]

@TheDoc lol

@CrocoDillon Well, a few reasons. First and foremost, the benefits of PDO are exaggerated. Switching from MySQL to MSaccess, for example, is going to be a difficult process no matter what – who cares if you don’t have to change all the method calls (and you _will_ have to change some of them anyway)? You’re still going to have to go through every last line of SQL, and modify most of them.

Next, there are some things PDO can’t do. Multi-queries where you return a result, for example (PDO can do multiqueries, but only via `PDO->exec()` – so if you need to SELECT anything in one of those queries, you can’t get the results).

Don’t get me wrong, though. PDO is fine, and more than suitable for most applications.

[chrisburton]

@traq @CrocoDillon According to my database, I set the collation to `utf8_unicode_ci` and the charset is `UTF-8 Unicode` : http://cloud.chrisburton.me/Q0IS

My php document is set to UTF-8 so I’m not sure where it’s going wrong.

I’m going to drop my table and recreate it to see if that solves anything.

[chrisburton]

$DB->set_charset( ‘utf8’ );

That worked. But so did adding the following to `my.ini`

[mysqld]
port=3306
character-set-server = utf8
skip-character-set-client-handshake

Also, I used `INSERT IGNORE INTO`. When I refreshed the page, it returned an error of duplicate entries. `IGNORE` stops that from happening.

[chrisburton]

@traq There’s also an issue with the regex.

Normal URL:

http://m.theatlanticwire.com/national/2013/06/army-has-partially-blocked-access-guardian/66675/

After:

http://.theatlanticwire.com/national/2013/06/army-has-partially-blocked-access-guardian/66675/

And when you wrote `oblie`

$match = “#^(?:[^?]*?url=)(https?://)(?:m(?:oblie)?)?(.*)$#ui”;

I assume you meant `obile`

$match = “#^(?:[^?]*?url=)(https?://)(?:m(?:obile)?)?(.*)$#ui”;

[Alen]

Here’s PDO version: https://gist.github.com/alenabdula/5913146

Tested and working.

[chrisburton]

Thanks for creating that @AlenAbdula!

[Author]

Posts