I'm using PHP to create a simple, dynamic MySQL SELECT query.
The user chooses a selection from a HTML Form SELECT element's many options
and submits the form via a POST action.
The SELECT query is built as follows:
$itemtype = stripslashes(trim($_POST['selType']));
$query = "select * from item where item_subtype like '%$itemtype%'";
I've used:
echo "
".$query."
";
to see that the queries are being properly constructed.
When the option with the value of 'finding' is chosen, no records are
displayed, although there are many records in the item_subtype field of the
item table that contain the string 'finding', such as 'Earring finding' and
'chandelier finding'.
The display of records works fine when other values are chosen (other than
the value of 'finding')
Help!
Can anybody help me figure out what might be preventing records with
'finding' in the item_subtype field from showing.
I have no problem with any other of the SELECT element's values being passed
to the query.
Here is the 'offending' query: select * from item where item_subtype like
'%finding%'
Thank you so very much, in advance, for your help!
Peace!
(by the way, I'm not sure if the peculiarity is due to a problem in MySQL or
PHP, so I've posted in this newsgroup)
Re: Is "finding" a MySQL keyword or such?
am 15.06.2006 11:46:27 von Bart Van der Donck
ElkGroveR wrote:
> The SELECT query is built as follows:
>
> $itemtype = stripslashes(trim($_POST['selType']));
> $query = "select * from item where item_subtype like '%$itemtype%'";
>
> I've used:
> echo "
".$query."
";
> to see that the queries are being properly constructed.
>
> When the option with the value of 'finding' is chosen, no records are
> displayed, although there are many records in the item_subtype field of the
> item table that contain the string 'finding', such as 'Earring finding' and
> 'chandelier finding'.
>
> The display of records works fine when other values are chosen (other than
> the value of 'finding')
>
> Help!
>
> Can anybody help me figure out what might be preventing records with
> 'finding' in the item_subtype field from showing.
>
> I have no problem with any other of the SELECT element's values being passed
> to the query.
>
> Here is the 'offending' query: select * from item where item_subtype like
> '%finding%'
'finding' is not a reserved word.
A few thoughts:
- the % in %finding% might be implemented so that at least one
character is mandatory for each %-placeholder, so that 'Earring
finding' is not found (on MySQL 4.0 this isn't the case though).
- case sensitive
- non-matching characters that look the same/similar (different
charcode)
- spaces at start/end of $itemtype in form value (PS the actual value
is passed from the form, not its corresponding text that user sees in
Re: Is "finding" a MySQL keyword or such?
am 15.06.2006 17:33:57 von ElkGroveR
Hi Bart
Thanks for your reply.
I want the SELECT query to retrieve records that contain 'finding' (or any
option element value) whether it is found at the beginning, middle or end of
the value in the item_subtype field, so the % works in that context - it
works for any other option element value - any value except 'finding'.
I will look into using REGEXP, especially when users enter search data /
keywords themselves.
Peace!
John
"Bart Van der Donck" wrote in message
news:1150364787.273673.319220@y41g2000cwy.googlegroups.com.. .
> ElkGroveR wrote:
>
>> The SELECT query is built as follows:
>>
>> $itemtype = stripslashes(trim($_POST['selType']));
>> $query = "select * from item where item_subtype like '%$itemtype%'";
>>
>> I've used:
>> echo "
".$query."
";
>> to see that the queries are being properly constructed.
>>
>> When the option with the value of 'finding' is chosen, no records are
>> displayed, although there are many records in the item_subtype field of
>> the
>> item table that contain the string 'finding', such as 'Earring finding'
>> and
>> 'chandelier finding'.
>>
>> The display of records works fine when other values are chosen (other
>> than
>> the value of 'finding')
>>
>> Help!
>>
>> Can anybody help me figure out what might be preventing records with
>> 'finding' in the item_subtype field from showing.
>>
>> I have no problem with any other of the SELECT element's values being
>> passed
>> to the query.
>>
>> Here is the 'offending' query: select * from item where item_subtype like
>> '%finding%'
>
> 'finding' is not a reserved word.
>
> A few thoughts:
>
> - the % in %finding% might be implemented so that at least one
> character is mandatory for each %-placeholder, so that 'Earring
> finding' is not found (on MySQL 4.0 this isn't the case though).
> - case sensitive
> - non-matching characters that look the same/similar (different
> charcode)
> - spaces at start/end of $itemtype in form value (PS the actual value
> is passed from the form, not its corresponding text that user sees in
>