Query Fails In MySQL 5

Mysql Query Fails Due To A ' In Data.
I am having problems now with the following code.

An example of my topic.

"we'll meet again"

But when the following line searches for it i get an erro saying that the code is wrong.

I know that it is to do with the ' , but how do Iget round this problem i have. without having to remove all of them from the data.

$topics = mysql_query("SELECT * FROM `forum_topics` WHERE `topic` = '" . $topic . "'") or die(mysql_error());

---

Update Query Fails In Previous MySQL Version
The following query runs fine on my development server (v4.0.17):

"update user inner join events on user.username = events.username
set user.LastMessageDate = '$current_time'
where events.text_sent=0 and events.to_mobile >0 and events.GMT_event_send_time<='$send_time'"

but fails on my production server running v3.23.56.

Anyone any ideas why it doesn't run and what I need to do to fix it?

---

Using IF() For Choosing The Query Fails
The following code is for the Archive. (Where users can store special messages)
I want to receive one row for each message that has been sent OR that has been received.
With the code beneath I get this error: "#1241 - Operand should contain 1 column(s)". How can I fix this?

INFO AND CODE ......

---

Cross Table Query Fails
I get this error which I can't quite understand....PHP Code:

 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select x10_channel from devices where device = 'Lamp')' at line 

The line mentioned involves a cross table query:PHP Code:

 $sql = "select commandID from commands where x10_channel = (select x10_channel from devices where device = '$device')"; 

which works perfectly on a friends laptop. I'm guessing there is a configuration difference between our installations.

---

Insert Data Into 2 Tables With 1 Query Fails
I found 2 old threads on how to insert data into 2 tables with 1 query. Both of them said that i should seperate the inserts with a ;. But i must be doing something wrong, because it comes up with an error like this ".....right syntax to use near ' INSERT INTO eiland_details"

What am i doing wrong? It's possible, right? To insert data into multiple tables with 1 query?

