Skip to main content
Inspiring
February 24, 2007
Question

Issue when using $_POST['xyz'] in SELECT

  • February 24, 2007
  • 1 reply
  • 843 views
REPOST:
The situation: Searching a small database with PHP/MySQL with DW8.0.2
1. I have a form where users can enter a name (or a portion of a name) and select which col in the db the name relates to. It POSTs the results to a seperate page to look up and display the result. The 2 form fields are name and column.
2. I have created a simple test page whereby I place the POST values for the fields into 2 vars ($name and $column)
3. I created a simple recordset:
SELECT *
FROM table3
WHERE $column LIKE '%$name%'
ORDER BY FamilyName ASC
which works perfectly. The recordset code is:

mysql_select_db($database_connConvictsMySQL, $connConvictsMySQL);
$query_rsConvicts = "SELECT * FROM table3 WHERE $col LIKE '%$name%' ORDER BY FamilyName ASC";
$rsConvicts = mysql_query($query_rsConvicts, $connConvictsMySQL) or die(mysql_error());
$row_rsConvicts = mysql_fetch_assoc($rsConvicts);
$totalRows_rsConvicts = mysql_num_rows($rsConvicts);

4. So with that in mind I attempted to use the $_POST values directly in the recordset using the Add Varianbles section and removed the vars I mentioned in 2 above. I thought this would provide a more secure, elegant solution as the code created adds in all the extra security code. I finished up with a recordset SQL as:
SELECT *
FROM table3
WHERE column LIKE %name%
ORDER BY FamilyName ASC
and the record set code created is:

$name_rsConvicts = "-1";
if (isset($_POST['enteredName'])) {
$name_rsConvicts = (get_magic_quotes_gpc()) ? $_POST['enteredName'] : addslashes($_POST['enteredName']);
}
$column_rsConvicts = "-1";
if (isset($_POST['selectedColumn'])) {
$column_rsConvicts = (get_magic_quotes_gpc()) ? $_POST['selectedColumn'] : addslashes($_POST['selectedColumn']);
}
mysql_select_db($database_connConvictsMySQL, $connConvictsMySQL);
$query_rsConvicts = sprintf("SELECT * FROM table3 WHERE %s LIKE CONCAT('%%', %s, '%%') ORDER BY FamilyName ASC", GetSQLValueString($column_rsConvicts, "text"),GetSQLValueString($name_rsConvicts, "text"));
$rsConvicts = mysql_query($query_rsConvicts, $connConvictsMySQL) or die(mysql_error());
$row_rsConvicts = mysql_fetch_assoc($rsConvicts);
$totalRows_rsConvicts = mysql_num_rows($rsConvicts);

In this case I dont get any results. I would really appreciate any suggestions . Thanks.
This topic has been closed for replies.

1 reply

Inspiring
February 24, 2007
WollombiWombat wrote:
> In this case I dont get any results. I would really appreciate any suggestions

Go to the following page:

http://www.adobe.com/go/b6c2ae2a

Download the Dreamweaver extension fix (the link's in the first
paragraph). Install the fix and restart Dreamweaver. It should then work
as expected.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/
ikoniqozAuthor
Inspiring
February 25, 2007
David
Many thanks for your quick reply - over the weekend too !
Sadly the "Hot Fix" turned out to be a cold failure. But, here is what happened:
1. When I opened up and closed the Recordset SB the code was altered.
2. I re-opened the Recordset and clicked on the Test button and I was presented with a full listing of the data in the table. (Previously no data was returned).
3. I uploaded the file to the testing server and tested it. Same result as previously. ie: No data.
4. I then started to edit the SELECT statement and firstly I changed -
LIKE CONCAT('%%', %s, '%%')
to plain old
LIKE %s
which gave the same result as in steps 2 and 3 above.
5. I then edited the SELECT statement further from:
$query_rsConvicts = sprintf("SELECT * FROM table3 WHERE %s LIKE %s ORDER BY FamilyName ASC", GetSQLValueString($column_rsConvicts, "text"),GetSQLValueString($name_rsConvicts, "text"));

TO:

$query_rsConvicts = ("SELECT * FROM table3 WHERE $column_rsConvicts LIKE '%$name_rsConvicts%' ORDER BY FamilyName ASC");

Note that I dropped the sprintf code. And, naturally, it worked.

I am running XP Pro with MySQL 5.0.18 & PHP 5.1.1 and have magic_quotes_gpc turned on.
Could this be some further bug in DW8.0.2 Hot Fix perhaps? I cant imagine it is, but why else would the "factory" code fail ?
BTW: Do you know where to go on the Adobe site to see a list of DW related TechNotes in chrono order by DW version ?? Crickey, even finding the TechNote you referred to is a challenge as it is NOT LISTED on the DW TechNote Index page. AAaaarrrggghhhhh...

Regards,