Posts by smantscheff

You are confusing file names and page titles.
The page title which a browser shows is stored in the html <title> element.
You can redirect file names in the apache webserver using the redirect module (ModRewrite). If the redirect engine is installed and active and redirection is allowed for your .htaccess file and directory, then you can use .htaccess to redefine file names. Example:

RewriteEngine On
RewriteRule test1 page.php?id=1
RewriteRule test2 page.php?id=2

or more generally RewriteRule test([0-9]+) page.php?id=$1

Set the connection character set to utf-8, too.
mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");
or
mysql_set_charset( "utf-8" );

You cannot reduce the number of queries.
You need a subset of questions, but for each question a complete set of choices.
You cannot do the following because mysql doesn't allow limits in subsets:

select * from questions q, choices c
where q.question_id = c.question_id
and q.question_id in 
(select question_id from questions x where cat_id=3 order by rand() limit 10);

You could first isolate the subset of questions in a separate table:

drop table if exists tmp_questions;
create temporary table tmp_questions like questions:
insert into tmp_questions select * from questions where cat_id=3 order by rand() limit 10;
select * from tmp_questions q, choices c where q.question_id = c.question_id;

You could use INSERT IGNORE.
First INSERT IGNORE into your user table, then into the item table, then in tableA. Then update tableA. You can be sure that the correct data are in it afterwards:

INSERT IGNORE INTO user values('x');
INSERT IGNORE INTO item values(1);
INSERT IGNORE INTO tableA values('x',1,'z');
UPDATE tableA set value='z' where userid='x' and itemid=1;

This is a bit redundant on the query side, but easy to code and to maintain.

Use the replace function:

update theTable set theField = replace( theField, '±', '±');

Obviously your interfaces got mixed up with UTF-8 and other encodings.

Its not too complicated.
You need two fields in your user table: accesstime and ip_address.
When a user tries to login, do as follows:
IF ip_address in database == currently used ip address
OR ip_address in database is empty
OR accesstime is back more than xxx minutes
THEN
accept login
update accesstime
update ip_address in database
ELSE
reject login

When a users logs out, clear the IP field.
With each access of a user to your application, update the access time.

If you want to exclude that your users suffer from an IP address switch during a session, you have to create your own session tokens. Use the rand() function of mysql. Then you need an additional user table field "session_token", and the procedure changes to:

IF ip_address in database == currently used ip address
OR ip_address in database is empty
OR accesstime is back more than xxx minutes
OR submitted session_token is the same as in database
THEN
accept login
create, transmit and store session token
update accesstime
update ip_address in database
ELSE
reject login

It might be a better idea to link the cast to the films in a separate link table with foreign keys to speed up queries and to avoid data corruption.

The code from the mysql prompt goes like

load data infile "george.csv" into table george fields terminated by "\t";

See http://dev.mysql.com/doc/refman/5.1/en/load-data.html

@priteas: No, I only remember dimly to have read it on the mysql website itself that LOAD DATA is the fastest upload operation.

MySQL supports replication. Have your web database as the master and replicate it to your home server - which means in effect that only the changes are transferred to the replica.
Or try the poor man's version: run your web database with a text log and play it from time to time into your local installation.

What's an "item", what is a "course" in your database?

You are mistaking the PHP function parameter for a MySQL input value.
If the PHP value for $email is null, than your query reads: insert into user (username, email) values('somename', ''); because PHP will replace the null value by an empty string.
If you change this query to insert into user (username, email) values('somename', NULL); the database field email will be set to NULL instead of ''.
That's what my str_replace(...) is for.

SELECT CONCAT(ifnull(PropertyRef, ''), ' ', ifnull(SaleType, ''), ' ',ifnull(UnitNo, ''), ' ', ifnull(PropertyType, ''), ' ',ifnull(ProductName, ''), ' ',ifnull(Development, ''), ' ', ifnull(Country, ''), ' ',ifnull(PriceofProperty, '')) AS PropertyRef, PropertyID, ProductTypeID FROM properties

Your last ifnull clause has only one parameter [ifnull(properties.PriceofProperty)].
The ifnull() function returns the first parameter except if it is NULL. In this case the second parameter gets returned.
If the concat() function gets a NULL as one of its parameters, it will return NULL.
Therefore each column has to be guarded against NULL values if you want to use it in a concat function.

This does not help against social engineering, of course. If your customers use to leave their computers unattended while logged in, a VPN does not help too much.
Maybe you are overdoing it anyway. If your data are stored in a mysql database with user rights properly set and with no internet connection (except via the PHP website) and your interface displays only what it may and the webserver is password protected, who would go into the trouble of breaking in for some charity worker's marital statuses? The other info they might find in the phone book, anyway.
Make sure that your website is protected against SQL injection and keep your server up to date. That should frustrate 99% of the script kiddies.

