help with sql query setting

pphon = request.form("phon")
mySQL = "SELECT * FROM crminfo WHERE tel1 LIKE %" &pphon& "%"

i need to find inside column tel1 number like the nuber that was recieved from the submited form.
some how that wuery do not work fine...
what, what shell i do? where i got wrong?;)
sasha85Asked:
Who is Participating?
 
b0lsc0ttConnect With a Mentor IT ManagerCommented:
Please don't use all caps unless you are really yelling at me. :)  (I hope you aren't)

What is the info in that field?  How will it be used?  It seems like the info is phone number so text/string is appropriate.  If you tried to use a number type then entering 800-555-1212 would just leave -967 in that field (the math would be done).  In most cases phone numbers, zips, etc are text fields for this reason.

However if it is a number then it can be a disadvantage to use a text field type.  A detailed explanation on that can be done in a different question (one of its own) but the examples above should give you an idea.

Let me know if the suggestion above worked or not or if you have a question.

bol
0
 
simonkinCommented:
Hi,

Try this, your syntax is wrong...

Example:


pphon = request.form("phon")
mySQL = "SELECT * FROM crminfo WHERE tel1 LIKE'%&pphon&%'"

Open in new window

0
 
simonkinCommented:
Ooops...

Remove the ampersands '&' too...


$pphon = request.form("phon")
mySQL = "SELECT * FROM crminfo WHERE tel1 LIKE'%$pphon%'"

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
b0lsc0ttIT ManagerCommented:
sasha85,

The ampersands are needed to use the variable.  They should be fine as you have it.

What type of field is tel1?  Even though you use a number if it is a string or text field then you need single quotes or sometype of delimiter.  What type of field is it and what database do you use?  I assume MySQL but please confirm.

Try ...

mySQL = "SELECT * FROM crminfo WHERE tel1 LIKE '%" &pphon& "%'"

Let me know if you have any questions or need more information.

b0lsc0tt
0
 
b0lsc0ttIT ManagerCommented:
sasha85,

Also, try running the query outside of your code (i.e. in MySQL or using a 'query analyser') to see if there are no results for that query.  The problem may not be a problem but just no results.

Let me know if you have a question.

b0lsc0tt
0
 
sasha85Author Commented:
i use mysql...u know what...i see that the tel1 is text type...how can it effect this?
0
 
b0lsc0ttIT ManagerCommented:
If it is that type then it has to have the delimiters.  Look at the code in my last post and that should fix it.  For MySQL the delimiters for text/strings are single quotes (').  When used with LIKE the % sign would go inside the single quotes with the rest of the "string."

Let me know how it works or if you have a question.  Thanks for confirming the type of DB and field.

bol
0
 
b0lsc0ttIT ManagerCommented:
Sorry, not really "last" post.  See http:#20330179.

The code is ...

mySQL = "SELECT * FROM crminfo WHERE tel1 LIKE '%" &pphon& "%'"

bol
0
 
sasha85Author Commented:
REALY, HOW IF I STORE NUMBERS INSIDE MYSQL COLUMN THAT DEFINED AS TEXT...HOW BAD CAN OT BE?:)
0
 
sasha85Author Commented:
i defined it text cause i wanted prevent situation where user will disable javascript validations and will insert chars instead numbers...what would give him an error...
0
 
b0lsc0ttIT ManagerCommented:
I'm glad I could help.  Thanks for the grade, the points and the fun question.

bol
0
All Courses

From novice to tech pro — start learning today.