$query = "
INSERT
INTO sub_pages
(mainpage_id, intro_text)
VALUES
('$main_data->mainpage_id','$sub_intro');
INSERT
INTO eiland_details
(inwoners, oppervlakte)
VALUES
('$detail_inwoners',$detail_oppervlakte')";

---

Mysql Fulltext Search... Fails???
Something seems to be wrong. See, I make query:
MySQL
SELECT  DISTINCT g.GameID, g.Title, p.FullName PlatformFROM game gINNER  JOIN platform p ON p.PlatformID = g.PlatformIDWHERE g.`Title`  LIKE  '%age%'

I get results like:
res.txt
That's OK.
And when I make query:

MySQL
SELECT DISTINCT g.GameID, g.Title, p.FullName PlatformFROM game gINNER JOIN platform p ON p.PlatformID = g.PlatformIDWHERE MATCH(g.`Title`) AGAINST('age')
OR

MySQL
SELECT DISTINCT g.GameID, g.Title, p.FullName PlatformFROM game gINNER JOIN platform p ON p.PlatformID = g.PlatformIDWHERE MATCH(g.`Title`) AGAINST('age' IN BOOLEAN MODE)
It gives me NOTHING!
Empty set and that's all... What's wrong???

---

Mysql 4.x Week Function Fails
I think there is a bug in mysql 4:

select week("2003-08-15") from po.orders limit 1;

produces: 32

after:

set session default_week_format=3;

it produces: 33 (which is correct in the Netherlands)

The documentation states:

set global default_week_format=3; (As root!)

This command should set the default_week_format. This command fails:

mysql> set global default_week_format=3;
Query OK, 0 rows affected (0.00 sec)

mysql> select week('2003-08-14');
+--------------------+
| week('2003-08-14') |
+--------------------+
| 32 |
+--------------------+
1 row in set (0.00 sec)

This result is incorrect. It should be 33.

If I use:

mysql> set session default_week_format=3;
Query OK, 0 rows affected (0.00 sec)

mysql> select week('2003-08-14');
+--------------------+
| week('2003-08-14') |
+--------------------+
| 33 |
+--------------------+
1 row in set (0.00 sec)

This is result is correct...

I use mysql 4.0.14. I also did the same test on mysql 4.1 alpha, which has
the same results.

Harm de Laat
Informatiefabriek
The Netherlands

---

Remote MySQL Connection Fails
I have two XP computers, with the same version of MySQL on each. I
have the same database structure and data on each. Each computer has
the same user name, and each MySQL database has the same user names.
Locally on each, I can connect and work with the data. I can ping
each computer from the other computer. My problem is that when I try
to connect from one computer to the other I get the following error:
Host 'XXX.XXX.XX.XX' is not allowed to connect to this MySQL server.
This happens on both computers. I can not find the reason for the
error.

---

Remote MySQL Connection Fails
I have two XP computers, with the same version of MySQL on each. I
have the same database structure and data on each. Each computer has
the same user name, and each MySQL database has the same user names.
Locally on each, I can connect and work with the data. I can ping
each computer from the other computer. My problem is that when I try
to connect from one computer to the other I get the following error:
Host 'XXX.XXX.XX.XX' is not allowed to connect to this MySQL server.
This happens on both computers. I can not find the reason for the
error.

---

MySQL Fails To Launch On Mac OSX Command Error
I just bought kevin Yanks "build your own database driven website using php & MySQL" and I was going step by step on the installation process for Mac OSX users. After I installed MySQL and attempted to login using the:

shell%sudo/usr/local/mysql/bin/mysqld_safe

command i get this error:

[Denise-Womacs-Computer:~] denisewo% shell%sudo/usr/local/mysql/bin/mysqld_safe
shell%sudo/usr/local/mysql/bin/mysqld_safe: Command not found.

---

MySQL Fails To Start After Reboot In Win2K
I get this error (code 87).
I install MySQL 5.0, everything is fine.
I can work with it all day, no problem, all month, no problem.
I reboot ONCE, it fails to start automatically.
I manually start it... FAIL AGAIN.
I reinstall, all ok.

I catch too much info on google and I've read like 20 pages without any solution... just the same problem.....

---

Fails Normalization?
I have a massive table with around 10 million records. Probably 50% of these records have redundant information. For example maybe 1 column out of 15 is different between 2 or 3 different records. Note: the address is divided into pieces in the actual database, but I wrote it as one field in this example.

ID...Name...Address....Cat....Lat..........Long...
123 ABC 123 Main St 45 3387287 -12077273
124 ABC 123 Main St 87 3387287 -12077273
125 ABC 123 Main St 64 3387287 -12077273
etc
I'm thinking that if I could collect all these Cat values and put them into 1 single column, maybe seperated by semicolons or something, that I could improve the performance of queries on the data.

ID...Name...Address.........Cat........Lat..........Long...
123 ABC 123 Main St 45;87;64 3387287 -12077273
etc
The main problems I forsee with this method is if my user is searching for Category 64, I'll have to use a LIKE '%64%' statement or something inefficient.

Another possible solution might be to separate the data into normalized tables such as this:

AddressTable:
ID...Name...Address.......Lat..........Long...
123 ABC 123 Main St 3387287 -12077273

CatsTable:
ID...AddID...Cat
1 123 45
2 123 87
3 123 64
I'm afraid that if I have a 5 million row AddressTable and a 10 million row CatsTable that running constant joins would bog down the system.

---

Update Sql Fails For 4.0.26
I executed the following sql commands on 4.0.26(linux)

drop table gtest;

create table gtest(id int default 116, name varchar(20) default 'peter');

insert into gtest(id, name) values(100, default);

select * from gtest;

+------+-------+
| id | name |
+------+-------+
| 100 | peter |
+------+-------+

1 row in set (0.00 sec)

Then i tried the following

update gtest set id=default;
update gtest set name=default;

I get the following error

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'default' at line 1

I looked up the docs and its a SQL stmt parse error.

---

Update Fails
I'll put this very simply, will give my current code if necessary (first trying to explain it here I came to an conclusion I'd better erase it and only ask..)

I have a script, which by using mysql_affected rows, checks if UPDATE query was used to update already existing data. If that fails, INSERT query will create that data. However Mysql only returns affected rows as true, if the old data is different than the new one.

What I'm asking here is - Which do you think is the best way to update - or if nothing to update - insert new data to sql. I guess REPLACE wont work when it should be done by id fields?

---

Replication Fails
we want to set up a replication server to distinguish between reads and writes
where one database server is for reads (m1), and the other is for writes (m2)
m1 will replicate the data from m2
so that when we serve pages, users only connect to the reads database server
now, what we need is a system whereby we are notified if replication stops for whatever reason
so that we can switch reads to the write server temporarily or restart the slave
does anyone know of an app or way to get alerted as soon as replication stops?

