php-general Digest 14 Mar 2010 11:14:37 -0000 Issue 6638
Topics (messages 302788 through 302795):
Re: Migration Scheme - from one mysql DB to another mysql DB
302788 by: MEM
Re: I need a fresh look at storing variables in MySQL
302789 by: Paul M Foster
302790 by: Paul M Foster
302791 by: Jochem Maas
302792 by: Rene Veerman
302793 by: Rene Veerman
302794 by: Ashley Sheridan
302795 by: Rene Veerman
Administrivia:
To subscribe to the digest, e-mail:
php-general-digest-subscr...@lists.php.net
To unsubscribe from the digest, e-mail:
php-general-digest-unsubscr...@lists.php.net
To post to the list, e-mail:
php-gene...@lists.php.net
----------------------------------------------------------------------
--- Begin Message ---
>
> I'd like to know
>
> - what type of hosting and OS the 2 databases are running.
> - if they're (staying) on the same machine, and if not: whats the
> available bandwith (up&down, low&peak) between the machines? any cost
> to that bandwith?
> - if you have admin rights on both databases.
> - how often you need to do this and for how much data (in Mb)
>
Thank you all for your replies.
I will leave you with more details, but, I believe the "single query
approach with two PDO (or mysqli) connections should be quite well. Both DB
are on the same server, Linux based one, but different dsn should apply.
The bandwith cost will not be an issue, because we are talking about 15MB of
data, or something similar.
I don't have admin rights but I can try to have them if I must to.
I believe this will be a single time, or, one in a long time run but, we can
never predict.
The destination table also has some random data filled fields that I need to
generate and introduce with the old data on this new database.
Wondering if mySQL Migration Toolkit can do the job?
Thanks a lot for your help,
Márcio
--- End Message ---
--- Begin Message ---
On Sat, Mar 13, 2010 at 02:45:37PM -0500, Phpster wrote:
> I'd go with a mysql data modelled approach as it will allow mysql to
> do lots of the heavy lifting during analysis as you've mentioned. If
> there are a lot of entries, it's gonna get complex and expensive
> memory-wise to manage XML or session based datasets.
>
> Plus having each question as it's own record should give you greater
> flexibility in packaging the data for analysis and reporting.
>
+1
I invariably find that the original design for a project needs to be
tweaked. The customer wants to add or delete questions, they want to
add/change reports for the data. So I nearly always approach this kind
of project this way. Maybe:
vote table:
id serial/sequence not null primary key
voter_id int references voters (voter_id)
question_id varchar(10)
answer varchar(10)
You can easily subset by voter, or by question ID. Or analyze answers in
relation to other answers, etc.
Paul
--
Paul M. Foster
--- End Message ---
--- Begin Message ---
On Sat, Mar 13, 2010 at 09:04:46PM +0100, Rene Veerman wrote:
<snip>
>
> and the OP may not have good db design skills yet.
> for a noob, it's one timeconsuming thing to build a datamodel, but
> it's harder to get it efficient yet simple.
>
<snip>
>
> OP: if you need a mysql datamodel for reports, i'm willing to give it
> a free shot. i'm sure others here would too, or improve upon mine.
> It's probably not more than 3 tables i think.
> Let us know eh..
Tedd's perfectly capable of speaking for himself, but I can tell you
he's been on this list for a long time, and his skills are plenty
adequate for this task. He's just asking for second opinions.
Paul
--
Paul M. Foster
--- End Message ---
--- Begin Message ---
Hi Tedd,
just a few thoughts that might help ...
Op 3/13/10 6:10 PM, tedd schreef:
> Hi gang:
>
> I just completed writing a survey that has approximately 180 questions
> in it and I need a fresh look at how to store the results so I can use
> them later.
first off - wasn't there a cut'n'dried piece of survey software out there
that did the job? don't know off hand what the 'market' currently offers but
I'm pretty sure there are a number of candidate php-based wotsits.
as such they might be worth looking at just to check out their data models.
> The survey requires the responder to identify themselves via an
> authorization script. After which, the responder is permitted to take
> the survey. Everything works as the client wants so there are no
> problems there.
>
> My question is how to store the results?
>
> I have the answers stored in a session variable, like:
>
> $_SESSION['answer']['e1']
> $_SESSION['answer']['e2']
> $_SESSION['answer']['e2a']
> $_SESSION['answer']['e2ai']
> $_SESSION['answer']['p1']
> $_SESSION['answer']['p1a']
> $_SESSION['answer']['p1ai']
>
> and so on. As I said, there are around 180 questions/answers.
>
> Most of the answers are integers (less than 100), some are text, and
> some will be null.
>
> Each "vote" will have a unique number (i.e., time) assigned to it as
> well as a common survey id.
what happens when 2 people vote at the same time?
>
> My first thought was to simply record the "vote" as a single record with
> the answers as a long string (maybe MEDIUMTEXT), such as:
>
> 1, 1268501271, e1, 1, e2, 16, e2a, Four score and ..., e2a1, ,
that would make life very difficult if you wanted to use the
> Then I thought I might make the data xml, such as:
>
> <survey_id>1</survey_id><vote_id>1268501271</vote_id><e1>1</e1><e2>16</e2><e2a>Four
> score and ...</e2a><e2ai></e2ai>
doesn't seem like XML is the answer at all. isn't it Larry Garfield with the
sig line that says:
Sometime a programmer has a problem and thinks "I know I'll use XML",
now he has 2 problems.
:)
> That way I can strip text entries for <> and have absolute control over
> question separation.
>
> Then I thought I could make each question/answer combination have it's
> own record while using the vote_id to tie the "vote" together. That way
> I can use MySQL to do the heavy lifting during the analysis. While each
> "vote" creates 180 records, I like this way best.
is there only ever going to be one survey of which the questions/structure
is now fixed/definitive?
if so I'd probably opt for the simple approach of a table
with 180 columns purely because that would make for the easiest
reporting queries (no self-referencing joins needed to answer the
question posed below ... which would be the case if you normalized
the data to one row per question+answer+vote[r])
... although possibly not speediest in terms of SQL performance
(you'd have to be careful with creating lots of indexes because that
would affect insert performance)
basically one table with 180 answer columns and an addition primary [voter?]
key,
possibly also a survey id if your going to be repeating the survey over time.
a more normalized approach would be to define all the questions and their
answer types in one table, surveys in another, with answers per qestion in
another:
survey table:
id INT (PK)
name VARCHAR
date TIMESTAMP
questions table:
id INT (PK)
position INT - order of questions
survey_id INT
question VARCHAR/TEXT
question_type ENUM?
voters table:
id INT (PK)
name VARCHAR ??
answers tables:
id INT (PK)
voter_id INT
question_id INT
answer ?
with the answer values in the answers table you might consider a field for
each question_type you define so that you can use a proper data type - this
would be somewhat denormalized because you'd only ever use one of those fields
per row but it might come in handy.
--- End Message ---
--- Begin Message ---
On Sun, Mar 14, 2010 at 7:18 AM, Paul M Foster <pa...@quillandmouse.com> wrote:
>
> Tedd's perfectly capable of speaking for himself, but I can tell you
> he's been on this list for a long time, and his skills are plenty
> adequate for this task. He's just asking for second opinions.
>
Wouldn't someone with adequate DB skills know if he(/she) even needs
to build a datamodel, and given the simplicity of this one, how? Based
on what i mentioned earlier, type and amount of use of stored reports?
I don't mind noobishness in any area, but i have learned to keep code
as simple as possible.
BTW;
- as always, i recommend adodb.sf.net for DB abstractions.
- if you are storing in DB and displaying from DB later you need to
prevent code injections (sql, html, js, flash) by pushing all strings
used in sql insert- and update-fields;
$sql = 'insert into table (field1_int, field2_string,etc) values
('.$field1.', "'.antiSQLinjection($field2).'", ...);
I'm using this function atm, maybe someone can improve upon it. This
disables all sql injections, and strips all html, js & flash.
function antiSQLinjection ($string) {
//anti SQL injections:
if (phpversion() >= '4.3.0')
{
$string = mysql_real_escape_string($string);
}
else
{
$string = mysql_escape_string($string);
}
if(get_magic_quotes_gpc()) // prevents duplicate backslashes
{
$string = stripslashes($string);
}
//anti HTML/JS/flash injections (into searchterms, for instance):
$string = strip_tags ($string);
return $string;
}
--- End Message ---
--- Begin Message ---
On Sun, Mar 14, 2010 at 8:22 AM, Jochem Maas <joc...@iamjochem.com> wrote:
>
> first off - wasn't there a cut'n'dried piece of survey software out there
> that did the job? don't know off hand what the 'market' currently offers but
> I'm pretty sure there are a number of candidate php-based wotsits.
>
> as such they might be worth looking at just to check out their data models.
>
+1, good point.
I know there are free cloud services for dutch petitions and surveys,
i bet there are for english too.
A google for "free online survey hosting" will reap many such sites.
--- End Message ---
--- Begin Message ---
On Sun, 2010-03-14 at 11:15 +0100, Rene Veerman wrote:
> On Sun, Mar 14, 2010 at 7:18 AM, Paul M Foster <pa...@quillandmouse.com>
> wrote:
> >
> > Tedd's perfectly capable of speaking for himself, but I can tell you
> > he's been on this list for a long time, and his skills are plenty
> > adequate for this task. He's just asking for second opinions.
> >
> Wouldn't someone with adequate DB skills know if he(/she) even needs
> to build a datamodel, and given the simplicity of this one, how? Based
> on what i mentioned earlier, type and amount of use of stored reports?
>
> I don't mind noobishness in any area, but i have learned to keep code
> as simple as possible.
>
> BTW;
> - as always, i recommend adodb.sf.net for DB abstractions.
> - if you are storing in DB and displaying from DB later you need to
> prevent code injections (sql, html, js, flash) by pushing all strings
> used in sql insert- and update-fields;
> $sql = 'insert into table (field1_int, field2_string,etc) values
> ('.$field1.', "'.antiSQLinjection($field2).'", ...);
>
> I'm using this function atm, maybe someone can improve upon it. This
> disables all sql injections, and strips all html, js & flash.
>
> function antiSQLinjection ($string) {
>
> //anti SQL injections:
> if (phpversion() >= '4.3.0')
> {
> $string = mysql_real_escape_string($string);
> }
> else
> {
> $string = mysql_escape_string($string);
> }
>
> if(get_magic_quotes_gpc()) // prevents duplicate backslashes
> {
> $string = stripslashes($string);
> }
>
> //anti HTML/JS/flash injections (into searchterms, for instance):
> $string = strip_tags ($string);
>
> return $string;
> }
>
That function won't always work. You're using a PHP version check for
mysql_real_escape_string() when the most likely failure point for it is
if no database connection has been opened.
Also, you shouldn't strip the tags from a string that's being inserted
into the database. strip_tags() is for the display of data on a web
page. It's best practice not to alter the actual data you've stored but
to convert it once it's displayed. Don't forget that the browser display
may not be the only use for that data.
Thanks,
Ash
http://www.ashleysheridan.co.uk
--- End Message ---
--- Begin Message ---
On Sun, Mar 14, 2010 at 11:16 AM, Ashley Sheridan
<a...@ashleysheridan.co.uk>wrote:
> That function won't always work. You're using a PHP version check for
> mysql_real_escape_string() when the most likely failure point for it is if
> no database connection has been opened.
>
I never call it without an open db connection..
>
> Also, you shouldn't strip the tags from a string that's being inserted into
> the database. strip_tags() is for the display of data on a web page. It's
> best practice not to alter the actual data you've stored but to convert it
> once it's displayed. Don't forget that the browser display may not be the
> only use for that data.
>
Let's call that a coder's / payer's preference..
If i'd need human text, i'd want to strip it of computer code before it
enters the db. Possibly log the attempt to insert code.
--- End Message ---
