cancel
Showing results for 
Search instead for 
Did you mean: 

Help need with WHERE clause in form queries

N/A

Help need with WHERE clause in form queries

Hey all you professional scripters can you help a newbie with a very frustrating problem?
I have a table on my database that contains details of members - having field names name, contacts, details, region.
I have a form on my website for visitors to seek for members by name. As they may not know the full name I am attempting to use LIKE in the folowing line
$resultID = mysql_query("SELECT name, contacts, details FROM pbfamembers WHERE search LIKE '%'", $linkID)
but I don't know what to put in after LIke - I know it has to have the percentage marks and single quotes but what goes in between?
Here is an example as a visitor would use it - I am looking for say John Williams but I am not sure if his first name is John and the field on the table contains both names, so in the text box which has the name ="search" I type in Williams
The result is that I get either all records or a rude message.
If I remove the WHERE clause the script works (albeit I get all records) so I guess it is the WHERE clause screwing me up.
I have tried varius options but nothing seems to work so if anyone can help me it would be wonderful cos it is driving me nuts
--
Gina
8 REPLIES
N/A

RE: Help need with WHERE clause in form queries

SELECT name, contacts, details FROM pbfamembers WHERE search LIKE '%$linkID'

should do the trick

Also that will only match ___$linkID, so if $linkID = an then scan, plan, an etc will be matched but not ant. If you want to return everything that contains $linkID you should use an extra % as such:

SELECT name, contacts, details FROM pbfamembers WHERE search LIKE '%$linkID%'
N/A

RE: Help need with WHERE clause in form queries

Also the field that you're LIKE matching must obviously exist in the table(s) you're querying.
N/A

RE: Help need with WHERE clause in form queries

