sql syntax error
hello, i am working on my personal website wih php 5.4.16 / mysql 5.6.12 (my
system : windows 7 / wampserver 2).
i have a bug when i am running my connection to database webpage.
My error message is the following :
Erreur SQL : 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 ''membres2' WHERE
'pseudo_membre' = '\'Flarose59\'' GROUP BY
'id_membre'' at line 1
Ligne : 29.
this is my php code :
$result = sqlquery(SELECT COUNT('id_membre') AS nbr, 'id_membre',
'pseudo_membre', 'mdp_membre' FROM 'espace_membre2'.'membres2' WHERE
'pseudo_membre' = '\'.mysql_real_escape_string($_POST['pseudo']).\'' GROUP BY
'id_membre', 1);
I tried several delimitator for the query function (like ' \* ').
Re: sql syntax error
- Original Message - From: florent larose florent.lar...@hotmail.com Subject: sql syntax error near ''membres2' WHERE [...] FROM 'espace_membre2'.'membres2' WHERE You were on the right path - mysql is wibbly about quotes. Either remove the quotes entirely ( espace_membre2.membres2 ) or try backticks ( ` ). They're MySQL's favourite quote, presumably because they were convenient to type on whatever abomination Monty used to type code :-) As your code is french, I'll assume you're on Azerty; the backtick is Alt-Gr plus the rightmost key (right next to return) on the middle row. Enjoy spraining your fingers :-p /johan -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: sql syntax error
Hi, Le 08/08/2014 17:48, Johan De Meersman a écrit : As your code is french, I'll assume you're on Azerty; the backtick is Alt-Gr plus the rightmost key (right next to return) on the middle row. Enjoy spraining your fingers :-p /johan Alt-GR plus '7' for French keyboard layout ;) Christophe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: [PHP] SQL Syntax
[ I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. snip I appreciate your thoughts on this. My first thought is that you're going to endup with some very inefficient queries or come unstuck with that table schema the first time you have an attributes of different types. What happens if attribute 1 is dateTaken has the type date, attribute 2 is authorName with the type string and attribute 3 is an aspect ratio N:n? My second thought is to make sure you have a unique index on (pid,aid) in table b. Sticking to the question you asked. Lets assume the search for this run of the search query is owned by userId 35 and two attribute clauses: has attribute 1 50 and attribute 3 = 4 I'd use: drop temporary table if exists AttSearchMatches; select pid as targetPid, count(*) as criteraMatched from B where userId=35 and ( (b.aid=1 and b.value 50) OR (b.aid=3 and b.value =4) ) group by pid having criteraMatched = 2; drop temporary table if exists AttSearchMatches; select fields you want from criteraMatched cm on cm. inner join A on a.pid=criteraMatched.pid; drop temporary table AttSearchMatches; For best performance specify the temp table structure explicitly and add an index to pid. You could do this with a single query containing a sub-query rather than temporary tables but I've been bitten by sub-query performance before. Hope that helps, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] SQL Syntax
On Wed, 2010-06-16 at 08:59 +0100, Nigel Wood wrote: I'd use: drop temporary table if exists AttSearchMatches; select pid as targetPid, count(*) as criteraMatched from B where userId=35 and ( (b.aid=1 and b.value 50) OR (b.aid=3 and b.value =4) ) group by pid having criteraMatched = 2; drop temporary table if exists AttSearchMatches; select fields you want from criteraMatched cm on cm. inner join A on a.pid=criteraMatched.pid; drop temporary table AttSearchMatches; Nope :-) Without the silly errors I'd use: drop temporary table if exists AttSearchMatches; select pid, count(*) as criteraMatched from B where b.userId=35 and ( (b.aid=1 and b.value 50) OR (b.aid=3 and b.value =4) ) group by b.pid having criteraMatched = 2; select a.fields you want from AttSearchMatches asm inner join A on a.pid=asm.pid; drop temporary table AttSearchMatches; Sorry, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] SQL Syntax
Hi! Daniel Brown wrote: [Top-post.] You'll probably have much better luck on the MySQL General list. CC'ed on this email. On Tue, Jun 15, 2010 at 20:58, Jan Reiter the-fal...@gmx.net wrote: Hi folks! [[...]] I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. [[...]] You need to do a multi-table join, table A joined to one instance of table B for each attribute relevant to your search. Roughly, syntax not tested, it is something like SELECT a.uid, a.pid FROM a JOIN b AS b1 ON a.pid=b1.pid JOIN b AS b2 ON a.pid=b2.pid JOIN ... WHERE b1.aid = 1 AND b1.value 100 AND b2.aid = 3 AND b2.value = 5 AND ... (assuming 'jpg' is coded as 5, what I take from your text). Now, I see some difficulties with this: 1) You are using the value column for anything, that may cause data type problems. 2) AFAIR, there was a post recently claiming the alias names (b1, b2, ...) could not be used in WHERE conditions, and the recommendation was to replace WHERE by HAVING. 3) If you need to support many attributes in one search, the number of tables joined grows, and the amount of data to handle (cartesian product!) will explode. What works fine with 3 criteria on 10 pictures (10 * 10 * 10 = 1000) may totally fail with 4 criteria on 200 pictures (200**4 = 800.000.000 = 800 million) 4) The more different attributes you store per picture, the larger your table B will become, and this will make the data grow for each join step. If you store 4 attributes each for 200 pictures, table B will already have 800 entries. In itself, that isn't much, but now the 4-way join will produce a cartesian product of 800**4 = 8**4 * 100**4 = 4096 * 100.000.000 = 409.600.000.000 combinations. In your place, I would use a separate table for attributes which are expected to be defined for all pictures, like size and image type. Then your general attributes table B will hold much fewer rows, thus each join step will profit. 5) Because of that explosion, it may be better to work with a temporary table, joining it to B for one attribute and thus reducing the data, then looping over such a step for all the relevant attributes. Good luck in experimenting! Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz Amtsgericht Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] SQL Syntax
[Top-post.] You'll probably have much better luck on the MySQL General list. CC'ed on this email. On Tue, Jun 15, 2010 at 20:58, Jan Reiter the-fal...@gmx.net wrote: Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! I appreciate your thoughts on this. Regards, Jan -- /Daniel P. Brown daniel.br...@parasane.net || danbr...@php.net http://www.parasane.net/ || http://www.pilotpig.net/ We now offer SAME-DAY SETUP on a new line of servers! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Now() : SQL syntax error. But why?
Thanks, Scott. I thought I couldn't have missed ','(comma) before. But today somehow it works... ;; I wasted hours figuring this out, but you saved me! Maybe I'm still a complete newbie! Thanks, again. Have a great day. :) On Thu, Apr 30, 2009 at 12:52 PM, Scott Haneda talkli...@newgeo.com wrote: On Apr 29, 2009, at 11:29 AM, Antonio PHP wrote: This is MySQL data structure. - I underlined where it causes the error message. (datetime) `id_Company` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL `Revenue` mediumint(6) NOT NULL, `Company_Size` mediumint(6) NOT NULL, `Ownership` tinyint(1) NOT NULL, `Homepage` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Job_Source` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, *`Created` datetime NOT NULL, *PRIMARY KEY (`id_Company`), KEY `Ownership` (`Ownership`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 FOREIGN KEY (`Ownership`) REFERENCES `ownership` (`id_Ownership`) ON DELETE CASCADE ON UPDATE CASCADE; Next time can you include unmodified SQL so it is a copy and paste for me, rather than debugging what changes you made that are causing error. Here is php script - $sql = INSERT INTO company SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source' *Created=NOW() // if I remove this line it works fine. *; mysql_query ($sql) or die (mysql_error()); Same here, as I am not sure your edits are just edits, or the lack of a comma after the job source variable is the issue. This works on my end: $Name = 'Tom'; $Revenue = '100'; $Company_Size = '500'; $Ownership= 'partner'; $Homepage = 'example.com'; $Job_Source = 'friend'; $sql = INSERT INTO mailing SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source', Created=NOW(); echo $sql; mysql_query ($sql) or die (mysql_error()); -- Scott * If you contact me off list replace talklists@ with scott@ *
Re: Now() : SQL syntax error. But why?
Always echo out your SQL string, it will make it a lot more obvious. You want to see the result. I php concatenated string can be confusing at times. Also, you are not escaping your data, so if you had a word of 'stops, here' that would break it as well. So in your case, you very well may break it by changing the data you put in. You could also do something like stuffing drop database foo; into your data, and be in for real fun. Pass every string to http://us2.php.net/mysql_real_escape_string On Apr 30, 2009, at 9:27 PM, Antonio PHP wrote: I thought I couldn't have missed ','(comma) before. But today somehow it works... ;; I wasted hours figuring this out, but you saved me! Maybe I'm still a complete newbie! -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Now() : SQL syntax error. But why?
Thanks. NOW() and php date(); work for my newly created test tables, but it
doesn't work for my working table. I can't insert date, time or now()
into my old table (which is as below).
For now, I'm using MySQL auto timestamp ('Updated' field), but I need to
insert date when the data was created!
Why is this? Please help me. (I'm using the newest versions of PHP and
MySQL)
This is MySQL data structure. - I underlined where it causes the error
message. (datetime)
`id_Company` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
`Revenue` mediumint(6) NOT NULL,
`Company_Size` mediumint(6) NOT NULL,
`Ownership` tinyint(1) NOT NULL,
`Homepage` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`Job_Source` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT
NULL,
`Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
*`Created` datetime NOT NULL,
*PRIMARY KEY (`id_Company`),
KEY `Ownership` (`Ownership`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
FOREIGN KEY (`Ownership`) REFERENCES `ownership` (`id_Ownership`) ON DELETE
CASCADE ON UPDATE CASCADE;
Here is php script -
$sql = INSERT INTO company SET
Name='$Name',
Revenue='$Revenue',
Company_Size='$Company_Size',
Ownership='$Ownership',
Homepage='$Homepage',
Job_Source='$Job_Source'
*Created=NOW() // if I remove this line it works fine.
*;
mysql_query ($sql) or die (mysql_error());
Also, this doesn't work for this table.
$Datetime = date( 'Y-m-d H:i:s');
INSERT INTO
Created='$Datetime'...
On Wed, Apr 29, 2009 at 9:28 AM, Scott Haneda talkli...@newgeo.com wrote:
We need to see your entire query and the table structure. timestamp fields
can have options set to auto update them, where order matters, and only one
field can support that feature.
Please supply more data.
On Apr 28, 2009, at 2:18 PM, Antonio PHP wrote:
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 'Created =
NOW(),
Updated = NOW()' at line 8
'Created' and 'Updated' are set to datetime (InnoDB).
The same syntax works for some newly created tables... and gives no error.
It's very strange. 'Now()' works for some tables, and it doesn't for some.
(All set in phpmyadmin...)
What could have caused this? Any similar experience?
--
Scott * If you contact me off list replace talklists@ with scott@ *
Re: Now() : SQL syntax error. But why?
On Apr 29, 2009, at 11:29 AM, Antonio PHP wrote: This is MySQL data structure. - I underlined where it causes the error message. (datetime) `id_Company` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL `Revenue` mediumint(6) NOT NULL, `Company_Size` mediumint(6) NOT NULL, `Ownership` tinyint(1) NOT NULL, `Homepage` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Job_Source` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, *`Created` datetime NOT NULL, *PRIMARY KEY (`id_Company`), KEY `Ownership` (`Ownership`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 FOREIGN KEY (`Ownership`) REFERENCES `ownership` (`id_Ownership`) ON DELETE CASCADE ON UPDATE CASCADE; Next time can you include unmodified SQL so it is a copy and paste for me, rather than debugging what changes you made that are causing error. Here is php script - $sql = INSERT INTO company SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source' *Created=NOW() // if I remove this line it works fine. *; mysql_query ($sql) or die (mysql_error()); Same here, as I am not sure your edits are just edits, or the lack of a comma after the job source variable is the issue. This works on my end: $Name = 'Tom'; $Revenue = '100'; $Company_Size = '500'; $Ownership= 'partner'; $Homepage = 'example.com'; $Job_Source = 'friend'; $sql = INSERT INTO mailing SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source', Created=NOW(); echo $sql; mysql_query ($sql) or die (mysql_error()); -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Now() : SQL syntax error. But why?
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 'Created = NOW(), Updated = NOW()' at line 8 'Created' and 'Updated' are set to datetime (InnoDB). The same syntax works for some newly created tables... and gives no error. It's very strange. 'Now()' works for some tables, and it doesn't for some. (All set in phpmyadmin...) What could have caused this? Any similar experience? Please help~.
Re: Now() : SQL syntax error. But why?
Can you please give the full table structure and query? On Tue, Apr 28, 2009 at 23:18, Antonio PHP php.anto...@gmail.com wrote: 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 'Created = NOW(), Updated = NOW()' at line 8 'Created' and 'Updated' are set to datetime (InnoDB). The same syntax works for some newly created tables... and gives no error. It's very strange. 'Now()' works for some tables, and it doesn't for some. (All set in phpmyadmin...) What could have caused this? Any similar experience? Please help~. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Now() : SQL syntax error. But why?
We need to see your entire query and the table structure. timestamp fields can have options set to auto update them, where order matters, and only one field can support that feature. Please supply more data. On Apr 28, 2009, at 2:18 PM, Antonio PHP wrote: 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 'Created = NOW(), Updated = NOW()' at line 8 'Created' and 'Updated' are set to datetime (InnoDB). The same syntax works for some newly created tables... and gives no error. It's very strange. 'Now()' works for some tables, and it doesn't for some. (All set in phpmyadmin...) What could have caused this? Any similar experience? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQL syntax
- Original Message - From: Scott Yamahata [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 29, 2006 1:29 AM Subject: SQL syntax Hi, I'm getting the following error message: 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 ' enabled = '1'' at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' and can't quite figure out what it means. Any help is greatly appreciated. Someone may have replied to you by now so I apologize if this is old news but I think the problem _precedes_ enabled = '1' Take a look at the SQL just BEFORE that where you wrote: countryid = , That is surely not valid SQL in any dialect of SQL I have ever seen. You need an appropriate value before the comma, such as: countryid = 7, or countryid = '7', If you don't want to assign a value to countryid, just omit the entire 'countryid =,' altogether. -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax
Hi Scott, all, Scott Yamahata wrote: Hi, I'm getting the following error message: 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 ' enabled = '1'' at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' and can't quite figure out what it means. Any help is greatly appreciated. The error in your statement is that it does not provide a value for the countryid field. I suspect this whole statement was generated by your application, but the input data did not contain a value for this field. In such a case, your statement must not contain the countryid = part, and the field will receive its default value. Alternatively, you can explicitly set that field to NULL (provided your create table allows NULL for it). Then, there is an error in your mail: You intended to start a new thread, but your mail contains an in-reply-to header which makes it part of another thread. Probably you used some reply function where write (new) was appropriate. I assume you did not intentionally hijack that other thread, but it does make reading more difficult than necessary. Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 VoIP: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL syntax
Hi, I'm getting the following error message: 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 ' enabled = '1'' at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' and can't quite figure out what it means. Any help is greatly appreciated. Thanks, Scott _ Experience the magic of the holidays. Talk to Santa on Messenger. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://imagine-windowslive.com/minisites/santabot/default.aspx?locale=en-us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax
Hi, have you checked the 'enabled' field datatype or can you give the query. Thanks ViSolve DB Team. - Original Message - From: Scott Yamahata [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 29, 2006 11:59 AM Subject: SQL syntax Hi, I'm getting the following error message: 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 ' enabled = '1'' at line 3 INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' and can't quite figure out what it means. Any help is greatly appreciated. Thanks, Scott _ Experience the magic of the holidays. Talk to Santa on Messenger. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://imagine-windowslive.com/minisites/santabot/default.aspx?locale=en-us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax
Hi Scott, at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' The error is because you havent specified the value for the column countryid. If you do not want to insert the value to the column countryid then use the following query.. do not leave the value of column blank. INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid =NULL , enabled = '1' Thanks, ViSolve DB Team - Original Message - From: Scott Yamahata [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 29, 2006 11:59 AM Subject: SQL syntax Hi, I'm getting the following error message: 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 ' enabled = '1'' at line 3INSERT INTO clf_cities SET cityname = 'Santa Barbara', countryid = , enabled = '1' and can't quite figure out what it means. Any help is greatly appreciated. Thanks, Scott _ Experience the magic of the holidays. Talk to Santa on Messenger. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://imagine-windowslive.com/minisites/santabot/default.aspx?locale=en-us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.29/607 - Release Date: 12/28/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1064 (42000): You have an error in your SQL syntax;
Hi All, gee I really hate bugging you all for this. I looked at this page, http://dev.mysql.com/doc/refman/5.0/en/alter-table.html which has this, To change column |a| from |INTEGER| to |TINYINT NOT NULL| (leaving the name the same), and to change column |b| from |CHAR(10)| to |CHAR(20)| as well as renaming it from |b| to |c|: ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); for changing the name of a column, right? So, why doesn't the below work? mysql ALTER TABLE actors CHANGE director_id actor_id; I get this, ERROR 1064 (42000): 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 '' at line 1 Sorry, little confused right about now, eh. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1064 (42000): You have an error in your SQL syntax;
At 0:09 +1000 8/6/06, Mark Sargent wrote: ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); for changing the name of a column, right? So, why doesn't the below work? mysql ALTER TABLE actors CHANGE director_id actor_id; I'm no great expert myself, but off the top of my head, maybe you need to specify the type even if it's unchanged (I assume all you want to do is rename the column?). So supposing director_id was a SMALLINT(3) UNSIGNED, try: ALTER TABLE actors CHANGE director_id actor_id SMALLINT(3) UNSIGNED; Any good? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ If at first you don't succeed, try, try again. Then quit. No use being a damn fool about it. -- W.C. Fields -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: ERROR 1064 (42000): You have an error in your SQL syntax;
Mark, With the CHANGE clause of ALTER TABLE statement, you must provide the column definition, so something like this is what you need: ALTER TABLE actors CHANGE director_id actor_id MEDIUMINT UNSIGNED NOT NULL; or whatever your original definition is. AFAIK there is no way to rename a column without giving the column type. --Rob Hi All, gee I really hate bugging you all for this. I looked at this page, http://dev.mysql.com/doc/refman/5.0/en/alter-table.html which has this, To change column |a| from |INTEGER| to |TINYINT NOT NULL| (leaving the name the same), and to change column |b| from |CHAR(10)| to |CHAR(20)| as well as renaming it from |b| to |c|: ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); for changing the name of a column, right? So, why doesn't the below work? mysql ALTER TABLE actors CHANGE director_id actor_id; I get this, ERROR 1064 (42000): 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 '' at line 1 Sorry, little confused right about now, eh. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- Original Message -- FROM: Mark Sargent [EMAIL PROTECTED] TO:mysql@lists.mysql.com DATE: Thu, 08 Jun 2006 00:09:45 +1000 SUBJECT: ERROR 1064 (42000): You have an error in your SQL syntax; Hi All, gee I really hate bugging you all for this. I looked at this page, http://dev.mysql.com/doc/refman/5.0/en/alter-table.html which has this, To change column |a| from |INTEGER| to |TINYINT NOT NULL| (leaving the name the same), and to change column |b| from |CHAR(10)| to |CHAR(20)| as well as renaming it from |b| to |c|: ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); for changing the name of a column, right? So, why doesn't the below work? mysql ALTER TABLE actors CHANGE director_id actor_id; I get this, ERROR 1064 (42000): 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 '' at line 1 Sorry, little confused right about now, eh. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 1064 (42000): You have an error in your SQL syntax;
You can't just change the name without changing (or stating) the type. ALTER TABLE actors CHANGE director_id actos_id varchar(96) default NULL; J.R. -Original Message- From: Mark Sargent [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 10:10 AM To: mysql@lists.mysql.com Subject: ERROR 1064 (42000): You have an error in your SQL syntax; Hi All, gee I really hate bugging you all for this. I looked at this page, http://dev.mysql.com/doc/refman/5.0/en/alter-table.html which has this, To change column |a| from |INTEGER| to |TINYINT NOT NULL| (leaving the name the same), and to change column |b| from |CHAR(10)| to |CHAR(20)| as well as renaming it from |b| to |c|: ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); for changing the name of a column, right? So, why doesn't the below work? mysql ALTER TABLE actors CHANGE director_id actor_id; I get this, ERROR 1064 (42000): 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 '' at line 1 Sorry, little confused right about now, eh. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: ERROR 1064 (42000): You have an error in your SQL syntax;
At 15:19 +0100 7/6/06, Rob Desbois wrote: With the CHANGE clause of ALTER TABLE statement, you must provide the column definition, so something like this is what you need: ALTER TABLE actors CHANGE director_id actor_id MEDIUMINT UNSIGNED NOT NULL; or whatever your original definition is. Wow! I was right. I'm learning... :-) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ If at first you don't succeed, try, try again. Then quit. No use being a damn fool about it. -- W.C. Fields -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Syntax Errors
671 Did not find any old versions with SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26', attempt to insert one: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) 671 Did not find any old versions with SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26', attempt to insert one: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) 676 $result = $sth-execute(); 678 Insert must of have worked! DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. 676 $result = $sth-execute(); At the end are my print messages from a perl program using MySQL (v 4.1, how do I tell for sure?) with the DBI interface. The first integer on the left is the line number. I first check to see if the record exists: SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26' When I don't find an entry, I try an insert: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) This indicates success. But then it tells me I have a syntax error! DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. The primary key consists of two fields: fkJobPosting (integer) and dtSnapshot (date). Now if my SQL had a syntax error, would it not give me an error every time? So why do I get syntax error? I don't get a syntax error every time. Most of the time, everything works fine. Thanks, Siegfried --- 683 insert failed: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) /try delete: 'DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' select='SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26''Duplicate entry '209689-2005-06-26' for key 1 DBD::mysql::st execute failed: 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 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' at li at ./crawl-hot-jobs.pl line 686. Use of uninitialized value in concatenation (.) or string at ./crawl-hot-jobs.pl line 707. 707 $nDBVersion[0] = 1 version=1 nDBVersion=() fkJobId = 209689 No need to update database,it is more recent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Errors
Siegfried Heintze [EMAIL PROTECTED] wrote on 06/29/2005 03:09:28 PM: 671 Did not find any old versions with SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26', attempt to insert one: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) 671 Did not find any old versions with SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26', attempt to insert one: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) 676 $result = $sth-execute(); 678 Insert must of have worked! DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. 676 $result = $sth-execute(); At the end are my print messages from a perl program using MySQL (v 4.1, how do I tell for sure?) with the DBI interface. The first integer on the left is the line number. I first check to see if the record exists: SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26' When I don't find an entry, I try an insert: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) This indicates success. But then it tells me I have a syntax error! DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. The primary key consists of two fields: fkJobPosting (integer) and dtSnapshot (date). Now if my SQL had a syntax error, would it not give me an error every time? So why do I get syntax error? A syntax error is not your first error message. I see a message that you are attempting to duplicate a key value that already exists. Are you sure that your initial check is returning FALSE when you look for a matching record? Have you considered using the INSERT ... ON DUPLICATE KEY format or possibly the INSERT IGNORE format? Either one of those will let you deal with the case of what you should do if you attempt to create a record that would duplicate an existing records PK values. I don't use DBD or I could offer better advice. However, some database libraries force you to execute your commands one at a time. Could this be what is happening to you bewteen lines 683 and 686? I would also check (print so that you can see) the full text of the statement you are attempting to execute in line 686. It could be that you have a mismatched set of single quotes. You have to remember to escape all of the special characters used in a string literal or it will corrupt your statement. If, for example, you are building an INSERT statement that contains the name of a buisiness plus some other fields and that business has an apostrophe in its name, that apostrophe needs to be escaped or it will break your INSERT statement. I don't get a syntax error every time. Most of the time, everything works fine. Thanks, Siegfried --- 683 insert failed: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) /try delete: 'DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' select='SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26''Duplicate entry '209689-2005-06-26' for key 1 DBD::mysql::st execute failed: 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 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' at li at ./crawl-hot-jobs.pl line 686. Use of uninitialized value in concatenation (.) or string at ./crawl-hot-jobs.pl line 707. 707 $nDBVersion[0] = 1 version=1 nDBVersion=() fkJobId = 209689 No need to update database,it is more recent. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
(oops, corrections to that last email message) RE: SQL Syntax Errors
Sorry, I accidentally pasted some garbage at the beginning of that last email message. Here is what I intended: I first check to see if the record exists: SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26' When I don't find an entry, I try an insert: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) The execute function indicated success. But then (sometimes) it tells me I have a duplicate entry: DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. How could this be? Now here is another example where I detect a duplicate and delete the statement before trying to insert: DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 211151 DBD::mysql::st execute failed: 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 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 211151' at li at ./crawl-hot-jobs.pl line 686. I don't see any syntax error in that DELETE statement: do you? The primary key consists of two fields: fkJobPosting (integer) and dtSnapshot (date). Now if my SQL had a syntax error, would it not give me an error every time? So why do I get syntax error? I don't get a syntax error every time. These errors are very eratic and I cannot discern what is different when these errors occur. Thanks, Siegfried --- 683 insert failed: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) /try delete: 'DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' select='SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26''Duplicate entry '209689-2005-06-26' for key 1 DBD::mysql::st execute failed: 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 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' at li at ./crawl-hot-jobs.pl line 686. Use of uninitialized value in concatenation (.) or string at ./crawl-hot-jobs.pl line 707. 707 $nDBVersion[0] = 1 version=1 nDBVersion=() fkJobId = 209689 No need to update database,it is more recent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (oops, corrections to that last email message) RE: SQL Syntax Errors
Siegfried Heintze wrote: Sorry, I accidentally pasted some garbage at the beginning of that last email message. Here is what I intended: I first check to see if the record exists: SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26' When I don't find an entry, I try an insert: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) The execute function indicated success. But then (sometimes) it tells me I have a duplicate entry: DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. How could this be? Either you incorrectly interpreted the SELECT result, or someone/something inserted a row into the table between your SELECT and your INSERT, or one of your queries isn't what you think it is. It would help if you would show us your real code (not an edited version). Now here is another example where I detect a duplicate and delete the statement before trying to insert: DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 211151 DBD::mysql::st execute failed: 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 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 211151' at li at ./crawl-hot-jobs.pl line 686. I don't see any syntax error in that DELETE statement: do you? Yes. The syntax is DELETE FROM tablename You are missing the FROM. See the manual for details http://dev.mysql.com/doc/mysql/en/delete.html. The primary key consists of two fields: fkJobPosting (integer) and dtSnapshot (date). Now if my SQL had a syntax error, would it not give me an error every time? So why do I get syntax error? I don't get a syntax error every time. These errors are very eratic and I cannot discern what is different when these errors occur. Thanks, Siegfried --- Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1064: You have an error in your SQL syntax....
I am a newbie here. I created a simple table defined as: create table test ( testID int unsigned not null auto_increment, testName varchar(128) not null, primary key (testID) ) type = MyISAM; Now, I filled out test table, and looking for the testName with max characters. The following caused the same error of: 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 'select max(length(testName)) from test)' at line 1 select testName from test where length(testName) = (select max(length(testName)) from test); Then I copied a simple line from MySQL book: select * from president where birth = (select min(birth) from president); and adapted to my table with: select * from test where testName = (select min(testName) from test); and executed it with exactly the same error result. MySQL version I am using is: 4.0.21-standard Please help me why I get this error. Thank you Chuzo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1064: You have an error in your SQL syntax....
Chuzo, SELECT MAX(LENGTH(...)) FROM ... ought to work. SQL doesn't allow aggregate funcs like MAX() in the WHERE clause. Use HAVING(). For nested queries like SELECT ... WHERE colvalue=(SELECT...) you need version 4.1 or later. Peter Brawley http://www.artfulsoftware.com - Chuzo Okuda wrote: I am a newbie here. I created a simple table defined as: create table test ( testID int unsigned not null auto_increment, testName varchar(128) not null, primary key (testID) ) type = MyISAM; Now, I filled out test table, and looking for the testName with max characters. The following caused the same error of: 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 'select max(length(testName)) from test)' at line 1 select testName from test where length(testName) = (select max(length(testName)) from test); Then I copied a simple line from MySQL book: select * from president where birth = (select min(birth) from president); and adapted to my table with: select * from test where testName = (select min(testName) from test); and executed it with exactly the same error result. MySQL version I am using is: 4.0.21-standard Please help me why I get this error. Thank you Chuzo -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL-Syntax Check
Hello Users does anyone know a tool or a way for validation sqlcode on the command_line??? For example ./sqlsyntaxchecker select * f test -- Error not valid sql syntax thx christian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL syntax error: help a noob
Dear list, My ColdFusion server tells me I have an error in my query syntax, but I can't work out what it is - because I'm working with code that someone very kindly gave me and I only have a vague idea of what the first line's doing! Can anyone see the problem here? SELECT DATEDIFF(leadtime_type, GETDATE(), deadline)'Difference', tasks.leadtime, tasks.lead_time_type_id, leadtime_type.leadtime_type FROM tasks JOIN leadtime_type ON tasks.lead_time_type_id = leadtime_type.leadtime_type_id Thanks in advance, CK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax error: help a noob
Chris Kavanagh wrote: My ColdFusion server tells me I have an error in my query syntax, but I can't work out what it is - because I'm working with code that someone very kindly gave me and I only have a vague idea of what the first line's doing! Can anyone see the problem here? SELECT DATEDIFF(leadtime_type, GETDATE(), deadline)'Difference', tasks.leadtime, tasks.lead_time_type_id, leadtime_type.leadtime_type FROM tasks JOIN leadtime_type ON tasks.lead_time_type_id = leadtime_type.leadtime_type_id It would be helpfll if you told us what error message you got, and what version of MySQL you are using. The only obvious error I can spot is GETDATE(), this is not a standard MySQL function. Try CURDATE(). -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL syntax error: help a noob
[snip] My ColdFusion server tells me I have an error in my query syntax, but I can't work out what it is - because I'm working with code that someone very kindly gave me and I only have a vague idea of what the first line's doing! Can anyone see the problem here? SELECT DATEDIFF(leadtime_type, GETDATE(), deadline)'Difference', tasks.leadtime, tasks.lead_time_type_id, leadtime_type.leadtime_type FROM tasks JOIN leadtime_type ON tasks.lead_time_type_id = leadtime_type.leadtime_type_id [/snip] The datediff() function is new to version 4.1. What version of mysql are you running? --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax error: help a noob
Thanks very much for the replies, guys. My version is 4.1.7-max. The error message I get is: -- Error Executing Database Query. Syntax error or access violation: 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 ' deadline)'Difference', tasks.leadtime, tasks.lead_time_type_id, leadtime_' at line 1 -- I changed GETDATE() to CURDATE() but it still gives me the same error. Thanks for the suggestion anyway, Roger. Best regards, CK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL syntax error: help a noob
I think datediff only takes two arguments and you have three listed. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Chris Kavanagh Sent: Monday, January 31, 2005 5:33 PM To: mysql@lists.mysql.com Subject: Re: SQL syntax error: help a noob Thanks very much for the replies, guys. My version is 4.1.7-max. The error message I get is: -- Error Executing Database Query. Syntax error or access violation: 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 ' deadline)'Difference', tasks.leadtime, tasks.lead_time_type_id, leadtime_' at line 1 -- I changed GETDATE() to CURDATE() but it still gives me the same error. Thanks for the suggestion anyway, Roger. Best regards, CK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SOLVED: SQL syntax error: help a noob
On 31 Jan 2005, at 11:39 pm, Tom Crimmins wrote: I think datediff only takes two arguments and you have three listed. Nailed it! Thanks, Tom. Best regards, CK. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error in your SQL syntax
I have a problem. 1064 - You have an error in your SQL syntax near '(((specials INNER JOIN (products_to_categories INNER JOIN categories ON products' at line 2 SELECT DISTINCT specials.specials_id, products_to_categories.products_id, categories.parent_id, products_description.products_name, products.products_price, products.products_tax_class_id, products.products_image, specials.specials_new_products_price, languages.languages_id FROM languages INNER JOIN (((specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories.categories_id) ON specials.products_id = products_to_categories.products_id) INNER JOIN products ON specials.products_id = products.products_id) INNER JOIN products_description ON specials.products_id = products_description.products_id) ON languages.languages_id = products_description.language_id WHERE (((categories.parent_id)=285) AND ((languages.languages_id)=1)) i run this query in my computer and work, but in the internet server don´t. If anyone can solve this problem answer me. Daniel Sousa
Re: error in your SQL syntax
You have an awful lot of brackets in the query, many of which don't appear to be needed. For example, I don't see why you have brackets in this phrase: AND ((languages.languages_id)=1)) Perhaps removing the unnecessary ones will help the query work better and more consistently on each machine. Are the different machines all running the exact same version of MySQL? Rhino - Original Message - From: Daniel Sousa [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 26, 2005 6:45 AM Subject: error in your SQL syntax I have a problem. 1064 - You have an error in your SQL syntax near '(((specials INNER JOIN (products_to_categories INNER JOIN categories ON products' at line 2 SELECT DISTINCT specials.specials_id, products_to_categories.products_id, categories.parent_id, products_description.products_name, products.products_price, products.products_tax_class_id, products.products_image, specials.specials_new_products_price, languages.languages_id FROM languages INNER JOIN (((specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories.categories_id) ON specials.products_id = products_to_categories.products_id) INNER JOIN products ON specials.products_id = products.products_id) INNER JOIN products_description ON specials.products_id = products_description.products_id) ON languages.languages_id = products_description.language_id WHERE (((categories.parent_id)=285) AND ((languages.languages_id)=1)) i run this query in my computer and work, but in the internet server don´t. If anyone can solve this problem answer me. Daniel Sousa No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 21/01/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.2 - Release Date: 21/01/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error in your SQL syntax
Here is your original query, reformatted merely so that we humans can read it better: SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM languages INNER JOIN ( ( (specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories.categories_id )ON specials.products_id = products_to_categories.products_id ) INNER JOIN products ON specials.products_id = products.products_id ) INNER JOIN products_description ON specials.products_id = products_description.products_id ) ON languages.languages_id = products_description.language_id WHERE ( ( (categories.parent_id)=285 ) AND ( (languages.languages_id)=1 ) ) This query design stinks (reeks) of being autogenerated by M$ Access. The excessive use of parentheses when they aren't needed and the nested JOINs just complicate the query unnecessarily. May I suggest a simplification? SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM categories INNER JOIN products_to_categories ON products_to_categories.categories_id = categories.categories_id INNER JOIN products ON products.products_id = products_to_categories.products_id INNER JOIN specials ON specials.products_id = products.products_id INNER JOIN products_description ON products.products_id = products_description.products_id INNER JOIN languages ON products_description.language_id = languages.languages_id WHERE categories.parent_id=285 AND languages.languages_id=1; I have also noticed in my Windows command shell that it does not process extremely long lines in pastes from the clipboard well. If you copied that straight from Access to a MySQL prompt, it would have been just one long line of information and the DOS command processor would have eventually stopped taking input mid-query. I suspect that is what caused your otherwise acceptable (and I use that term loosely ;-) ) query to be invalid. The last third of it never made it into the MySQL CLI. When I break my queries into shorter lines (human friendly) and paste them into the MySQL command line interface (CLI), everything works just fine. Just copy the entire query (line breaks and all) onto the clipboard and paste it at the MySQL prompt (if that's how you are doing it) and see if it works now. Notepad is my best friend when working in the CLI. I compose and format long queries in Notepad then copy-paste into MySQL. I know it's doing it the hard way (yes, I have and do use the GUI tools too) but it's how I prefer to analyze certain issues. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel Sousa [EMAIL PROTECTED] wrote on 01/26/2005 06:45:32 AM: I have a problem. 1064 - You have an error in your SQL syntax near '(((specials INNER JOIN (products_to_categories INNER JOIN categories ON products' at line 2 SELECT DISTINCT specials.specials_id, products_to_categories. products_id, categories.parent_id, products_description. products_name, products.products_price, products. products_tax_class_id, products.products_image, specials. specials_new_products_price, languages.languages_id FROM languages INNER JOIN (((specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories. categories_id) ON specials.products_id = products_to_categories. products_id) INNER JOIN products ON specials.products_id = products. products_id) INNER JOIN products_description ON specials.products_id = products_description.products_id) ON languages.languages_id = products_description.language_id WHERE (((categories.parent_id)=285) AND ((languages.languages_id)=1)) i run this query in my computer and work, but in the internet server don´t. If anyone can solve this problem answer me. Daniel Sousa
Re: error in your SQL syntax
Thanks, works fines. I use access because i don´t know a GUI tool that make SQL querys more easy. Thanks all again, Daniel Sousa - Original Message - From: [EMAIL PROTECTED] To: Daniel Sousa Cc: mysql@lists.mysql.com Sent: Wednesday, 26 January, 2005 14:57 Subject: Re: error in your SQL syntax Here is your original query, reformatted merely so that we humans can read it better: SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM languages INNER JOIN ( ( (specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories.categories_id )ON specials.products_id = products_to_categories.products_id ) INNER JOIN products ON specials.products_id = products.products_id ) INNER JOIN products_description ON specials.products_id = products_description.products_id ) ON languages.languages_id = products_description.language_id WHERE ( ( (categories.parent_id)=285 ) AND ( (languages.languages_id)=1 ) ) This query design stinks (reeks) of being autogenerated by M$ Access. The excessive use of parentheses when they aren't needed and the nested JOINs just complicate the query unnecessarily. May I suggest a simplification? SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM categories INNER JOIN products_to_categories ON products_to_categories.categories_id = categories.categories_id INNER JOIN products ON products.products_id = products_to_categories.products_id INNER JOIN specials ON specials.products_id = products.products_id INNER JOIN products_description ON products.products_id = products_description.products_id INNER JOIN languages ON products_description.language_id = languages.languages_id WHERE categories.parent_id=285 AND languages.languages_id=1; I have also noticed in my Windows command shell that it does not process extremely long lines in pastes from the clipboard well. If you copied that straight from Access to a MySQL prompt, it would have been just one long line of information and the DOS command processor would have eventually stopped taking input mid-query. I suspect that is what caused your otherwise acceptable (and I use that term loosely ;-) ) query to be invalid. The last third of it never made it into the MySQL CLI. When I break my queries into shorter lines (human friendly) and paste them into the MySQL command line interface (CLI), everything works just fine. Just copy the entire query (line breaks and all) onto the clipboard and paste it at the MySQL prompt (if that's how you are doing it) and see if it works now. Notepad is my best friend when working in the CLI. I compose and format long queries in Notepad then copy-paste into MySQL. I know it's doing it the hard way (yes, I have and do use the GUI tools too) but it's how I prefer to analyze certain issues. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel Sousa [EMAIL PROTECTED] wrote on 01/26/2005 06:45:32 AM: I have a problem. 1064 - You have an error in your SQL syntax near '(((specials INNER JOIN (products_to_categories INNER JOIN categories ON products' at line 2 SELECT DISTINCT specials.specials_id, products_to_categories. products_id, categories.parent_id, products_description. products_name, products.products_price, products. products_tax_class_id, products.products_image, specials. specials_new_products_price, languages.languages_id FROM languages INNER JOIN (((specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories. categories_id) ON specials.products_id = products_to_categories. products_id) INNER JOIN products ON specials.products_id = products. products_id) INNER JOIN products_description ON specials.products_id = products_description.products_id) ON languages.languages_id
RE: error in your SQL syntax
Try Query Browser ( http://dev.mysql.com/downloads/query-browser ) for building queries for MySQL. Regards, Artem -Original Message- From: Daniel Sousa [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 11:18 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: error in your SQL syntax Thanks, works fines. I use access because i don´t know a GUI tool that make SQL querys more easy. Thanks all again, Daniel Sousa - Original Message - From: [EMAIL PROTECTED] To: Daniel Sousa Cc: mysql@lists.mysql.com Sent: Wednesday, 26 January, 2005 14:57 Subject: Re: error in your SQL syntax Here is your original query, reformatted merely so that we humans can read it better: SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM languages INNER JOIN ( ( (specials INNER JOIN (products_to_categories INNER JOIN categories ON products_to_categories.categories_id = categories.categories_id )ON specials.products_id = products_to_categories.products_id ) INNER JOIN products ON specials.products_id = products.products_id ) INNER JOIN products_description ON specials.products_id = products_description.products_id ) ON languages.languages_id = products_description.language_id WHERE ( ( (categories.parent_id)=285 ) AND ( (languages.languages_id)=1 ) ) This query design stinks (reeks) of being autogenerated by M$ Access. The excessive use of parentheses when they aren't needed and the nested JOINs just complicate the query unnecessarily. May I suggest a simplification? SELECT DISTINCT specials.specials_id , products_to_categories.products_id , categories.parent_id , products_description.products_name , products.products_price , products.products_tax_class_id , products.products_image , specials.specials_new_products_price , languages.languages_id FROM categories INNER JOIN products_to_categories ON products_to_categories.categories_id = categories.categories_id INNER JOIN products ON products.products_id = products_to_categories.products_id INNER JOIN specials ON specials.products_id = products.products_id INNER JOIN products_description ON products.products_id = products_description.products_id INNER JOIN languages ON products_description.language_id = languages.languages_id WHERE categories.parent_id=285 AND languages.languages_id=1; I have also noticed in my Windows command shell that it does not process extremely long lines in pastes from the clipboard well. If you copied that straight from Access to a MySQL prompt, it would have been just one long line of information and the DOS command processor would have eventually stopped taking input mid-query. I suspect that is what caused your otherwise acceptable (and I use that term loosely ;-) ) query to be invalid. The last third of it never made it into the MySQL CLI. When I break my queries into shorter lines (human friendly) and paste them into the MySQL command line interface (CLI), everything works just fine. Just copy the entire query (line breaks and all) onto the clipboard and paste it at the MySQL prompt (if that's how you are doing it) and see if it works now. Notepad is my best friend when working in the CLI. I compose and format long queries in Notepad then copy-paste into MySQL. I know it's doing it the hard way (yes, I have and do use the GUI tools too) but it's how I prefer to analyze certain issues. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel Sousa [EMAIL PROTECTED] wrote on 01/26/2005 06:45:32 AM: I have a problem. 1064 - You have an error in your SQL syntax near '(((specials INNER JOIN (products_to_categories INNER JOIN categories ON products' at line 2 SELECT DISTINCT specials.specials_id, products_to_categories. products_id, categories.parent_id, products_description. products_name, products.products_price, products. products_tax_class_id, products.products_image, specials. specials_new_products_price
SQL syntax error
I've had this going over on the php-general list. Thought I would throw it out here . Running PHP 4.0.22 Keep getting this error - SELECT PostStart, JobTitle, Industry, LocationState, VendorID FROM VendorJobsSELECT PostStart, JobTitle, Industry, LocationState, VendorID FROM VendorJobsWHERE VendorJobs.Industry = '2','3','4','5'Query failed: 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 '.Industry = '2','3','4','5'' at line 2 The first is the printout of my statement followed by the mysql_error . Here is my code. This is driving me nuts. Sorry $sql = SELECT PostStart, JobTitle, Industry, LocationState, VendorID FROM VendorJobs; echo $sql; //if ($Ind) $sql .= WHERE VendorJobs.Industry = $s_Ind; As you can see above s_ind is an array , comma delimited. To me this all looks fine. to the parser, well ;) Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax error
On Sat, Nov 13, 2004 at 12:30:43PM -0800, Stuart Felenstein wrote: $sql = SELECT PostStart, JobTitle, Industry, LocationState, VendorID FROM VendorJobs; echo $sql; //if ($Ind) $sql .= WHERE VendorJobs.Industry = $s_Ind; As you can see above s_ind is an array , comma delimited. To me this all looks fine. to the parser, well ;) You can't compare a column with a comma-delimited list of numbers like that, and you also want to make sure there is a space before the 'WHERE' keyword. You want: $sql .= WHERE VendorJobs.Industry IN ($s_Ind); -- Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax error
--- Jim Winstead [EMAIL PROTECTED] wrote: You can't compare a column with a comma-delimited list of numbers like that... What should the seperator be then ? Thank you Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax error
On Sat, Nov 13, 2004 at 12:46:12PM -0800, Stuart Felenstein wrote: --- Jim Winstead [EMAIL PROTECTED] wrote: You can't compare a column with a comma-delimited list of numbers like that... What should the seperator be then ? My point was that you can't compare a column with an array of numbers using the '=' operator. You have to use the IN operator, as in the line of code I posted: $sql .= WHERE VendorJobs.Industry IN ($s_Ind); (where $s_Ind is a comma-delimited list of numbers or quoted strings.) -- Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re:[SOLVED] SQL syntax error
--- Jim Winstead [EMAIL PROTECTED] wrote: My point was that you can't compare a column with an array of numbers using the '=' operator. You have to use the IN operator, as in the line of code I posted: Thank you Jim , it's working now! Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Syntax Problem
-Original Message-
From: David Blomstrom [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 10, 2004 4:08 PM
To: [EMAIL PROTECTED]
Subject: SQL Syntax Problem
$sql = 'SELECT
F.IDArea,
C.IDArea, C.Name, C.Pop, C.Nationality,
C.NationalityPlural, C.NationalityAdjective FROM cia_people
C, famarea2 F WHERE (C.Nationality is not null) AND (F.IDArea
= \'eur\') ORDER BY $_POST[\'order\'],
$_POST[\'direction\']'; $res = mysql_query($sql) or
die('Failed to run ' .
$sql . ' - ' . mysql_error());
If you change the single quotes on the outside of the SQL statement to
double quotes, PHP will parse variables inside the string. Try
$sql = SELECT F.IDArea, C.IDArea, C.Name, C.Pop, C.Nationality,
C.NationalityPlural, C.NationalityAdjective
. FROM cia_people C, famarea2 F
. WHERE (C.Nationality is not null) AND (F.IDArea = 'eur')
. ORDER BY {$_POST['order']}, {$_POST['direction']};
Notice that you need to put the variables in curly braces when you have
arrays being parsed.
--
Pat Adams
Applications Programmer
SYSCO Food Services of Dallas, L.P.
(469) 384-6009
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Problem
Think I found it. I made the changes with explanations of what I did.
If you have any further questions feel free to ask. Oh and this should
be on the list for others to see and maybe learn from
Respectfully,
Ligaya Turmelle
head[DATABASE CONNECTION]/head
body
div class=formdiv
form action=remote.php method=GET
select name=order !-- values here are what the switch is based
off of.. so I changed them--
option value=1'Country, etc./option
option value=2'Population/option
option value=3'Nationality/option
option value=4Nationality: Plural/option
option value=5Nationality: Adjective/option
option value=6Geographic Region/option
/select
input type=radio name=direction value=0+
input type=radio name=direction value=1-
input type=submit name=submit value=Submit
/form
/div
?php
$colors = array( '#eee', '', '#ff9', '', '#cff', '', '#cfc', '' );
$n=0;
$size=count($colors);
$result = mysql_query('select count(*) FROM cia_people C, famarea2 F
WHERE C.IDArea = F.IDArea AND F.IDParent = eur AND C.Nationality is
not null');
if (($result) (mysql_result ($result , 0) 0)) {
// continue here with the code that starts
//$res = mysql_query (SELECT * FROM type.
} else {
die('Invalid query: ' . mysql_error());
}
switch($_GET['order']) // use the Get method requires the $_GET
super variable
{ // see http://www.php.net/en/language.variables.predefined
in the manual
case 1:
$order = 'cia_people.Name';
break;
case 2:
$order = 'cia_people.Pop';
break;
case 3:
$order = 'cia_people.Nationality';
break;
case 4:
$order = 'cia_people.NationalityPlural';
break;
case 5:
$order = 'cia_people.NationalityAdjective';
break;
case 6:
$order = 'famarea2.IDParentReg';
break;
default:
$order = 'cia_people.Name';
break;
}
switch($_GET['direction']) // same reason as above
{
case 0:
$direction = 'ASC';
break;
case 1:
$direction = 'DESC';
break;
default:
$direction = 'ASC';
break;
}
$sql =
'SELECT F.IDArea,
C.IDArea,
C.Name,
C.Pop,
C.Nationality,
C.NationalityPlural,
C.NationalityAdjective
FROM cia_people C, famarea2 F
WHERE (C.Nationality is not null)
AND (F.IDArea = \'eur\')
ORDER BY ' . $order . ',' . $direction; /* here we just use the local
variables we moved everything into in the switch statements */
$res = mysql_query($sql) or die('Failed to run ' .
$sql . ' - ' . mysql_error());
echo 'table class=sortphp id=tab_cia_people_peo
thead
trthCountry/ththX/th/tr
/thead
tbody';
//!-- BeginDynamicTable --
$rowcounter=0;
while ($row = mysql_fetch_array ($res)) {
$c=$colors[$rowcounter++%$size];
echo tr style=\background-color:$c\ class='.
$row['Name'] .'. $_SERVER['PHP_SELF'] .'?id='.
$row['IDArea'] .
td class='tdname' '. $row['Name'] .'.
$row['Name'] ./td
tdnbsp;/td/tr\n;
}
?
/tr
/tbody
/table
/body
/html
David Blomstrom wrote:
Thanks. I guess this is turning into a PHP question
now, but I wondered if you tell me one more thing.
I made the change you suggested, and I now get this
parse error message:
Parse error: parse error, unexpected '{' in
C:\sites\geoworld\about\guide\world\eur\remote.php on
line 119
This is apparently the line it refers to, but it
doesn't make sense to me. I tried deleting the curly
braces/brackets, but it didn't fix anything.
ORDER BY ' . {$_POST['order']} . ',' .
{$_POST['direction']};
This is the script from Hell; every time I change it,
I get a new parse error!
Oh, yes - I also just discovered the single quotes in
my option values, like the one after Nationality:
option value=cia_people.Nationality'
I'm not sure where I picked those up; are they
supposed to be there? I removed them, but, again, it
didn't fix anything.
Thanks.
head[DATABASE CONNECTION]/head
body
div class=formdiv
form action=remote.php method=GET
select name=order
option value=cia_people.Name'Country,
etc./option
option
value=cia_people.Pop'Population/option
option
value=cia_people.Nationality'Nationality/option
option
value=cia_people.NationalityPlural'Nationality:
Plural/option
option
value=cia_people.NationalityAdjective'Nationality:
Adjective/option
option
value=famarea2.IDParentRegGeographic
Region/option
/select
input type=radio name=direction
value=0+
Re: SQL Syntax Problem
--- Ligaya Turmelle [EMAIL PROTECTED] wrote:
Think I found it. I made the changes with
explanations of what I did.
If you have any further questions feel free to ask.
Oh and this should
be on the list for others to see and maybe learn
from
Wow, thanks so much for going to all that trouble.
Several other people sent me tips, too. I feel bad to
tell you that it still doesn't work. I got an
immediate parse error.
Also, I don't know if I should continue this on the
list since it may be turning into more of a PHP
problem. But it is a cool script that others might
like to learn about. You can see a working example on
my website at
http://www.geoworld.org/reference/people/ (A good
column to sort is Population; you'll see China at the
top of the column if you choose DESCENDING.)
But this page only sorts data from ONE database table.
I'm now trying to make one that will sort fields from
multiple tables. The problem is that there are too
many elements, none of which I really understand. So
if I fix a parse error, the data doesn't display, and
if I fix it so the data displays, the PHP sorting
switch doesn't work.
I have learned a few things:
1. For some reason, I can't limit the display with a
regular WHERE query. It displays ALL the rows (all the
world's nations), even if I ask it to display rows
only WHERE F.IDParent = 'eur' (Eurasia).
To make it work, I have to use an official join,
like this:
FROM cia_people C
LEFT JOIN
famarea2 F ON C.IDArea = F.IDArea
WHERE F.IDParent = 'eur'
* * * * * * * * * *
2. I had the wrong field for the 'eur' values; it
should be F.IDParent, not IDArea.
* * * * * * * * * *
3. This is the most critical code:
ORDER BY ' . $_POST['order'] . ',' .
$_POST['direction'].';
It's usually the first to flake out, either causing a
parse error or simply not functioning. Every time I
modify another key function, I have to modify this
line, and it's too complex for me to re-engineer.
* * * * * * * * * *
4. I've received a variety of opinions on the quotes,
on functions throughout the source code. I'm not sure
sure if I should be using single quotes, double quotes
or no quotes at all in certain instances.
* * * * * * * * * *
5. There may also be a conflict with globals and
$_Post. Again, I don't understand this stuff. If I
understand correctly, I should either turn globals on
or off (or not have them in the first place), and use
$_Post in one instance but not the other?
* * * * * * * * * *
I'm amazed there isn't more information about this
script readily avaiable. It seems like such a useful
function, I thought it would be rather common.
Below is my current source code. It displays the data
correctly, without errors, but the sort function
doesn't work. Once again, it draws from two tables,
named cia_people and famarea2, joined by the field
they share in common, IDArea.
Every field cited as an option value is from table
cia_people except IDParentReg, which is the field from
table famarea2 I want to sort by. Actually, both
tables share a field named Name, but I think I
identified cia_people.Name in the query.
Don't feel obligated to pursue this; I've already
spent two days on it! :)
Thanks.
* * * * * * * * * *
head[DATABASE CONNECTION]/head
body
div class=formdiv
form action=remote.php method=GET
select name=order
option value=NameCountry, etc./option
option value=PopPopulation/option
option value=NationalityNationality/option
option value=NationalityPluralNationality:
Plural/option
option value=NationalityAdjectiveNationality:
Adjective/option
option value=IDParentRegGeographic
Region/option
/select
input type=radio name=direction value=0+
input type=radio name=direction value=1-
input type=submit name=submit value=Submit
/form
/div
?php
$colors = array( '#eee', '', '#ff9', '', '#cff', '',
'#cfc', '' );
$n=0;
$size=count($colors);
$result = mysql_query('select count(*) FROM cia_people
C, famarea2 F
WHERE C.IDArea = F.IDArea AND F.IDParent = eur
AND C.Nationality is not null');
if (($result) (mysql_result ($result , 0) 0)) {
// continue here with the code that starts
//$res = mysql_query (SELECT * FROM type.
} else {
die('Invalid query: ' . mysql_error());
}
switch($order)
{
case 1:
$order = 'Name';
break;
case 2:
$order = 'Pop';
break;
case 3:
$order = 'Nationality';
break;
case 4:
$order = 'NationalityPlural';
break;
case 5:
$order = 'NationalityAdjective';
break;
case 6:
$order = 'IDParentReg';
break;
default:
$order = 'Name';
break;
}
switch($direction)
{
case 0:
$direction = 'ASC';
break;
case 1:
$direction = 'DESC';
break;
default:
$direction = 'ASC';
break;
}
$sql =
SELECT F.IDArea,
C.IDArea,
C.Name,
C.Pop,
C.Nationality,
C.NationalityPlural,
C.NationalityAdjective
FROM
SQL Syntax Problem
This may be a purely PHP problem, but the error
message says SQL syntax. Check the manual that
corresponds to your MySQL server version...
More important, I haven't been able to find a solution
on any PHP forums. :)
This is the complete error message:
Failed to run SELECT F.IDArea, C.IDArea, C.Name,
C.Pop, C.Nationality, C.NationalityPlural,
C.NationalityAdjective FROM cia_people C, famarea2 F
WHERE (C.Nationality is not null) AND (F.IDArea =
'eur') ORDER BY $_POST['order'], $_POST['direction'] -
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 '['order'],
$_POST['direction']' at line 11
But the line it references isn't really line 11. This
is it:
$_POST[\'order\'], $_POST[\'direction\']';
$res = mysql_query($sql) or die('Failed to run ' .
$sql . ' - ' . mysql_error());
Someone suggested the problem is the word order. So
I replaced every instance of order with reorder
and got the same results. Another individual suggested
I remove the backward slashes in the first line, but I
had to add those to get rid of a series of parse
errors.
Does anyone have a clue what the problem/solution is?
Or can you tell me exactly what I'm supposed to look
up in the manual?
Thanks.
head[DATABASE CONNECTION]/head
body
div class=formdiv
form action=remote.php method=GET
select name=order
option value=cia_people.Name'Country,
etc./option
option
value=cia_people.Pop'Population/option
option
value=cia_people.Nationality'Nationality/option
option
value=cia_people.NationalityPlural'Nationality:
Plural/option
option
value=cia_people.NationalityAdjective'Nationality:
Adjective/option
option
value=famarea2.IDParentRegGeographic
Region/option
/select
input type=radio name=direction
value=0+
input type=radio name=direction
value=1-
input type=submit name=submit
value=Submit
/form
/div
?php
$colors = array( '#eee', '', '#ff9', '', '#cff', '',
'#cfc', '' );
$n=0;
$size=count($colors);
$result = mysql_query('select count(*) FROM cia_people
C, famarea2 F
WHERE C.IDArea = F.IDArea AND F.IDParent = eur
AND C.Nationality is not null');
if (($result) (mysql_result ($result , 0) 0)) {
// continue here with the code that starts
//$res = mysql_query (SELECT * FROM type.
} else {
die('Invalid query: ' . mysql_error());
}
switch($order)
{
case 1:
$order = 'cia_people.Name';
break;
case 2:
$order = 'cia_people.Pop';
break;
case 3:
$order = 'cia_people.Nationality';
break;
case 4:
$order = 'cia_people.NationalityPlural';
break;
case 5:
$order = 'cia_people.NationalityAdjective';
break;
case 6:
$order = 'famarea2.IDParentReg';
break;
default:
$order = 'cia_people.Name';
break;
}
switch($direction)
{
case 0:
$direction = 'ASC';
break;
case 1:
$direction = 'DESC';
break;
default:
$direction = 'ASC';
break;
}
//-- [...]
$sql = 'SELECT
F.IDArea,
C.IDArea, C.Name, C.Pop, C.Nationality,
C.NationalityPlural, C.NationalityAdjective
FROM
cia_people C, famarea2 F
WHERE
(C.Nationality is not null)
AND
(F.IDArea = \'eur\')
ORDER BY
$_POST[\'order\'], $_POST[\'direction\']';
$res = mysql_query($sql) or die('Failed to run ' .
$sql . ' - ' . mysql_error());
echo 'table class=sortphp id=tab_cia_people_peo
thead
trthCountry/ththX/th/tr
/thead
tbody';
//!-- BeginDynamicTable --
$rowcounter=0;
while ($row = mysql_fetch_array ($res)) {
$c=$colors[$rowcounter++%$size];
echo tr style=\background-color:$c\ class='.
$row['Name'] .'. $_SERVER['PHP_SELF'] .'?id='.
$row['IDArea'] .
td class='tdname' '. $row['Name'] .'.
$row['Name'] ./td
tdnbsp;/td/tr\n;
}
?
/tr
/tbody
/table
/body
/html
?
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Problem
It's not translating your vars to their respective values. I didn't look to see why... But MySQL doesn't know what $_POST['order'] is. David Blomstrom wrote: This may be a purely PHP problem, but the error message says SQL syntax. Check the manual that corresponds to your MySQL server version... Failed to run SELECT F.IDArea, C.IDArea, C.Name, C.Pop, C.Nationality, C.NationalityPlural, C.NationalityAdjective FROM cia_people C, famarea2 F WHERE (C.Nationality is not null) AND (F.IDArea = 'eur') ORDER BY $_POST['order'], $_POST['direction'] - 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 '['order'], $_POST['direction']' at line 11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Problem
First echo out the SQL and verify it is what you are expecting. If it
isn't try changing it to:
$sql =
'SELECT F.IDArea,
C.IDArea,
C.Name,
C.Pop,
C.Nationality,
C.NationalityPlural,
C.NationalityAdjective
FROM cia_people C, famarea2 F
WHERE (C.Nationality is not null)
AND (F.IDArea = \'eur\')
ORDER BY ' . {$_POST['order']} . ',' . {$_POST['direction']};
and try it again. Note the variables are outside the string and
surounded by brackets.
Respectfully,
Ligaya Turmelle
Michael J. Pawlowsky wrote:
It's not translating your vars to their respective values.
I didn't look to see why...
But MySQL doesn't know what
$_POST['order']
is.
David Blomstrom wrote:
This may be a purely PHP problem, but the error
message says SQL syntax. Check the manual that
corresponds to your MySQL server version...
Failed to run SELECT F.IDArea, C.IDArea, C.Name,
C.Pop, C.Nationality, C.NationalityPlural,
C.NationalityAdjective FROM cia_people C, famarea2 F
WHERE (C.Nationality is not null) AND (F.IDArea =
'eur') ORDER BY $_POST['order'], $_POST['direction'] -
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 '['order'],
$_POST['direction']' at line 11
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Assistance with SQL syntax: pulling duplicates back
I think this is an easy question...I've set up a SQL statement like so:
SELECT resume.Section_Value, candidate.Location FROM resume, candidate WHERE
resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST ('html')
AND candidate.Location LIKE '%CA%' OR 'California'
--
And where 'html' should come up in 1 entry, I get duplicates when printing
out the field to the screen:
--
html unix network php Over 10 years of HTML experience. 2 years networking
administration.
html unix network php Over 10 years of HTML experience. 2 years networking
administration.
--
I can't decide if this is my code, or the SQL syntax. Would it be possible,
based on this statement, to have pulled back duplicates from the same
record?
- Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Assistance with SQL syntax: pulling duplicates back
You are joining two tables, resume and candidate. Without a join condition,
you get a Cartesian product, each row of the first table paired with each
and every row of the second table. (Some on this list would go so far as to
say that's not even a join.) You need to specify how rows in resume should
be lined up with rows in candidate. You are filtering the resulting rows
with your WHERE conditions, but that's not the same thing.
I would expect that you have a relationship between resumes and candidates.
One of them should have a column which holds a key with the ID value in
the other. In the first case, you would add something like
resume.candidate_id = candidate.id
to your WHERE clause, and in the second case you would add something like
candidate.resume_id = resume.id
to your WHERE clause. I'd expect one candidate per resume, but possibly
more than one resume per candidate, so I'd expect the first case.
Michael
Eve Atley wrote:
I think this is an easy question...I've set up a SQL statement like so:
SELECT resume.Section_Value, candidate.Location FROM resume, candidate WHERE
resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST ('html')
AND candidate.Location LIKE '%CA%' OR 'California'
--
And where 'html' should come up in 1 entry, I get duplicates when printing
out the field to the screen:
--
html unix network php Over 10 years of HTML experience. 2 years networking
administration.
html unix network php Over 10 years of HTML experience. 2 years networking
administration.
--
I can't decide if this is my code, or the SQL syntax. Would it be possible,
based on this statement, to have pulled back duplicates from the same
record?
- Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Assistance with SQL syntax: pulling duplicates back
You have a cartesian join because you do not have join criteria between the
resume and candidate tables.
-Original Message-
From: Eve Atley
To: [EMAIL PROTECTED]
Sent: 8/17/04 12:22 PM
Subject: Assistance with SQL syntax: pulling duplicates back
I think this is an easy question...I've set up a SQL statement like so:
SELECT resume.Section_Value, candidate.Location FROM resume, candidate
WHERE
resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST
('html')
AND candidate.Location LIKE '%CA%' OR 'California'
--
And where 'html' should come up in 1 entry, I get duplicates when
printing
out the field to the screen:
--
html unix network php Over 10 years of HTML experience. 2 years
networking
administration.
html unix network php Over 10 years of HTML experience. 2 years
networking
administration.
--
I can't decide if this is my code, or the SQL syntax. Would it be
possible,
based on this statement, to have pulled back duplicates from the same
record?
- Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Assistance with SQL syntax: pulling duplicates back
Hi Eve,
You have made a very common mistake while using the comma-join method. I
think if I translate your implicit inner join to an explicit inner join
you will spot your own mistake:
SELECT resume.Section_Value, candidate.Location
FROM resume
INNER JOIN candidate
WHERE resume.Section_ID = '1'
AND MATCH (resume.Section_Value) AGAINST ('html')
AND candidate.Location LIKE '%CA%' OR 'California'
You did not link your two tables. You didn't say that this column in
resume matches up with this column in candidate so the query engine put
together what is known as a Cartesian product. You are finding all of the
possible combinations of rows from both tables where your WHERE clause is
true.
You said you only get one row from MATCH ... ('HTML') (that's the
contribution from the resume table). However, you are getting two rows
from the candidate table based on location like. That's why you had
two rows in your results. Imagine if you had gotten 3 rows back from the
Match... clause... You would have had 6 records in your results and been
really confused, eh?
You can cure this by somehow equating the two tables. Depending on what
form of INNER JOIN you want to write you ether need another WHERE
condition or an ON clause.
FORM 1(I prefer this form):
SELECT resume.Section_Value, candidate.Location
FROM resume
INNER JOIN candidate
ON candidate.id = resume.candidate_id
WHERE resume.Section_ID = '1'
AND MATCH (resume.Section_Value) AGAINST ('html')
AND candidate.Location LIKE '%CA%' OR 'California'
FORM 2 (in comma-joined format):
SELECT resume.Section_Value, candidate.Location
FROM resume, candidate
WHERE resume.Section_ID = '1'
AND MATCH (resume.Section_Value) AGAINST ('html')
AND candidate.Location LIKE '%CA%' OR 'California'
AND candidate.id = resume.candidate_id
Like I said, it's a common mistake when you write your queries that way
(comma-join) to leave out the relationship condition.
Best Wishes,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Eve Atley [EMAIL PROTECTED] wrote on 08/17/2004 01:22:45 PM:
I think this is an easy question...I've set up a SQL statement like so:
SELECT resume.Section_Value, candidate.Location FROM resume, candidate
WHERE
resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST
('html')
AND candidate.Location LIKE '%CA%' OR 'California'
--
And where 'html' should come up in 1 entry, I get duplicates when
printing
out the field to the screen:
--
html unix network php Over 10 years of HTML experience. 2 years
networking
administration.
html unix network php Over 10 years of HTML experience. 2 years
networking
administration.
--
I can't decide if this is my code, or the SQL syntax. Would it be
possible,
based on this statement, to have pulled back duplicates from the same
record?
- Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Assistance with SQL syntax: pulling duplicates back
Eve,
From your earlier post, I see it should be
resume.Candidate_ID = candidate.Candidate_ID
===
I should also point out that there are several problems with your Location
matching. You have
candidate.Location LIKE '%CA%' OR 'California'
First, this evaluates as
(candidate.Location LIKE '%CA%') OR ('California')
'California' evaluates as false, so only the first part can match. You
probably meant
candidate.Location LIKE '%CA%' OR candidate.Location LIKE 'California'
In any case, '%CA%' matches 'California', so the latter part is still
redundant. '%CA%' also matches 'Ocala', or any other string which contains
'ca'. I don't think that's what you want. Also, if the LIKE comparison
string starts with a wildcard, an index on Location can't be used.
If candidate.Location contains only the state, then there is no need for the
wildcards:
candidate.Location LIKE 'CA' OR candidate.Location LIKE 'California'
In fact, you wouldn't even need LIKE then:
candidate.Location IN ('CA', 'California')
So, now your query would be
SELECT resume.Section_Value, candidate.Location
FROM resume JOIN candidate ON resume.Candidate_ID = candidate.Candidate_ID
WHERE resume.Section_ID = '1'
AND MATCH (resume.Section_Value) AGAINST ('html')
AND candidate.Location IN ('CA', 'California');
You could improve this still further by changing all the states to the
2-letter form in your table and requiring the 2-letter state codes in the
future. Then Location could be changed to the smaller, faster CHAR(2), and
the last part of the WHERE clause would be
candidate.Location = 'CA'
On the other hand, if candidate.Location contains more than just the state,
you're in trouble. It will be difficult to reliably separate rows which
contain CA meaning California from rows which contain ca as part of
something else. Possible, but difficult, and the solution will almost
certainly prevent use of an index on Location.
Michael
Michael Stassen wrote:
You are joining two tables, resume and candidate. Without a join
condition, you get a Cartesian product, each row of the first table
paired with each and every row of the second table. (Some on this list
would go so far as to say that's not even a join.) You need to specify
how rows in resume should be lined up with rows in candidate. You are
filtering the resulting rows with your WHERE conditions, but that's not
the same thing.
I would expect that you have a relationship between resumes and
candidates. One of them should have a column which holds a key with the
ID value in the other. In the first case, you would add something like
resume.candidate_id = candidate.id
to your WHERE clause, and in the second case you would add something like
candidate.resume_id = resume.id
to your WHERE clause. I'd expect one candidate per resume, but possibly
more than one resume per candidate, so I'd expect the first case.
Michael
Eve Atley wrote:
I think this is an easy question...I've set up a SQL statement like so:
SELECT resume.Section_Value, candidate.Location FROM resume, candidate
WHERE
resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST ('html')
AND candidate.Location LIKE '%CA%' OR 'California'
--
And where 'html' should come up in 1 entry, I get duplicates when
printing
out the field to the screen:
--
html unix network php Over 10 years of HTML experience. 2 years
networking
administration.
html unix network php Over 10 years of HTML experience. 2 years
networking
administration.
--
I can't decide if this is my code, or the SQL syntax. Would it be
possible,
based on this statement, to have pulled back duplicates from the same
record?
- Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Assistance with SQL syntax: pulling duplicates back
Eve,
Best to keep threads on the list. Others may have better ideas, and future
readers may benefit.
The comparison
candidate.Location IN ('CA', 'California')
will match 'CA' and 'California', but will not match 'Cupertino, CA' because
it isn't either of those strings. To match that row as well, you would need
to use LIKE and a wildcard (or RLIKE) instead. Something like:
candidate.Location LIKE '%CA' OR candidate.Location LIKE '%California'
Unfortunately, the index can't be used then because of the wildcard. Mysql
will have to look at every row. The more rows you have, the worse the
impact will be. That may be OK if the rest of your WHERE criteria
sufficiently pare down the number of rows first, but you'd have to test to
be sure.
Even then, this method will generally yield incorrect matches. For example,
consider
candidate.Location LIKE '%NE' OR candidate.Location LIKE '%Nebraska'
That would match 'Bangor, Maine' because it ends with 'ne'. Or how about
candidate.Location LIKE '%IA' OR candidate.Location LIKE '%Iowa'
That would match 'California' because it ends with 'ia'. See the problem?
We could reduce these by making the comparisons case-sensitive with the
BINARY keyword:
candidate.Location LIKE BINARY '%IA'
OR candidate.Location LIKE '%Iowa'
That would no longer match 'California', but it would still match 'CALIFORNIA'.
Finally, consider that
candidate.Location LIKE BINARY '%CA'
OR candidate.Location LIKE '%California'
will not match 'Pasadena, California, USA'.
I doubt it's what you wanted to hear, but the problem is that the Location
column is poorly designed. It contains the answers to different questions.
That is, multiple/different kinds of data are crammed into one column.
The only sure-fire way to perform searches by state is to have a state
column. You need to fix the db, and its data. If you really cannot fix the
db and data, you will have to live with slow queries and imperfect results.
Michael
Eve Atley wrote:
Thanks for helping me out, Michael! I've learned *several* things today.
I have ended up using:
candidate.Location IN ('CA', 'California')
However, you're correct: candidate.Location can contain more than just the
state, at times. It was the way the database was previously designed,
unfortunately. It can include:
'Cupertino, CA'
or
'CA'
or
'California'
Will this still work for what I require?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Syntax Question
Thank you for trying to help me.
The output is wrong
I get either
Event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Or
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
But not what I need
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 04, 2004 12:08 AM
To: Karl-Heinz Schulz; [EMAIL PROTECTED]
Subject: Re: SQL Syntax Question
- Original Message -
From: Karl-Heinz Schulz [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, August 03, 2004 9:18 PM
Subject: SQL Syntax Question
I tried to get an answer on the PHP mailing list and I was told that this
list would be quicker to get me a solution.
I have two tables Event and Eventdetails (structures dump can be found
at the end of the message).
I want to display all events and the related information from the
eventdetails table like
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
Etc.
I cannot figure it out.
Here is my PHP code.
--
--
?php
require(../admin/functions.php);
include(../admin/header.inc.php);
?
?
$event_query = mysql_query(select id, inserted, information, eventname,
date, title from event order by inserted desc LIMIT 0 , 30);
while($event = mysql_fetch_row($event_query)){
print(bspan style=\font-family: Arial, Helvetica,
sans-serif;color:#003300;font-size:14px;\.html_decode($event[5])./span
/bbr);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($event[4])./spanbr);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($event[2])./spanp);
$eventdetail_query = mysql_query(select informations, titles, file_name
from eventdetail, event where eventdetail.event =.$event[0]);
//$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail, event where eventdetail.event = event.id);
while($eventdetail = mysql_fetch_row($eventdetail_query)){
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[0])./span);
print(nbspspan style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[1])./span);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[2])./spanp);
}
}
?
--
--
What am I missing?
TIA
Karl-Heinz
#
# Table structure for table `event`
#
CREATE TABLE event (
id smallint(2) unsigned NOT NULL auto_increment,
veranstaltung smallint(2) unsigned NOT NULL default '0',
inserted date NOT NULL default '-00-00',
information text NOT NULL,
eventname text NOT NULL,
date varchar(30) NOT NULL default '',
title varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Table structure for table `eventdetail`
#
CREATE TABLE eventdetail (
id smallint(2) unsigned NOT NULL auto_increment,
event smallint(2) NOT NULL default '0',
informations text NOT NULL,
titles varchar(100) NOT NULL default '',
file_name varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
Tracking #: 5CF2A36BDC27D14BA1C3A19CBAC7214ED510CB7E
What you've already given us is great but it would really help if you
described the problem you are encountering. It's not clear whether you are
getting error messages from MySQL or your result sets simply don't match
your expectations or if you are getting compile errors from php.
If you could state just what the problem is, and ideally show the result you
are getting (if any) versus the result you expected, it would be easier to
help you.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Question
Karl-Heinz Schulz wrote: Thank you for trying to help me. The output is wrong I get either Event 1 Event 2 Details 1 for event 1 Details 2 for event 1 Details 3 for event 1 that query is wrong : $eventdetail_query = mysql_query(select informations, titles, file_name from eventdetail, event where eventdetail.event =.$event[0]); try : select informations, titles, file_name from eventdetail, event where event.id=.$event[0] AND event.id=eventdetails.event -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Syntax Question
Philippe,
I changed my to the following but the result is now (I deleted the print
stuff for better reading)
?
$event_query = mysql_query(select id, inserted, information, eventname,
date, title from event order by inserted desc LIMIT 0 , 30);
while($event = mysql_fetch_row($event_query)){
$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail, event where event.id=eventdetail.event AND
event.id=.$event[0]);
while($eventdetail = mysql_fetch_row($eventdetail_query)){
}
}
?
Event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
But I would need
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
Is this even possible?
TIA
-Original Message-
From: Philippe Poelvoorde [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 04, 2004 5:52 AM
To: Karl-Heinz Schulz
Cc: [EMAIL PROTECTED]
Subject: Re: SQL Syntax Question
Karl-Heinz Schulz wrote:
Thank you for trying to help me.
The output is wrong
I get either
Event 1
Event 2
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
that query is wrong :
$eventdetail_query = mysql_query(select informations, titles, file_name
from eventdetail, event where eventdetail.event =.$event[0]);
try :
select informations, titles, file_name
from eventdetail, event where event.id=.$event[0] AND
event.id=eventdetails.event
Tracking #: 3842A5D2EB81014B918FDB71F1DE0830A35E8D56
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Question
- Original Message -
From: Karl-Heinz Schulz [EMAIL PROTECTED]
To: 'Philippe Poelvoorde' [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, August 04, 2004 6:41 AM
Subject: RE: SQL Syntax Question
Philippe,
I changed my to the following but the result is now (I deleted the print
stuff for better reading)
?
$event_query = mysql_query(select id, inserted, information, eventname,
date, title from event order by inserted desc LIMIT 0 , 30);
while($event = mysql_fetch_row($event_query)){
$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail, event where event.id=eventdetail.event AND
event.id=.$event[0]);
while($eventdetail = mysql_fetch_row($eventdetail_query)){
}
}
?
Karl-Heinz,
I used the following SQL in a script and got the answer that I think you
want:
select informations, titles, file_name
from eventdetail d inner join event e on e.veranastaltung = d.event
where d.event = 1
This gave me just the eventdetails for event 1.
This is not in php format of course. I don't know php but it looks similar
to other languages I know so I'm guessing that you would write it as follows
in php:
$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail d inner join event e on e.veranstaltung = d.event
where event.id=.$event[0]);
Explanation:
Since you named two tables in the 'from' clause of the eventdetail query,
you are clearly attempting to join the tables. I'm assuming you want an
inner join. In other words, you only want to show details if there is a
corresponding event row that matches your detail row. To get a proper join,
you need to identify what the two tables have in common. If I understand
your data correctly, the veranstaltung column in the Event table is going to
have the same value as the event column in the Eventdetail table when the
rows are describing the same event. Therefore, that is what I put in the
'on' clause of the query. The 'where' clause is the one I'm least sure how
to write in php but, based on what you had in your queries, I assume that
this is the way to tell the query to return only rows where the event column
in the join result has the same value as the event value in the event row
currently being processed in the outer loop.
In short, you were doing a join implicitly but hadn't properly specified the
joining condition so you weren't getting the rows you really wanted.
By the way, I really wasn't completely clear on the meaning of the data in
the tables so I made some guesses about the contents of each column. This is
the script I wrote to create and populate the tables. Your original event
query, which is unchanged, appears after that and my best guess for the
eventdetail query is at the end.
-
use tmp;
#Event table contains one row for each event.
select 'Drop/create Event table';
drop table if exists event;
create table if not exists event
(id smallint(2) unsigned not null auto_increment,
veranstaltung smallint(2) not null default '0',
inserted date not null default '-00-00',
information text not null,
eventname text not null,
date varchar(30) not null default '',
title varchar(100) not null default '',
primary key(id)
) TYPE=MyISAM;
select 'Populate Event table';
insert into event (veranstaltung, inserted, information, eventname, date,
title) values
(1, '2004-04-20', 'information-01', 'Canada Day', '2004-07-01', 'title-01'),
(2, '2004-05-03', 'information-02', 'Labour Day', '2004-09-04', 'title-02'),
(3, '2004-08-15', 'information-03', 'Christmas Day', '2004-12-25',
'title-03');
select 'Display Event table';
select * from event;
#Event_Detail table contains one row for each aspect of an event.
select 'Drop/create Eventdetail table';
drop table if exists eventdetail;
create table if not exists eventdetail
(id smallint(2) unsigned not null auto_increment,
event smallint(2) not null default '0',
informations text not null,
titles varchar(100) not null default '',
file_name varchar(100) not null default '',
primary key(id)
) TYPE=MyISAM;
select 'Populate Eventdetail table';
insert into eventdetail (event, informations, titles, file_name) values
(1, 'information-01a', 'title-01a', 'file-01a'),
(1, 'information-01b', 'title-01b', 'file-01b'),
(1, 'information-01c', 'title-01c', 'file-01c'),
(2, 'information-02a', 'title-02a', 'file-02a'),
(2, 'information-02b', 'title-02b', 'file-02b'),
(2, 'information-02c', 'title-02c', 'file-02c'),
(3, 'information-03a', 'title-03a', 'file-03a'),
(3, 'information-03b', 'title-03b', 'file-03b'),
(3, 'information-03c', 'title-03c', 'file-03c');
select 'Display Eventdetail table';
select * from eventdetail;
select 'Event query';
select id, inserted, information, eventname, date, title
from event
order by inserted desc limit 0, 30;
select 'Eventdetail query';
select informations, titles, file_name
SQL Syntax Question
I tried to get an answer on the PHP mailing list and I was told that this
list would be quicker to get me a solution.
I have two tables Event and Eventdetails (structures dump can be found
at the end of the message).
I want to display all events and the related information from the
eventdetails table like
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
Etc.
I cannot figure it out.
Here is my PHP code.
?php
require(../admin/functions.php);
include(../admin/header.inc.php);
?
?
$event_query = mysql_query(select id, inserted, information, eventname,
date, title from event order by inserted desc LIMIT 0 , 30);
while($event = mysql_fetch_row($event_query)){
print(bspan style=\font-family: Arial, Helvetica,
sans-serif;color:#003300;font-size:14px;\.html_decode($event[5])./span
/bbr);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($event[4])./spanbr);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($event[2])./spanp);
$eventdetail_query = mysql_query(select informations, titles, file_name
from eventdetail, event where eventdetail.event =.$event[0]);
//$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail, event where eventdetail.event = event.id);
while($eventdetail = mysql_fetch_row($eventdetail_query)){
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[0])./span);
print(nbspspan style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[1])./span);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[2])./spanp);
}
}
?
What am I missing?
TIA
Karl-Heinz
#
# Table structure for table `event`
#
CREATE TABLE event (
id smallint(2) unsigned NOT NULL auto_increment,
veranstaltung smallint(2) unsigned NOT NULL default '0',
inserted date NOT NULL default '-00-00',
information text NOT NULL,
eventname text NOT NULL,
date varchar(30) NOT NULL default '',
title varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Table structure for table `eventdetail`
#
CREATE TABLE eventdetail (
id smallint(2) unsigned NOT NULL auto_increment,
event smallint(2) NOT NULL default '0',
informations text NOT NULL,
titles varchar(100) NOT NULL default '',
file_name varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
Tracking #: 5CF2A36BDC27D14BA1C3A19CBAC7214ED510CB7E
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Question
- Original Message -
From: Karl-Heinz Schulz [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, August 03, 2004 9:18 PM
Subject: SQL Syntax Question
I tried to get an answer on the PHP mailing list and I was told that this
list would be quicker to get me a solution.
I have two tables Event and Eventdetails (structures dump can be found
at the end of the message).
I want to display all events and the related information from the
eventdetails table like
Event 1
Details 1 for event 1
Details 2 for event 1
Details 3 for event 1
Event 2
Details 1 for event 2
Details 2 for event 2
Details 3 for event 2
Etc.
I cannot figure it out.
Here is my PHP code.
--
--
?php
require(../admin/functions.php);
include(../admin/header.inc.php);
?
?
$event_query = mysql_query(select id, inserted, information, eventname,
date, title from event order by inserted desc LIMIT 0 , 30);
while($event = mysql_fetch_row($event_query)){
print(bspan style=\font-family: Arial, Helvetica,
sans-serif;color:#003300;font-size:14px;\.html_decode($event[5])./span
/bbr);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($event[4])./spanbr);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($event[2])./spanp);
$eventdetail_query = mysql_query(select informations, titles, file_name
from eventdetail, event where eventdetail.event =.$event[0]);
//$eventdetail_query = mysql_query(select titles, informations, file_name
from eventdetail, event where eventdetail.event = event.id);
while($eventdetail = mysql_fetch_row($eventdetail_query)){
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[0])./span);
print(nbspspan style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[1])./span);
print(span style=\font-family: Arial, Helvetica,
sans-serif;font-size:12px;\.html_decode($eventdetail[2])./spanp);
}
}
?
--
--
What am I missing?
TIA
Karl-Heinz
#
# Table structure for table `event`
#
CREATE TABLE event (
id smallint(2) unsigned NOT NULL auto_increment,
veranstaltung smallint(2) unsigned NOT NULL default '0',
inserted date NOT NULL default '-00-00',
information text NOT NULL,
eventname text NOT NULL,
date varchar(30) NOT NULL default '',
title varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Table structure for table `eventdetail`
#
CREATE TABLE eventdetail (
id smallint(2) unsigned NOT NULL auto_increment,
event smallint(2) NOT NULL default '0',
informations text NOT NULL,
titles varchar(100) NOT NULL default '',
file_name varchar(100) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;
Tracking #: 5CF2A36BDC27D14BA1C3A19CBAC7214ED510CB7E
What you've already given us is great but it would really help if you
described the problem you are encountering. It's not clear whether you are
getting error messages from MySQL or your result sets simply don't match
your expectations or if you are getting compile errors from php.
If you could state just what the problem is, and ideally show the result you
are getting (if any) versus the result you expected, it would be easier to
help you.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL -- SQL syntax error.....
When I use this SQL statement, ...
--snip--
UPDATE BUSINESS_CATEGORY SET
(BUSINESS_CATEGORY.BUS_CAT,BUSINESS_CATEGORY.BUS_DESC) =
('JUNKKK','JUNK123KK') WHERE BUSINESS_CATEGORY.BUS_CAT_ID = '733788'
--snip--
I get the SQL syntax error saying,
--snip--
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
'(BUSINESS_CATEGORY.BUS_CAT,BUSINESS_CATEGORY.BUS_DESC) = ('JUNK
--snip--
So, I looked up in MySQL's documentation at
http://dev.mysql.com/doc/mysql/en/UPDATE.html, it didn't say anything
helpful about the SQL syntax. So, what did I do wrong??
Thanks,
Scott F.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL -- SQL syntax error.....
From: Scott Fletcher [mailto:[EMAIL PROTECTED]
When I use this SQL statement, ...
--snip--
UPDATE BUSINESS_CATEGORY SET
(BUSINESS_CATEGORY.BUS_CAT,BUSINESS_CATEGORY.BUS_DESC) =
('JUNKKK','JUNK123KK') WHERE BUSINESS_CATEGORY.BUS_CAT_ID = '733788'
--snip--
I get the SQL syntax error saying,
--snip--
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
'(BUSINESS_CATEGORY.BUS_CAT,BUSINESS_CATEGORY.BUS_DESC) = ('JUNK
--snip--
So, I looked up in MySQL's documentation at
http://dev.mysql.com/doc/mysql/en/UPDATE.html, it didn't say anything
helpful about the SQL syntax. So, what did I do wrong??
I don't think MySQL supports using parens in that regard.
Try this:
UPDATE BUSINESS_CATEGORY SET
BUSINESS_CATEGORY.BUS_CAT = 'JUNKKK',
BUSINESS_CATEGORY.BUS_DESC = 'JUNK123KK'
WHERE BUSINESS_CATEGORY.BUS_CAT_ID = '733788'
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL -- SQL syntax error.....
Try this:
UPDATE BUSINESS_CATEGORY
SET BUSINESS_CATEGORY.BUS_CAT = 'JUNKKK'
,BUSINESS_CATEGORY.BUS_DESC = 'JUNK123KK'
WHERE BUSINESS_CATEGORY.BUS_CAT_ID = '733788'
Scott Fletcher
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
com cc:
Fax to:
06/16/2004 04:08 Subject: MySQL -- SQL syntax
error.
PM
When I use this SQL statement, ...
--snip--
UPDATE BUSINESS_CATEGORY SET
(BUSINESS_CATEGORY.BUS_CAT,BUSINESS_CATEGORY.BUS_DESC) =
('JUNKKK','JUNK123KK') WHERE BUSINESS_CATEGORY.BUS_CAT_ID = '733788'
--snip--
I get the SQL syntax error saying,
--snip--
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
'(BUSINESS_CATEGORY.BUS_CAT,BUSINESS_CATEGORY.BUS_DESC) = ('JUNK
--snip--
So, I looked up in MySQL's documentation at
http://dev.mysql.com/doc/mysql/en/UPDATE.html, it didn't say anything
helpful about the SQL syntax. So, what did I do wrong??
Thanks,
Scott F.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql syntax error
I'm using amavisd-new -20030616p9, RH 3.0 ES and mysql 3.23.58-1 trying to do sql lookups for user prefs. I've done this before and have compared my sql statements and can't figure out the problem. When i start amavisd-new with the debug switch, here's what i get: # /usr/local/sbin/amavisd debug Error in config file /etc/amavisd.conf: syntax error at /etc/amavisd.conf line 829, near ' ORDER BY users.priority DESC '; Here are the lines from my /etc/amavisd.conf file: $sql_select_policy = 'SELECT *,users.vuid FROM users,policy_names'. ' WHERE (users.policy=policy_names.id) AND (users.username IN (%k))'. ' ORDER BY users.priority DESC '; Please help! Thanks in advance, Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL syntax? [Select within Insert]
Struggling to get an INSERT to work, can anyone help?
Here's my scenario:
Students[table]
Student_ID [primary key, auto-increment]
Student_name
Student_sex
Extra_Credit[table]
EC_ID [primary key, auto-increment]
Student_ID
Points
First: INSERT INTO Students (Student_name, Student_sex) VALUES('Josh
Baxter, M);
[suceeds]
Then: INSERT INTO Extra_Credit (Student_ID, Points) SELECT MAX(Student_ID)
from Students,
(1) ...VALUE ('25');
or
(2) ... '25' as Points;
Either one fails... Any hints on syntax to achieve the insert (pulling the
Student_ID in from the just modified record in the Students table)?
TIA!
Eric Pederson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax? [Select within Insert]
Hi,
Then: INSERT INTO Extra_Credit (Student_ID, Points) SELECT MAX(Student_ID)
from Students,
(1) ...VALUE ('25');
or
(2) ... '25' as Points;
I think this is your query:
INSERT INTO Extra_Credit(Student_ID, Points) SELECT MAX(Student_ID), '25' from Students
Take care,
Aleksandar
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax? [Select within Insert]
As I understand it, you don't really want the MAX(Student_ID), you want
the actual Student_ID of the last insert. It is important to note that
they are not necessarily the same. If you insert Student 24, then I
insert Student 25, then you check MAX(Student_ID), you will get 25, not
24. Hence, you'll end up using the wrong value. Also, some table types
will reuse IDs from deleted rows.
Fortunately, mysql provides a solution. The LAST_INSERT_ID() function
returns the most recent AUTO_INCREMENT value. It is also
connection-specific, so it is not affected by what someone else is
doing. So, your second statement should be
INSERT INTO Extra_Credit (Student_ID, Points)
VALUES (LAST_INSERT_ID(), 25)
Michael
EP wrote:
Struggling to get an INSERT to work, can anyone help?
Here's my scenario:
Students[table]
Student_ID[primary key, auto-increment]
Student_name
Student_sex
Extra_Credit[table]
EC_ID [primary key, auto-increment]
Student_ID
Points
First: INSERT INTO Students (Student_name, Student_sex) VALUES('Josh
Baxter, M);
[suceeds]
Then: INSERT INTO Extra_Credit (Student_ID, Points) SELECT
MAX(Student_ID) from Students,
(1)...VALUE ('25');
or
(2)... '25' as Points;
Either one fails... Any hints on syntax to achieve the insert (pulling
the Student_ID in from the just modified record in the Students table)?
TIA!
Eric Pederson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL syntax error
Hi All I have been pushing my syslogs to the following mysql table However whenever it sees lines with a ' (apostrophe) it complains about SQL syntax Here are two lines with ' from my syslog: Jan 1 03:58:15 dal-svcs-02.inet.qwest.net 203: *Jan 1 08:58:13.926 UTC: %PFINIT-SP-5-CONFIG_SYNC: Sync'ing the startup configuration to the standby Router Jan 01 00:57:06 [65.119.67.5.17.126] %NTP-W-NOTIMEZONE, Time will not be set until timezone is configured; use 'system set timezone' to configure Here is how it complains: failed to run query: 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 'ing the startup configuration to the standby Router ')' at line failed to run query: 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 'system set timezone' to configure ', '087) (RST) ')' at line 1 Here is how my mysql table looks like +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | timestamp | varchar(16) | YES | | NULL|| | host | varchar(255) | YES | | NULL|| | prog | varchar(255) | YES | | NULL|| | mesg | text | YES | | NULL|| +---+--+--+-+-++ Is there anyway I can modify the host,prog and mesg field types to accept apostrophe as part of the record ? Thanks for all the help (Happy New Year !!) -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=getsearch=0x8B686E08 There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL syntax error
Hi Asif, Asif Iqbal wrote: I have been pushing my syslogs to the following mysql table However whenever it sees lines with a ' (apostrophe) it complains about SQL syntax You need to escape those reserved characters, i.e. have ' replaced by \' because otherwise mysql will treat the apostrophe as the string delimiting character. Greets Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql syntax
Hello, my name's Marlon. I have a question about sql and I need some help! How can I do something like it using mysql? update registre set (name='NewName' where lastname='OldLastName'), (name='OldName' where lastname='NewLastName'); Tank you Marlon _ Voce quer um iGMail protegido contra vírus e spams? Clique aqui: http://www.igmailseguro.ig.com.br Ofertas imperdíveis! Link: http://www.americanas.com.br/ig/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql syntax
Hello, my name's Marlon. I have a question about sql and I need some help! How can I do something like it using mysql? update registre set (name='NewName' where lastname='OldLastName'), (name='OldName' where lastname='NewLastName'); I _believe_ you can do it this way. I'm sure someone will correct me if I am mistaken: UPDATE registare SET CASE lastname WHEN 'OldLastName' THEN name = 'NewName' WHEN 'NewLastName' THEN name = 'OldName' END; Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql syntax problem with mysql 3.23.49
Hello, This sql query works fine with mysql 4.0.15, but it gives an error with mysql 3.23.49: SELECT officiele_naam, rechtsvorm, activiteit1, activiteit2, adres, postnummer, gemeente, Biogarantie, Hefboom, Netwerk_Vlaanderen, Vibe, Fair_Trade, NULL , Vosec, Solidr, Demeter, Europees_Ecolabel, Belgisch_Sociaal_label, vestiging_ID FROM onderneming JOIN vestiging ON onderneming.bedrijfsnummer = vestiging.bedrijfsnummer LEFT JOIN rubrieken AS r1 ON onderneming.rubriek_ID_1 = r1.rubriek_ID LEFT JOIN rubrieken AS r2 ON rubriek_ID_2 = r2.rubriek_ID LEFT JOIN rubrieken AS r3 ON onderneming.rubriek_ID_3 = r3.rubriek_ID WHERE 1 ORDER BY officiele_naam LIMIT 100 #1064 - You have an error in your SQL syntax near 'ON onderneming.bedrijfsnummer = vestiging.bedrijfsnummer LEFT JOIN rubrieken AS' at line 3 What exactly is the cause of this error here? Could it be that mysql3 does not support the AS clause within a LEFT JOIN clause? How could I rewrite this query to make it work with mysql 3.23.49? Frederik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL syntax on an UPDATE
Hi All, I'm programming a method to delete a parent record and all its children in a child table in one go. This is what I have: UPDATE item i, category_item ci SET i.date_deleted = ?, ci.date_deleted = ? WHERE ci.item_id = i.item_id AND ci.category_id = ? and it works. But then I realised that I have never used this syntax to delete from two tables simultaneously before. I would like to know if it is meant to work, or if it is a dirty hack, and whether it is SQL standard, ie. can I use this if I want to run my app on Oracle? (Not that I do but I might want to sell it to people who do) thanks Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Syntax question
These are tables that I did not design (and would not have in this fashion), but I have to make do with them Table 1 structure: id_num number, descr1 varchar(30), descr2 varchar(30), descr3 varchr(30) Table 2 structure id_name varchar(15), ext_descr varchar(30) Table 2 is a child of table 1 (sort of) id_name in table 2 = id_num from table 1, preceeded by zero fill, superceeded by a three digit number (1 - 999). For example if id_num = 1234567, id_name might be 01234567001 and there might also be a 01234567002, etc. I need to produce a query (so that I can do a report) that has the following result: id_num descr1 descr2 descr3 ext_descr ext_descr ext_descr ...ETC... The bottom line here is that I need to get a select on the id_num in table 1 and all corresponding records in table 2. I know I build the first 12 characters of the id_name by using the id_num, zero filling and inquiring on substr(id_name,1,12). However, I am having a little trouble building the sql statement itself. Any thoughts would be appreciated. Thanks. Mark Roberts Sr. Systems Analyst Corporate Compliance Governance Applications
SQL Syntax
Contents are Direct Alliance Corporation CONFIDENTIAL - How do you type check in mysql. I have a column of type varchar(20) with both floats and strings. Is there a way to check the type? Example: Select If(is_float(col1), 'is a float', 'not a float') as type From table Thxs Cory This message is for the designated recipient(s) only and contains Direct Alliance Corporation privileged and confidential information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of this email is prohibited.
Re: SQL Syntax
At 11:40 -0700 7/22/03, Cory Lamle wrote: Contents are Direct Alliance Corporation CONFIDENTIAL - How do you type check in mysql. I have a column of type varchar(20) with both floats and strings. Is there a way to check the type? In this case, the type of the column as far as MySQL is concerned is varchar(20). If you want to check the type of individual column values, you'll need to impose your own semantic tests. Depending on how varied your values are, you might be able to use a REGEXP match. For example: IF(col1 REGEXP '^[0-9]+\\.[0-9]+$','is a float','not a float') AS type However, that pattern requires digits both before and after the decimal point and may not be suitable for your purposes. Example: Select If(is_float(col1), 'is a float', 'not a float') as type From table Thxs Cory -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Syntax
Cory, I'm not sure I understand what you're asking. MySQL casts the data to the appropriate column type when that data is entered into the database. Data in a varchar column is always stored as a string, just as data in an INT field is always going to be of type INT. -Rob -Original Message- From: Cory Lamle [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 2:40 PM To: MySQL LIST Subject: SQL Syntax Contents are Direct Alliance Corporation CONFIDENTIAL - How do you type check in mysql. I have a column of type varchar(20) with both floats and strings. Is there a way to check the type? Example: Select If(is_float(col1), 'is a float', 'not a float') as type From table Thxs Cory This message is for the designated recipient(s) only and contains Direct Alliance Corporation privileged and confidential information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of this email is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax
On Sat 2003-02-01 at 10:35:46 -, [EMAIL PROTECTED] wrote: Hi Benjamin, Wow, that sure sorted that problem out... I had to rejig it slightly to get it to work, Oops... too much copypaste by me :-) but this is the final working version: Glad it worked out. Bye, Benjamin. SELECT b.id, p.part_code, p.product_type, p.description, po.options, b.price, b.quantity, b.price*b.quantity AS total FROM basket_header bh INNER JOIN basket b ON b.basket_id = bh.basket_id LEFT JOIN products p ON p.prod_id = b.prod_id LEFT JOIN product_options po ON po.po_id = b.op_id WHERE bh.basket_id = 4 GROUP BY b.id, p.part_code, p.product_type, p.description, po.options, b.price, b.quantity, total, bh.basket_id, p.options Many thanks, now I'll work out why I couldn't do that so I can better understand it. [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL Syntax (JOINS) Help
Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity AS total FROM (products LEFT JOIN product_options ON products.prod_id=product_options.prod_id) RIGHT JOIN (product_options AS product_options_1 RIGHT JOIN (basket_header INNER JOIN basket ON basket_header.basket_id=basket.basket_id) ON product_options_1.po_id=basket.op_id) ON products.prod_id=basket.prod_id GROUP BY basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity, basket_header.basket_id, products.options HAVING (((basket_header.basket_id)=4)); Here is the error message MySQL reports: ERROR 1064: You have an error in your SQL syntax near '(product_options AS product_options_1 RIGHT JOIN (basket_header INNER JOIN baske' at line 9 Seems MySQL doesn't like the RIGHT JOIN syntax. Any ideas to the correct syntax? Just give you a better idea, here is a graphical schema of the query from MS Access. http://www.netsmith.ltd.uk/example.gif Thanks, Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL Syntax
That is one bloody complex query :). As far as I know, MySQL does not support RIGHT JOIN leyword, so that's where it's failing. Someone slap me if I'm wrong. It may be possible to fetch the results you want without such a hairy query. Just include a partial dump of involved tables and concise desctiption of what you're trying to select. Otherwise, I don't have guts to look into that huge query myself! Hopefully someone will! :-P Sherzod : : : Hi All, : : Can anyone help me get this query working in MySQL, this : was created using : Access, but it doesn't port well for MySQL syntax: : : SELECT basket.id, : products.part_code, : products.product_type, : products.description, : product_options_1.options, : basket.price, basket.quantity, : basket.price*basket.quantity AS total : FROM (products LEFT JOIN product_options ON : products.prod_id=product_options.prod_id) : RIGHT JOIN (product_options AS product_options_1 RIGHT JOIN : (basket_header : INNER JOIN basket ON basket_header.basket_id=basket.basket_id) ON : product_options_1.po_id=basket.op_id) ON : products.prod_id=basket.prod_id : GROUP BY basket.id, products.part_code, products.product_type, : products.description, product_options_1.options, basket.price, : basket.quantity, basket.price*basket.quantity, : basket_header.basket_id, : products.options : HAVING (((basket_header.basket_id)=4)); : : Here is the error message MySQL reports: : : ERROR 1064: You have an error in your SQL syntax near : '(product_options AS : product_options_1 RIGHT JOIN (basket_header INNER JOIN : baske' at line 9 : : Seems MySQL doesn't like the RIGHT JOIN syntax. Any ideas : to the correct : syntax? : : Thanks, : : Kevin : : : : - : Before posting, please check: :http://www.mysql.com/manual.php (the manual) :http://lists.mysql.com/ (the list archive) : : To request this thread, e-mail [EMAIL PROTECTED] : To unsubscribe, e-mail : [EMAIL PROTECTED] : Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php : : : : - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Syntax
Hi. On Fri 2003-01-31 at 15:46:37 -, [EMAIL PROTECTED] wrote: Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity AS total FROM (products LEFT JOIN product_options ON products.prod_id=product_options.prod_id) RIGHT JOIN (product_options AS product_options_1 RIGHT JOIN (basket_header INNER JOIN basket ON basket_header.basket_id=basket.basket_id) ON product_options_1.po_id=basket.op_id) ON products.prod_id=basket.prod_id GROUP BY basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity, basket_header.basket_id, products.options HAVING (((basket_header.basket_id)=4)); Reformatting for readability that is: SELECT basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity AS total FROM ( products LEFT JOIN product_options ON product_options.prod_id = products.prod_id ) RIGHT JOIN ( product_options AS product_options_1 RIGHT JOIN ( basket_header INNER JOIN basket ON basket.basket_id = basket_header.basket_id ) ON product_options_1.po_id = basket.op_id ) ON products.prod_id = basket.prod_id GROUP BY basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, total, basket_header.basket_id, products.options HAVING basket_header.basket_id=4; Here is the error message MySQL reports: ERROR 1064: You have an error in your SQL syntax near '(product_options AS product_options_1 RIGHT JOIN (basket_header INNER JOIN baske' at line 9 Seems MySQL doesn't like the RIGHT JOIN syntax. Any ideas to the correct syntax? Oh, RIGHT JOINs are fine. What it doesn't like are the parenthesis, I think. So simply reordering the joins (and by that replacing RIGHT JOINs with LEFT JOINs were appropriate and vice versa) should do the trick. FROM ( ( basket_header INNER JOIN basket ON basket.basket_id = basket_header.basket_id ) LEFT JOIN product_options AS product_options_1 ON product_options_1.po_id = basket.op_id ) LEFT JOIN ( products LEFT JOIN product_options ON product_options.prod_id = products.prod_id ) ON products.prod_id = basket.prod_id Now, a lot of the parenthesis are redundant. Written this way, it becomes more obvious, that product_options (not product_options_1) is neither referenced by a other table in an ON clause nor used in the select part, so what is the reason to include it to begin with? It's redundant. Additionally, I don't see the reason for the HAVING clause. IMHO the condition would be as good in the WHERE clause (where the optimizer can make better use of it). Aside from that, I prefer table aliases to get rid of the long names, so the end result would look like SELECT basket.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity AS total FROM basket_header bh INNER JOIN basket b ON b.basket_id = bh.basket_id LEFT JOIN products p ON p.prod_id = b.prod_id LEFT JOIN product_options po1 ON po1.po_id = b.op_id HAVING bh.basket_id = 4; GROUP BY b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, total, bh.basket_id, p.options (I did not rename po1 to po in order to avoid confusion.) HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Syntax Help
On Fri, Jan 31, 2003 at 02:07:11PM -, Kevin Smith wrote: Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity AS total FROM (products AS p LEFT JOIN product_options AS po ON p.prod_id = po.prod_id) RIGHT JOIN (product_options AS po1 RIGHT JOIN (basket_header AS bh INNER JOIN basket AS b ON bh.basket_id = b.basket_id) ON po1.po_id = b.op_id) ON p.prod_id = b.prod_id GROUP BY b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity, bh.basket_id, p.options HAVING (((bh.basket_id)=4)); Try FROM (((basket_header AS bh INNER JOIN basket AS b ON bh.basket_id = b.basket_id) LEFT JOIN product_options AS po1 ON po1.po_id = b.op_id) LEFT JOIN products AS p ON p.prod_id = b.prod_id) LEFT JOIN product_options AS po ON p.prod_id = po.prod_id MySQL tends to be more finicky than Jet about how you group things. I haven't tried this, but I think it will avoid confusing the MySQL optimizer. Bob Hall - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Syntax
Hi Benjamin, Wow, that sure sorted that problem out... I had to rejig it slightly to get it to work, but this is the final working version: SELECT b.id, p.part_code, p.product_type, p.description, po.options, b.price, b.quantity, b.price*b.quantity AS total FROM basket_header bh INNER JOIN basket b ON b.basket_id = bh.basket_id LEFT JOIN products p ON p.prod_id = b.prod_id LEFT JOIN product_options po ON po.po_id = b.op_id WHERE bh.basket_id = 4 GROUP BY b.id, p.part_code, p.product_type, p.description, po.options, b.price, b.quantity, total, bh.basket_id, p.options Many thanks, now I'll work out why I couldn't do that so I can better understand it. Kevin - Original Message - From: Benjamin Pflugmann [EMAIL PROTECTED] To: Kevin Smith [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, February 01, 2003 4:32 AM Subject: Re: SQL Syntax Hi. On Fri 2003-01-31 at 15:46:37 -, [EMAIL PROTECTED] wrote: Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity AS total FROM (products LEFT JOIN product_options ON products.prod_id=product_options.prod_id) RIGHT JOIN (product_options AS product_options_1 RIGHT JOIN (basket_header INNER JOIN basket ON basket_header.basket_id=basket.basket_id) ON product_options_1.po_id=basket.op_id) ON products.prod_id=basket.prod_id GROUP BY basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity, basket_header.basket_id, products.options HAVING (((basket_header.basket_id)=4)); Reformatting for readability that is: SELECT basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity AS total FROM ( products LEFT JOIN product_options ON product_options.prod_id = products.prod_id ) RIGHT JOIN ( product_options AS product_options_1 RIGHT JOIN ( basket_header INNER JOIN basket ON basket.basket_id = basket_header.basket_id ) ON product_options_1.po_id = basket.op_id ) ON products.prod_id = basket.prod_id GROUP BY basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, total, basket_header.basket_id, products.options HAVING basket_header.basket_id=4; Here is the error message MySQL reports: ERROR 1064: You have an error in your SQL syntax near '(product_options AS product_options_1 RIGHT JOIN (basket_header INNER JOIN baske' at line 9 Seems MySQL doesn't like the RIGHT JOIN syntax. Any ideas to the correct syntax? Oh, RIGHT JOINs are fine. What it doesn't like are the parenthesis, I think. So simply reordering the joins (and by that replacing RIGHT JOINs with LEFT JOINs were appropriate and vice versa) should do the trick. FROM ( ( basket_header INNER JOIN basket ON basket.basket_id = basket_header.basket_id ) LEFT JOIN product_options AS product_options_1 ON product_options_1.po_id = basket.op_id ) LEFT JOIN ( products LEFT JOIN product_options ON product_options.prod_id = products.prod_id ) ON products.prod_id = basket.prod_id Now, a lot of the parenthesis are redundant. Written this way, it becomes more obvious, that product_options (not product_options_1) is neither referenced by a other table in an ON clause nor used in the select part, so what is the reason to include it to begin with? It's redundant. Additionally, I don't see the reason for the HAVING clause. IMHO the condition would be as good in the WHERE clause (where the optimizer can make better use of it). Aside from that, I prefer table aliases to get rid of the long names, so the end result would look like SELECT basket.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity AS total FROM basket_header bh INNER JOIN basket b ON b.basket_id = bh.basket_id LEFT JOIN products p ON p.prod_id = b.prod_id LEFT JOIN product_options po1 ON po1.po_id = b.op_id HAVING bh.basket_id = 4; GROUP BY b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, total, bh.basket_id, p.options (I did not rename po1 to po in order to avoid confusion.) HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com
SQL Syntax Help
Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity AS total FROM (products AS p LEFT JOIN product_options AS po ON p.prod_id = po.prod_id) RIGHT JOIN (product_options AS po1 RIGHT JOIN (basket_header AS bh INNER JOIN basket AS b ON bh.basket_id = b.basket_id) ON po1.po_id = b.op_id) ON p.prod_id = b.prod_id GROUP BY b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity, bh.basket_id, p.options HAVING (((bh.basket_id)=4)); Here is the error message MySQL reports: You have an error in your SQL syntax near '(product_options AS po1 RIGHT JOIN (basket_header AS bh INNER JOIN basket AS b O' at line 1 Any ideas to the correct syntax? Thanks, Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL Syntax
Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity AS total FROM (products LEFT JOIN product_options ON products.prod_id=product_options.prod_id) RIGHT JOIN (product_options AS product_options_1 RIGHT JOIN (basket_header INNER JOIN basket ON basket_header.basket_id=basket.basket_id) ON product_options_1.po_id=basket.op_id) ON products.prod_id=basket.prod_id GROUP BY basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity, basket_header.basket_id, products.options HAVING (((basket_header.basket_id)=4)); Here is the error message MySQL reports: ERROR 1064: You have an error in your SQL syntax near '(product_options AS product_options_1 RIGHT JOIN (basket_header INNER JOIN baske' at line 9 Seems MySQL doesn't like the RIGHT JOIN syntax. Any ideas to the correct syntax? Thanks, Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Syntax
Also, this might help to solve the problem, this is a graphical schema of the query from MS Access, to give you all a better idea of what I'm trying to accomplish... http://www.netsmith.ltd.uk/example.gif - Original Message - From: Kevin Smith [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 31, 2003 3:46 PM Subject: SQL Syntax Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity AS total FROM (products LEFT JOIN product_options ON products.prod_id=product_options.prod_id) RIGHT JOIN (product_options AS product_options_1 RIGHT JOIN (basket_header INNER JOIN basket ON basket_header.basket_id=basket.basket_id) ON product_options_1.po_id=basket.op_id) ON products.prod_id=basket.prod_id GROUP BY basket.id, products.part_code, products.product_type, products.description, product_options_1.options, basket.price, basket.quantity, basket.price*basket.quantity, basket_header.basket_id, products.options HAVING (((basket_header.basket_id)=4)); Here is the error message MySQL reports: ERROR 1064: You have an error in your SQL syntax near '(product_options AS product_options_1 RIGHT JOIN (basket_header INNER JOIN baske' at line 9 Seems MySQL doesn't like the RIGHT JOIN syntax. Any ideas to the correct syntax? Thanks, Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Thanks and SQL Syntax help
I don't know if i understood you very well, but here's a try.. mysql select * from Classes; ++-+ | ID | Name| ++-+ | 1 | XO-312 | | 2 | PA-211a | | 3 | XUL-001 | ++-+ 3 rows in set (0.00 sec) mysql select * from Workshops order by ClassID,Date; ++-++ | ID | ClassID | Date | ++-++ | 1 | 1 | 2002-05-15 | | 8 | 1 | 2002-09-22 | | 7 | 1 | 2002-10-29 | | 2 | 1 | 2003-02-20 | | 3 | 2 | 2002-05-15 | | 9 | 2 | 2003-01-01 | | 4 | 2 | 2003-02-17 | | 5 | 3 | 2002-05-15 | | 10 | 3 | 2002-12-16 | | 6 | 3 | 2003-01-01 | ++-++ 10 rows in set (0.00 sec) mysql select ClassID, MIN(Date) min, MAX(Date) max, Classes.Name - FROM Workshops LEFT JOIN Classes ON (ClassID=Classes.ID) - GROUP BY ClassID HAVING now() BETWEEN min and max; +-+++-+ | ClassID | min| max| Name| +-+++-+ | 1 | 2002-05-15 | 2003-02-20 | XO-312 | | 2 | 2002-05-15 | 2003-02-17 | PA-211a | +-+++-+ 2 rows in set (0.00 sec) Hope this helps... On Sat, 2003-01-11 at 16:25, Steve Lefevre wrote: First of, thanks to all who replied to my questions earlier! Now I have another problem. I have a table of Classes and Workshops. Each Class has a number of workshops. Each workshop has a date. I have a query that gives me the date range of a class - the min and max dates of its workshops. select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID GROUP BY ClassID; gives me: +-+++-+ | ClassID | MIN(Date) | MAX(Date) | Name| +-+++-+ | 56 | 2002-05-15 | 2002-12-29 | XO-312 | | 408 | 2002-05-15 | 2002-05-17 | PA-211a | | 600 | 2002-05-15 | 2002-05-16 | XUL-001 | +-+++-+ 3 rows in set (0.00 sec) Now I want to get *active* classes - WHERE Now() Between MIN(Date) and Max(Date) -- but I can't figure out where to put the friggin clause. I get errors all over the place. Can I use the between function with a group by function? select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID WHERE Now() BETWEEN MIN(Date) and MAX(Date) GROUP BY ClassID; What am I doing wrong? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication halts with sql syntax error
Description: There seems to be a problem with mysql replication. When the slave server encounters sql that uses backticks instead of single quotes. The +replication stops until you manually advance the position number in the master.info +file. How-To-Repeat: 1. Set up a replication server 2. enter in sql on the master server using backticks instead of singlequotes eg. DROP TABLE `ee_camera`, `ee_camera_type`, `ee_category`, `ee_counter`, `ee_country`, `ee_exhibition`, `ee_exhibition_feedback`, +`ee_lens`, `ee_light`, `ee_location`, `ee_message`, `ee_microthumb_path`, `ee_news`, +`ee_owner`, `ee_photo`, `ee_photo_size`, +`ee_photo_to_category`, `ee_photo_to_exhibition`, `ee_size`, `ee_thumb_path`, +`ee_thumbs`, `ee_workflow` 3. Watch the error log on the slave, it will stop replication at this point. Fix: Submitter-Id: submitter ID Originator:markb Organization: Server101.com MySQL support: none Synopsis: Replication halts with sql syntax error Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.54 (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.54, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.54-Max Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 7 hours 45 min 44 sec Threads: 1 Questions: 50694 Slow queries: 0 Opens: 4433 Flush tables: 1 Open tables: 64 Queries per second avg: 1.814 Environment: Intel, Redhat 7.2 System: Linux launch.server101.com 2.4.16-xfs #2 SMP Tue Jan 15 05:26:12 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.2 2.96-112.7.1) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Nov 8 02:13 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1285884 Oct 11 03:19 /lib/libc-2.2.4.so -rw-r--r--1 root root 27338282 Oct 11 02:48 /usr/lib/libc.a -rw-r--r--1 root root 178 Oct 11 02:48 /usr/lib/libc.so lrwxrwxrwx1 root root 10 May 28 2002 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--without-innodb' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Thanks and SQL Syntax help
First of, thanks to all who replied to my questions earlier! Now I have another problem. I have a table of Classes and Workshops. Each Class has a number of workshops. Each workshop has a date. I have a query that gives me the date range of a class - the min and max dates of its workshops. select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID GROUP BY ClassID; gives me: +-+++-+ | ClassID | MIN(Date) | MAX(Date) | Name| +-+++-+ | 56 | 2002-05-15 | 2002-12-29 | XO-312 | | 408 | 2002-05-15 | 2002-05-17 | PA-211a | | 600 | 2002-05-15 | 2002-05-16 | XUL-001 | +-+++-+ 3 rows in set (0.00 sec) Now I want to get *active* classes - WHERE Now() Between MIN(Date) and Max(Date) -- but I can't figure out where to put the friggin clause. I get errors all over the place. Can I use the between function with a group by function? select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID WHERE Now() BETWEEN MIN(Date) and MAX(Date) GROUP BY ClassID; What am I doing wrong? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql syntax help
You almost got it. Your syntax will be something like this: UPDATE Table SET address=REPLACE(address,'#','Number') WHERE column like%#% When I am trying to figure out the syntax for something, I always add a LIMIT 1 at the end so that only one record gets changed. On Saturday, October 5, 2002, at 12:45 AM, Scott Johnson wrote: I have a db with slightly over 614,000 records of names and addresses. In the address column, there are quite a few records like 123 any rd # 2 319 w. 1st st # B 4321 test blvd # 42 etc I want to replace all the number signs with the actual word 'number'. Is there a SQL command I can use for this or do I need the help of a scripting language (php or vb)? I was trying to construct something like update into table.column select where column like '%#%' replace with '%number%' but of course that is not going to work. I am a SQL newb btw. Any help appreciated. Thanks Scott - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: sql syntax help
Scott, Saturday, October 05, 2002, 7:45:16 AM, you wrote: SJ I have a db with slightly over 614,000 records of names and addresses. In SJ the address column, there are quite a few records like SJ 123 any rd # 2 SJ 319 w. 1st st # B SJ 4321 test blvd # 42 SJ etc SJ I want to replace all the number signs with the actual word 'number'. SJ Is there a SQL command I can use for this or do I need the help of a SJ scripting language (php or vb)? SJ I was trying to construct something like update into table.column select SJ where column like '%#%' replace with '%number%' SJ but of course that is not going to work. I am a SQL newb btw. Take a look at string function REPLACE(): http://www.mysql.com/doc/en/String_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sql syntax help
I have a db with slightly over 614,000 records of names and addresses. In the address column, there are quite a few records like 123 any rd # 2 319 w. 1st st # B 4321 test blvd # 42 etc I want to replace all the number signs with the actual word 'number'. Is there a SQL command I can use for this or do I need the help of a scripting language (php or vb)? I was trying to construct something like update into table.column select where column like '%#%' replace with '%number%' but of course that is not going to work. I am a SQL newb btw. Any help appreciated. Thanks Scott - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Unsure of SQL Syntax
I am working with a MySQL database and I am hoping someone can help me out with this. CREATE TABLE CLASS ( CID int(22) NOT NULL auto_increment, LOC int(11) default NULL, CLI int(8) NOT NULL default '0', TYPE tinyint(4) NOT NULL default '0', STATUS char(3) NOT NULL default '', UID int(22) NOT NULL); CREATE TABLE GROUP ( GRID int(11) NOT NULL auto_increment, NAME varchar(40) NOT NULL default '', MAXACT int(11) NOT NULL default '0', LEVEL int(11) NOT NULL default '0', ADMIN int(22) NOT NULL default ''); Now I need to update the class table: set the CLASS.UID field to the `GROUP`.ADMIN field (join them on CLASS.CLI=`GROUP`.GRID) if CLASS.UID=2 and `GROUP`.GRID1. What would that SQL syntax look like? Thanks SK - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Syntax
Pada Sun, 25 Aug 2002 22:04:13 +0200 David Durham [EMAIL PROTECTED] menulis : update CompanyContacts set ByEmailAddress = '[EMAIL PROTECTED]' where Description like '%marve%' If I say: select * from CompanyContacts where Description like '%marve%' What was the error message displayed on update query ? From the syntax, it's ok for me :D -- Write clearly - don't be too clever. - The Elements of Programming Style (Kernighan Plaugher) MySQL 3.23.51 : up 66 days, Queries : 356.361 per second (avg). -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan 12790 Phone : +62 21 79199577 - HP : +62 8551044244 - Web : http://www.1rstwap.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL Syntax
This may be a truly dumb question, but could someone please tell me why this sql query/ statement does not work: update CompanyContacts set ByEmailAddress = '[EMAIL PROTECTED]' where Description like '%marve%' If I say: select * from CompanyContacts where Description like '%marve%' I get the correct response. Thanks in anticipation. -David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
