Posts by smantscheff

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

You are probably looking for the mysql function LAST_INSERT_ID()

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

Have a look at the date/time functions: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
To avoid conversion issues use something like

SELECT * FROM timetable WHERE unix_timestamp(time) - unix_timestamp() < 3600

Alternatively you could use the timediff() function.

Does the LIMIT clause work in this condition? I never tried it, but I know that you cannot use LIMIT in a subquery like in SELECT a FROM (SELECT b FROM c LIMIT x).

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?

If your question is whether you can use the "CALL" statement in PHP against an MySQL server, the answer is YES. The version of your MySQL client is irrelevant.
You can test it with

delimiter //
create procedure t()
begin
  update a set x = 1;
end
//
delimiter ;

drop table if exists a;
create table a (x integer default 0);
insert into a values (0);

and the php program

<?php
require_once('db.php');
mysql_connect( MYSQL_HOST, MYSQL_USER, MYSQL_PWD );
mysql_select_db( MYSQL_DATABASE );
mysql_query( 'update a set x = 0' );
mysql_query( 'call t' );

Result

select * from a;
+---+
| x |
+---+
| 1 |
+---+
1 row in set

So I wonder why you don't just try.

Try to ask an understandable question; then you will likely get an helpful answer.

What has this to do with mysql?

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.

Please show the table structure (in form of a CREATE TABLE statement). Maybe your datetime fields are in unix seconds format? Also please show the row with the HotelID 14 which should match your query.

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.

What did you try already that did not work for you?

If phpMyAdmin does deliver correct results and your PHP query does not, this is most likely due to the fact that phpMyAdmin issues the correct character set statements. You php script seems to use the wrong character sets.
Have a look at http://dev.mysql.com/doc/refman/5.1/de/charset-connection.html

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.

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

If your code is not relevant, what is? Does it mean you didn't even try a solution?

Test this one:

create table tBefore 
(id integer,
y integer,
z integer,
p integer);
insert into tBefore values
(1,1,9,8),
(2,4,7,10),
(3,7,12,3);

create table tAfter
(id integer not null auto_increment primary key,
y integer,
z integer,
p integer);
insert into tAfter (y,z,p) (select y,z,p from tBefore order by p);
select * from tAfter;

And what do you mean by "order it randomly"? Maybe you just replace "order by p" in the above query with "order by rand()";

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

Please show us your code.

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.

Your query makes no sense, since it is an union of a set and a subset of this set.
This query will always return the whole set - the entire view.
The desired result makes no sense, either. What are the selection criteria so that user 1 (Ademole Adebayo) shall be listed with "Women @ West St.", but not with "Mothers and Toddlers"? This user has two roles in different ministries and is therefore listed twice. If she was to be listed only once, why with "Women @ West St." and not with "Mothers and Toddlers"?
Thanks a lot for submitting a complete test case right away.

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.

Use an IF clause, like in

select sum(amount * if(ordertype='BUY',1,-1)) from mytable

If this does not suit your needs, submit a test case.

There are more than one problems with your syntax.
The INDEX() clause does not necessarily need a name, but at least one field. The name goes before the brackets, the indexed fields inside them.
The FOREIGN KEY clause needs the name of the foreign key column inside the brackets.
The REFERENCES clause needs the name of the referenced column inside the brackets.
Your table is lacking the UserID field on which you want to reference the user table.

And a tip: For basic development don't use phpMyAdmin but the MySQL command line. It's a lot faster, you can edit your queries and you will learn faster.

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

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

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

http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

As far as I understood it the query optimization uses only one of the indexed fields in the query execution. For some reasons it concludes that this field is CATEGORY_ID and therefore chooses an index which contains this field. The second field in your index is not taken into consideration by the optimizer. So for this query you - to the best of my knowledge - might drop the second index.

If the numerical fields are either 0 or the desired value, you may use the maximum of each field. Replace your IF clauses by max() if(o.sales_stage='Prospecting', o.amount, '') as PROSPECTING becomes max(o.amount) as PROSPECTING in your query.
Or, if you have several rows for each category, you might use the sum() instead of the max() function - depends on the semantics of your data.

So which part of your problem is it you don't know? Have a look at the server variables like $_SERVER and the preg_replace function. Anything else you need?

To the best of my knowledge you cannot do this in SQL or MySQL, for that matter.

If you have access to PHP, you can read all column names in an array with mysql_field_name() and then sort and traverse the array.

Also have a look into the INFORMATION_SCHEMA database which contains information about all tables and columns. But don't you ever update it.

The obvious solution would be

select ExamId,StudentId,Mark from marks where (ExamId,Mark) in 
	(select ExamId as e, mark as m from marks 
		order by mark 
		limit 2
	);

But MySQL replies with an error:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

From a practical point of view it's only a minor inefficency to do two queries, one for the distinct ExamIds and one for each ExamId group, as you proposed. But I must concede that it would be more elegant to have it all in one query.

Also the question arises which results you would like to have. Do you want to limit the number of exams returned or the the number of distinct marks?
Example: If you want the two best results, are this in my example (physics,dinky,1) and (physics,bofur,1). Or does (physics,gollum,2) also belong to the result because mark=2 is the 2nd best result?

Maybe you could design a nested subquery which utilizes the min/max aggregate function but excludes the values already consumed one level above.

Your query is fine. The database server crash must have other reasons.

I'm setting up a SOAP service using the PHP SOAP server and client library.
I've coded some structured data types into my wsdl file with some heavy restrictions.
Do you know of functionality in the PHP SOAP library or of a 3rd party library which will check the actual parameters in the the server against the definitions of the WSDL file? Or do I have to code all my parameter checking explicitly into my SOAP server class, even though it is already coded in the WSDL?

If you chose to argue instead of repairing your test case until at least the problem becomes clear, fine with me.
If you instead opt for getting helped, please submit a reproducable test case.

[The above post was erroneously sent twice.]