Creating stock search with MySQL?

Major_Disaster · 8 Jun 2010 at 19:36

Hello all,

Im in the process of creating a website, and would like to implement a stock search facility.

I have a dump of the company stock which is a 8000 row excel file, with columns Part Number, Brand, Qty.

What i would like is for a visitor to be able to enter a part number to search for, which searches a MySQL database of these parts, with matching (and partial matches) shown.

How easy would this be to achieve? I have no experience with SQL, but am familiar with PHP/HTML/JS etc. Would it be a simple case of importing the excel file (somehow) and then setting up a query with PHP?

Cheers for any advice - im rather looking forward to learning more about this!

Major_Disaster · 8 Jun 2010 at 21:13

Thanks for the help - so its definately doable with little (ie no) prior knowledge?

Im just about to create a MySQL database via cPanel to test this out on - any tips? What permissions should i grant myself? Any security tips?

Thanks

Major_Disaster · 8 Jun 2010 at 23:17

Thanks for all the help.

To my surprise i have managed to setup a database via cPanel and import the 7909 stock lines successfully - Im currently playing around and searching to my heart's content via phpmyadmin!

I was going to steal kwerk's code to try to whip up a php page to test this, but i was wondering, how do i go about creating the MySQL connection? What is the syntax and should i be using localhost?

@_TubbZ_ - yep i have used PHP to sanitize a contact form, so i think i should be ok there, but as for the link... whats it for?!

Cheers

Major_Disaster · 9 Jun 2010 at 00:16

Well i had a go following some tutorials and im having some trouble.

The database name is: ryanbal1_stock
The table name is: Stock Search
The table has 3 fields: Part Number, Manufacturer, Quantity
The code im using:

PHP:

<body>
<?php
	mysql_connect("localhost", "UserName", "Password") or die(mysql_error());
	echo "Connected to MySQL<br />";
	mysql_select_db("ryanbal1_stock") or die(mysql_error());
	echo "Connected to Database";
?>


<?php
if (isset($_POST['submit'])){
$partname = "$_POST('partname')";

$query = mysql_query("SELECT * FROM 'Stock Search' WHERE 'Part Number' LIKE '%$partname%'");

while ($rows = mysql_fetch_array($query)) {
echo $rows['Part Number'] ." (". $rows['Quantity'] ." units in stock)<br />";
}
} else {
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Part Name:&nbsp; <input name="partname" type="text" /><br />
<input name="submit" type="submit" value="Search" /></form>

<?php
}
?>  

</body>

Im getting the following error:

Code:

Connected to MySQL
Connected to Database
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/ryanbal1/public_html/search.php on line 23

Line 23 corresponds to the opening 'while' line - any ideas?

Cheers for any help

Major_Disaster · 9 Jun 2010 at 02:11

Thanks a lot for all your help - its grealty appreciated.

Im playing around with your code now - trying to output it into a table, but im running into problems with not understanding how to properly escape characters, could you have a look:

PHP:

	// Display results
	if (mysql_num_rows($query) == 0) {
		echo "No results found!<br />";
	} else {
		echo "<table><tr><th>Part Number</th><th>Manufacturer</th><th>Quantity</th><tr>";
		while ($rows = mysql_fetch_array($query)) {
		echo "<tr><td>".$rows['Part Number']."</td>" - "<td>".$rows['Manufacturer']."</td>" - "<td>".$rows['Quantity']."</td></tr> units in stock<br />";
		echo "</table>";
		}
	}

Thanks again

EDIT: Its not pretty but it works:

PHP:

	// Display results
	if (mysql_num_rows($query) == 0) {
		echo "No results found!<br />";
	} else {
		echo "<table border=\"1\" align=\"center\">";
		echo "<tr><th>Part Number</th>";
		echo "<th>Manufacturer</th>";
		echo "<th>Quantity</th><tr>";
		while ($rows = mysql_fetch_array($query)) {
			echo "<tr><td>";
			echo $rows['Part Number'];
			echo "</td><td>";
			echo $rows['Manufacturer'];
			echo "</td><td>";
			echo $rows['Quantity'];
			echo "</td></tr>";
		}
		echo "</table>";
	}

Major_Disaster · 9 Jun 2010 at 15:24

I have been plaing around with this a bit more, and what i would like to be able to do is have a checkbox by each part that can be selected. The user then enters their details in a form below the search results, clicks send request and it sends an email requesting the selected parts.

I think i can handle the emailing and all that, im just not sure what i would need to do with the checkboxes and then sending them to the email?

What i am trying to achieve is shown here