Posts by smantscheff

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

If your app doesn't use a browser or a window, how is it connected? Some mobile device? Or a background service or daemon? Can it be programmed to periodically update its status against your database? If this is the case, you could work with a mysql trigger which records the last time of activity and assumes a user as logged out after a certain period of inactivity.

The following construct does the redirect:

if (preg_match( '~iPad;~', $_SERVER['HTTP_REFERER'])) {
  header( "Location: http://$mySpecialIpadSite" );
  exit;
}

Refine the preg expression and substitute your site for $mySpecialIpadSite.

Storing binary image data has its advantages, too. You can easily code for referential integrity without the danger of files getting moved, overwritten or lost. And the application becomes more portable.

PHP ordinarily is used as a server side script language. If you want to create a cookie without connecting to the server again you would rather use JavaScript. [http://techpatterns.com/downloads/javascript_cookies.php]

First of all this looks like really bad database design, storing records of different types in the same table.
That said, go along the line (assuming mysql as your database):

$select = '<select>';
$result = mysql( "select * from code there type_cd = '2'" );
while ($row = mysql_fetch_object( $result )) {
  $select .= "<option value='row->id'>$row->description</option>";
}
$select .= '</select>';

and insert the $select variable in your form.

Look here: http://www.tizag.com/phpT/fileupload.php
Come back if you have problems with this.

Have a look at your server log. Is the server receiving requests from the swf? If yes, are they valid? Put some debugging code in the php script to see if it gets its parameters correctly.

Start here:
http://www.freewebmasterhelp.com/tutorials/phpmysql

You are using security_image.php as the SRC attribute in an IMG tag. If it does not produce an image as output, the browser cannot display an image. So you first have to find out why security_image.php does not generate an image. Maybe your server is configured to serve php files always as text/html and somehow overrides the header() call in this file.

They call it a bug. To get rid of it, try debugging.
Sprinkle your code with print_r() and vardump() to see what's going on.

Is your server set up to show you any errors? You can set the error reporting level in the script with error_reporting(E_ALL) - if your server configuration allows it.
http://php.net/manual/en/function.error-reporting.php

Can you load /contact/security_image.php in your browser? Does it produce an image as output?

Debug all variables of line 31. Probably you'll find that $to contains two domain names - one which the user entered and one ('example.com') which your program added.

You have to order the result set by an inline function like this:

select id, title from product 
order by concat(if(title like "%$user_input%", '000',''), title );

This will precede the title field used for sorting with a literal '000' if the search string (here named $user_input) is part of the title.

How does the server redirect what - probably your browser? Then it's either an HTML header or a meta-tag within the output which is responsible for the redirection.
In the first case you cannot do anything. In the second you can disable redirections in your browser (or use wget or curl) and have a look at the php output. Which will probably not show you anything interesting except the redirection tag.

If you are in training, use the mysql command line. phpMyAdmin can tweak queries and results. As far as I know, command line mysql is the only interface which does no relevant post-processing on the mysql output (except result formatting) so that you can see exactly what the mysql server is telling you.

On my system the output is correct:

drop table if exists pm_user;
create table pm_user (
user_id int(9) primary key,
user_name varchar(100),
user_first_name varchar(100)
);
insert into pm_user values (
2, 'theName', 'theFirstName' 
);

drop procedure if exists Sp1;
CREATE PROCEDURE Sp1(OUT p VARCHAR(100),OUT p1 VARCHAR(100),IN p2 INT)
SELECT user_name,user_first_name INTO p,p1 FROM pm_user WHERE user_id=p2;

CALL Sp1(@Name,@Fname,2);

SELECT @Name,@Fname;
+---------+--------------+
| @Name   | @Fname       |
+---------+--------------+
| theName | theFirstName |
+---------+--------------+

Which interface do you use? Command line mysql? Or phpMyAdmin? Or what?

I don't have a clue, but if this was my problem I would dump the database content and all configuration files and look for the function name. Maybe this provides a hint.
The mysql manual says that you get into trouble if your server runs with the option --skip-grant-tables (http://dev.mysql.com/doc/refman/5.0/en/udf-compiling.html). Maybe you activated this switch during debugging and it is still active?

Or, if you stay with a text field to store the cast, use a regex:

select * from table where cast = "12" or cast rlike "(^12,|,12,|,12$)"

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.

Your problem is bad performance and slow queries. To help you with them we need to see the queries and the table structures. What you posted is the definition for exactly one table. As you tell and as I assume there are more tables involved. What is their definition? What are the slow queries?

You can solve your problem on the application level using a query which checks for row count in table B and only inserts into table A if there are less than 60 rows in B.
You can also have half a solution with a trigger which counts the records in B and provokes an error if there are too many. Like this:

drop table if exists a;
drop table if exists b;
create table a (id integer auto_increment primary key);
create table b (id integer auto_increment primary key);
drop trigger a_before_insert;
delimiter //
create trigger a_before_insert before insert on a
for each row
begin
	declare i integer;
	select count(*) from b into i;
	if i > 3 then 
		set new.id = 1;
	end if;
end;
//
delimiter ;
insert into b values (1),(2),(3);
insert into a values (1);
insert into b values (4);
insert into a values (2);
>> ERROR 1062 : Duplicate entry '1' for key 'PRIMARY'

This way you can avoid the insertion of too many records on the database level, but it is a very dirty solution as it manipulates data and has certain data prerequisites and provokes an error. Maybe someone has a better idea?

The solution is in the last post of mwasif.

Since you explicitly asked for more advice, here it is:
Write in English, not in amputated abbreviations.
Submit complete test cases with CREATE TABLE statements, INSERT data statements and the queries which are problematic. That way others can just paste and copy your test case into mysql and analyze the problem.
Do not abbreviate table names, field names or variable names. Make them meaningful.
When working with databases, solve as much as you can on the database level and only the rest with the display interface. Use the command line mysql to test and refine your solutions.

Either you submit a complete test case with all relevant table definitions, test data and queries which take too long, or you will not get substantial help.
Why do you define latitude and longitute as varchar? Wouldn't they better be floats? Are any slow queries operating on lat/lon ranges? - And with only 15.000 rows in the table, there should not be serious performance issues.

PHP has an interface which submits SQL queries to MySQL exactly as you would enter them in the mysql command line interface or any other interface I know of - because that is the language MySQL is understanding.
The PHP interface has no database abstraction but is a (nearly) 1:1 translation of the MySQL API.
Put your query in a PHP text string or a variable and submit it with the mysql() or the mysql_query() function.

I cannot tell without seeing the table definition and the queries which take the most time.

You are confusing your table names. You should try it with more meaningful ones. Having "table" as table name is not especially helpful.
That said, try it with
SELECT * FROM ofrd_subjects as subjects, fnsh_table as students WHERE students.Subj_No = subjects.Subj_No and remarks = 'passed'

Yes, this way you can order results by any scalar values (even float and double). You can also combine ASC and DESC orders for different result fields.

SQL does not support the concept of "first" and "last" records or a natural record order. All ordering and sorting has to be done explicitly.
Since you use an auto_increment field you can sort your query result by this field for the desired result:

select * from news order by id desc;

But the fact that you don't know this leads me to recommend that you first take a basic mysql primer course before venturing on real database work. Could save you a lot of time.

I'm referring to the command line tool mysql, not to mysqladmin or any GUI. Command line mysql seems to be the only tool around which does not filter any messages you might want to see - in this case the output of "SHOW CREATE TABLE"

Are you using the mysql command line interface? If not, you should.

Recreate the table before each run with a complete "CREATE TABLE" statement.
Or tell mysql:

alter table news auto_increment = 0;

Have a look at the output of "SHOW CREATE TABLE news" which will make things clearer.

Give it at least a try. Set up some SQL code which shows the unfiltered data. Then add the filter as a WHERE clause.
Relate the tables like this:

SELECT * from ofrd_subjects as subjects, fnsh_table as students WHERE students.Subj_No = subjects.Subj_No

Is it a prerequisite that you use unreadable abbreviations as table names?

Of course. Replication is a mechanism to spread a master database to several slave locations. If you can establish establish a connection between your remote and your local machine, then you can set up replication.

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.

What is your comment?

Optimize the slow queries - which may mean: optimize your data structures and your app. Without a test case I cannot be more specific.
Then move the database to a really fast machine. Hardware is so cheap these days that you should at least give it a try before setting up partitioning and replication.

@priteas: As far as I know you're wrong. CSV import is always the fastest with MySQL. If there are foreign keys, foreign key checking should be disabled during import for speed.

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.

There are (linux) load balancers out there (I forgot which) which switch incoming traffic randomly to various web servers (www1, www2 etc.). So install one of those, see how it works and install more web servers as needed. It does not make sense to count users or hits - just have the traffic dispersed to more than one web server.
Regarding the database server I do not see much potential for optimizing. Increasing the file descriptors would help if the system ran out of them - does it? I see the most potential in optimizing your tables and queries, and if that cannot be done or does not lead to significant less load, consider upgrading your database server.
You might also want to look in database partitioning which might be a chance to reduce the server load with several smaller machines. You might consider to run with replicated database servers, too, but if your apps do a lot of updating this might be the wrong way.

For your web server you could quite easily duplicate the installation and install a load balancer. This would allow you to add more (small) machines as necessary.
But on your web server the swap space has not yet even been touched, so you seem to be still on the safe side there. Therefore I assume that the problem is the performance of the database server.
I rented some shared space for my web and db servers on a quite large machine. Here the typical top output:

top - 21:18:54 up 100 days,  9:32,  1 user,  load average: 0.03, 0.01, 0.00
Tasks:  24 total,   2 running,  22 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  109036776k total,    99396k used, 108937380k free,        0k buffers

If I had your problem I would rent such a machine (at www.netclusive.de, if you call them you can also have a one weeks test-drive on the machine) and install the database on it. If the problem goes away you can be sure that the DB performance was you problem.

This is a MS SQL server, not a MySQL problem.

The connections seem o.k.
I don't know about the table locks. Is there a lot of transaction processing going on?
Can you identify the queries in the slow query log which take most time? That might be a starting point for optimizing your apps.
The rest looks fine to me, so I fear you might to have to consider moving to a bigger machine.

It's still quite confusing.
If I get it right, you have the tables
- schedules
- events
- link_schedules
- link_events
with link_schedules and link_events having an ID field which denotes the user and a schedule_id resp. events_id field which links to the schedules resp. events table.
Also the schedule may link to an event with an event_id field.
If this is correct, then you can find out about the number of users (ids) linked to a schedule with the id XXX with this query (not tested):

select count(distinct(id)) from
(
 (select id from link_schedules ls where ls.schedule_id=XXX)
 union
 (
 select id from link_events le 
 join events e on le.event_id=e.event_id 
 join schedules s on s.event_id=e.event_id
 where s.schedule_id=XXX
 )
)

If the database server is running at or above 1.0 most of the time that means that the CPU is busy all the time. This explains the timeouts.
Have a look at the mysql slow query log on the database server and try to identify the problematic queries.
Could it be that your web server does not close the connections properly? What does
mysql> show status
tell you about the connected and running threads?
Can you help your slow queries by defining indexes?

Your problem is in the clause

COUNT(DISTINCT((SELECT id FROM link_schedules WHERE schedule_id = schedules.schedule_id UNION SELECT id FROM
link_events WHERE event_id = events.event_id))) as Count

I don't understand what you are aiming at. Cut out this clause and reconstruct it from scratch. What is it that you want to count?

Yes of course. MySQL is not aware of the OS it's running on.

The left join is exactly for pulling out ALL users, including those who don't have a photo.
Is it correct that you can have several users for the same photo? Otherwise you would not need a join table.
With the join table the query runs like:

select u.id, p.id from users u 
left join users_and_photos u_p
on u.id=u_p.id_user
join photos p
on u_p.id_photo=p.id

You can also have all the photos in one row using the group_concat function.

First you have to check if it is the web server or the database server which has too much load - too much meaning a load average above 1.0 over longer periods of time. If it is the web server, then check the number of page impressions or hits and post it together with the basic machine data (procezssor, speed, RAM). If it is the database server, check the slow query log. If there isn't one, configure MySQL to log the slow queries and check where they come from and how you can optimize them.