PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Moderator: General Moderators
Vegan
Forum Regular
Posts: 574 Joined: Fri Sep 05, 2008 3:34 pm
Location: Victoria, BC
Contact:
Post
by Vegan » Sun Feb 26, 2017 8:37 am
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?
Hardcore Games™ Legendary is the Only Way to Play™
My site is powered by LAMP
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Sun Feb 26, 2017 8:46 am
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?
Vegan
Forum Regular
Posts: 574 Joined: Fri Sep 05, 2008 3:34 pm
Location: Victoria, BC
Contact:
Post
by Vegan » Sun Feb 26, 2017 10:23 am
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?
Hardcore Games™ Legendary is the Only Way to Play™
My site is powered by LAMP
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Sun Feb 26, 2017 11:12 am
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?
Vegan
Forum Regular
Posts: 574 Joined: Fri Sep 05, 2008 3:34 pm
Location: Victoria, BC
Contact:
Post
by Vegan » Sun Feb 26, 2017 5:01 pm
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
Hardcore Games™ Legendary is the Only Way to Play™
My site is powered by LAMP
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Sun Feb 26, 2017 6:31 pm
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.
Vegan
Forum Regular
Posts: 574 Joined: Fri Sep 05, 2008 3:34 pm
Location: Victoria, BC
Contact:
Post
by Vegan » Mon Feb 27, 2017 5:31 pm
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;
Hardcore Games™ Legendary is the Only Way to Play™
My site is powered by LAMP