Searching MySQL DB for specific words in fields.

RtK_Evasion

I have a question. (Im still a PHP noob 😛)

How would I go about searching my MySQL Database for specific customers in a customer list. Is there a specific query command for this or is it just a complicated SELECT query.

Thanks,
Evasion

bradgrafelman

That would really depend on what your DB structure looks like. It shouldn't be that complicated of query at all... if it is, it probably suggests a poor DB design.

RtK_Evasion

My DB Structure:

Database Name: iPrint
Table Name: customers
Rows: CID, Business Name, Contact Name, Email, Address, Phone, Comments

For example I might want to search for Business Name: Microsoft in the DB. How would I go about doing that.

bradgrafelman

Well you'd just do a basic SELECT query. If you wanted exact matches, you'd add a WHERE clause like:

WHERE `Business Name` = 'Microsoft'

or if you want to match any part of the column:

WHERE `Business Name` LIKE '&#37;Microsoft%'

RtK_Evasion

bradgrafelman;10942784 wrote:
Well you'd just do a basic SELECT query. If you wanted exact matches, you'd add a WHERE clause like:
WHERE `Business Name` = 'Microsoft'
or if you want to match any part of the column:
WHERE `Business Name` LIKE '%Microsoft%'

Right ok. So the %'s in front and at the end show that it can be anywhere? What exactly do they mean?

bradgrafelman

The %'s have special meaning when used in a LIKE statement. See the manual page here for more information.

RtK_Evasion

What is the problem with this statement?

$sql = "SELECT `Name`,`Comments` FROM `customers` WHERE `Name` = '$short_searchname'";

(Using the above DB Structure. Name and Comments are fields.)

johanafm

RtK Evasion;10942978 wrote:
(Using the above DB Structure. Name and Comments are fields.)

Comments is, Name is not.

Also, does Comments really contain more than one comment? If this is the case, you should think about normalizing your schema.

RtK_Evasion

Hmm. Sorry, what I meant by name was Business Name. No there is only 1 comment.

halojoy

This will catch any business name
which has 'micro' in it
LIKE
Microsoft
Advanced Micro Devices
Micromania Co.

Search for a

Name LIKE '[B]%[/B]micro[B]%[/B]'

% ... means anything,
here anything before and after the 'micro' part

This will find any name with 'micro'

<?php

$short_searchname = '%micro%';

$sql = "SELECT `Name`,`Comments` 
    FROM `customers` 
    WHERE `Name` LIKE '$short_searchname'";

?>

RtK_Evasion

Ok perfect. So what I have now is:

$bname = $_POST['bname']; // Business Name from form on addcomments.php
$comments = $_POST['comments']; // Comments from form on addcomments.php


$sql = "SELECT * FROM `customers` WHERE `Name` LIKE '$bname'";
mysql_query($sql);

if(!mysql_query($sql))
{
    exit('<br>' . $sql . '<br>' . mysql_error());
}
else
    {
        print "Select Completed.";
    }

Now, how would I go about Inserting the comments stored in $comments into the Comments section of 'Microsoft'. For example, Microsofts comments could be "a very big company", how would I insert it into Microsoft only, not all the others.

Something like this?:

$sql1 = "INSERT INTO (`Comments`) VALUES ('$comments')";
mysql_query($sql1);

if(!mysql_query($sql1))
{
    exit('<br>' . $sql . '<br>' . mysql_error());
}
else
    {
        print "Insert Completed.";
    }

When I try that I get this error:

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 '(Comments) VALUES ('This is a comment')' at line 1

johanafm

The reference manual is your friend. Assuming you use mysql: insert statement . You can find references for other DBMS through a simple web search...

INSERT INTO tablename VALUES(...)

Means you need to supply values for ALL fields.

INSERT INTO tablename(field1, field2...) VALUES(...)

means you don't.