Inserting Data into Table

[chrisburton]

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.

[chrisburton]

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?

[chrisburton]

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

[chrisburton]

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

[chrisburton]

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

[chrisburton]

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

[chrisburton]

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

Thanks. I’m not too worried if I lose that data. Right now I’m just testing to make sure everything works before I publish the site.

>`order` INT NOT NULL UNIQUE AUTO_INCREMENT

WORKS! Thanks so much!

[chrisburton]

@traq I’m back again with an issue related to how the data is ordered. For some reason, depending on how many times I refresh the page and perhaps it has something to do with me using ajax as well, the numbers jump substantially.

I’m thinking I need to actually set `order` in my PHP to get the proper values.

From this:

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

To something like this:

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

and then output by DESC or do I have it reversed?

[chrisburton]

> you can’t use ORDER BY on INSERT (it doesn’t make sense – you can order/arrange records when you retrieve them, but inside the DB, there is no order).

What I meant was to use an `id` and set that to increment. However, it might just do the same exact thing as the `order` row you had me add.

> I had expected the AUTO_INCREMENTing key to reflect the order the values were provided in – if that’s not the case, we’ll have to do something else.

Same here but for some reason I refreshed the table to make sure it was still working after I read another article. The `order` value was something like `4368` when it should have been `22`.

> are you talking about refreshing the page on INSERT, or on SELECT?

This is the part where I’m not sure. I refresh the page here and there and that’s when the numbers jump substantially the very next time data is inserted into the table. So I assume on INSERT. But I’m also using Ajax to refresh the div every minute.

> if we’re talking about SELECT, are you using ORDER BY`order`DESC in your SELECT statement?

I am. I recently added that part to the code. The value jumping was still happening before I put that in. Here is my code:

// OUTPUT
$query = “SELECT * FROM `read` ORDER BY `order` DESC”;
$result = $DB->query($query);
$data = array();

while ( $row = $result->fetch_assoc() ) {
array_push($data, $row);
}
$title = $data[0];
$url = $data[0];

@traq

[Alen]

He shouldn’t have any duplicates, since he set the table to accept unique values. right? That’s how I have it set up on my PDO version, it throws exception for duplicates.

[chrisburton]

@traq

> What do you mean by “refreshed the table”? Refreshed the page that you’re accessing/selecting the data from? Refreshed phpMyAdmin?

There’s a refresh link that basically just refreshes the page in the gray box area of the SQL statements. http://cloud.chrisburton.me/QDSy

> If you’ve been doing a lot of testing, that might be expected: PK values persist, even if the values below them are gone. So, if you’ve inserted 1,000 records, and then delete 999 of them, the next PK value will still be 1,001. If you want to change this, you can (you have to use ALTER TABLE), but it doesn’t really matter: the numbers just need to be ascending (they don’t need to be sequential) to use them for ordering.

At some point the numbers might get out of hand which is why I’d like to figure it out and have them display properly.

> So, the same page is INSERTing and SELECTing…? Are you using basically what we have in the gist? Could you post the current code?

Basically, yes. Ah ha! That’s probably why when I refresh the page, I get a duplicate error stating the entry is already in the database which is why I had to switch to `INSERT IGNORE`.

Update: Yep, that was why. So I created two separate files:

+ `input.php`: adds the data to the database.
+ `output.php`: outputs the data
+ `index.php`: uses an include to grab `output.php`

Now on `output.php` I just reconnected to the database instead of relying on the same connection from when I inserted the data into the database. If this makes any sense.

###Code

input.php.

output.php

> (You don’t have any duplicate entries, do you? What we wrote shouldn’t be inserting the same info more than once.)

Nope. No actual duplicate entries. But as I explained above, sometimes when I’d refresh the page, I’d get an error of a duplicate but nothing actually inserted. That’s when I started using `INSERT IGNORE INTO…`. Reconnecting to the database with `output.php` solved that.

[chrisburton]

No, I didn’t mean “out of hand” as in losing space, I meant that it’s easier to understand when the numbers reflect the number of entries in ascending order.

Thanks for the update on the output gist. I’m using an include on `index.php` to include the `output.php` to output the data onto a new page.

@traq

[Alen]

Are you talking about the id’s (auto increment)? You could just get the number by querying the database for the count, you should not depend on the id’s to give you accurate number of entries.

So something like `SELECT count(id) FROM my_table`

[Alen]

You could also recreate the database table once you’re ready for production, then id will be in sequence. Another option is in your phpMyAdmin if you go to Operations link up top, in the options box you’ll see next incremental value, if you only have 14 records in your database this number should be 15. If it says something else, you can change it to next sequential number.

[chrisburton]

@Alenabdula That second part is what I was doing when the latest value was substantially higher. Thanks for that SQL statement. Now that I removed the insert from the page ill be using it on, I don’t think it will be a problem.

[Author]

Posts