---

DNS Lookup Fails
I have a mysql server that is set up in a subdomain. I also have all my permissions set to restrict users from this domin (i.e.) 'user'@'%.sub.domain.com'. On ocassion the DNS lookup seems to fail, and the IP doesn't from which the user is connecting to doesn't get resolved to a DNS name. In such cases, users can not connect because the domain name is no longer being used as the host, but the ip. The mysql server is running on windows. I don't beleive this to be an error with mysql, but i'm wondering if there is some kind of mysql.hosts file in which i can enter the ip's and respective DNS entries for known connecting computers? I'm just fishing here, maybe there is another work around for this, but i want to avoid having to make duplicate entries for each user: one for the DNS and one for the IP.
These occurences are few and far between, but most other windows apps don't seem to be to affected by this, i suppose because of the way it caches domain names, but that is just a guess.

---

Restore From Sql Dump Fails
I have just been given laptop to configure with mysql and I have installed my usual proven 5.0.27.

When I try to re-install DBs from sql dumps, it only restores very small files and stops after a few thousands lines when attempting the bigger sql files.

I have therefore updated to the latest 5.0.45 but I am still having the same issue. Has anybody experience something similar on windoz?

---

Insert Fails On Access
I have a database with about ten tables. I'm trying to fill one table with
some dummy data (its a contact manager table holding names of people,
addresses and whatever but I'm createing random values for the moment). The
insert fails telling me that access is denied - however I use the exact same
username/password/database name/host name on the other tables, and the
insert and updates work just fine.

I know this sounds odd - but is there any other reason as to why I might get
the access denied message, other than what I assume should be just for an
invalid username/password? Code:

---

Setting Up Password Fails
Having a problem setting up a password for MySQL

The reported problem when attempting to set up a root password is;

C:mysqlin>mysqladmin version
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to MySQL server on 'localhost' (10061)'
Check that mysqld is running on localhost and that the port is 3306.
You can check this by doing 'telnet localhost 3306'

When checking this:

C:mysqlin>telnet localhost 3306
Connecting To localhost...Could not open connection to the host, on port
3306: C
onnect failed

---

ON UPDATE CASCADE Fails
I have a table which has a foreign key relationship with itself. I want and expect my updates to cascade (deletes definitely cascade as expected) but instead I just get error 1217: foriegn key error.

I have written example code to use in reproducing the problem: Code:

---

Delete Foreign Key Fails
I have 2 tables (create is below).

table 1 is a "person". A person has a "status". So, status table contains, for example, "dead", "alive", "mutant", etc etc...

Now, the question is - why cant I delete from table "person"? I get this message:

"Cannot delete or update a parent row: a foreign key constraint fails"

Now, I think the "person" is the child table, and the "status" the parent. I only want to delete the child (a person record),and leave the "parent" or lookup value there. I hope you can help, many thanks!

Here are the create tables:

CREATE TABLE `status` (
`status_id` int(10) unsigned NOT NULL auto_increment,
`description` varchar(40) default NULL,
PRIMARY KEY (`status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 3072 kB'

CREATE TABLE `person` (
`person_id` int(10) unsigned NOT NULL auto_increment,
`MembershipNumber` varchar(32) default NULL,
`Title` varchar(10) default NULL
code...

---

Setting Password Fails
The reported problem when attempting to set up a root password is;

C:mysqlin>mysqladmin version
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to MySQL server on 'localhost' (10061)'
Check that mysqld is running on localhost and that the port is 3306.
You can check this by doing 'telnet localhost 3306'

When checking this:

C:mysqlin>telnet localhost 3306
Connecting To localhost...Could not open connection to the host, on port
3306: C
onnect failed.

---

Database Connection Fails
I've been having problems with my CGI script, it overloads the
server. I think it's because the database connection fails because
the following error is logged whenever the server overloads.

DBI->connect(server=localhost;database=Employee) failed: at db.pm
line 53
db.cgi:: Could not connect to database
db.cgi:: No database connection exists
db.cgi:: No database connection exists

And I use these settings to run mysqld.

/usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr
--datadir=/var/lib/mysql --user=mysql
--pid-file=/var/run/mysqld/mysqld.pid --skip-locking

I read some documentation that skip-external-locking should be used
instead of skip-locking, but that seems like a minor thing. I'm not
entirely sure it is the database connections, I'm just trying to
eliminate possible causes.

---

PHP Mail() Function Fails Due To SQL Errors
I'm able to write to my database, but something is going wrong with SELECT queries. I'm trying to post MySQL data by email. I set an error message to display if mail() failed, and I got SQL error messages. I got a lot of messages like:

Error 1054 : Unknown column 'address_name' in 'field list'

Not knowing what else to do, I deleted each line of my SET list include file that had fields that yielded this message one by one. Finally, I got a null error message:

Error 0:

---

Insering Data Into Bit Field Fails
I know there's an issue with mySQL not being able to display BIT values, but that's not the issue, it's just not inserting them.

Here's some info about server / table before i explain the problem.

mysql Ver 14.12 Distrib 5.0.24a, for pc-linux-gnu (i486) using readline 5.1

The field causing problems is bit(3)

Right, when I do...

INSERT INTO waypoints (lat_long,date,type,marker) VALUES('12313123132,1231321','1174415400','2','1');

I get the line inserted and I get a warning, as follows.

"Out of range value adjusted for column 'type' at row 1"

At first I assumed this was because I was entering a decimal number, but when I do an update it inserts the decimal number correctly.

---

Mysql_fetch_array Fails Bit Does Not Send An Error
while($n<$rows) {
echo "<tr>";
$query="SELECT * FROM `survey` WHERE `id`=$id";
$data=mysql_query($query) or die ('<b>ERROR 5: unable to retrieve data from database: '.mysql_error().'. Please try again later.</b>');
$row=mysql_fetch_row($data) or die ('<b>ERROR 6: unable to fit data into an array: '.mysql_error().'. Please try again later.</b>');
//......
//code
//......
$n=$n+1;
}

I get "ERROR 6..." thrown at me, but there is no error message from mysql to display. If it helps, I'm using mysql v4 not v5. If you need more surrounding code, just ask.

---

Timezone Test Fails With 4.0.18 On Linux?
I recently installed MySQL 4.0.18 on my slackware 9.1 machine (kernel
2.4.25) by compiling the source. It works fine, and all tests pass
except one. The timezone test. Can someone tell me why I'm getting
this error? Is there something with the TZ variable I have to set for
the shell? Code:

---

SQL Fails On MySQL4 To MySQL5 Upgrade
I have some SQL that works perfectly in mysql4, but fails when it's run in mysql5, on exactly the same schema. I'm not an expert and can't work out what's wrong. Can anyone spot what's going on? The error message is

Unknown column 'head.id' in 'on clause'

The SQL is
SELECT head.*, types.*, count(review.headphoneId) as reviews FROM headphone head, headphonetypes types LEFT JOIN review ON review.headphoneId = head.id WHERE types.headphoneTypeId = head.typeId GROUP BY head.id ORDER BY head.model .

---

Datetime Field Default Now() Fails
I have a mySQL database table with a column field datatype of datetime that I set up in the original create table statement like this:

create table nnet_usermetadata (
....
nnet_record_entered datetime default 'now()'
)

However, upon execution, each time a record is entered into nnet_usermetadata the results are:

00-00-0000 00:00:00

Is there a way I can always ensure that the current date and time are always entered into the datetime field?

---

Deamon Fails To Start In Windows
I've experienced this a couple of times on different Windows
workstations and it's driving me crazy. I've installed mysql-4.0 from
the installer to c:mysql. Then just for kicks I try to start it by
double clicking mysqld-nt.exe.

It opens a window and hangs for about
15 seconds then closes. So i opened a command prompt and ran it, same
thing. I've discovered that if I put anything on the command line as
a parameter it will start. Also, installation as a service is failing
because it sets the service up to run "c:mysqld-nt.exe MySQL" which
is the wrong location. I setup the my.cnf from those provided in the
install and it still doesn't work. I put garbage characters into the
my.cnf file to see if it was even reading it. It did nothing. BUT
then I ran it with "mysqld-nt.exe --" and it complained about the
screwed up conf file. If I remove the my.cnf file and run it with
"mysqld-nt.exe --", it works.
So I can't do mysqld-nt.exe log-error"
because it works right then. Can I get logging without that
parameter?

---

Select On Float-column Fails
Tried executing the following 3 SQL statements:

CREATE TABLE tab (floatval FLOAT(7, 3));
INSERT INTO tab VALUES (2.400);
SELECT * FROM tab WHERE floatval='2.400';

The last select doesn't return any rows in MySQL ver 4.1.9-nt. Why? If I change the value to 2.500, a row is returned.

---

UPDATE Fails On Somewhat Large Data Sets
I am running into a problem with an UPDATE statement. I am using MySQL 5.0 server, and the mysql command line client that came with it.

update tableA a, tableB b set a.value = b.value where a.key1 = 'foo' and a.id =
b.id and a.col2 = b.col2;

The tables use InnoDB as the engine. With small data sets, this works fine. However, with larger data sets, the UPDATE runs for a long time, then I get an error saying that the number of locks exceeds lock table size. In my case, the data sets in the two tables are < 5 million rows. I kept monitoring the InnoDB status, and see the number of log entries go up until finally rolling back.

I saw a bug on the mysql bugsite: http://bugs.mysql.com/bug.php?id=15667 which sounds like the problem I am facing - though my data sets are not as large as the bug states.

How do I overcome this issue? I was thinking about splitting the query into smaller ones, using LIMIT and doing some sort of ordering to guarantee same order of rows retrieved.

Any better ideas? Maybe configuring the InnoDB differently (I read that the lock table size cannot be changed, darn.)

---

Solaris 8 Daemon Fails Because Unknown Option?
I guess people did not see my post from the install thread... I am really really stuck now! Does anyone work on Solaris???

Whatever option I use to start the daemon I get this:

050310 15:27:35 mysqld started
050310 15:27:35 [ERROR] /opt/mysql/bin/mysqld: unknown option '--~'

Please Help

Note that I am using the binaries from this site.

---

Cannot Delete Or Update A Parent Row: A Foreign Key Constraint Fails
I am trying to delete a record through a user interface, but when i try to delete the record i get the following message:

Cannot delete or update a parent row: a foreign key constraint fails

The parent table is a products table and has four child tables linked through a product id foreigh key. The table is an innoDB type.

---

Machine Name &amp; IP Address Fails For Remote Admin Login
I've just set up 5.0 on both a desktop server and a laptop with an essentially default install using WinXP (brand new, naive user here!).

I find that when I use the Administrator Tool that I can only log into ServerHost: "localhost" using Username: "root" and the password I made. This is made worse as I can only hit the desktop from the laptop using Remote Desktop connection.

What I can't do is either use the WinXP machine name, or the IP address for the Serverhost. This becomes an issue for the laptop especially as the IP address can change (although this is accessed through a VPN, so the WinXP machine name might be used).

I am quite confused by the documentation regarding servernames, bindings, hostnames, etc.

---

Insert Multiple Fails Completely If Any Duplicates Exist
I've been having some problems with database performance... Several
threads are constantly attempting INSERTs of new records into a large
table - that is hundreds of thousands of records -large by my account
:-) The table has a VARCHAR field tagged as UNIQUE and inserts are
kind of slow. i'm relying on the INSERT to fail on duplicates, i'm
not performing any checking before i attempt the INSERT (i figured an
additional SELECT would slow things even more - but any advice to the
contrary would be appreciated).

While these back end threads are constantly banging away with their
INSERTS, front end users are hitting the DB with searches. so i end
up with several threads simultaneously attempting very expensive DB
operations. the result is that the front end that users see is
DREADFULLY slow.

My proposed speedup is to modify the behavior of the backend threads
so that they don't constantly attempt INSERTS of single entries, but
build up a hashtable of local entries and at some interval, attempt a
multiple insert something like:

INSERT INTO table (col1,col2,col3 VALUES (x1,y1,z1), (x2,y2,z2)....

While i do expect to take a big hit when these multiple inserts
launch, in the time between, i expect front end performance to be
significantly improved.

The problem with this situation is that the multiple insert may
contain one or more new entries which will cause duplicates on my
table's UNIQUE VARCHAR field. I would like to have duplicates ignored
on insert, but have all newly unique entries be added. Sadly, from
what i've seen, once a duplicate violation for any of the new entries
is found, the call fails, leaving my newly UNIQUE entries NOT ADDED.
Is there any way to get the call to work as i want? Doing many single
row inserts sounds like it'll be much slower (judging from what i've
read) so i'd rather not resort to that.

I don't have a ton of DB experience (which may be obvious) so if i've
overlooked something simple please let me know. If there's a better
way to improve performance other than these multiple inserts, that
would be nice to know about too. Thanks for any insight,suggestions,
etc...

---

Error 1452 Cannot Add Or Update A Child Row: A Foreign Key Constraint Fails.
I am creating a contacts database using family information.

My tables in question look like this:
create table Household
( HouseholdID int(4) not null,
LastName varchar(100),
constraint pk_HouseholdID primary key (HouseholdID)
);

create table Personal
( PersonalID int (4)not null,
HouseholdID int (4) not null,
AddressID int (4) not null,
FirstName varchar (20),
LastName varchar (60),
constraint pk_PersonalID primary key (personalID)
);

create table ContactInfo
( AddressID int (4) not null,
PersonalID int (4) not null,
lastName varchar(50) not null,
mailingAddress varchar(60),
City varchar(25),
state varchar (20),
ZipCode varchar(5),
PhoneNumber int (10),
EmailAddress varchar (60),
constraint pk_AddressID primary key (AddressID)
);
** I have others, but these are the ones involoved in the issue**

Here is what I am trying to update with:

insert Personal
values (0100,0001, 1000, 'Gram & Gram', 'Holbrook');

THis is my error:
[mobile3] ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (`directory/personal`, CONSTRAINT `fk_AddressID` FOREIGN KEY (`AddressID`) REFERENCES `contactinfo` (`AddressID`))

---

Master Master Replication, Truncate Table Fails, Version 5.0.22
I have set up a master master replication link between two databases, prototyping an idea. Anyhow, adding records and deleting works, both get updated, creating and dropping tables works, but truncating tables doesn't work.

My main questions are, why doesn't truncate work, and if it doesn't work, what else doesn't work across a master-master replication link?

---

LOAD DATA Fails When Non-ascii Chars In Data
I am trying to load a cities database that is in CSV format. Some of the fields contain extended characters like the umlaut or accented chars. The load fails with: Code:

---

&quot;SHOW CHARACTER SET;&quot; Fails.
I'm reading and trying to follow this manual page : http://dev.mysql.com/doc/mysql/en/charset-mysql.html

Whey I try to do "SHOW CHARACTER SET;" either in "mysql>" or in phpmyadmin I get an error.
The mysql> error looks like this :

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 30462 to server version: 4.0.24

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> SHOW CHARACTER SET;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET' at line 1
mysql>

---

How To Create Efficient MySQL Query From A Pseudo Query
I'm trying to build a webapplication where users can search for a person having a particular preference for color and material. To store this information I use the following structure (a MySQL dump can be found at the end of this post):
*table person with fields:
-persid: autoincrement id
-name: name of the person
*table material with fields:
-materialid: autoincrement id
-material: name of the material eg "wood"
*table color with fields:
-colorid: autoincrement id
-color: name of the color eg "green"
*table persmaterial with fields:
-persmatid: autoincrement id
-persid: link to table person
-materialid: link to table material
*table perscolor with fields:
-perscolorid: autoincrement id
-persid: link to table person
-colorid: link to table color

In the webapplication the search can be entered by the users as a kind of pseudo query:
(color=red OR color=blue) AND color=green AND material=iron

My question is: how can I automatically transform this pseudo query into an efficient MySQL query?
I have tried out some different options:


Option 1:
(SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) UNION
(SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1))
Remarks:
*I do not see how to turn a general pseudo query into a query like the one in option 1, except for turning the pseudo query into a sum of products form where the sulms would correspond to the UNIONs. IS there a clever way to obtain such a sum of products form from an arbitrary pseudo query?


Option 2:
SELECT persid FROM person p WHERE
(EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=1 AND p.persid=pc.persid)
OR
EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=3 AND p.persid=pc.persid))
AND
EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=2 AND p.persid=pc.persid)
AND
EXISTS(SELECT * FROM persmaterial pm WHERE pm.materialid=2 AND p.persid=pm.persid)
Remarks:
*very easy to get from pseudo query to MySQL query but what about performance?

Option 3:
SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE
p.persid=pc.persid
AND
(pc.colorid=1 OR pc.colorid=2 OR pc.colorid=3)
AND p.persid=pm.persid
AND pm.materialid=2
GROUP BY p.persid HAVING
sum(case when pc.colorid in (&#391;',&#393;') then 1 else 0 end) >= 1
AND
sum(case when pc.colorid=&#392;' then 1 else 0 end)>=1
AND
sum(case when pm.materialid=&#392;' then 1 else 0 end)>=1
Remarks:
*this option requires the pseudo query to be turned into a product of sums form; again is their a clever way to obtain such a form;




Option 4
SELECT DISTINCT pc1.persid FROM perscolor pc1
INNER JOIN perscolor pc2
ON pc1.persid=pc2.persid AND pc2.colorid=2
INNER JOIN persmaterial pm1
ON pc1.persid=pm1.persid AND pm1.materialid=2
LEFT OUTER JOIN perscolor pc3
ON pc1.persid=pc3.persid AND pc3.colorid=1
LEFT OUTER JOIN perscolor pc4
ON pc1.persid=pc4.persid AND pc4.colorid=3
WHERE COALESCE(pc3.persid,pc4.persid) IS NOT NULL
Remarks:
*this option requires the pseudo query to be turned into a product of sums form

Option 5:
SELECT p.persid FROM person p, persmaterial pm,perscolor pc1,perscolor pc2,perscolor pc3 WHERE p.persid=pm.persid AND p.persid=pc1.persid AND p.persid=pc2.persid AND p.persid=pc3.persid AND (pc1.colorid=1 OR pc2.colorid=3) AND pc3.colorid=2 AND pm.materialid=2 GROUP BY p.persid
Remarks:
*very easy to get from pseudo query to MySQL query but what about performance?



-- phpMyAdmin SQL Dump
-- version 2.6.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 19, 2006 at 01:13 PM
-- Server version: 4.1.9
-- PHP Version: 4.3.10
--
-- Database: `aston`
--

-- --------------------------------------------------------

--
-- Table structure for table `color`
--

CREATE TABLE `color` (
`colorid` int(11) NOT NULL auto_increment,
`color` varchar(30) NOT NULL default '',
PRIMARY KEY (`colorid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `color`
--

INSERT INTO `color` VALUES (1, 'red');
INSERT INTO `color` VALUES (2, 'green');
INSERT INTO `color` VALUES (3, 'blue');
INSERT INTO `color` VALUES (4, 'yellow');

-- --------------------------------------------------------

--
-- Table structure for table `material`
--

CREATE TABLE `material` (
`materialid` int(11) NOT NULL auto_increment,
`material` varchar(30) NOT NULL default '',
PRIMARY KEY (`materialid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `material`
--

INSERT INTO `material` VALUES (1, 'wood');
INSERT INTO `material` VALUES (2, 'iron');

-- --------------------------------------------------------

--
-- Table structure for table `perscolor`
--

CREATE TABLE `perscolor` (
`perscolorid` int(11) NOT NULL auto_increment,
`persid` int(11) NOT NULL default &#390;',
`colorid` int(11) NOT NULL default &#390;',
PRIMARY KEY (`perscolorid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `perscolor`
--

INSERT INTO `perscolor` VALUES (1, 1, 1);
INSERT INTO `perscolor` VALUES (2, 1, 2);
INSERT INTO `perscolor` VALUES (3, 2, 1);
INSERT INTO `perscolor` VALUES (5, 3, 3);
INSERT INTO `perscolor` VALUES (6, 3, 2);

-- --------------------------------------------------------

--
-- Table structure for table `persmaterial`
--

CREATE TABLE `persmaterial` (
`persmatid` int(11) NOT NULL auto_increment,
`persid` int(11) NOT NULL default &#390;',
`materialid` int(11) NOT NULL default &#390;',
PRIMARY KEY (`persmatid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `persmaterial`
--

INSERT INTO `persmaterial` VALUES (1, 1, 1);
INSERT INTO `persmaterial` VALUES (2, 1, 2);
INSERT INTO `persmaterial` VALUES (3, 2, 1);
INSERT INTO `persmaterial` VALUES (5, 3, 2);

-- --------------------------------------------------------

--
-- Table structure for table `person`
--

CREATE TABLE `person` (
`persid` int(11) NOT NULL auto_increment,
`name` varchar(30) NOT NULL default '',
PRIMARY KEY (`persid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `person`
--

INSERT INTO `person` VALUES (1, 'john');
INSERT INTO `person` VALUES (2, 'emily');
INSERT INTO `person` VALUES (3, 'liz');

---

How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy!

I can make a simple single query using MYSQL Query Browser, say:

qry1: SELECT ID, Area FROM data GROUP BY Area

How can I store this as a query inside MYSQL, rather than having to code it each time?

In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query:

qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE

How can I store this as a query and then pass the variable from code?

In Microsoft Access I could base a query on the results of another query, so following example above:

qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area;

How can I store this as a query in MYSQL.

---

MySQL Error 2013: Lost Connection To MySQL Server During Query
I'm running sugarcrm on mysql and the following query is returning "MySQL error 2013: Lost connection to MySQL server during query":

---

Different Results In Mysql & Mysql Query Browser?
I've come across an extremely strange problem. The exact same query in
both mysql command line client, and mysql query browser gives entirely
different results. I was hoping someone out there could shed some
light.

Ok, the query (I've stripped it bare, the real query is a bit more
complex)...

Each person in the people table has an associated 'place', which is an
integer that maps onto a suburb in the suburbs table. The 'place' CAN
be NULL.

so, for all intensive purposes, the schema is roughly:

---

Help With Php/MySQL Query?
I'm trying to pull records from a MySQL database using a sql query - to pull records where the "exp_date" field is greater than or equal to today. How do I do this?
Here is my query:
PHP
$query = "SELECT v_title,link_id,v_descr,hw_added,hw_region_id,approved,user_approved,exp_date FROM ec3_ad WHERE hw_feat=1 AND approved=1 AND user_approved=1 AND hw_region_id=103 AND exp_date>=strftime('%d %b %Y %H:%M:%S') ORDER BY hw_added DESC LIMIT 20";
... but this is obviously not working because I don't know how to add today's date/time dynamically to the query.

---

Can Anyone Help With This Mysql Query?
I'm using the following query to join the 3 tables gallery_category, gallery_photos and spectacle:

PHP

SELECT gallery_category.id AS gcid, gallery_category.name AS gcname, private,
COUNT(photo_id) AS countim, spectacle.id AS sid, spectacle.name AS showname
FROM gallery_category
LEFT JOIN gallery_photos ON gallery_category.id=gallery_photos.photo_category
LEFT JOIN spectacle ON gallery_category.spectacle=spectacle.id
GROUP BY gallery_photos.photo_category
ORDER BY gallery_category.name ASC

It seems to work fine, but it will only return 3 rows. If I add a new entry to the gallery_category table, the result from the query doesn't return it. If I then add an entry to the gallery_photos table with the new gallery_category in the column gallery_photos.photo_category (i.e. I add a photo to the new category), then the new category will be returned in my result set. My first thought was that it was ignoring categories with no photos in, but this is not the case (one of the 3 rows returned has no photos - countim=0).

---

MySQl Query
SELECT *
FROM test_downloads, test_download_ride, atm_rides, atm_areas, atm_area_ride
WHERE atm_areas.area_id =1
AND atm_area_ride.area_id = atm_areas.area_id
AND atm_rides.ride_id = atm_area_ride.ride_id
AND test_download_ride.ride_id = atm_rides.ride_id
AND test_downloads.download_id = test_download_ride.ride_id

I would like to keep the "and" format for the time being instead of using joinsor any other method.

---

Mysql Query Help
I have 2 tables in my database that I want to link
in policy table I have an orgid and a policyid
in the policypermissions table I have the permissionid and policy id.

my problem is that I have a bunch of duplicate policiepermissions assigned to different policyid's and I want to delete a specific permission id

Quote:

select count(p.orgid), p.orgid, pp.policyid from policypermissions pp join policies p on
p.policyid=pp.policyid where permissionid=7 group by p.orgid Having (count(p.orgid) >1)

the count for the orgid is right but I need to see all of the policyids for each orgid. is there a query that I can run that will delete all but the first policyid?

---

MySQL Query, Is It Possible?
I have the following table-structure:

countID
countItemCode
countIPaddress
countWhat (in or outclick)
countReferer

Is it possible to make a mySQL query that outputs the following

#38AFG66
total clicks: 34
(in:12,out:20) referrer (site1:10,site2:12,site3:2)

#JHGS676
total clicks: 45
(in:31,out:14) referrer (site1:8,site2:7,site3:5,site4:25)

---