Forums

The forums ran from 2008-2020 and are now closed and viewable here as an archive.

Home Forums Back End Inserting Data into Table

  • This topic is empty.
Viewing 15 posts - 1 through 15 (of 34 total)
  • Author
    Posts
  • #46023
    chrisburton
    Participant

    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?

    #141244
    chrisburton
    Participant

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

    #141245
    Alen
    Participant

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

    #141246
    __
    Participant

    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.

    #141247
    chrisburton
    Participant

    @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

    #141249
    __
    Participant

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

    #141250
    chrisburton
    Participant

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

    #141251
    __
    Participant

    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.

    #141114
    TheDoc
    Member

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

    gold star

    #141292
    __
    Participant

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

    #141318
    chrisburton
    Participant

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

    #141323
    chrisburton
    Participant

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

    #141328
    chrisburton
    Participant

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

    #141346
    Alen
    Participant

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

    Tested and working.

    #141347
    chrisburton
    Participant

    Thanks for creating that @AlenAbdula!

Viewing 15 posts - 1 through 15 (of 34 total)
  • The forum ‘Back End’ is closed to new topics and replies.