Inserting Data into Table
# July 1, 2013 at 11:06 pm
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?# July 1, 2013 at 11:15 pm
`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.# July 1, 2013 at 11:40 pm
You should use PDO to connect.
$config = array(‘user’=>’USERNAME’,’pass’=>’PASSWORD’);
$connect = new PDO(‘mysql:host=HOST;dbname=DBNAME’, $config, $config);
echo ‘Error’ . $e->getMessage() . ‘
$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))’);# July 1, 2013 at 11:41 pm
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.
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.# July 2, 2013 at 12:57 am
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
>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.)# July 2, 2013 at 1:31 am
@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!# July 2, 2013 at 1:56 am
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.
… 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.# July 2, 2013 at 5:46 am
This reply has been reported for inappropriate content.
Definitely an encoding issue somewhere down the line. I’ve had that as well and making sure
utf8is used everywhere solved it (you might have to fix the database manually afterwards if
latin1characters are stored already while
utf8is expected, either by clearing and repopulating or fixing manually).
@traq, why do you prefer
mysqli? (just out of curiosity)# July 2, 2013 at 11:16 am
@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.# July 2, 2013 at 1:06 pm
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.# July 2, 2013 at 1:40 pm
$DB->set_charset( ‘utf8’ );
That worked. But so did adding the following to `my.ini`
character-set-server = utf8
Also, I used `INSERT IGNORE INTO`. When I refreshed the page, it returned an error of duplicate entries. `IGNORE` stops that from happening.# July 2, 2013 at 2:12 pm
@traq There’s also an issue with the regex.
And when you wrote `oblie`
$match = “#^(?:[^?]*?url=)(https?://)(?:m(?:oblie)?)?(.*)$#ui”;
I assume you meant `obile`
$match = “#^(?:[^?]*?url=)(https?://)(?:m(?:obile)?)?(.*)$#ui”;
You must be logged in to reply to this topic.