Needed functions:
mysql_connect() (in this example it's presupposed that a connection to your database already exists)
explode()
array()
foreach()
implode()
mysql_query()
mysql_error()
php:
$keywords = 'searching in a mysql database';
You could also write a form where the keywords could be entered.
php:
$association = 'AND';
Search mode AND or OR, can also be defined e.g. by RADIO-buttons.
php:
$keywords = explode(" ",$keywords);
We must participate the keywords because we want later create a mysql query with them.
php:
$query = array();
foreach($keywords as $keyword) {
$query[] = "( `name_of_first_field` LIKE '%".$keyword."%'
OR `name_of_second_field` LIKE '%".$keyword."%'
OR `name_of_third_field` LIKE '%".$keyword."%'
OR `and_so_on` LIKE '%".$keyword."%')
";
}
Every keyword must be changed that we can use it in a mysql query. The %-symbol is a substitute symbol for the possible other text in the data row.
php:
$query = implode("\n ".$association." ",$query);
Now we have to put the keywords together again by using the AND or the OR-association.
php:
$sql = "
SELECT *
FROM `name_of_table`
WHERE ".$query."
;"
$res = mysql_query($sql);
if (!$res) {
/**
* This lines print the SQL statement in a viewable form.
*/
print '<pre>';
print $sql;
print '</pre>';
/**
* E_USER_ERROR will exit the script.
* E_USER_NOTICE and E_USER_WARNING won't stop the script.
*/
trigger_error('Database query failed: '.mysql_error(), E_USER_ERROR);
} else {
/**
* Add here the routines to print the results.
*/
}
...and at last, of course, we'll send the mysql - query and you could print the results as usual with mysql_fetch_object() or mysql_fetch_array()...
Here's the whole script:
php:
$keywords = 'searching in a mysql database';
$association = 'AND';
$keywords = explode(" ",$keywords);
$query = array();
foreach($keywords as $keyword) {
$query[] = "( `name_of_first_field` LIKE '%".$keyword."%'
OR `name_of_second_field` LIKE '%".$keyword."%'
OR `name_of_third_field` LIKE '%".$keyword."%'
OR `and_so_on` LIKE '%".$keyword."%')
";
}
$query = implode("\n ".$association." ",$query);
$sql = "
SELECT *
FROM `name_of_table`
WHERE ".$query."
;"
$res = mysql_query($sql);
if (!$res) {
/**
* This lines print the SQL statement in a viewable form.
*/
print '<pre>';
print $sql;
print '</pre>';
/**
* E_USER_ERROR will stop the script.
* E_USER_NOTICE und E_USER_WARNING won't stop the script.
*/
trigger_error('Database query failed: '.mysql_error(), E_USER_ERROR);
} else {
/**
* Add here the routines to print the results.
*/
}
mysql_connect() (in this example it's presupposed that a connection to your database already exists)
explode()
array()
foreach()
implode()
mysql_query()
mysql_error()
php:
$keywords = 'searching in a mysql database';
You could also write a form where the keywords could be entered.
php:
$association = 'AND';
Search mode AND or OR, can also be defined e.g. by RADIO-buttons.
php:
$keywords = explode(" ",$keywords);
We must participate the keywords because we want later create a mysql query with them.
php:
$query = array();
foreach($keywords as $keyword) {
$query[] = "( `name_of_first_field` LIKE '%".$keyword."%'
OR `name_of_second_field` LIKE '%".$keyword."%'
OR `name_of_third_field` LIKE '%".$keyword."%'
OR `and_so_on` LIKE '%".$keyword."%')
";
}
Every keyword must be changed that we can use it in a mysql query. The %-symbol is a substitute symbol for the possible other text in the data row.
php:
$query = implode("\n ".$association." ",$query);
Now we have to put the keywords together again by using the AND or the OR-association.
php:
$sql = "
SELECT *
FROM `name_of_table`
WHERE ".$query."
;"
$res = mysql_query($sql);
if (!$res) {
/**
* This lines print the SQL statement in a viewable form.
*/
print '<pre>';
print $sql;
print '</pre>';
/**
* E_USER_ERROR will exit the script.
* E_USER_NOTICE and E_USER_WARNING won't stop the script.
*/
trigger_error('Database query failed: '.mysql_error(), E_USER_ERROR);
} else {
/**
* Add here the routines to print the results.
*/
}
...and at last, of course, we'll send the mysql - query and you could print the results as usual with mysql_fetch_object() or mysql_fetch_array()...
Here's the whole script:
php:
$keywords = 'searching in a mysql database';
$association = 'AND';
$keywords = explode(" ",$keywords);
$query = array();
foreach($keywords as $keyword) {
$query[] = "( `name_of_first_field` LIKE '%".$keyword."%'
OR `name_of_second_field` LIKE '%".$keyword."%'
OR `name_of_third_field` LIKE '%".$keyword."%'
OR `and_so_on` LIKE '%".$keyword."%')
";
}
$query = implode("\n ".$association." ",$query);
$sql = "
SELECT *
FROM `name_of_table`
WHERE ".$query."
;"
$res = mysql_query($sql);
if (!$res) {
/**
* This lines print the SQL statement in a viewable form.
*/
print '<pre>';
print $sql;
print '</pre>';
/**
* E_USER_ERROR will stop the script.
* E_USER_NOTICE und E_USER_WARNING won't stop the script.
*/
trigger_error('Database query failed: '.mysql_error(), E_USER_ERROR);
} else {
/**
* Add here the routines to print the results.
*/
}
