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]