The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Programming Languages
> PHP Development
|
PHP4 - Help with SQL Statement
Discuss Help with SQL Statement in the PHP Development forum on Dev Shed.
Help with SQL Statement PHP Development forum discussing coding practices, tips on PHP, and other PHP-related topics. PHP is an open source scripting language that has taken the web development industry by storm.
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
September 24th, 2012, 02:44 PM
|
|
Registered User
|
|
Join Date: Sep 2012
Posts: 8
Time spent in forums: 2 h 38 m 42 sec
Reputation Power: 0
|
|
|
PHP4 - Help with SQL Statement
I need to know how to make the following statement work.
Here is the statement:
PHP Code:
$colname_test = "-1";
if (isset($_GET['Vendor'])) {
$colname_test = $_GET['Vendor'];
}
$colname1_test = "-1";
if (isset($_GET['Equipment'])) {
$colname1_test = $_GET['Equipment'];
}
mysql_select_db($database_TechSupport, $TechSupport);
$query_test1 = sprintf("SELECT MaillingList.Switch, GROUP_CONCAT(MaillingList.Emailaddress) as Email FROM MaillingList WHERE MaillingList.Switch IN (SELECT MaillingList.Switch FROM MaillingList WHERE MaillingList.Vendor = %s AND( MaillingList.Postion = 'DR' OR MaillingList.Postion = 'MS' OR MaillingList.Postion = 'MC' OR MaillingList.Postion = 'ST') AND 'Y' = %s) GROUP BY MaillingList.Switch", GetSQLValueString($colname_test, "text"),GetSQLValueString($colname1_test, "text"));
$test1 = mysql_query($query_test1, $TechSupport) or die(mysql_error());
$totalRows_tes1t = mysql_num_rows($test1);
Where i am having issues is the 'Y' = %s in the code. The %s is a Field in my SQL database. It is putting quotations around it. How do i get take them out so that way the sql statement treats it like a field in the database.
|
September 24th, 2012, 02:57 PM
|
|
Registered User
|
|
Join Date: Sep 2012
Posts: 8
Time spent in forums: 2 h 38 m 42 sec
Reputation Power: 0
|
|
|
Here is the echo of the statement
SELECT MaillingList.Switch, GROUP_CONCAT(MaillingList.Emailaddress) as Email FROM MaillingList WHERE MaillingList.Switch IN (SELECT MaillingList.Switch FROM MaillingList WHERE MaillingList.Vendor = 'Lucent' AND( MaillingList.Postion = 'DR' OR MaillingList.Postion = 'MS' OR MaillingList.Postion = 'MC' OR MaillingList.Postion = 'ST') AND 'Y' = 'BWM') GROUP BY MaillingList.Switch
What i market in red is where the issue is.
BWM is a field in my database and because of the quotes it doesn't work.
|
September 24th, 2012, 04:17 PM
|
|
|
|
September 25th, 2012, 06:47 AM
|
|
Registered User
|
|
Join Date: Sep 2012
Posts: 8
Time spent in forums: 2 h 38 m 42 sec
Reputation Power: 0
|
|
|
When i do it that way I get a error
syntax error , unexpected '%'
any other ideas
|
September 25th, 2012, 07:07 AM
|
 |
