homepage Welcome to WebmasterWorld Guest from 54.235.16.159
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Using PHP to search an entire column contents in MySQL
Just another quick newbie issue!
SEO Speedster




msg:1313100
 2:54 pm on Nov 8, 2001 (gmt 0)

Hello all-

In a PHP document, I need to have the ability to search a MySQL table's column data for a given string... But, it comes from a form first...

So, Is there (in PHP) a request.Form(value) like there is in ASP that I can use?

Also, how would I add in the search for such a string into my SQL statement?

I know I'm a newbie!

THanks
~Speedster

 

ggrot




msg:1313101
 3:02 pm on Nov 8, 2001 (gmt 0)

Not quite sure what you are asking here, but to get the value of a field from a form that was submitted to php, all you need to do is access the variable $fieldname, which is the name of the field in that form. It will be filled with the value that the user sent. To form a mysql query, you could use something like this

$query = "SELECT * FROM SOMETABLE WHERE SOMEFIELD = $FIELDNAME";

The only problem there is that if the user is malicious, they could submit quote characters and other things in fieldname and basically run whatever mySQL query they wanted to. So you need to do something like:
$fieldnam = htmlspecialchars($fieldname);
first which will escape quotes and other stuff/

Air




msg:1313102
 5:03 pm on Nov 8, 2001 (gmt 0)

everything ggrot said, and if you wanted to search for occurances of the incoming form variable string:

$query = "SELECT * FROM some_table WHERE some_field LIKE \"%$Fieldname%\";

eg. suppose the form variable $Fieldname contained the value Horses, the above query would return every row where some_field contained the string Horses.


SEO Speedster




msg:1313103
 5:10 pm on Nov 8, 2001 (gmt 0)

Sorry for being a little vague-

Here is the process I need to solve, from a user point of view.

1) User accesses an HTML document with a form. That form contains a list menu in which a user selects a city, then submits that information.

2) That information is then parsed (with mySQL selecting the value from the list within a given column)

3) Data is then used in supplying the user with a document.

I know it's pretty simple, but I'm coming from an ASP/MSSQL background, and my great "use for any case" scripts just can't transer all that well into PHP.

I can build the form, but don't know where to go from there. I suspect that it would be best to set up the final PHP document with the ability to read a querystring, and create the information on the fly... like: "results.phtml?city=boston" - - But it's getting the beginning of that PHP document squared away that confuses me.

Okay.. I think in the most basic form, what I need is to be able to declare a variable, assign it's value from a querystring, then insert it into my SQL statement.

Make sense?

Again, thank you for helping - all of you!

~Speedster

Air




msg:1313104
 5:28 pm on Nov 8, 2001 (gmt 0)

You're probably having trouble believing it's this easy with PHP :)

You don't need to declare the variables and you don't need to do anything special to retrieve name, value, pairs from a form, just use them in the script called from the form action= field in the html by putting a $ in front of the name.

i.e.

<form method="POST" action="some_script.php">
Favourite animal? <input type="text" name="animal" value="">
<input type="submit" value="Show Favourite Animal">

When the user submits this form and therefore launches some_script.php, PHP automatically sets the variable $animal to contain whatever was entered by the user of the form. Now just refer to $animal anytime you want in some_script.php

SEO Speedster




msg:1313105
 6:00 pm on Nov 8, 2001 (gmt 0)

Air...

If you were nearby, I'd be buying your lunch for the next month...

Perhaps the worst thing about my up-bringing thus far hails from an ASP/MSSQL background... Where everything is a bit more... well... Painful!

Much thanks, and I will be sure to make an attempt at it shortly!

~Speedster

SEO Speedster




msg:1313106
 6:45 pm on Nov 8, 2001 (gmt 0)

I am getting this:

"Warning: Supplied argument is not a valid MySQL result resource in blah-blah-blah/results.php on line 37"

and the 36th, and 37th line reads as:

$result = mysql(xyz, 'select * from directory where city_service LIKE \"%$form_city%\" ORDER BY last_name');

$num = mysql_numrows($result);

Air




msg:1313107
 7:50 pm on Nov 8, 2001 (gmt 0)

Ok, try it like this:

$db = mysql_connect("localhost","userid","password") or die(mysql_error());
mysql_select_db("databasename",$db) or die(mysql_error());

$query = "SELECT * FROM tablename WHERE city_service LIKE \"%$form_city%\" ORDER BY last_name";
$result = mysql_query("$query") or die(mysql_error());
$row = mysql_fetch_array($result, MYSQL_ASSOC);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
print "<b>$form_city</b> -- $row[city_service] $row[last_name]<br>\n";
}

SEO Speedster




msg:1313108
 3:24 pm on Nov 10, 2001 (gmt 0)

Air,

I need to get code like the following within the output per record-set...

echo "<b><font size='3'>";
echo mysql_result($result,$i,"first_name");
echo " ";
echo mysql_result($result,$i,"middle_name");
echo " ";

... i can't figure out why this doesn't seem to work, and I keep trying and trying.. with no luck at all.

Ideas?

Air




msg:1313109
 1:51 am on Nov 13, 2001 (gmt 0)

Did the code I gave as an example work for you after substituting the correct variables for:

userid, password, databasename, tablename ?

It should have just worked after substituting the correct variables for the italicized ones above, and then you could reference any other table entries by using $row[field_variable_name] and print them to the browser as required.

SEO Speedster




msg:1313110
 1:38 am on Nov 14, 2001 (gmt 0)

Sorry for the delay Air.

Yes, everything worked wonderfully! I have applied the sae basic code to other areas as well.

Again, thank you SO much for your assistance!

~ Speedster

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved