StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Well, to be perfectly honest, I voted to close, because the way you're going about this is just a recipe for disaster. You can't go ahead and create a new database for every school. What you <em>really</em> shouldn't be doing is use a deprecated extension. What you're asking is similar too: <em>"Hey, I've put convicted paedophiles to work in a nursery, but there are complaints... How shoudl I handle them?"</em>. The only right answer is to <em>not</em> do what you're doing.</p> <p>For a kick-off: You're assuming people will nicely put in a valid DB name in the form... They could exploit the form and enter something like:</p> <pre><code>tbl`( id INT AUTO_INCREMENT PRIMARY KEY, field_name1 VARCHAR(255) NOT NULL PRIMARY KEY, -- normal fields field_name2 INTEGER(11) NOT NULL PRIMARY KEY, field_name3 VARCHAR(255) NOT NULL DEFAULT '', inserted TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP )ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_general_ci; -- </code></pre> <p>Which is valid SQL, but rather messes up your query. This assumes the user knows about injection, though. Even if they don't, what makes you think that any school name is a valid DB name? Suppose a school like <em>"Institute of Numbskulls"</em> or, a non-fictional one: <em>"M.I.T"</em>. Of course that won't work as a DB name!</p> <p>What you should do is have a simple table, called <em>schools</em>, that looks like this:</p> <pre><code>+----------+----------+ | id | name | +----------+----------+ | 12 | foobar | | 15 | M.I.T. | +----------+----------+ </code></pre> <p>Now every school has its own unique id, in all your other tables, like student login, add a field <code>school_id</code>:</p> <pre><code>//tbl student_login +--------------------+--------------------+--------------------+--------------------+ | id | school_id | login | passw | +--------------------+--------------------+--------------------+--------------------+ | 1 | 12 | Bobby | hash | | 2 | 15 | Bobby | hash | +--------------------+--------------------+--------------------+--------------------+ </code></pre> <p>Now every record of every student is linked to the correct school. What's more, you can now use all the goodies a DB offers, like foreign keys, stored procedures, views and what have you... Make the tables relational!</p> <p>If you want to get all student logins for <em>foobar</em> students: Easy:</p> <pre><code>SELECT students.login FROM schools LEFT JOIN studend_login AS students ON schools.id = studends.school_id WHERE schools.name = 'foobar'; </code></pre> <p>You can create a view per school, so that you don't have to keep on writing the same join over and over, if you really want...<br/> If a school should happen to close/not require your services any longer, simply delete it from the <code>schools</code> table, and thanks to some cleverly placed foreign keys you can set your tables so, that all records in the other tables that link back to that school are deleted, or updated (soft-delete), too.</p> <p>A quick google for <em>"Relational design mysql"</em>, brought me <a href="http://arachnoid.com/MySQL/relational_databases.html" rel="nofollow">here</a>, I haven't read through it, but the diagrams do show what relational design is about: more tables, containing less data, but with proper indexes and keys</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload