simple php search is working but not perfect

im struggling with retrieving more advanced results

[riggerz29]

Hi all im new to php and what i want to do is search my database and echo the results. I have a simple search which searchs 1 table and 1 column in my database and displays the result.

what i would like to do is query more columns in my table and echo the result with a link to the detail of my db contents.

here is my code that i used for a simple search

$q = mysql_real_escape_string($_GET["q"]);
$newq = strtoupper($q);

$searchQuery = mysql_query("SELECT * FROM electric WHERE UPPER(`model`) LIKE '%$newq%' ORDER BY `id` DESC LIMIT 10");

while ($row = mysql_fetch_assoc($searchQuery))
{
echo "Record ID:" . $row["id"] . "<br /><br />" . stripslashes($row["model"]) . "<hr />" . "<br />";
}

$row["model"];

Here is my code ive tried, to make a more advanced search

$q = mysql_real_escape_string($_GET["q"]);
$newq = strtoupper($q);

$searchQuery = mysql_query("SELECT model, year, ref, photo1 FROM electric WHERE UPPER model LIKE '%$newq%' ¦¦ year LIKE '%$newq%' ¦¦ ref LIKE '%$newq%' ORDER BY `id` DESC LIMIT 10");

while ($row = mysql_fetch_assoc($searchQuery))
{
echo "Search No:" . $row["id"] . "<br /><br />" . stripslashes($row["model"]) . " ¦ " . stripslashes($row["year"]) . " ¦ " . stripslashes($row["ref"]) . " ¦ " . "<img src='images/trucks/".$row["photo1"] . "<hr />";
}

$row["model"];

any help at all would be greatly appreciated as my method is not working at all thanks in advance.

[rocknbil]

A couple things wrong here, mostly related to flexibility of your programming.

$searchQuery = mysql_query("SELECT model, year, ref, photo1 FROM electric WHERE UPPER model LIKE '%$newq%' ¦¦ year LIKE '%$newq%' ¦¦ ref LIKE '%$newq%' ORDER BY `id` DESC LIMIT 10");

Repeat after me: I will not hard code mySQL statements directly in the mysql_query command. :-) This means every time you want to change a statement, you have to dig through your code to find it, debug it, etc. Put it in a variable.

$query = "SELECT model, year, ref, photo1 FROM electric WHERE UPPER model LIKE '%$newq%' or year LIKE '%$newq%' or ref LIKE '%$newq%' ORDER BY id DESC LIMIT 10";

$searchQuery = mysql_query($query);

Before we continue, first be extremely aware that or can be a slippery beast, especially when combined with and. If any of them return true, the entire statement returns true. As you work with complex conditionals, you will see what I mean.

Second, in looking at your select I think you want and, not or. I see what you're doing, using a single text field for input and searching all fields, in which case you do want "or." But it's not the best approach to a search. If that's all you need, you just need to remove the PHP logical or operators ¦¦ and substitute them with the word or, as shown above -the statement is a mySQL statement, not a PHP statement.

The other reason for not having these in the command is that you will need to build your where clause dynamically based on input. An example,

$where=$select=''; 
if (isset($input['model']) && ($input['model'] != '')) { 
 // first time through you don't need the and 
 if ($where !='') { $where .= ' and'; } 
 $where .= " model like '%" . $input['model'] . "%'"; 
} 
if (isset($input['year']) && ($input['year'] > 0)) { 
 if ($where !='') { $where .= ' and'; } 
 // Year should be a select list on a valid year 
 // format, not a text input, retrieval is much slower 
 $where .= " year = '" . $input['year'] . "'"; 
} 
if (isset($input['ref']) && ($input['ref'] != '')) { 
 if ($where !='') { $where .= ' and'; } 
 $where .= " ref like '%" . $input['ref'] . "%'"; 
} 

Your where should now have any of the following, and more, dependent on input:

model like '%Chevy%'
year = '1956'
ref like '%one two%'
model like '%Chevy%' and year = '1956'
model like '%Chevy%' and year='1956' and ref like '%one two%'

Note: you don't need to upper case like, it is case insensitive.

Now create the select. If where is blank (which is why I set it to '') you will just select all records:

$query = "select * from table";
if ($where != '') { $query .= " where $where"; }

Note: in all of the above, note the placement of the space before any concatenation. This is important or you get select * from tablewherethisandthis, which will error.

The last bit is your order and limit. You should (and can) really pare these off into a generic order and limit function that can be used from any aspect of your program. For simplicity, let's just say these are user-configured in the form, with a program preset default.

$per_page = (isset($input['per_pg']) and ($input['per_pg'] > 0))?$input['per_pg']:$default_per_page;

$order = (isset($input['order_by']) and ($input['order_by'] != ''))?$input['order_by']:$default_order_by;

$direction = (isset($input['direction']) and ($input['direction'] != ''))?$input['direction']:$default_direction;

Append it to the query,

$select .= " order by $order_by limit $per_page $direction";

And now you should have:

"select * from table where model like '%Chevy%' and year='1956' and ref like '%one two%' order by price limit 20 desc"

This gets more complex as you add more features, and in this sample the limit doesn't account for more selection midway through results (limit 30, 30), but the first step is to get it out of the PHP command. The samples here are insecure, do not use input directly from post or get, filter the values first.

[riggerz29]

Thank you sooo much for your reply i appriciate it :) i will revise what you have said and get working on it and i will also never hard code mySQL statements in mysql_query command agin :)