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;