Grow your CSS skills. Land your dream job.

Inserting Data into Table

  • # July 1, 2013 at 11:06 pm

    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?

    # 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’);
    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))’);

    __
    # 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.

    *****
    ######_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.

    # July 1, 2013 at 11:45 pm

    @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

    __
    # 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

    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.)

    # 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

    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.

    # July 2, 2013 at 2:15 am

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

    gold star

    # July 2, 2013 at 5:46 am

    Definitely an encoding issue somewhere down the line. I’ve had that as well and making sure `utf8` is used everywhere solved it (you might have to fix the database manually afterwards if `latin1` characters are stored already while `utf8` is 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

    @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.

    # July 2, 2013 at 1:06 pm

    @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.

    # July 2, 2013 at 1:40 pm

    $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.

    # July 2, 2013 at 2:12 pm

    @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”;

    # July 2, 2013 at 5:09 pm

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

    Tested and working.

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".