|Using PHP to search an entire column contents in MySQL|
Just another quick newbie issue!
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!
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/
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.
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.
Again, thank you for helping - all of you!
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.
<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
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!
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);
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";
I need to get code like the following within the output per record-set...
echo "<b><font size='3'>";
echo " ";
echo " ";
... i can't figure out why this doesn't seem to work, and I keep trying and trying.. with no luck at all.
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.
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!