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: 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]
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]
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]
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]
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: 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
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]
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
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? [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]
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]
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]
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
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
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: 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
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
Re: SQL Syntax
David [ mysql,query ] update CompanyContacts set ByEmailAddress = '[EMAIL PROTECTED]' where Description like '%marve%' Can you show us the output of: describe CompanyContacts ...and also what is the exact error message? Are you connected as a user who is allowed to update the CompanyContacts table? I often have select only users and forget that I can't update/delete etc on the database tables :-) DSL -- Say you'll share with me one love, one lifetime Lead me, save me from my solitude. Say you'll want me with you, here beside you Anywhere you go let me go too... (Webber/Hart/Black) - 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 (May be OT)
Hello!
David Durham wrote:
If this question is off topic, please let me know.
I have a description field with a set of keywords. I need to update
some data based on a keyword, which is randomly placed in the field.
Can someone please help with a SQL syntax to find the word within the
field.
WHERE field LIKE '%keyword%'
maybe
WHERE upper(field) LIKE upper('%keyword%')
Greetings
Ralf
--
Ralf Narozny
SPLENDID Internet GmbH Co KG
Skandinaviendamm 212, 24109 Kiel, Germany
fon: +49 431 660 97 0, fax: +49 431 660 97 20
mailto:[EMAIL PROTECTED], http://www.splendid.de
-
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 (May be OT)
Use LIKE '%KEYWORD%' At least this works for Oracle -Original Message- From: David Durham [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 25, 2002 3:39 PM To: [EMAIL PROTECTED] Subject: SQL Syntax (May be OT) If this question is off topic, please let me know. I have a description field with a set of keywords. I need to update some data based on a keyword, which is randomly placed in the field. Can someone please help with a SQL syntax to find the word within the field. 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 - 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 question
Graham, Monday, May 13, 2002, 8:45:09 PM, you wrote: GN I have a directory of professional magicians, consisting of a MySQL table GN like GN this: GN +-++-+ GN | artist | area| magic | GN +-++-+ GN | Joe Bloggs | AZ*IN*TX | childrens | GN +-++-+ GN | Fred Smith | All | close-up | GN +-++-+ GN A surfer will select an area and then the type of magic they require via a GN php/HTML form. So to locate a performer who does magic for children in Texas GN I use GN SELECT * FROM artist WHERE (area LIKE '%$area%' OR area = 'All') AND magic GN LIKE '%$magic%' GN (where $area and $magic are variables passed from PHP). GN However, this does not give the desired result, it just returns any perfomer GN who does magic for children. What am I doing wrong please? I've tried GN several other syntax combinations without success. What are the values of your php variables? Are you sure that they are correct? GN kind regards, GN Graham Nichols. -- 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
Re: SQL syntax question
At 10:02 AM -0800 12/18/01, Steve Osborne wrote:
I would like to use an input form to add users to my database, however, if
the name is already in use, I do not want to add a duplicate record. I also
need this to be case insensitive (ie Santa Claus = santa Claus).
Make the (LastName, FirstName) a primary key and use INSERT IGNORE. Then test
mysql_affected_rows() to see whether it's 1 or 0. If it's 1,
the record was inserted. If it's 0, you tried to insert a dup.
I've tried the following code, but it doesn't seem to be working
$chknamerow = mysql_fetch_array(runsql(SELECT FirstName,LastName FROM Names
WHERE FirstName LIKE '$addfirstname' AND LastName LIKE '$addlastname' ));
$chkname = $chknamerow[FirstName] . $chknamerow[LastName];
if( ($chknamerow[FirstName]) AND ($chknamerow[LastName]) )
{
$Evalname = $addfirstname2 . $addlastname2;
$evalchange = is ALREADY entered as ;
$NewName = $chkname;
printf(p class=\subtitle\The name %s was not added to the
database./p\n, $Evalname);
}
The function runsql() is as follows:
function runsql($query)
{
global $debugit;
global $dbname;
global $mysql_link;
$runresult = mysql_db_query($dbname, $query, $mysql_link);
if (($debugit ) AND ($runresult == ))
{
mysql_error($mysql_link);
echo mysql_errno().: .mysql_error($mysql_link). on database
$dbnameBR;
echo While running SQL: $queryBR;
}
return ($runresult);
}
Any advice?
Steve Osborne
[EMAIL PROTECTED]
?php
/* Happy Holidays */
mysql_select_db('North_Pole');
mysql_query('SELECT reindeer FROM stable WHERE nose_color=red');
?
-
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 error
I created the file with ver 3.23.43, tried to recreate database on 3.22.32. I administer 2 other databases the same commands and they work fine. I looked at the difference in the sql files, and those ones don't contain the PACK_KEYS statement...just TYPE=myISAM. The command used to create the sql files hasn't changed, and the databases are hosted by the same ISP. It's a confusing situation. I don't want to have to go back and manually change each .sql file each day to complete the backup process. I wouldn't get any other work done. Any other suggestions? Steve [EMAIL PROTECTED] - Original Message - From: Steve Werby [EMAIL PROTECTED] To: Steve Osborne [EMAIL PROTECTED]; MySQL (E-mail) [EMAIL PROTECTED] Sent: Friday, November 30, 2001 2:58 PM Subject: Re: SQL syntax error Steve Osborne [EMAIL PROTECTED] wrote: I am receiving the following error when I try to restore a database backup from an .sql file: ERROR 1064 at line 12: You have an error in your SQL syntax near 'PACK_KEYS=1' at line 11 snip ) TYPE=ISAM PACK_KEYS=1; /snip Cmd used to create sql: mysqldump --user=username --add-drop-table -pblahblah dbdatabasenamefilename.sql Cmd used to restore from sql: mysql --user=root -p backupdatabasenamefilename.sql You didn't mention your version of MySQL, but I suspect it's old. Remove the PACK_KEYS=1 text and it should work fine. I also suggest upgrading to a more recent MySQL if in fact you're running an older version. -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.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 - 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 error
In the last episode (Nov 30), Steve Osborne said: I created the file with ver 3.23.43, tried to recreate database on 3.22.32. I administer 2 other databases the same commands and they work fine. I looked at the difference in the sql files, and those ones don't contain the PACK_KEYS statement...just TYPE=myISAM. The command used to create the sql files hasn't changed, and the databases are hosted by the same ISP. It's a confusing situation. I don't want to have to go back and manually change each .sql file each day to complete the backup process. I wouldn't get any other work done. Any other suggestions? * Upgrade the 2nd server to mysql 3.23 (preferable). It's really old. * Remove the PACK_KEYS flag from the 1st server's tables. mysql ALTER TABLE mytable PACK_KEYS=0; * Parse the mysqldump output to remove the PACK_KEYS flag before loading: mysqldump -h oldhost db | sed -e 's/ PACK_KEYS=1//' | mysql -h newhost db -- Dan Nelson [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 INSERT
Robert Martin wrote: Hi, I?m still learning sql so I hope this doesn?t sound to basic. I would like to find out if there is a way to insert a record only when (X and Y) do not exist. I have the value to check against stored in a variable. ALTER TABLE table ADD UNIQUE u_xy_idx ( x, y ); Then when you go to insert a duplicate x,y you will get an error from MySQL. --Bill - 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 INSERT
You probably want a unique index for those cols. Check out: http://www.mysql.com/doc/C/R/CREATE_TABLE.html (search for unique in page) and http://www.mysql.com/doc/C/R/CREATE_INDEX.html -Joe -Original Message- From: Robert Martin [mailto:[EMAIL PROTECTED]] Sent: Monday, October 01, 2001 12:50 PM To: [EMAIL PROTECTED] Subject: sql syntax INSERT Hi, Im still learning sql so I hope this doesnt sound to basic. I would like to find out if there is a way to insert a record only when (X and Y) do not exist. I have the value to check against stored in a variable. My table looks like the following: ID | X | Y - 1 | 10 | 20 2 | 11 | 21 ID is an auto-increment field, X and Y are INT(4). The syntax Im looking for is sort of like this: Success: INSERT INTO table_name (X, Y) VALUES (10,23) WHERE (X myX) AND (Y myY) Fail: INSERT INTO table_name (X, Y) VALUES (10,20) WHERE (X myX) AND (Y myY) I know this wont work, but Im trying to avoid the overhead of running a query and checking the result before each INSERT. Would a compound primary key help? (To be honest I only have a slight idea of what one is.) Can one be created in MySQL? Thanks in advance, Robert Martin - 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 near 'IDENTIFIED BY'...
Date |Tue, 28 Aug 2001 15:14:54 +0500 From |Alexander Barkov [EMAIL PROTECTED] Hello! AB Hello! AB These query produces an SQL syntax error AB in 3.23.29a-gamma: AB GRANT ALL PRIVILEGES ON databases.* AB TO 'foo'@'localhost' IDENTIFIED BY 'bar'; AB SHOW TABLES FROM databases; AB Probably this is because of databases is AB a keyword. However, mysqladmin does allow to AB create database 'databases'. AB Is it a bug? Or is that already fixed in AB later releases? AB mysqladmin create databases does not output any AB error messages, however it does not create database AB databases. AB However, mysqladmin drop databases produce this: AB /usr/local/mysql/etc/ access.grant mysqladmin drop databases AB Dropping the database is potentially a very bad thing to do. AB Any data stored in the database will be destroyed. AB AB Do you really want to drop the 'databases' database [y/N] AB y AB mysqladmin: drop of 'databases' failed; AB error: 'You have an error in your SQL syntax near 'databases' at line 1' I'll try to do all what you write. ~| mysqladmin -u root -p create databases Enter password: ~| so - create base 'databases' complete. ~| mysqladmin -u root -p drop databases Enter password: Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'databases' database [y/N] y Database databases dropped In short words - mysqladmin create and drop databases not through mysql but directly. PS: I use 3.23.39 version. ___ For technical support contracts, visit https://order.mysql.com/ This email is sponsored by SWSoft, http://www.asplinux.ru/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Grigory Bakunov [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB / SWSoft /_/ /_/\_, /___/\___\_\___/ ___/ 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
RE: SQL Syntax question
On 26-Apr-01 [EMAIL PROTECTED] wrote: I have two table I need to join in a query. The second table needs to be join twice (I think) to the first. Details as follows (tables pared down)... Table games gameid hometeamid guestteamid Table team teamid sponsor I want a query to return game.gamid, team.sponsor (hometeam), team.sponsor (guestteam). can someone point me in the right direction for this please? select gameid,home.sponsor,guest.sponsor from games,team as home,team as guest where hometeamid=home.teamid and guestteamid=guest.teamid; Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. - 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 question
Hi there, I'm using mysql 3.22.27 and get error when trying to run this select statement: SELECT custmls.mlsnumber,custmls.streetnumber,custmls.streetdirect, FORMAT(custmls.currentprice,0),custmls.streetnam,custmls.streetaddtl, custmls.municname,custmls.state,custmls.zipcd,custmls.salesassoc, ipix.url,custmls.listagentname,FORMAT(custmls.numrooms,0), FORMAT(custmls.numbedrooms,0),custmls.fullbaths FROM custmls,ipix where custmls.mlsnumber = ipix.mlsnumber AND custmls.listingoffice = 0251 AND (custmls.listingstatus = 'ACT' or custmls.listingstatus = 'A*') AND custmls.scategory = 1 ORDER BY custmls.currentprice The error is: "1064 You have an error in your SQL syntax near 'ON ipix custmls.mlsnumber = ipix.mlsnumber where custmls.listingoffice = 0251 AN' at line 1 " Any ideas? Thanks Pat Sir, the error message was obviously from an SQL statement other than the one you quote above. Since I don't know your table structure, I can't be sure what the problem is, but it looks like you are writing columns and tables as table_name.column_name. This is backwards. It should be column_name.table_name. The alternative is that you are selecting from a boat load of tables that aren't mentioned in the FROM clause. I see two problems with the snippet of SQL quoted in the error message. First of all, the word 'ipix' after ON isn't serving any function, other than to confuse MySQL. Secondly, you are trying to join two tables, but your ON clause joins a column from mlsnumber to another column from mlsnumber. If this is not the error mentioned above, then you need to join a column in mlsnumber to a column in the second table. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - 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 list
At 10:09 PM +0200 2/2/01, Ciprian wrote: *This message was transferred with a trial version of CommuniGate(tm) Pro* Anybody knows where I can find a list with all SQL syntax -es. Eventually explained. Thanks If you mean the SQL statements supported by MySQL, the MySQL Reference Manual is a good source. If you're looking for something that covers SQL in general, try "SQL-99 Complete, Really" (Gulutzan and Pelzer). If you mean something else, please be more specific. -- Paul DuBois, [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
