MySQL Search - Can you Autogenerate Keywords using PHP?

General PHP Help
#1

Hi,

I have a pretty basic puzzle/question… is it possible to use PHP to create automated input into form fields?

I.e. in a record insertion form, can you duplicate & combine the input values of 2 form fields, e.g. “Client Name” & “Client Age” and put them into a hidden field called “Client Name & Age” without the user having to re-type their entries into 3 seperate form fields?

This may seem like a strange query, I’m a looking for the simplest way to produce a records search facility on my website, based on a limited knowldege of PHPl… My table is like this:

  • Product category
  • Product make
  • Product description
  • Product model

I want PHP to automatically generate these keywords based on what the user inputs into the form - almagamating all of their categories of info into one recordset. Can it be done? I do have a search facility up and running, but it matches up records seperately from all these 4 different collumns based on the form input. This has limitations - i.e. you can’t currently search both make & model at the same time.

This is a snippet of the code I have used - it works, i.e. the page functions and the record is inserted, but the KeyWords collumn remains empty. As a new PHP learner, I’m wondering have I hit a wall in the basics of scripting…?? Help much appreciated, thanks :)

$editFormAction = $_SERVER[‘PHP_SELF’];
if (isset($_SERVER[‘QUERY_STRING’])) {
$editFormAction .= “?” . htmlentities($_SERVER[‘QUERY_STRING’]);
}

if ((isset($_POST[“MM_insert”])) && ($_POST[“MM_insert”] == “form1”)) {

$insertSQL = sprintf(“INSERT INTO tbl_audio_amps (productID, category, make, model, description, price, quantity, keyWords) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)”,
GetSQLValueString($_POST[‘productID’], “int”),
GetSQLValueString($_POST[‘category’], “text”),
GetSQLValueString($_POST[‘make’], “text”),
GetSQLValueString($_POST[‘model’], “text”),
GetSQLValueString($_POST[‘description’], “text”),
GetSQLValueString($_POST[‘price’], “text”),
GetSQLValueString($_POST[‘quantity’], “text”),
GetSQLValueString($_POST[‘category’][‘make’][‘model’][‘description’], “text”));

mysql_select_db($database_connUsedEquipment, $connUsedEquipment);
$Result1 = mysql_query($insertSQL, $connUsedEquipment) or die(mysql_error());

$insertGoTo = “display/used_equipment.php”;
if (isset($_SERVER[‘QUERY_STRING’])) {
$insertGoTo .= (strpos($insertGoTo, ‘?’)) ? “&” : “?”;
$insertGoTo .= $_SERVER[‘QUERY_STRING’];
}

#2

I am just wondering why you can’t search make and model a the same time, maybe I am misunderstanding what you are trying to say, but the following query will:

[php]<?
$q = “SELECT * FROM mytable WHERE model = '$model and make = ‘$make’”;

//OR SOMETHING LIKE

$q = “SELECT * FROM mytable WHERE model = ‘$searchString’ or make = ‘$searchString’”;
[/php]

And yes you can combine 2 form fields using concatination. (.)

[php]<?
$my = “my”;
$space = " ";
$string = “string!”;
$full_string = $my . $space . $string;

echo $full_string; //OUTPUTS: my string!
?>[/php]

#3

If you’re looking for full-text searching, I’d suggest taking a look at the LIKE clause that MySQL provides. It’s quite powerful. Keywords? Perhaps you could create an algorythm that scans the content to be searched for multiple hits on certain words shrugs

#4

Thanks for all your help. The statement worked by changing

GetSQLValueString($_POST[‘category’][‘make’][‘model’][‘description’],
“text”)

to:

GetSQLValueString($_POST[‘category’] . " " . $_POST[‘make’] . " " .
$_POST[‘model’] . " " . $_POST[‘description’], “text”)

Just wondering - if I’m doing it this way, how can I modify the search so that it looks at individual keywords seperately, regardless of what order you put them in?

I have a collumn set up now that contains category, make, model and description - if the customer searches for make + model in that order it works, but if they search for category + model, say, it doesn’t work. :( The words all have to be in the same order as they’re entered in the database. There must be an easier way to do this? Can it pick out individual words seperately…?

#5

I think you need to use OR for that in SQL SELECT queries.

#6

Thanks - sorry to be a so literal, I agree with you/get what you mean but I don’t quite know exactly how or where I should change that in the code; could you help by taking a look at it? I’ve set it up as a recordset callled “rsKeyWords” with a filter, where the filter takes the input from the form value “criteria” and matches it to the keywords.

SELECT *
FROM tbl_audio_amps
WHERE keyWords LIKE ‘%colname%’
ORDER BY productID DESC

which reads in the code as this below:

<?php require_once('Connections/connUsedEquipment.php'); ?> <?php $colname_rsKeyWords = "-1"; if (isset($_POST['criteria'])) { $colname_rsKeyWords = (get_magic_quotes_gpc()) ? $_POST['criteria'] : addslashes($_POST['criteria']); } mysql_select_db($database_connUsedEquipment, $connUsedEquipment); $query_rsKeyWords = sprintf("SELECT * FROM tbl_audio_amps WHERE keyWords LIKE '%%%s%%' ORDER BY productID DESC", $colname_rsKeyWords); $rsKeyWords = mysql_query($query_rsKeyWords, $connUsedEquipment) or die(mysql_error()); $row_rsKeyWords = mysql_fetch_assoc($rsKeyWords); $totalRows_rsKeyWords = mysql_num_rows($rsKeyWords); ?>

I then use a table containing dynamic fields from “rsKeyWords” to display the results.

#7

The SQL query would have to be changed:

SELECT *
FROM tbl_audio_amps
WHERE keyWords LIKE ‘%colname%’ OR keyWords LIKE ‘%colname2%’
ORDER BY productID DESC

#8

No, that doesn’t seem to work either.

Don’t both %colname% and %colname2% are refer to the same variable: $_POST[‘criteria’]? - if so, I’m not sure how the OR clause works in seperating individual words inputted into the same form input box… I could see how it would maybe work if you were using different form input boxes…?

Am I missing something ?

#9

Ah, I see now. There’s only one variable that holds the multiple keywords (right?). You could explode() the variable on the delimiter (say, you’ve delimited the keywords with a comma). You’ll have to unravel the separate keywords and use them separately in OR clauses.

OR … you could use the IN clause :wink:

WHERE keywords IN (’%keyword1%’, ‘%keyword2%’, ‘%keyword3%’)

Sponsor our Newsletter | Privacy Policy | Terms of Service