Posts by smantscheff

Mysql has a nifty function for it: group_concat
Like in

select org_name, group_concat(activity_name)
from organisation o, activities a, org_activity oa
where o.org_id=oa.org_id
and oa.activity_id=a.activity_id
group by o.org_id
;

(Code not tested)
Alternatively you may look at the various JOIN syntax varieties of MySQL.

How does a single entry look like? How do you compute bags_packed? Is it a sum or a count or a field value?

As I said before, it's not a good idea to store easily computable values in a database. What for do you need a computed subscription date if the subscription period is stable? It only makes sense if you allow manual changing of the subscription end.

Apart from that, for a subscription reminder you have to set up a periodical process which does the checking. Such a mechanism is neither built into the database nor in PHP. Under linux you can set up a cronjob which regularly executes a script which does the checking. (For Windows, google "cronjob windows"). The script would run along the lines:

$q = myqsl('select * from customers where date_add(now(),interval 1 week) > subscription_end_date');
while ($record = mysql_fetch_array($q))
  mail($record['email'],'Subscription about to end', 'bla bla');

[By the way, are you working for my brother or what do you mean?]

Storing computable values is never a good idea, except for caching and performance reasons.
You could define a query or a view instead like as

select *, date_add(registration_date, interval 6 month) as subscription_end from mytable;

I never saw this php syntax before. Maybe you try value= "<?php echo $itemID; ?>" instead.
And if removing the foreign key constraint removes your problem, than you have a problem, because the foreign key constraint makes a lot of sense. You would not have any orphan records, would you?
And, as I said before, debug the content of your $sql variable before you submit it to mysql. I'm pretty sure there is no value for itemID in it.

Let's see how you did it.

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.

As I said before, the problem is a null value for itemID in postad.php: <input type="hidden" name="itemID" /> The value="..." attribute is missing in the input tag.

This cannot be the whole story. The error message starts with "Error, query failed" which is not a mysql error message and which I do not find in the code. Seems that you have edited the script again before posting.
The error is that the hidden input field named itemID does not have a value. You might have seen it if you had a debug output of the insert query which you are trying to submit.

Please show us your script to determine what is going wrong.
And your first "CREATE TABLE categories" statement cannot be correct. It references an index field (itemID) which is not in the table. Please submit the table structure as shown by "SHOW CREATE TABLE categories" and not your edited version.

MySQL is similar with Access in being a relational database. If you use InnoDB as storage engine you can have foreign key constraints to guard the relational integrity. It has stored procedures, triggers and views - I don't know if Access has those.
A nice GUI for MySQL is Navicat. A browser-based alternative is phpMyAdmin (somewhat clumsy to use, though).
I recommend learning the command line interface, though. It is by far the fastest way to get things done.

Go learn about regular expressions and the program grep. There are a lot of editors which can search for regular expressions, too. I recommend EditPad++
But this was not the error, though.
Look at

INSERT INTO content (publisher_code,book_isbn,book_title,artist,song,page_num) VALUES ('HL','0-634-01176-6','The Greatest Rock Guitar Fake Book','The Who','My Generation',277)

The semicolon is missing at the end.

Well, then show us your script. The error will be in there.
And no, the circumflex ^ means "not" in regular expressions. So my expression intends to search for a (single) quote, followed by anything which is neither a quote nor a comma, followed by a quote, followed by something not a comma.

If you restore your database from a dump file the error message should tell the correct line number of this file.
Most common problems when inserting from a dump are non-escaped quotes in field content. Search for a regular expression like "'[^',]+'[^,]" (not tested) which might find unescaped quotes in your input.

http://dev.mysql.com/doc/refman/5.1/en/windows-installation.html

Depending on your MySQL setup it might be that you have to use the OLD_PASSWORD function:
http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_old-password

I mean each of the SELECT clauses up to the following union, like
(SELECT a FROM x...) UNION (SELECT b FROM Y order by z) UNION (SELECT...)

Put parentheses around the subqueries. And google for "incorrect usage of UNION and ORDER BY"

You would need a limit clause in your subquery, but MySQL doesn't support this yet.
And since you are mixing logic and layout anyway, maybe you would be better off using a procedural language to display your query results.
What do you mean by but

I just cannot get it to accept the changes.

? What is the error message?

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.

What do you mean - once I revert --?
I once ran into this bug, too. I think it really is a bug, MySQL not properly handling situations with low disk space. But this was once in 15 years, so I forget.

You have to group your query by the date:

select date(from_unixtime(registered)) as theDate, count(*) from ucard group by date(from_unixtime(registered))

For further help please submit the table structure and some test data.

What I would try:
- Dump the table to a text file, drop it and recreate it from the dump.
- Restart the server.

Your table and/or index file(s) are corrupt. Restore them from a backup or try to repair them. Have a look at http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html

Post a full test case with table creation and data insertion code together with your presumably erroneous query.

Create the table structure by
mysqldump myDatabase -w0 | mysql myNewDatabase
Then dump your contents with myssqldump -T myDatabase
Then you have a bunch of files (one for each table) which you can load with "LOAD DATA".

And are you sure that your mysql daemon runs as root? This would be a very unusual setup. If I remember right the "SELECT ... INTO OUTFILE" runs with the privileges of the mysqld process, not with those of the mysql user or the unix user who called mysql.

And I forgot to ask:
What value does name have after the insert? Is it NULL or an empty string? Could it be that somewhere in your configuration the emtpty string is set as a default?

In my version of MySQL (5.1.34-community) your code produces the desired error:

Field 'name' doesn't have a default value

So after your INSERT there is really a new row in your table? Or do you just suppress the error messsages?

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

I'm not sure what you mean.
If you want to know if a point in time has passed for a certain time span use something like

SELECT * from mytable where TIMEDIFF(startdate,NOW()) > '48:00:00'

That's what I meant by doing the math in the database, not in PHP.
And yes, of course, your time fields should be of type TIME or DATETIME, respectively.

You should solve the math on mysql level, not in php.
Have a look into the date/time functions: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Maybe you should find a Mssql forum.
And try to ask well defined questions there. Otherwise consult Google.

If you are using Navicat don't expect performance. Navicat is an easy interface, but not performant. Also it seems that you are transferring databases from one server to another one using your local PC as the middle man. This means that all the traffic is being downloaded to your PC and uploaded to the 2nd server.
Look into mysqldump and the "LOAD DATA" statement of mysql. These are the most performant tools for the task.
Dump the source database, zip it, transfer it to the target, unzip it and feed it into the target using "LOAD DATA". You need of course shell access to both source and target server.

I believe that John Linux did some thinking already before posting his problem.
Apart from that, your solution is technically more efficient while mine IMHO is better readable.
And what I do not like about your solution is that you have to change it in more than one place (WHERE and HAVING) when you add more conditions.

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');

In my experience the fastest method would be:

1) Copy only the structure of the source to the target.
2) Dump the table contents to single CVS (TXT) files.
3) Load the text files with "Load data..."