Posts by smantscheff

$tbl_name is not a valid table name.
Please quote the complete error message and show the CREATE TABLE statment for that table.

Do you mean: just the med_names and nothing else? You can of couse select them directly from the items table. But this is not what you mean, I suppose. What do you mean instead? If you want to show some kind of relationship between items and pharmacies you do need the junction table for an m:n relation (if you don't want ugly solutions not in 3NF involving set fields and the like).

Please show a data sample and the CREATE TABLE statement.

Make sure that TaskDueDate contains exactly the date value you want. Or, better, alter it to a date field instead of a datetime field.

Some punctuation marks would make your gibber more readable.
Set the timeouts wait_timeout and global interactive_timeout in your server configuration and restart the mysql server.

Check it out first. I'm not prepared to spell my way through your view full of (problemwise) irrelevant fields, ugly apostrophes and ad libitum formatting (I suppose you are using Navicat?). Submit a clean test case with table structure and test data and I'm willing to delve deeper into it.
Apart from that, the idea of a left join is to have all rows from the first table and all matching data from the second one, with NULL values filled in for non-matched rows in the second table. I think that is what you want.

http://www.webdevelopersnotes.com/tutorials/sql/index.php3
This is just a randomly picked link of the google result for "mysql primer".

Study the LEFT JOIN syntax and semantics. That's what you need.
http://dev.mysql.com/doc/refman/5.0/en/join.html

The httpd.conf timeout is a timeout for the web server, not for mysql. Which language or framework do you use for connecting your website to your mysql server? And how does your code explicitly close the connection? Please show.

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.

This is called screen grabbing, and there are lots of useful techniques for it.
Use wget to grab the whole site and a couple of grep and sed scripts or PHP with preg_match or other regular expressions to extract the relevant content to CSV. Then load the CSV tables into a all-encompassing mysql table and normalize it.

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

Of course, you have to adapt the logic to your query.
Or you can join the hotel table to itself on the hotel_code:

SELECT distinct a.hotel_code FROM facilities a, facilities.b 
WHERE a.hotel_code = b.hotel_code
AND a.facility_name='Parking' and b.facility_name='AC'

But I assume this comes not before lesson 7.

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

If this is an mysql problem, you can check it by logging into your mysql server with the mysql command line client with the same parameters as your website scripts. Then enter "show status" at the mysql command line. If there are a lot of processes running, your script does not come back from its queries and you will have to check it.

Do it in two steps. First without the plot, then update the plot field with the plot field content from the old table.
This assumes, that all non-null plot fields have identical content. If they have not, select the longest field content.

insert into movies_merged (id, title, genre)
select a.id, a.title, group_concat(a.genre)
from movie_genre a
group by a.title
;
update movies_merged a set plot = 
(select plot from movie_plot b where a.title=b.title and plot is not null)
;

This should also be much more performant than the single query.

Try on a smaller sample first.
Set an index on all relevant fields.
Which interface are you using? I would recommend to start the process from a mysql console on the database server. Be prepared to wait quite some time until it finishes (if ever). You can look at what the server is doing with "show status" in a second mysql console.

You can insert a field into a table with the ALTER TABLE statment. Google the mysql.com site for it.
Your screenshot looks like you are using phpMyAdmin. phpMyAdmin has a page for altering the table structure, too.
If you just insert the field into your table, your PHP code must be aware of it to display it properly. So you will have to alter the PHP code.
Did you ever write some PHP code to retrieve values from a database? If not, learn about it. Google for a primer on connecting to MySQL via PHP.

I don't understand. Show some sample code and data.

I don't see any errors in the structure, so presumably it's in the data. Please show them.

First of all make sure that your set definition is complete. As of now, it lacks "Biography" and "Music".

Afterwards go along those lines:

drop table if exists movie_genre;
create table movie_genre
(id integer
,title text
,genre varchar(255)
);
drop table if exists movie_plot;
create table movie_plot
(id integer
,title text
,plot text
);
insert into movie_genre values
(1, 'Copying Beethoven (2006)', 'Biography' ),
(2, 'Copying Beethoven (2006)', 'Drama' ),
(3, 'Copying Beethoven (2006)', 'Music' )
;
insert into movie_plot values 
(1, 'Copying Beethoven (2006)', 'A fictionalized account of the last year...')
;
drop table if exists movies_merged;
create table movies_merged 
(id integer
,title text
,plot text
,genre set ('Action','Adventure','Adult','Animation','Biography','Comedy','Crime','Documentary','Drama','Fantasy','Family','Film-Noir','Horror','Music','Musical','Mystery','Romance','Sci-Fi','Short','Thriller','War','Western')
,rating float
);
insert into movies_merged (id, title, plot,genre)
select a.id, a.title, b.plot, group_concat(a.genre)
from movie_genre a, movie_plot b
where a.title=b.title
group by a.title
;

Beware: MySQL will pick any (presumably the first) ID from the group, as this field is not processed by an aggregate function.

No it doesn't. It shows that you have a table with a field named H1. So what?
If you want to get something in it, use the INSERT statement. If you do not know how to use it study the MySQL manual.
If your question is how to extract the H1 content from a text, there are lots of techniques - it all depends on the programming language. You can do it with a bash script or write a C++ program for it - whatever suits your needs.
But it is still not clear what the problem is and where you are stuck.

If you are not able to prepare some test data - which do not have to be your actual customer's data - you should go back to square 1 and try again.
Apart from that your WHERE clause is quite unreadable. I doubt that even you fully understand what you are doing there.
Bracket your AND and OR clause to avoid logical errors.
Your WHERE clause

(DateSignup BETWEEN '2010-11-01' AND '2010-12-01' OR PaymentCycle = "1" AND DateSignup <= '2010-12-01') 
OR 
(PaymentCycle = "12" AND 
	(YEAR(DateSignup) < YEAR('2010-11-01') 
	AND YEAR(DateSignup) < YEAR('2010-12-01') 
	AND MONTH(DateSignup) >= MONTH('2010-11-01') 
	AND MONTH(DateSignup) <= MONTH('2010-12-1') 
	AND DAY(DateSignup) BETWEEN DAY('2010-11-01') 
	AND DAY('2010-12-01')
))

can be recoded as

(	(DateSignup BETWEEN '2010-11-01' AND '2010-12-01') 
OR 
	(PaymentCycle = "1" AND DateSignup <= '2010-12-01')
)
 
OR 
(PaymentCycle = "12" AND 
	(YEAR(DateSignup) < 2010
	AND YEAR(DateSignup) < 2010
	AND MONTH(DateSignup) >= 11
	AND MONTH(DateSignup) <= 12
	AND DAY(DateSignup) BETWEEN 1 AND 1
))

which is obviously nonsense.

So prepare some test data and we'll see how we can help with that.

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.

Noob there, please write in English.

1) Use the MySQL INSERT statement.
2) Use the MySQL LOAD DATA statement.

