[PHP-DB] One field or a new table?
Hey Guys, I have been developing for a while and never found the best solution yet. The question is lets say a game has 10 collectibles you can earn, to keep track of the number of collectibles each user has, do you have one field in the users table with all the numbers separated via a divider, or do you make a new table called collectibles and have each one as a field? These are high traffic sites (100,000+ people) and so I was initially thinking the solution of creating a separate table is best because the main users row is loaded on every page, and on top of that you would need to use explode on the field. Currently I am having a separate table, but I was wondering if people have better solutions. Hussein J.
Re: [PHP-DB] PHP-MySQL connection for particular module
I should've checked the assumption.
A little strange though for a scripting language. I'd say, since
default behaviour of mysql_* functions is to assume the last opened
link when a parameter is not passed, and since default behaviour is to
evaluate null, false and 0 the same, mysql_* functions should issue a
warning when a not valid link is passed but still use the last opened
link (in that logic, null should also generate a warning though). I'm
guessing the reason is that mysql_connect returns false when it fails,
but i can't see a reason why an undefined variable cannot be used.
All that and I don't remember what it was for.
On Fri, Jun 20, 2008 at 1:57 AM, Chris [EMAIL PROTECTED] wrote:
[new code]
if (!mysql_connect($a, $b, $c)) return;
if (!mysql_select_db($dbname)) return;
$res = mysql_query(SELECT * FROM manual;, $link);
[/new code]
Isn't going to work because $link is not defined and definitely not a
resource like mysql_query expects.
OR, optionally, to surpress the warnings:
[new code]
if (!mysql_connect($a, $b, $c)) return;
$link = null;
if (!mysql_select_db($dbname)) return;
$res = mysql_query(SELECT * FROM manual;, $link);
[/new code]
Isn't going to work because mysql_query needs a resource to connect to.
You've defined $link as null.
$ cat test.php
?php
$user = 'my_db_user';
$pass = 'my_pass';
$host = 'localhost';
$db = 'my_db';
error_reporting(E_ALL);
ini_set('display_errors', true);
if (!mysql_connect($host, $user, $pass)) {
die(unable to connect);
}
if (!mysql_select_db($db)) {
die(unable to choose db);
}
$link = null;
$res = mysql_query('select version() as version', $link);
while ($row = mysql_fetch_assoc($res)) {
print_r($row);
}
$ php test.php
Warning: mysql_query(): supplied argument is not a valid MySQL-Link
resource in /path/to/test.php on line 19
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL
result resource in /path/to/test.php on line 20
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] One field or a new table?
Whenever you can say: X has zero or more Y and Y has zero or more X you're talking about a Many to Many relationship between X and Y, and you need a link table with foreign key constraints. Example: Table X: id name 1 A 2 B 3 C Table Y: id name 1 K 2 L 3 M Linktable: X_id Y_id 1 1 1 2 2 3 2 1 3 1 3 2 3 3 The link table is the only table that needs to be updated when a relationship between an entry of X and an entry of Y starts to exist (in your example, a user from X collects an item from Y). It is wise to use foreign keys when using linktables - but you can only do this when using INNODB. Check http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html Evert On Fri, Jun 20, 2008 at 8:38 AM, Hussein Jafferjee [EMAIL PROTECTED] wrote: Hey Guys, I have been developing for a while and never found the best solution yet. The question is lets say a game has 10 collectibles you can earn, to keep track of the number of collectibles each user has, do you have one field in the users table with all the numbers separated via a divider, or do you make a new table called collectibles and have each one as a field? These are high traffic sites (100,000+ people) and so I was initially thinking the solution of creating a separate table is best because the main users row is loaded on every page, and on top of that you would need to use explode on the field. Currently I am having a separate table, but I was wondering if people have better solutions. Hussein J. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL circular buffer
Hello, I'm looking at implementing a database-level stack for a (multi stack) mail merge queue (able to queue up to 1 million messages per stack). Current workflow: server 1 (containing main db) transmits data sets (used to populate mail merge template) to server 2 server 2 web-facing script (script 1) puts data sets onto stack (db) server 2 mail process script (script 2) pulls single data block (say 100 rows) from front of stack (fifo), merges the data with the template and sends data to smtp process server 2 script 2 removes processed block of rows The problems I am considering include keeping track of the value of the primary key across multiple instances of script 2 (the script will run from a cron), whether to select by limit or range (i.e. stack_id 500 stack_id 601 vs where stack_id = 500 limit 100) and looping the index back to zero while ensuring there is no data that hasn't been deleted. So - it seems easier to avoid these problems and implement a circular buffer :) What I would like to know is if anybody has experience implementing this sort of data structure in MySQL (linked list?) or any advice. There don't seem to be any current implementations so the last question is - is there a good reason for that? Too many overheads? I know this sort of structure is best kept in memory and not on disk, but I am not sure of any other solution to a queue this size. Any comments welcome. Many thanks, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
