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]
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]