If this does not answer your questions, as I suppose, try to be more specific.

This query is not syntactically correct in MySQL. How do you validate it?

Test data would really help.
If I understand you correct, the WHERE condition would change to:

((DateSignup BETWEEN '2010-11-1' AND '2010-12-1') AND (PaymentCycle = "1" OR PaymentCycle = "12")) 
OR 
((DateSignup < '2010-01-01') AND (PaymentCycle = "12"))

Your where clause is in effect the same as facility_name = 'Parking' or facility_name = 'AC' But what you need is facility_name = 'Parking' [U]and[/U] facility_name = 'AC'

I do not see any debugging code in your sample. Add it and come back with the error message for mysql_query. If mysql_num_rows operates on an invalid resource, the error must have happened earlier in your code. I do not see any obvious flaws, though.
If you only want the number of rows, then the COUNT function is more efficient than applying mysql_num_rows on the result.

It is not clear what your problem is. Submit some test data and the results you would like to have.
I only noticed that the in the where clause (DateSignup BETWEEN '2010-11-1' AND '2010-12-1' AND PaymentCycle = "1") OR ((DateSignup BETWEEN '2010-11-1' AND '2010-12-1' AND PaymentCycle = "12") you are duplicating the DateSignup conditon which might lead to erroneous coding. I'd rather code: ((DateSignup BETWEEN '2010-11-1' AND '2010-12-1') AND (PaymentCycle = "1" OR PaymentCycle = "12"))

A query can validate and still have a result with no rows.
Please show your code sample.

Get yourself a MySQL primer to read and study.
If you have exactly one file per user, the administrative data for this file should go into the user_cv table, too.
Otherwise - that is, if there may be more than one file per user - there has to be a connection between the tables, presumably in form of a foreign key field like users_cv_id in the files table. You have to fill this field in the upload routine.
Then you can query both tables with a JOIN (learn about SELECT FROM ... JOIN).

Your query misses a closing bracket )
For debugging purposes add a routine which shows the mysql error messages if a query has no result. Use the functions mysql_errno() and mysql_error().

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.

Learn, read, study. It is the easiest way.
If you run into a problem doing your homework come back for help.

Did you ever think of using Google?
http://www.developer.com/java/data/article.php/3417381

