Best approach for adding timestamp to return most popular rows

[computerbarry]

Looking good @r937

I’ve done as suggested from #17 everything seems to be working ok, no breakdowns. And the hits table is also setup with two columns venue_id and dtstamp both index and PK.

then you can create the hits table as i outlined before, with venue_id as FK to id

Do you mean add the FK onto venue_id inside the new hits table, which is also the PK?
Is the hits table acting as the child table to venues table?

Or do I need to put the FK on id inside the venues table?

---

I’ve also realised that all my tables are using MyISAM I understand FKs can only be used on InnoDB.

The new hits table is InnoDB, my question, will the venues table break, cause any issues if I change from MyISAM to InnoDB which has lots of live data and queries?

Does sql act differently when using different storage engines? Meaning do I need to write the queries/code in a different way

Barry

[r937]

@computerbarry

… two columns venue_id and dtstamp both index and PK.

just to confirm what you mean by “index and PK” could you please do a SHOW CREATE TABLE

@computerbarry

Do you mean add the FK onto venue_id inside the new hits table, which is also the PK?
Is the hits table acting as the child table to venues table?

Or do I need to put the FK on id inside the venues table?

the most effective way i can think of to answer this question is to give you two hints:

1. FK always work in one direction only, from the dependent table to the table it references. In other words, one table, often called the parent, acts as the repository of valid values, and the other table, often called the child, is not allowed to have any values in it that cannot be referenced in the parent table

2. after i said “with venue_id as FK to id”, which direction do you think i meant?

@computerbarry

I’ve also realised that all my tables are using MyISAM I understand FKs can only be used on InnoDB.

fortunately, converting the tables is easy… i’ll let you google it

@computerbarry

Does sql act differently when using different storage engines? Meaning do I need to write the queries/code in a different way

at what time in the past have you ever coded any sql differently because of the target table type?

[computerbarry]

