Grow your CSS skills. Land your dream job.

Inserting Data into Table

  • # July 2, 2013 at 5:11 pm

    Thanks for creating that @AlenAbdula!

    # July 3, 2013 at 11:06 pm

    I tried the following:

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

    changes: (?:m`.` instead of (?:m

    Inserting a period after `m` worked. I’m pretty sure I tried that before and it gave an error. It seems to work now, though.

    # July 4, 2013 at 1:27 am

    Sorry about that.

    The dot should actually be escaped (so it’s a literal dot, and not “any character”), and should go at the end of the “mobile” subpattern ( `(?:obile)?` becomes `(?:obile)?.` ):

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

    Thoroughly tested this time. Updated gist.

    # July 4, 2013 at 1:46 am

    Perfect. Thanks, @traq.

    Also, when the data gets inserted into the database, it doesn’t get added in the order it should. How can I fix that?

    # July 4, 2013 at 3:17 am

    you’re welcome

    # July 7, 2013 at 6:06 am

    sorry; just saw this.

    >Also, when the data gets inserted into the database, it doesn’t get added in the order it should. How can I fix that?

    The “order” that records are stored in is irrelevant in SQL (and can’t really be relied on anyway). The meaning of any one record should not depend on other records in the same table. If you need to sort the records according to some particular info, you need to add that info to the record. I assume you want to order them in “the order I read them”?

    # July 7, 2013 at 2:30 pm

    > I assume you want to order them in “the order I read them”? -@traq

    Yes, that’s exactly what I want to do. It should look just like the insert code you posted in that gist. Where the latest article I’ve read is first.

    While posting that screenshot, I noticed that the INSERT data is not showing all the articles. It shows `Google Maps Update: 2013` as the first but yet `Kirby loves MySQL` is actually the first. Perhaps it’s an issue with output because all the articles I’ve read are in the database.

    # July 7, 2013 at 2:48 pm

    Does readability tell you _when_ you read each article (a timestamp, or something? I saw a pubdate attribute, but that’s not really what we want)? Or do we just have to rely on the order readability returns the items in?

    # July 7, 2013 at 2:58 pm

    @traq If I wasn’t relying on the RSS feed, I believe I could rely on when I actually added them. At least from taking a quick glance at their API.

    But since I’m not I think we’d have to use your suggestion of relying on the order I added them to my reading list. Which the array does automatically when we return it.

    example:

    $xml = simplexml_load_file( “http://readability.com/christopherburton/latest/feed” );
    $json = json_encode( $xml );
    $array = json_decode( $json,TRUE );
    $items = $array;

    // This returns all the articles in order of when I added them
    var_dump($items);

    # July 7, 2013 at 3:58 pm

    alright. We’d need to put that artificial index back into your table, and reverse the order of the entries before we INSERT them to the DB:

    DROP TABLE `read`;
    CREATE TABLE `read`(
    `order` NOT NULL UNIQUE AUTO_INCREMENT
    ,`title` VARCHAR( 50 ) NOT NULL
    ,`url` VARCHAR( 50 ) NOT NULL
    ,PRIMARY KEY( `title`,`url` )
    )DEFAULT CHARSET=UTF8;

    and instead of

    $SQL = “INSERT INTO `read`(`title`,`url`) VALUESn “.implode( “n,”,$sql_values );

    we’ll do

    $SQL = “INSERT INTO `read`(`title`,`url`) VALUESn “.implode( “n,”,array_reverse( $sql_values ) );

    When you SELECT, add ORDER BY `order` DESC to the query, and you’ll get most-recent-first.

    Updating the gist now.

    # July 7, 2013 at 4:11 pm

    >I noticed that the INSERT data is not showing all the articles. It shows Google Maps Update: 2013 as the first but yet Kirby loves MySQL is actually the first. Perhaps it’s an issue with output because all the articles I’ve read are in the database.

    Actually, I don’t see the Kirby article returned in readability’s response at all anymore. Perhaps they only serve the 15 most recent? If that’s the case, you might want to keep your existing records before rebuilding the DB table.

    # July 7, 2013 at 4:28 pm

    @traq There seems to be a syntax error in your SQL statement.

    Update: Let me catch up on this later on tonight. I have a family event to go to right now.

    # July 7, 2013 at 5:21 pm

    >There seems to be a syntax error in your SQL statement.

    Which? I’ve tested; it all works on my end.

    Have a good evening.

    # July 7, 2013 at 6:44 pm

    @traq

    Error:

    `#1064 – 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 ‘NOT NULL UNIQUE AUTO_INCREMENT ,`title` VARCHAR( 50 ) NOT NULL ,`url’ at line 2

    CREATE TABLE `read`(
    `order` NOT NULL UNIQUE AUTO_INCREMENT
    ,`title` VARCHAR( 50 ) NOT NULL
    ,`url` VARCHAR( 50 ) NOT NULL
    ,PRIMARY KEY( `title`,`url` )
    )DEFAULT CHARSET=UTF8;

    # July 7, 2013 at 10:43 pm

    gah!! I left out the datatype

    `order` NOT NULL UNIQUE AUTO_INCREMENT

    should be an INT column

    `order` INT NOT NULL UNIQUE AUTO_INCREMENT

Viewing 15 posts - 16 through 30 (of 49 total)

You must be logged in to reply to this topic.

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