> SELECT name, contacts, details FROM pbfamembers WHERE search LIKE '%$linkID'
>
> should do the trick
>
> Also that will only match ___$linkID, so if $linkID = an then scan, plan, an etc will be matched but not ant. If you want to return everything that contains $linkID you should use an extra % as such:
>
> SELECT name, contacts, details FROM pbfamembers WHERE search LIKE '%$linkID%'
>
Big Al
Thanks for that but I think I have misled you a bit.
The variable $linkID is a declared variable that includes all my connection details (host, userId, password) and is nothing to do with the actual query. I just put it on the end of all my queries after the finishing " and it seems to work.
The actual select query runs like this
$resultID = mysql_query("SELECT name, contacts, details FROM pbfamembers WHERE name LIKE '%$search%'", (then the text linkID);
"name" is the right column name for that table and "search" is the name of the text box on the html form.
If I take the WHERE clause out and use = '$search' it works just fine ( I put in a very short name to test it!)
Sorry if you have been slaving away for nothing, but your help is appreciated.




--
Gina
N/A

RE: Help need with WHERE clause in form queries

> $resultID = mysql_query("SELECT name, contacts, details FROM pbfamembers WHERE name LIKE '%$search%'", (then the text linkID);

Well that should work fine then, and don't worry there was no slaving away on my part. If you're stuck with SQL in general, you should have a look at the Language Reference chapter from the MYSQL manual available at http://www.mysql.com
N/A

RE: Help need with WHERE clause in form queries

> Also the field that you're LIKE matching must obviously exist in the table(s) you're querying.
Al you are a genius
I went and checked the html form and discovered that somewhere in my ramblings I had changed the text name to "search". I have now corrected my error, deleted all my temporary Internet files and wow it worked. Thank you so much for holding my hand.
On another matter I see you respond to the forums a lot and you also share my thoughts on the portal MySQL admin. I too have trouble putting data into one table I have created, and have no idea what I am doing wrong. I am cutting and pasting from a Excel spreadsheet at the moment But as I have 750 records to get in by the weekend, I would like a quicker way that works, without "internal errors" cropping up and no data saved.
I read in my MySQL manual that there is a very easy way of getting a text file into a table you have created but you need to use the command line interface for it. The syntax looks quite straightforward - I have a tab delimited text file the same name as a table I have created but I have one big problem - some of the fields have active links (email and website addresses mostly) Will these get destroyed during the import or do I tediously have to escape all the backslahes, and quotes?
Is this easy to use because I have never used it before and I am of a nervous dispostion.
But hey I have taken up enough of your time already -just ignore this geriatric novice programmer who should really be knitting socks for the grandchildren but programming keeps my brain alive.
Thanks again - very very much appreciated.


--
Gina
N/A

RE: Help need with WHERE clause in form queries

> > Also the field that you're LIKE matching must obviously exist in the table(s) you're querying.
> Al you are a genius
> I went and checked the html form and discovered that somewhere in my ramblings I had changed the text name to "search". I have now corrected my error, deleted all my temporary Internet files and wow it worked. Thank you so much for holding my hand.
> On another matter I see you respond to the forums a lot and you also share my thoughts on the portal MySQL admin. I too have trouble putting data into one table I have created, and have no idea what I am doing wrong. I am cutting and pasting from a Excel spreadsheet at the moment But as I have 750 records to get in by the weekend, I would like a quicker way that works, without "internal errors" cropping up and no data saved.
> I read in my MySQL manual that there is a very easy way of getting a text file into a table you have created but you need to use the command line interface for it. The syntax looks quite straightforward - I have a tab delimited text file the same name as a table I have created but I have one big problem - some of the fields have active links (email and website addresses mostly) Will these get destroyed during the import or do I tediously have to escape all the backslahes, and quotes?
> Is this easy to use because I have never used it before and I am of a nervous dispostion.
> But hey I have taken up enough of your time already -just ignore this geriatric novice programmer who should really be knitting socks for the grandchildren but programming keeps my brain alive.
> Thanks again - very very much appreciated.
>
>
>
> --
> Gina



Gina,
I think I was the guy that spoke to you about doing this a while ago, I made the code and couldn't find your email address.

I hope this code comes out right, if not I will email it to you. This will take the tabbed file and create a table in a browser for you. All you have to do to get it into a MySQL database is add the MySQL connection code above this and add a command something like this just before the end of the loop

mysql_query(sqlString);

You must created the variable sqlString before (you can make it inside the braces if you want. the SQL string would be something like:

INSERT INTO dbasename SET (name, tel, details, branch) VALUES ('$name','$tel','$branch','$details')

then your done, just run this page at your cgi space on crofters or whichever server your on and the data will be in the table. I will happily help if I can.

cheers.
Chris.

------------------PHP CODE below--------------------
<table cellspacing="0" style='font-size:8pt;'>
<?
$fname="PHPTEST.txt";
$fp=fopen($fname,"r") or die("Error found.");
$line = fgets($fp, 204Cool;

while(!feof($fp)) {
list($name,$tel,$details,$branch) = split( "\t", str_replace("\"\"", "'", $line), 4 );
print "<tr><td>";
print "$name";
print "</td><td bgcolor='#eeeeee'>";
print "$tel";
print "</td><td>";
print "$details";
print "</td><td bgcolor='#eeeeee'>";
print "$branch";
print "</td></tr>\n";
print "<tr bgcolor='silver'><td></td><td bgcolor='silver'></td><td></td><td bgcolor='silver'></td></tr>";
$line = fgets($fp, 204Cool;
}
fclose($fp);
?>
</table>


N/A

RE: Help need with WHERE clause in form queries

Chris
How wonderful to hear from you again! I see you are busy helping other people too.
I still have the PHPtest.txt you did for me adn I sent you a reply which you obviously never received.
I think you were changing emails at the time.
I thought I had mortally offended you adn you had given me up as a lost cause.
I actually said in that email - long time ago "thank you now what do I do?"
Anyway it is great to make contact again and any help you can offer is much appreciated. I will read your reply very closely and probably come back with questions if that is okay and then if I am brave enough have a go with some test records.
I really do not get on with the portal arrangement. I know I am probably doing something wrong but "internal error" means nothing although support did suggest I deleted all temporary Internet files.
The frustrating thing is that the other tables work well - it just doesn't seem to like these these bl**dy members
Hope to hear from you soon
My direct email address is gina@pbfa.org

>
> --
> Gina


--
Gina
N/A

RE: Help need with WHERE clause in form queries

Gina....I have emailed your other account.