0

I am having my first attempts to a search engine: I have a database called "global" and a table called "mpl" which contains 11 columns (Named: Customer, Part No, Descripton, Country Of Origin, and several other) with multiple rows for parts. What i aim to do with the code below - is to get the Description and Country Of Origin displayed for the Part No the user has entered to the search field.

Form:

<form action="search.php" method="post"> 
<input type="text" name="find" /><br /> 
<input type="submit" value="Search" /> </form>

And the PHP: 

$host = "localhost"; 
$dbuser = "root"; 
$dbpass = " "; 
$db = "global"; 

$con = mysql_connect($host, $dbuser, $dbpass);
    if(!$con){ die(mysql_error());
    } 
$select = mysql_select_db($db, $con);
    if(!$select){ die(mysql_error()); 
    } 
$item = $_REQUEST['find'];  
$data = mysql_query("SELECT * FROM mpl WHERE 'Part No' ='".$item."'"); 
while($row = mysql_fetch_array($data)){ 
        echo $row['Description']. "<br>";
        echo $row['Country Of Origin']. "<br><p>"; 
 } 

 ?>

Can someone tell me what am i doing wrong? Once i enter anything to my form 'find' - i get no results. If i run the search using LIKE instead of "=" with no value - it displays a bunch of Descriptions and Country of origin - this means i have connected to my DB correctly. This is driving me nuts..I feel i have messed up the mysql_query() part somehow - but i can't figure out which part.

Fredrik
  • 2,247
  • 18
  • 21
Mr_Oppenheimer
  • 101
  • 2
  • 3
  • 10
  • 2
    You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. – Quentin Jan 27 '13 at 13:04
  • 1
    Is the column *really* titled 'part no' (with a space)? That looks fishy. – ethrbunny Jan 27 '13 at 13:09
  • are you sure that you have data that match your query. be aware that `WHERE` makes comparison for identical values. please check it – mamdouh alramadan Jan 27 '13 at 13:12
  • Thanks for the comments regarding obsolete code, and vulnerability - i will look into that. – Mr_Oppenheimer Jan 27 '13 at 13:25
  • the column is really titled with the space in it (i now also know it is not the best decision), and i am entering exact match of what i am trying to find to the search field. The issue still persists. – Mr_Oppenheimer Jan 27 '13 at 13:27

6 Answers6

1

You are using the wrong characters to escape the Part No column name in your query. Escape them with the backticks (`) and it should be fine.

$data = mysql_query("SELECT * FROM mpl WHERE `Part No` ='".$item."'");

Also, you should validate the user's query to prevent SQL injection.

zyla
  • 11
  • 1
0

What if you change: 

$item = $_REQUEST['find'];

to 

$item = $_POST['find'];

Also some function like mysql_select_db() are deprecated and going to be removed. See:
http://php.net/manual/en/function.mysql-select-db.php

mallix
  • 1,399
  • 1
  • 21
  • 44
  • 1
    That shouldn't make a difference (although it might if there was a cookie named `find`). Not using `$_REQUEST` is best practise, but probably not the solution here. – Quentin Jan 27 '13 at 13:06
0

Try changing this potion.

$item = $_REQUEST['find'];   $data = mysql_query("SELECT * FROM mpl WHERE 'Part No' ='".$item."'");

to this

$item = $_POST['find'];  
$data = mysql_query("SELECT * FROM mpl WHERE Part No ='$item'");
Kanchana Randika
  • 550
  • 2
  • 12
  • 27
0

do something like this in your request to remove any possible whitespaces and normalize to upper case for select string.

$item = strtoupper(trim($_REQUEST['find']));

And do this in your SQL: to normalize as well.

$data = mysql_query("SELECT * FROM mpl WHERE UPPER(TRIM('Part No')) ='".$item."'");

You are basically not getting an exact match on your where clause

user1551869
  • 115
  • 4
0

A lot of people here have already pointed out possible and actual errors in your code, but here's the combined solution. Firstly I converted your code to mysqli which is the correct way of connecting to a mySQL database. The way you were connecting is out of date, and not recommended. Secondly I added some code to stop sql injection. Thirdly, I changed 'Part No' to `Part No``(ignore the second back tick) in your query.

<?php

$mysqli = new mysqli('localhost', 'root', DB_PASSWORD, 'global');

/* check connection */
if ($mysqli->connect_error)
  die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);

/* escape string from sql injection */
$item = $mysqli->real_escape_string($_POST['find']);

/* query database */
$result = $mysqli->query("SELECT * FROM `mpl` WHERE `Part No` = '".$item."'");
while ($col = $result->fetch_array(MYSQLI_ASSOC))
  echo '<p>' . $col['Description'] . '<br />' . $col['Country Of Origin'] . '</p>';
$result->close();

/* don't forget to close the connection */
$mysqli->close();

?>
Graham Walters
  • 2,054
  • 2
  • 19
  • 30
0

First off, I agree with Quentin; you should be using a database API like PDO or Mysqli. Secondly, it looks like people can search for parts by their part numbers or descriptions. Assuming the part numbers are numeric and the descriptions are strings... check the type of input and run the query accordingly.

$host = "localhost"; 
$dbuser = "root"; 
$dbpass = ""; 
$db = "global";

// Establish a database connection and select one.
// Try using one of the database API's.

// Then compose your sql by checking for the type of input from the form.
// Since your request method is a POST, then just look in the `_POST` superglobal.
$item = $_POST['find'];

if( is_numeric($item) ){
   $sql = "SELECT * FROM mpl WHERE 'Part No' = {$item}";
}else{
   $sql = "SELECT * FROM mpl WHERE 'Description' LIKE '%{$item}%'";
}

// Then perform the query.
James Woodruff
  • 963
  • 6
  • 10