CREATE TABLE

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

Post Reply
User avatar
Vegan
Forum Regular
Posts: 574
Joined: Fri Sep 05, 2008 3:34 pm
Location: Victoria, BC
Contact:

CREATE TABLE

Post 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?
Hardcore Games™ Legendary is the Only Way to Play™
My site is powered by LAMP
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: CREATE TABLE

Post 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?
User avatar
Vegan
Forum Regular
Posts: 574
Joined: Fri Sep 05, 2008 3:34 pm
Location: Victoria, BC
Contact:

Re: CREATE TABLE

Post 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?
Hardcore Games™ Legendary is the Only Way to Play™
My site is powered by LAMP
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: CREATE TABLE

Post 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?
User avatar
Vegan
Forum Regular
Posts: 574
Joined: Fri Sep 05, 2008 3:34 pm
Location: Victoria, BC
Contact:

Re: CREATE TABLE

Post 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
Hardcore Games™ Legendary is the Only Way to Play™
My site is powered by LAMP
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: CREATE TABLE

Post 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.
User avatar
Vegan
Forum Regular
Posts: 574
Joined: Fri Sep 05, 2008 3:34 pm
Location: Victoria, BC
Contact:

Re: CREATE TABLE

Post 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;
Hardcore Games™ Legendary is the Only Way to Play™
My site is powered by LAMP
Post Reply