I did not see it either. But I copied the code into a test client which told me what was wrong.

Get yourself a mysql command line client (in the MySQL installation package).
Start it with the parameters you also use in ODBC. It might give you more precise error messages.
A common pitfall is the format of the password. If you are using an old database server and a new client you might have to tell the client to use the old style password encryption. (No, I don't know how to do that.)

Well, I'm not sure if there is a bug. Big queries take some time. I fear that eventually you will have to implement some cache mechanism.
How does the response time change if you select only the ID and the grouped fields? I don't know how MySQL handles this, but I assume there is a lot of data copying going on in the memory when you select all those fields.

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

You cannot do this in one query because a query works in the context of a database connection, and for different servers you need different connections.
So probably mwasifs proposal to use mysqldump is the fastest way. You can do it with one line on the command line of your local machine: mysqldump -h <yourwebhost> -u <user> -p <password> TestDB emptb | mysql TempDB The new table would have the same name as the old one. If you want to rename it, you could either add one line of mysql or filter the above line through sed.

Always provide a full test case.

drop table if exists results;
create table results (user_id integer, test_points integer, test_time integer);
insert into results values
(1,1,1),
(1,3,2),
(1,4,2),
(2,1,5),
(2,4,1),
(2,4,0),
(3,1,1),
(3,2,2),
(3,2,1);

/* expected results :
2 4 0
1 4 2
3 2 1
*/
select user_id, test_points, min(test_time) tt
from results a
where test_points = (select max(test_points) from results b where b.user_id=a.user_id)
group by a.user_id
order by test_points desc, tt asc;

An XML (or object) database would be appropriate for highly hierarchical data which cannot be properly organized in tables. This might be the case if the objects have disparate and different attributes (fields) with little or no common subsets.
Your data sample seems to be a quite ordinary sample for tabular data which are best expressed and stored in a standard SQL database.
I don't know about SQLite, but for MySQL there is a bunch of editing tools out there in the wild (Navicat, HeidiSQL, phpMyAdmin, to name a few). The command line client mysql is still the best for efficient work and for scripts which convert from one format (XML) to the other (SQL).

Please explain the difference between LEFT JOIN and LEFT OUTER JOIN in MySQL. To the best of my knowledge there isn't any.

On second thoughts your query seems to be flawed.
You are mixing grouped with non-grouped fields. The result of the non-grouped fields is indeterminate. See http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html There is says:

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

What do you expect e.g. as the result for product_id in the same row with min(price) and max(price)? Your query assumes that all fields except the grouped fields have the same content, which is obviously not true. So I wonder how you get the expected results with this query.
Consider this example which will show the abitrarily chosen product_ids:

drop table if exists products;
create table products (
	id integer primary key auto_increment,
	price integer,
	category integer,
	model_no integer
);

insert into products values 
(NULL,1,1,1),
(NULL,1,2,2),
(NULL,1,3,3),
(NULL,2,1,4),
(NULL,2,2,1),
(NULL,2,3,2),
(NULL,3,1,3),
(NULL,3,2,4),
(NULL,3,3,1),
(NULL,1,1,2),
(NULL,1,2,3),
(NULL,1,3,4),
(NULL,2,1,1),
(NULL,2,2,2),
(NULL,2,3,3),
(NULL,3,1,4),
(NULL,3,2,1),
(NULL,3,3,2),
(NULL,1,1,3),
(NULL,1,2,4),
(NULL,1,3,1),
(NULL,2,1,2),
(NULL,2,2,3),
(NULL,2,3,4),
(NULL,3,1,1),
(NULL,3,2,2),
(NULL,3,3,3);

select id,min(price) as mip, max(price) as map, model_no, category from products where category = 1 group by model_no;

Try first to retrieve the minimum/maximum price for each category (use only aggregate functions), then retrieve in a second query the products which match those prices.

What did you try already and what did not work as expected?

Build a reference table with all month names and left join it to your data table.

I never dived into table partitioning. But as I understand it, with partitions you split tables so that they reside on several machines, and you set the condition on which the data are split. Thus by partitioning on product categories it might be possible to spread the workload to several machines while keeping the higher level database structure intact.

How can you have an index on "Group_by(clean_modelno, publishercategory) BTREE[/*]" - an index on a group by clause? This is possible?
What are the results of "EXPLAIN your_query" ?

There is no database application which you could "open". MySQL does not come with a GUI. MySQL server usually runs as a service. You can connect to the server with various command line client programs like mysql, mysqladmin, mysqldump etc. which you find in the mysql/bin folder.