Page 1 of 1

CREATE TABLE

Posted: Sun Feb 26, 2017 8:37 am
by Vegan
I am aware that for any database you need to have a unique key value of some kind or another, I usually use INT(8) but its probably overkill

Code: Select all

$sql = "CREATE TABLE adverts (
id BIGINT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
expires DATETIME NOT NULL,
affiliate_code VARCHAR(255) NOT NULL,
locale VARCHAR(2) NOT NULL,
impressions INT(4) NOT NULL,
max_impressions INT(4) NOT NULL
)";
missing anything?

Re: CREATE TABLE

Posted: Sun Feb 26, 2017 8:46 am
by requinix
Vegan wrote:I am aware that for any database you need to have a unique key value of some kind or another,
Not really, no.
Vegan wrote:missing anything?
Probably want an index or two in there.

But do you really expect to ever have more than 2.1 billion ads?

Re: CREATE TABLE

Posted: Sun Feb 26, 2017 10:23 am
by Vegan
It's the principle, I have a fundamental objection to short word containers

the only real index I can see if maybe the locale? Maybe the ad to update the impressions?

Code: Select all

CREATE INDEX locale_index ON adverts (locale);
CREATE INDEX adffiliate_code_index ON adverts (adffiliate_code);
any other thoughts on indexing my example? the fields I have now seem basic but what more do I really need?

I was expecting that a $ads = a bunch of results from MySQL as an array that I can then echo to the container I am in via a for loop or something?

Re: CREATE TABLE

Posted: Sun Feb 26, 2017 11:12 am
by requinix
For indexes you need to think about how you're going to be using the table.

What are the queries you're going to be running on it? What columns will you include in a WHERE and/or ORDER BY?

Re: CREATE TABLE

Posted: Sun Feb 26, 2017 5:01 pm
by Vegan
the idea mainly will be

SELECT affiliate-code, impressions FROM adverts WHERE locale = $location

then with an onload() event

impressions += 1;

UPDATE impressions FROM adverts WHERE affiliate-code = $advertisement

Re: CREATE TABLE

Posted: Sun Feb 26, 2017 6:31 pm
by requinix
Vegan wrote:SELECT affiliate-code, impressions FROM adverts WHERE locale = $location
Then an index on locale would be good.
Vegan wrote:impressions += 1;

UPDATE impressions FROM adverts WHERE affiliate-code = $advertisement
And one on affiliate_code.

But you're not actually doing a $impressions++ and UPDATE impressions = $impressions, right? That's no good for concurrency. Should be an UPDATE impressions = impressions + 1.

Re: CREATE TABLE

Posted: Mon Feb 27, 2017 5:31 pm
by Vegan
so is this a better syntax?

Code: Select all

UPDATE impressions = impressions + 1 FROM adverts WHERE affiliate-code = $advertisement
now dealing with a bunch of advertisements, I guess I can use a basic for loop?

I guess if I want to add more funtionality I can use

Code: Select all

ALTER TABLE adverts ADD COLUMN new_field field_type;