pollyanna
|
|
Join Date: Jul 2012
Location: Germany
|
|
|
Hi,
No idea what SecurityDavid was trying to do, but this doesn't work.
However, he's right about the backticks. You need to take the raw value(!), escape special characters and then wrap it in backticks. When you use your escape function, the value will always be wrapped in quotes -- which is not what you want.
But I find the whole idea rather odd and dangerous. You let the user choose any column he wants. That's a bad idea and points to a misdesign of either the application or the database.
And you should get used to indenting your queries. Those one-liners are completely unreadable and very error-prone.
|
September 25th, 2012, 07:27 AM
|
|
Registered User
|
|
Join Date: Sep 2012
Posts: 8
Time spent in forums: 2 h 38 m 42 sec
Reputation Power: 0
|
|
|
So how would i do this.
I have a data base with email address in it.
the database has 15 fields
the user has 2 options
1st option is a variable in field 1
2nd option is the field that is looking for Y
SO with the data that i have on my first post how would i do this
|
September 25th, 2012, 08:22 AM
|
|
|
Is that a convoluted version of this:
Code:
SELECT Switch
, GROUP_CONCAT(emailaddress) Email
FROM MaillingList
WHERE vendor = 'Lucent'
AND postion IN('DR','MS','MC','ST')
AND BWM = 'y'
GROUP
BY switch
?
Incidentally, not that it matters, but neither 'mailling' nor 'postion' have any meaning in English, although I note that they are similar in spelling to the recognised English words 'mailing' and 'position'.
Last edited by cafelatte : September 25th, 2012 at 08:30 AM.
|
September 25th, 2012, 09:31 AM
|
|
Registered User
|
|
Join Date: Sep 2012
Posts: 8
Time spent in forums: 2 h 38 m 42 sec
Reputation Power: 0
|
|
SELECT Switch, GROUP_CONCAT(MaillingList.Emailaddress) as Email FROM MaillingList
WHERE Switch IN (SELECT Switch FROM MaillingList
WHERE Vendor = 'Lucent'
AND(Postion = 'DR' ORPostion = 'MS' OR Postion = 'MC' OR Postion = 'ST')
)
AND 'Y' = 'BWM'
GROUP
BY Switch
This is my sql statement. The BWM is a column name in my database that i am passing from another web page. The issue is that it has the single qoutes around it. I need to remove the qoutes so the sql query will work. How can i do this
|
September 25th, 2012, 09:39 AM
|
|
Registered User
|
|
Join Date: Sep 2012
Posts: 8
Time spent in forums: 2 h 38 m 42 sec
Reputation Power: 0
|
|
The Code looks like this
PHP Code:
$colname_test = "-1";
if (isset($_GET['Vendor'])) {
$colname_test = $_GET['Vendor'];
}
$colname1_test = "-1";
if (isset($_GET['Equipment'])) {
$colname1_test = $_GET['Equipment'];
}
mysql_select_db($database_TechSupport, $TechSupport);
$query_test1 = sprintf("
SELECT MaillingList.Switch, GROUP_CONCAT(MaillingList.Emailaddress) as Email
FROM MaillingList
WHERE MaillingList.Switch IN
(SELECT MaillingList.Switch
FROM MaillingList
WHERE MaillingList.Vendor = %s(this is colname_test)
AND( MaillingList.Postion = 'DR' OR MaillingList.Postion = 'MS' OR MaillingList.Postion = 'MC' OR MaillingList.Postion = 'ST') )
AND 'Y' = %s ( THis is colname1_test)
GROUP BY MaillingList.Switch",
GetSQLValueString($colname_test, "text"),GetSQLValueString($colname1_test, "text"));
$test1 = mysql_query($query_test1, $TechSupport) or die(mysql_error());
$totalRows_tes1t = mysql_num_rows($test1);
|
September 25th, 2012, 09:47 AM
|
|
pollyanna
|
|
Join Date: Jul 2012
Location: Germany
|
|
Quote: | Originally Posted by rwtrwt2003 The BWM is a column name in my database that i am passing from another web page. The issue is that it has the single qoutes around it. I need to remove the qoutes so the sql query will work. How can i do this |
Haven't I already answered this question? 
Quote: | Originally Posted by Jacques1 You need to take the raw value(!), escape special characters and then wrap it in backticks. |
"Escaping" in this context means: apply mysql_real_escape_string or an equivalent function and delete all backticks (since they aren't allowed within identifiers at all).
|
September 25th, 2012, 10:03 AM
|
|
|
Quote: | Originally Posted by Jacques1 No idea what SecurityDavid was trying to do, but this doesn't work. | Sorry, I'm not really familiar with the use of the percent sign here. The answer was to make sure it was enclosed in backticks, which is correct; but I saw the %s as a variable, which I'm assuming is not correct. Can someone explain to me exactly what the % is used for here?
|
September 25th, 2012, 10:15 AM
|
|
Registered User
|
|
Join Date: Sep 2012
Posts: 8
Time spent in forums: 2 h 38 m 42 sec
Reputation Power: 0
|
|
I got it to work,
This is the statement that i used
PHP Code:
$colname_test = "-1";
if (isset($_GET['Vendor'])) {
$colname_test = $_GET['Vendor'];
}
$colname1_test = "-1";
if (isset($_GET['Equipment'])) {
$colname1_test = $_GET['Equipment'];
}
$colname1_test = mysql_real_escape_string($colname1_test);
mysql_select_db($database_TechSupport, $TechSupport);
$query_test1 = sprintf("SELECT MaillingList.Switch, GROUP_CONCAT(MaillingList.Emailaddress) as Email FROM MaillingList WHERE MaillingList.Switch IN (SELECT MaillingList.Switch FROM MaillingList WHERE MaillingList.Vendor = %s AND( MaillingList.Postion = 'DR' OR MaillingList.Postion = 'MS' OR MaillingList.Postion = 'MC' OR MaillingList.Postion = 'ST') AND 'Y' = $colname1_test) GROUP BY MaillingList.Switch", GetSQLValueString($colname_test, "text"),GetSQLValueString($colname1_test, "text"));
$test1 = mysql_query($query_test1, $TechSupport) or die(mysql_error());
$totalRows_tes1t = mysql_num_rows($test1);
Thank you for all your Help
|
September 25th, 2012, 10:16 AM
|
|
Registered User
|
|
Join Date: Sep 2012
Posts: 8
Time spent in forums: 2 h 38 m 42 sec
Reputation Power: 0
|
|
|
this is fixed
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
