Jump to content

Creating Search Engine

antoine01

By antoine01
in PHP Coding Help

 Share

Recommended Posts

antoine01 Newbie

antoine01

Posted
Posted

Please can someone assist. I am new to PHP & MySQL coding and am using dreamweaver to learn.

 

I have created a basic search page with dreamweaver and code for my website www.tova.co.za I am having some problems and would appreciate any assistance. Dreamweaver uses runtime variables to prevent a type of malicious attack known as SQL injection. Dreamweaver then replaces the runtime variables with PHP format specifiers (normally %s or %d), and uses the GetSQLValueString() function

 

I am using a dropdown list in a search form that collects the information from a dynamic table.

 

I want the initial entry to be blank but the populated field adds the first entry of the table in the drop down list. Currently the select items is popolated with the dynamic table, how do I show intial value as blank.

 

I'm also having a propblem with the search results and am not sure of the syntax to use. With the code below the user has to fill in all the search items as I am using AND in the WHERE clause. I have tried OR but this doesn't work. If the field is blank in the search item the results page must ignore it and only use the items selected.

 

Code

 

Results Page

<?php require_once('Connections/tova.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

 

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

}

 

 

if (isset($_POST['delegates'])) {

$varDel_results = $_POST['delegates'];

}

$varProv_results = "-1";

if (isset($_POST['province'])) {

$varProv_results = $_POST['province'];

}

$varCat_results = "-1";

if (isset($_POST['category'])) {

$varCat_results = $_POST['category'];

}

mysql_select_db($database_tova, $tova);

$query_results = sprintf("SELECT wp_dbt_venues.venuesID, wp_dbt_venues.name, wp_dbt_venues.category, wp_dbt_venues.province, wp_dbt_venues.city, wp_dbt_province.provinceID, wp_dbt_province.province, wp_dbt_conferencefacilties.venueid, wp_dbt_conferencefacilties.maxcapacity FROM ((wp_dbt_venues LEFT OUTER JOIN wp_dbt_province ON wp_dbt_venues.province = wp_dbt_province.provinceID) LEFT OUTER JOIN wp_dbt_conferencefacilties ON wp_dbt_venues.venuesID = wp_dbt_conferencefacilties.venueid) WHERE wp_dbt_venues.category = %s AND wp_dbt_venues.province = %s AND wp_dbt_conferencefacilties.maxcapacity < %s", GetSQLValueString($varCat_results, "text"),GetSQLValueString($varProv_results, "int"),GetSQLValueString($varDel_results, "int"));

$results = mysql_query($query_results, $tova) or die(mysql_error());

$row_results = mysql_fetch_assoc($results);

$totalRows_results = mysql_num_rows($results);

 

?>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Untitled Document</title>

</head>

 

<body>

<p>Search Results</p>

<table width="200" border="1">

<tr>

<td> </td>

<td>Name</td>

<td>Category</td>

<td>City</td>

<td>Province</td>

<td>Delegates</td>

</tr>

<?php do { ?>

<tr>

<td><?php echo $row_results['venuesID']; ?></td>

<td><?php echo $row_results['name']; ?></td>

<td><?php echo $row_results['category']; ?></td>

<td><?php echo $row_results['city']; ?></td>

<td><?php echo $row_results['province']; ?></td>

<td><?php echo $row_results['maxcapacity']; ?></td>

</tr>

<?php } while ($row_results = mysql_fetch_assoc($results)); ?>

</table>

<p> </p>

 

</body>

</html>

<?php mysql_free_result($results);

 

?>

 

Search Page

<?php require_once('Connections/tova.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

 

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 

 

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

}

 

 

mysql_select_db($database_tova, $tova);

$query_category = "SELECT category FROM wp_dbt_categories ORDER BY category ASC";

$category = mysql_query($query_category, $tova) or die(mysql_error());

$row_category = mysql_fetch_assoc($category);

$totalRows_category = mysql_num_rows($category);

 

mysql_select_db($database_tova, $tova);

$query_province = "SELECT * FROM wp_dbt_province ORDER BY province ASC";

$province = mysql_query($query_province, $tova) or die(mysql_error());

$row_province = mysql_fetch_assoc($province);

$totalRows_province = mysql_num_rows($province);

 

 

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Search</title>

</head>

 

<body>

<p><strong>Advanced Search</strong></p>

<form action="results.php" method="post" name="form1" target="_blank" id="form1">

<p>

<label>Category

<select name="category" id="category">

<?php