You could also use a VPN between the clients and your server so that the database cannot be compromised via the Internet.

Show the table defnition. In which field are your data stored? Presumably an integer field. Alter it to a time field.

For my part, I'm willing to help with concrete problems and questions, but not to walk you through the basics. There are lots of good material for that out in the wild. I'm not willing to share everything I know because that would mean a fulltime teaching occupation in a medium not exactly suited for it.

If the auditlog table has a primary auto_increment key, you can insert the records into a new table by selecting all fields except the auto_increment field for which you insert a NULL.
Example:

insert into newtable (id, field2, field3, field4) select NULL, field2, field3, field4 from oldtable;

This leads into problems, though, if there are some relations which refer to the id field. So if this does not solve your problem post the table structure and the foreign key constraints or relations.

Use plain brackets () instead of curly brackets {}
Add a semicolon ; after each statement (after "INNODB")

Not in standard SQL. But have a look at the MySQL function GROUP_CONCAT() which will help you.
And submit a complete test case if you want more help.

To retrieve the values, use a SELECT query.
Study the JOIN syntax (http://dev.mysql.com/doc/refman/5.1/en/join.html) and give it at least a try before you ask for help. Come back when you cannot work it out.
To create a table with those values, use the CREATE TABLE AS SELECT ... syntax (http://dev.mysql.com/doc/refman/5.1/en/create-table.html)

select surname, subj_name, mark
from student st
left join exam_marks em on st.stud_id=em.stud_id
join subject su on em.subj_id=em.subj_id

If this does not fit your needs, please post a complete test case with table structure, test data, your query, the expected results and the actual results. Then it will become clearer to you and to us what you are aiming at.

To be more precise, the trigger should not check how many but the maximum id value for cars of this brand and then add one.

Normalization means in effect that you store any piece of information exactly once.
A standard report format may (and should) be based on such a normalization, but contains lots of repeated information and structures. It does not make sense to map a report format 1:1 to a database design scheme.
Start with designing one table for customers, one for items, one for employees and then one for sales and for sales items, which link them all together.
A table named "info" is a guarantee for a troublesome database application. After all, which table does not contain "info" -?

And for summing up the field contents use:

SELECT sum(field1) as s1, sum(field2 as s2 from t2

Maybe you mean something like this:

drop table leagues;
create table leagues (id integer, f1 enum('no', 'yes'), f2 enum('no','yes'));
insert into leagues values 
(1,'no','no'),
(1,'yes','no'),
(2,'no','no');
select id, 
if(sum(if(f1 = 'no', 0, 1)) = 0, 'no', 'yes') as field1, 
if(sum(if(f2 = 'no', 0, 1)) = 0, 'no', 'yes') as field2
from leagues
group by id;

+------+--------+--------+
| id   | field1 | field2 |
+------+--------+--------+
|    1 | yes    | no     |
|    2 | no     | no     |
+------+--------+--------+

You can limit the paged entries in a SELECT DISTINCT clause like that:

drop table if exists a;
drop table if exists b;
drop table if exists ab;
create table a (id integer);
create table b (id integer);
create table ab (a_id integer, b_id integer);
insert into a values (1),(2),(3),(4),(5);
insert into b values (1),(2),(3),(4),(5);
insert into ab values (1,1),(1,2),(1,3),(2,1),(2,2);

/* page 1 */
select a.*, b.*
from a 
inner join (
  select distinct a_id from ab order by a_id limit 0,1
) x
on a.id=x.a_id
inner join ab
on a.id=ab.a_id
inner join b
on ab.b_id=b.id
;
+------+------+
| id   | id   |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
+------+------+

/* page 2 */
select a.*, b.*
from a 
inner join (
  select distinct a_id from ab order by a_id limit 1,1
) x
on a.id=x.a_id
inner join ab
on a.id=ab.a_id
inner join b
on ab.b_id=b.id
;
+------+------+
| id   | id   |
+------+------+
|    2 |    1 |
|    2 |    2 |
+------+------+

Looks to me to a Java question rather than MySQL. In MySQL you cannot pass arrays as query parameters. You would have to explode your array to a concatenation of column names 'a,b,c' and build a query like "select a, b, c from x..."

It's hard to tell without the table structure. How about:

select [B]P.prod_id, [/B]PA.proposal_id, PA.evaluator_ID, PA.Primary_Evaluation_Status_ID, PA.Secondary_Evaluation_Status_ID, U.user_Given_Name, U.user_surname, 
from users U, proposal_appraisal PA, [B]proposals P[/B]
where PA.evaluator_ID = U.user_id 
[B]and P.proposal_id=PA.proposal_id[/B]
group by PA.proposal_id, PA.evaluator_ID 
order by PA.proposal_ID, U.user_ID desc

You have to reset your query cursor. After walking through the result with mysql_fetch_array once there are no more records to be retrieved.
Insert mysql_data_seek($users,0) at the end of the inner loop.

You have to grant the appropriate permissions on your MySQL DB. See http://dev.mysql.com/doc/refman/5.0/en/grant.html
In the MySQL client you have to enter something like:

Grant all privileges on * to myName@% identified by `myPassword`

or, more restrictively,

Grant all privileges on * to myName@myHomeIP identified by `myPassword`

and a

Flush privileges

afterwards.

Long ago I used MS Access as an interface to MySQL (via ODBC) which then had a designer with vizualization of the table relationships.
Navicat has a view designer with some vizualization, but not for table relationships, only for views.
Nowadays I prefer the command line mysql over any of those tools - it's much faster than any GUI. Maybe it has a somewhat steeper learning curve, especially in data definition, but in the long run it pays not to need an GUI, in direct manipulation as well as in database programming. - Though, I concede, for interactive data manipulation Navicat is fine. But its command line client stinks.

So what you want is that user 1 (Ademole Adebayo) appears only once, but once for each ministry, which means twice. So you want user 1 to be displayed exactly once and exactly twice in the same query, which is impossible.
Maybe you would be happier using the group_concat function, with which you could group the result by user-ids and concatenate their memberships in a result field, like in

select id,firstname,lastname,group_concat(ministryname) from vwMembersMinistryDetails group by id

I would go with your proposal 1: Have 1 database with all tables and filter the content depending on the users. This can become tricky, though, if the users have an interface which allows them to directly update the contents of their views.
Proposal 2 is, as you say, bad design, and so is Proposal 3. You don't duplicate structures.
The performance issues, which might arise, can be somewhat dampened with a lot of indexing, but you should do some stress tests with your design before you consider it final.

Maybe your client gets angry because you promised too much?
Are your tables encoded in UTF? Please submit the table design, some test data and a query which should work but does not.

Or drop the quotes altogethere where you do not need them. With table and field names without spaces you need neither quotes nor backticks.

The line

ON (topics.managerId = quiz.".$managerId.")

should read

ON (topics.managerId = quiz.managerId)

By the way: you don't have to exclude PHP variables from double quotes. WHERE topics.$egroup = 1 is easier to read than WHERE topics.".$egroup." = 1

You forgot the GROUP BY:

drop table if exists recipes;
create table recipes 
(id integer
,name varchar(255)
,cat varchar(255)
,instructions text
);
insert into recipes values
(1,'Tacos','Mex.','Mix and Serve'),
(2,'Kabobs','Beef','Combine/Grill');

drop table if exists ingredients;
create table ingredients
(id integer,
recipe integer,
qty integer,
measurement varchar(255),
ingredient varchar(255)
);
insert into ingredients values 
( 1,'1','1','lb','Beef' ),
( 2,'1','1','md','Onion' ),
( 3,'2','1','lb','Beef' );

select r.id, r.name, i.recipe, r.cat, r.instructions, group_concat(distinct ingredient) 
from recipes r, ingredients i 
where r.id=i.recipe group by recipe;
+----+--------+--------+------+---------------+-----------------------------------+
| id | name   | recipe | cat  | instructions  | group_concat(distinct ingredient) |
+----+--------+--------+------+---------------+-----------------------------------+
|  1 | Tacos  |      1 | Mex. | Mix and Serve | Beef,Onion                        |
|  2 | Kabobs |      2 | Beef | Combine/Grill | Beef                              |
+----+--------+--------+------+---------------+-----------------------------------+

MySQL only allows for sorting on values, may they be field values or function results or constants or whatever, but not on the order of entry into the db.
In a client program like phpMyAdmin you could list the data in reverse order, though, by having a routine like

for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--) {
  mysql_data_seek($i);
  $row = mysql_fetch_row($result);
  display($row);
}

But the more natural and easier way would be to enhance the table by an auto_increment field on which you could sort DESC and ASC using the phpMyAdmin interface.

If you want any efficiency drop the PHP interface. Use mysql from the command line to perform your tests. If you don't know it, learn how to use it.
For timing your test use the linux "time" command as in time (echo 'update myTable set myField=myValue' | mysql myDatabase -umyUsername -pmyPassword)

select distinct studentid from results 
where studentid in (select studentid from results where course='mam1')
and   studentid in (select studentid from results where course='phy2');

Test:

drop table if exists results;
create table results (
  studentid integer,
  course varchar(10),
  percent integer
);
insert into results values 
(1001,'mam1', 63),
(1002,'phy2', 55),
(1002,'mam1', 99);
select distinct studentid from results 
where studentid in (select studentid from results where course='mam1')
and   studentid in (select studentid from results where course='phy2');