CREATE TABLE `tbl_venue_hits` (
 `venue_id` int(11) NOT NULL,
 `dtstamp` datetime NOT NULL,
 PRIMARY KEY (`venue_id`,`dtstamp`),
 KEY `dtstamp` (`dtstamp`,`venue_id`),
 CONSTRAINT `tbl_venue_hits_ibfk_1` FOREIGN KEY (`venue_id`) REFERENCES `tbl_venues_innodb` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I’ve added a FK as test also, based on my assumptions

@r937

after i said “with venue_id as FK to id”, which direction do you think i meant?

Venue to hits table
Hits table is the child and should have the FK?

If correct, I just read columns with FK can not be unique, venue_id inside hits is PK and unique, no?

fortunately, converting the tables is easy… i’ll let you google it

Yes I have done that ha cheers, easy than I thought

at what time in the past have you ever coded any sql differently because of the target table type?

I haven’t ha. Ok - good learning exercise, thank you.

Barry

[computerbarry]

Once we move forward @r937

I was previously using the below to update the hits column on my venues table:

$mysqli->query("UPDATE tbl_venues
  SET hits = hits + 1
  WHERE venue_id='" . str_replace("-", " ", strtolower($_GET['venue_id'])) . "'");

I’ll now need to update the hits_table to accomplish the same functionality, how should I change this now, is this an easy fix?

Barry

[r937]

you can keep doing that if you wish

however, you also need to insert a row for each hit into the venue_hits table

[computerbarry]

Ok nearly complete @r937

A couple of important questions so I can bring this to a close.

1. Is SHOW CREATE TABLE snippet in my reply in #23 correct?
2. As mentioned about the hits venue_id which is unique PK also FK to venues table. Is this correct?

The reason I ask, what I read…

"Foreign keys are almost always “Allow Duplicates,” which would make them unsuitable as a Primary Key.

Instead, find a field that uniquely identifies each record in the table, or add a new field (either an auto-incrementing integer or a GUID) to act as the primary key.

The only exception to this are tables with a one-to-one relationship, where the foreign key and primary key of the linked table are one and the same

After reading these comments, and if we stick with the PK and FK combination in the hits table, that would mean we’re using a one-to-one relationship, correct?

And could I use the same combination with a one-to-many relationship?

however, you also need to insert a row for each hit into the venue_hits table

How would the SQL look, little example?

Thanks, Barry

[DaveMaxwell]

@computerbarry

After reading these comments, and if we stick with the PK and FK combination in the hits table, that would mean we’re using a one-to-one relationship, correct?

And could I use the same combination with a one-to-many relationship?

Who ever wrote that should be smacked. A PK does not need to be a autoincrement field or a GUID. They are convenient when you’re using it as a FK to another table, but even then, they aren’t required. TECHNICALLY, a primary key isn’t required, period. Strongly recommended, and required by a lot of entity frameworks, but otherwise not required.

But your venue_id, dtstamp will most likely give you a unique value, so it’s fine for a primary key. And it will be, by definition, a one-to-many relationship because you will have multiple entries in tbl_venue_hits for a particular venue_id which ties to tbl_venues.

[computerbarry]

@DaveMaxwell

Who ever wrote that should be smacked.

Best quote today!

@DaveMaxwell

But your venue_id, dtstamp will most likely give you a unique value, so it’s fine for a primary key. And it will be, by definition, a one-to-many relationship because you will have multiple entries in tbl_venue_hits for a particular venue_id which ties to tbl_venues.

Thats great Dave, thanks for clearing this up. Puts my mind at ease

Couple of final questions:

1. So in theory, I can create other FKs from other tables also pointing to the venues table id?
   Reason being, I have a comments table, photo table about the same venue which I’ll be able to connect once I convert my tables to InnoDB.
2. What is the best way to add a row/hit to my hits table based on the snippet I mention in #24
3. Since updating my database from MyISAM to InnoDB my row count has now become a minus -49, is the normal?

Cheers, barry

[DaveMaxwell]

@computerbarry

1. So in theory, I can create other FKs from other tables also pointing to the venues table id?
   Reason being, I have a comments table, photo table about the same venue which I’ll be able to connect once I convert my tables to InnoDB.
2. What is the best way to add a row/hit to my hits table based on the snippet I mention in #24
3. Since updating my database from MyISAM to InnoDB my row count has now become a minus -49, is the normal?

1. Yes
2. Rudy already answered that above
3. I’m pretty sure having less than a row is impossible

[Mittineague]

@DaveMaxwell

I’m pretty sure having less than a row is impossible

Yes.

If a numeric field isn’t UNSIGNED it is possible it could be negative.

So it might be your query is returning a value instead of a count.

Else I’d guess there is an error in you (PHP?) code that is doing subtraction incorrectly.

[computerbarry]

Yes Ruby did mention though not sure how/correct way to code this?

Simple INSERT as below?

Knowing that one column is timestamp and the other is auto_increment, do I need the values?

mysqli->query("INSERT INTO table_hits (column1,column2)
VALUES (value1,value2)
WHERE venue_id='" . str_replace("-", " ", strtolower($_GET['venue_id'])) . "'");

Barry

[computerbarry]

I’ve just read:

If you look closely, you’ll notice it’s not a negative sign, it’s a tilde, which means “approximately”.

InnoDB tables do not store the exact count of rows in the table, so you are being shown approximately how many rows are in the table.

Though looks like a minus.

If a numeric field isn’t UNSIGNED it is possible it could be negative.

Should I make it unsigned, this is how it should be setup from the beginning?
And is it ok for FK to be unsigned?

Update
I’ve just made column unsigned, nothing changed.

[DaveMaxwell]

@computerbarry

mysqli->query(“INSERT INTO table_hits (column1,column2)
VALUES (value1,value2)
WHERE venue_id='” . str_replace(“-”, " ", strtolower($_GET[‘venue_id’])) . “'”);

Nope. It needs to be a standard insert (note, this is not the secure way to do it, and I’m too tired to look it up…

mysqli->query("INSERT INTO table_hits (venue_id, dtstamp)
VALUES ('" . str_replace("-", " ", strtolower($_GET['venue_id'])) . "', NOW()");

[computerbarry]

@DaveMaxwell

(note, this is not the secure way to do it, and I’m too tired to look it up…

No worries
The helps, I have been using prepare stmt which I think will make this more secure.

Thanks, barry

[computerbarry]

If anybody can help, small error when I run:

INSERT INTO tbl_venue_hits(venue_id, dtstamp) VALUES ('venuename',NOW())

Error

Cannot add or update a child row: a foreign key constraint fails (tbl_venue_hits, CONSTRAINT tbl_venue_hits_ibfk_1 FOREIGN KEY (venue_id) REFERENCES tbl_venues_innodb (id))

Barry

[r937]

[quote=“computerbarry, post:35, topic:242540, full:true”]
If anybody can help, small error [/quote]
it’s telling you that you’re trying to violate the FK constraint

you’re trying to add a row with a value for venue_id that doesn’t exist in the id column of tbl_venues_innodb

it’s doing exactly what you asked it to do – preventing you from inserting invalild data

[computerbarry]

@r937

it’s doing exactly what you asked it to do – preventing you from inserting invalild data

I did realise this, now we know things are setup correctly - a good protection.

you’re trying to add a row with a value for venue_id that doesn’t exist in the id column of tbl_venues_innodb

So what is the correct way r937?

Barry

[r937]

@computerbarry

So what is the correct way r937?

do not insert a row into the hits table for a venue that doesn’t exist in the venues table

[computerbarry]

do not insert a row into the hits table for a venue that doesn’t exist in the venues table

But thats the thing r937… ‘venuename’ is a row inside the venues table, it’s the venue_id of a venue, I just change it here to venuename for example.

What have I missed?

Barry

[computerbarry]

Ok, I’ve just added:

INSERT INTO tbl_venue_hits(venue_id, dtstamp) VALUES (1,now())

Which worked.

I now have one row inside the hits table:

venue_id : 1
dtstamp: 2016-11-09 22:38:51

How am I suppose to link this to the venue_id inside venues?
This could be anything.

How do I relate and pass the correct venue_id whith this?

Very confusing @r937