do {

?>

<option value="<?php echo $row_category['category']?>"<?php if (!(strcmp($row_category['category'], $row_category['category']))) {echo "selected=\"selected\"";} ?>><?php echo $row_category['category']?></option>

<?php

} while ($row_category = mysql_fetch_assoc($category));

$rows = mysql_num_rows($category);

if($rows > 0) {

mysql_data_seek($category, 0);

$row_category = mysql_fetch_assoc($category);

}

?>

</select>

</label>

</p>

<p>

<label>Province

<select name="province" id="province">

<?php

do {

?>

<option value="<?php echo $row_province['provinceID']?>"<?php if (!(strcmp($row_province['provinceID'], $row_province['provinceID']))) {echo "selected=\"selected\"";} ?>><?php echo $row_province['province']?></option>

<?php

} while ($row_province = mysql_fetch_assoc($province));

$rows = mysql_num_rows($province);

if($rows > 0) {

mysql_data_seek($province, 0);

$row_province = mysql_fetch_assoc($province);

}

?>

</select>

</label>

</p>

<p>

<label>Delegates

<input name="delegates" type="text" id="delegates" value="" />

</label>

</p>

<p>

<label>

<input type="checkbox" name="Facilities" value="golf" id="Facilities_0" />

Golf</label>

<br />

<label>

<input type="checkbox" name="Facilities" value="game" id="Facilities_1" />

Game</label>

<br />

</p>

<p>

<label>Search

<input type="submit" name="submit" id="submit" value="Submit" />

</label>

</p>

</form>

<p> </p>

</body>

</html>

<?php

mysql_free_result($category);

 

 

mysql_free_result($province);

?>

 

Link to comment
Share on other sites
antoine01 Newbie

antoine01

Posted
  • Author
Posted

Thanks I'll try to better this time.

 

The first problem is with the search page collects the information from a dynamic table. I'll use the some of the code from category table as a example;

 

mysql_select_db($database_tova, $tova);
$query_category = "SELECT category FROM wp_dbt_categories ORDER BY category ASC";
$category = mysql_query($query_category, $tova) or die(mysql_error());
$row_category = mysql_fetch_assoc($category);
$totalRows_category = mysql_num_rows($category);

 

The problem  is that I want the initial entry to be blank but the populated field adds the first entry of the table in the drop down list. Currently the select items is popolated with the dynamic table, how do I show intial value as blank.

 

The form calls the values from the dynamic table query created above;

<p>
<label>Category
<select name="category" id="category">
<?php
do { 
?>
<option value="<?php echo $row_category['category']?>"<?php if (!(strcmp($row_category['category'], $row_category['category']))) {echo "selected=\"selected\"";} ?>><?php echo $row_category['category']?></option>
<?php
} while ($row_category = mysql_fetch_assoc($category));
$rows = mysql_num_rows($category);
if($rows > 0) {
mysql_data_seek($category, 0);
$row_category = mysql_fetch_assoc($category);
}
?>
</select>
</label>
</p>

 

 

The next problem is with the search results and am not sure of the syntax to use. With the code below the user has to fill in all the search items as I am using AND in the WHERE clause. I have tried OR but this doesn't work. If the field is blank in the search item the results page must ignore it and only use the items selected.

 

$varCat_results = "-1";
if (isset($_POST['category'])) {
$varCat_results = $_POST['category'];
}
mysql_select_db($database_tova, $tova);
$query_results = sprintf("SELECT wp_dbt_venues.venuesID, wp_dbt_venues.name, wp_dbt_venues.category, wp_dbt_venues.province, wp_dbt_venues.city, wp_dbt_province.provinceID, wp_dbt_province.province, wp_dbt_conferencefacilties.venueid, wp_dbt_conferencefacilties.maxcapacity FROM ((wp_dbt_venues LEFT OUTER JOIN wp_dbt_province ON wp_dbt_venues.province = wp_dbt_province.provinceID) LEFT OUTER JOIN wp_dbt_conferencefacilties ON wp_dbt_venues.venuesID = wp_dbt_conferencefacilties.venueid) WHERE wp_dbt_venues.category = %s AND wp_dbt_venues.province = %s AND wp_dbt_conferencefacilties.maxcapacity < %s", GetSQLValueString($varCat_results, "text"),GetSQLValueString($varProv_results, "int"),GetSQLValueString($varDel_results, "int"));
$results = mysql_query($query_results, $tova) or die(mysql_error());
$row_results = mysql_fetch_assoc($results);
$totalRows_results = mysql_num_rows($results);

 

