[RESOLVED] do I need an array?

MartinHansell

Hi,

I am trying to help a friend resolve a problem in their code written by someone else, and I am new to php/mysql myself...

They want to do a search of a database using multiple criteria, yet it looks to me like the original code in searchresult.php can only handle a single criterion (but I'm not sure if I am right).

Perhaps you could take a look at the search page and test it out by searching on more than one item to see what I mean...

I know this is a big ask... but I hope someone can take me up on this... and if you need more info please don't hesitate to ask.
Cheers
Martin

Here is the code from the various pages.

SEARCH.PHP [where the form data in entered]

	<table>

<tr><td width="68" align="right"> Make : </td><td width="192" align="left"><input name="txtMake" type="text"/></td></tr>

<tr><td align="right"> Price : </td><td align="left"><input name="txtPrice" type="text" />&nbsp;

  <select name="compareprice">

    <option value="lower">Lower</option>

    <option value="equal">Equal</option>

    <option value="higher">Higher</option>

  </select>

  </td></tr>

<tr><td align="right"> Model : </td><td align="left"><input type="text" name="txtModel" /></td></tr>

<tr><td align="right" colspan="2"><input type="submit" name="Submit" value="Search Laptop" />&nbsp;<input name="Reset" type="reset" id="Reset" value="Reset" /></td></tr>

</table>

ALLIN.PHP  [this seems to be a general query handler to process all the queries on the site]


else if($_REQUEST['Submit'] == 'Search Laptop'){

	$laptop_make = $_REQUEST['txtMake'];

	$laptop_price = $_REQUEST['txtPrice'];

	$laptop_model = $_REQUEST['txtModel'];

	$compareprice = $_REQUEST['compareprice'];



	header( 'Location: searchresult.php?make='.$laptop_make.'&price='.$laptop_price.'&model='.$laptop_model."&compareprice=".$compareprice) ;

}

SEARCHRESULT.PHP [where the query is passed back to the site. It's here I think the code falls over coz each instance of the if statements from the 2nd onwards seems to set a new value for $query, rather than creating a cumulative value for a search on more than one criteria, and there is no code for handling multiple data after the first if statement which creates the "WHERE" code for the sql. You might expect to see "AND" data "AND "more data", no?]

            <?php

			$make = $_REQUEST['make'];

			$price = $_REQUEST['price'];

			$model = $_REQUEST['model'];

			$search = $_REQUEST['search'];

			$compareprice = $_REQUEST['compareprice'];



			$query = "SELECT * FROM table_laptop";

			if($make != "" || $price != "" || $model != "" || $search != "")

				$query = $query." WHERE";



			if($make != "")

				$query = $query.' laptop_make = "'.$make.'"'; 



			if($price != ""){

				if($compareprice == "higher")

					$query = $query.' laptop_price > '.$price; 

				elseif($compareprice == "lower")

					$query = $query.' laptop_price < '.$price; 

				elseif($compareprice == "equal")

					$query = $query.' laptop_price = '.$price; 

			}



			if($model != "")					

				$query = $query.' laptop_model = "'.$model.'"'; 



			if($search != "")

				$query = $query.' laptop_description like "%'.$search.'%"'; 



			$result=mysql_query($query) or die(header( 'Location: message.php?msg=1' ));

			$row=mysql_numrows($result);

			$gotResult = 'false';

			while($row = mysql_fetch_array($result, MYSQL_ASSOC))

			{

				$gotResult = 'true';

				echo '<table style="border-style:dotted">';   

				echo '<tr><td width="150px" align="right"><strong>Id : </strong></td><td align="left">'.$row['laptopid'].'</td></tr>';

				echo '<tr><td align="right"><strong>Make : </strong></td><td align="left">'.$row['laptop_make'].'</td></tr>';

				echo '<tr><td align="right"><strong>Model :</strong> </td><td align="left">'.$row['laptop_model'].'</td></tr>';

				echo '<tr><td align="right"><strong>Price :</strong> </td><td align="left">'.$row['laptop_price'].'</td></tr>';

				echo '<tr><td colspan="2"><strong>Description</strong></td></tr>';

				echo '<tr><td colspan="2">'.$row['laptop_description'].'</td></tr>';

				echo '</table>';

			}

			if($gotResult == 'false'){

				echo 'Can\'t find any product.';

			}

		?>

djjjozsi

it is the searchresult.php

In a mysql SELECT you must keep the syntax,
SELECT * FROM table WHERE (field='Someting' AND field2_price >5) OR search_tag LIKE '%C%'

<?php
/* make a function to the repeating events...   */
	function safe($var){
	if(!empty($_REQUEST[$var]))
	return mysql_real_escape_string(trim($_REQUEST[$var]));
	else
	return NULL;
	}

$make = safe('make');
$price = (int)$_REQUEST['price'];
$model = safe('model');
$search = safe('search');
$compareprice = trim($_REQUEST['compareprice']);
$query = "SELECT * FROM table_laptop";

if($make != "" || $price != "" || $model != "" || $search != "")
$query = $query." WHERE";

if($make != "")
$query = $query." AND laptop_make = '".$make."'";

if($price != "")
{

	switch($compareprice){
		case "higher":
			$sign='>';
			break;
		case "lower":
			$sign='<';
			break;
		case "equal":
			$sign='=';
			break;
		default:
			$sign='=';
			break;
	} // switch

$query = $query." AND laptop_price $sign ".$price;
}

if($model != "")
$query = $query." AND laptop_model = '".$model."'";

if($search != "")
$query = $query." AND laptop_description LIKE '%".$search."%'";

$query=str_replace("WHERE AND","WHERE ",$query);

$result=mysql_query($query) or die(header( 'Location: message.php?msg=1' ));

$rows_num=mysql_num_rows($result);  //And not mysql_numrows !!
if($rows_num==0)
$gotResult = 'false';
else
$gotResult = 'true';

while($row = mysql_fetch_assoc($result))
{
	echo '<table style="border-style:dotted">';
	echo '<tr><td width="150px" align="right"><strong>Id : </strong></td><td align="left">'.$row['laptopid'].'</td></tr>';
	echo '<tr><td align="right"><strong>Make : </strong></td><td align="left">'.$row['laptop_make'].'</td></tr>';
	echo '<tr><td align="right"><strong>Model :</strong> </td><td align="left">'.$row['laptop_model'].'</td></tr>';
	echo '<tr><td align="right"><strong>Price :</strong> </td><td align="left">'.$row['laptop_price'].'</td></tr>';
	echo '<tr><td colspan="2"><strong>Description</strong></td></tr>';
	echo '<tr><td colspan="2">'.$row['laptop_description'].'</td></tr>';
	echo '</table>';
}

if($gotResult == 'false')
{
	echo 'Can\'t find any product.';
}

?>

MartinHansell

Thanks so much!
I just hope one day I will be in the same position to do the same for someone else!
Martin

MartinHansell

Actually, "Thanks so much" is totally inadequate to explain myself well.

I am truly overwhelmed at the generosity of the open source community. Ever since I first got into it, I was impressed, but to be on the receiving end of it 1st hand in such a 1st class manner is just too much!

djjjozsi - you're amazing. I just took a look at your profile page, and with your stats already flying after such a short membership period - what can I say? Thanks, thanks, and thanks again!

bradgrafelman

Don't forget to mark this thread resolved (if it is) using the link in the thread tools menu.