I have used Dreamweaver which uses runtime variables to prevent a type of malicious attack known as SQL injection. Dreamweaver then replaces the runtime variables with PHP format specifiers (normally %s or %d), and uses the GetSQLValueString() function. I suspect I need to write the code as I don't think dreamweaver can do this

 

Apologies if its confusing but so am I  :'(

Link to comment
Share on other sites
Jessica Advanced Member

Jessica

Posted
Posted

Not sure why you made everything bold now. :)

 

The first problem is with the search page collects the information from a dynamic table. I'll use the some of the code from category table as a example;

The problem  is that I want the initial entry to be blank but the populated field adds the first entry of the table in the drop down list. Currently the select items is popolated with the dynamic table, how do I show intial value as blank.

 

Just add a blank option right after starting the select.

<select name="category" id="category">
<option> </option>

 

 

The next problem is with the search results and am not sure of the syntax to use. With the code below the user has to fill in all the search items as I am using AND in the WHERE clause. I have tried OR but this doesn't work. If the field is blank in the search item the results page must ignore it and only use the items selected.

 

For this problem, you need to create your query as a string, only adding the fields which are not empty. You can do it like this, where your posted fields have the name "search[]". You would use prepared statements to avoid SQL injection. I seriously suggest stop using Dreamweaver.

 

$query = "SELECT field FROM table";
$whereClauses = array();
$whereValues = array();
foreach($_POST['search'] AS $name=>$s){
   $s = trim($s);
   if(strlen($s)){
        $whereClauses[] = "`$name` = ?";
        $whereValues[] = $s;
   }
}
if(count($whereClauses)){
   $query .= " WHERE ".join(" OR ", $whereClauses);
}

//Now your query is $query and your arguments for the prepared statement are $whereValues;

Link to comment
Share on other sites
antoine01 Newbie

antoine01

Posted
  • Author
Posted
Just add a blank option right after starting the select.

I'm really struggling where to put the code as I get incorrect results and errors;

<form action="searchformresults.php" method="post" name="search" target="_blank" id="search">
  <p>
    <label>Category <select name="category" id="category"><?php

do {  		?>
      
     <option value=
 "<?php echo $row_getCategory['categoriesID']?>"<?php 
 if (!(strcmp($row_getCategory['categoriesID'], $row_getCategory['categoriesID']))) {echo "selected=\"selected\"";} ?>><?php
     echo $row_getCategory['category']?>
 </option>
 <?php
	} while ($row_getCategory = mysql_fetch_assoc($getCategory));
	  $rows = mysql_num_rows($getCategory);
	  if($rows > 0) {
		  mysql_data_seek($getCategory, 0);
		  $row_getCategory = mysql_fetch_assoc($getCategory);
	  }
?>
      </select>
</label>

 

For this problem, you need to create your query as a string, only adding the fields which are not empty. You can do it like this, where your posted fields have the name "search[]". You would use prepared statements to avoid SQL injection. I seriously suggest stop using Dreamweaver.

 

I'm very confused how to change the code, should I remove all the dreamweaver code and start just with the query or add it to the dreamweaver code. I use dreamweaver to teach myself coding.

 

I have added the following code to the body and am not sure which field to echo and I am also getting errors;

 

Notice: Undefined index: search in C:\xampp\htdocs\tova\searchformresults.php on line 69

Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\tova\searchformresults.php on line 69

 

<?php
$query = "SELECT 'category' FROM wp_dbt_venues.category";
$whereClauses = array();
$whereValues = array();
foreach($_POST['category'] AS $name=>$s){
   $s = trim($s);
   if(strlen($s)){
        $whereClauses[] = "`$name` = ?";
        $whereValues[] = $s;
   }
}
if(count($whereClauses)){
   $query .= " WHERE ".join(" OR ", $whereClauses);
}

?>

Link to comment
Share on other sites
Jessica Advanced Member

Jessica

Posted
Posted

For the first one I literally gave you the code, what is confusing about it?

 

For the second, there is nothing with an index of search in that code. The second error tells you $_POST['category'] is not an array. You have to adapt the code I wrote.

Link to comment
Share on other sites
Christian F. Member

Christian F.

Posted
Posted

Not quite on-topic, but...

 

I would actually recommend you to scrap DreamWeaver, and instead start to write the PHP code by hand. Start simple, with a tutorial showing you all of the basics, then work your way up. I believe the PHP manual has a good tutorial to start with.

In either case, the PHP manual is a very good resource, and which is highly recommended to read through!

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.