Page 1 of 1

Struggling Newbie - mysql/php problem

Posted: Fri Aug 13, 2010 11:45 am
by sparatacus
Hi,

I have 3 tables in a mysql database, testsuppliers, testpostcodes and testproducts.

-----------------------------------------
| supplier_ID | supplier name |
-----------------------------------------
| 1 | acme |
-----------------------------------------
| 2 | adidas | etc, etc.
----------------------------------------- supplier_ID is primary key

-----------------------------------------
| supplier_ID | postcode |
-----------------------------------------
| 1 | bt48 |
-----------------------------------------
| 1 | bt49 |
-----------------------------------------
| 2 | bt48 |
-----------------------------------------
| 2 | bt50 | etc, etc.
----------------------------------------- both columns are primary key

-------------------------------------------------------
| supplier_ID | product | price |
-------------------------------------------------------
| 1 | a | 45 |
--------------------------------------------------------
| 1 | b | 58 |
--------------------------------------------------------
| 2 | a | 48 | etc, etc.
-------------------------------------------------------- supplier_ID and product are primary key

I would like to be able to construct a form where the customer enters their postcode in a textbox and selects the desired product from a dropdown list and a table will appear listing by price, the suppliers who deliver to the customer's postcode.

I've been pulling my hair out about this but can't seem to get my head around it. This is what I've done, but it's not working. Where am I going wrong?

<?php
$dbhost = "----------";
$dbuser = "-----------";
$dbpass = "--------------";
$dbname = "----------------";
//Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
//Select Database
mysql_select_db($dbname) or die(mysql_error());
//Construct out join query
$query = SELECT testsuppliers.supplier_ID, testpostcodes.postcode, testproducts.product ".
"FROM testsuppliers, testpostcodes, testproducts ".
// Retrieve data from Query String
$postcode = $_GET['postcode'];
$product = $_GET['product'];
// Escape User Input to help prevent SQL Injection
$postcode = mysql_real_escape_string($postcode);
$product = mysql_real_escape_string($product);
//build query
$query = "SELECT * FROM testsuppliers JOIN testpostcodes ON testpostcodes.supplier_ID = testsuppliers.supplier_ID JOIN testproducts ON testproducts.supplier_ID = testpostcodes.supplier_ID";
//Execute query
$qry_result = mysql_query($query) or die(mysql_error());

//Build Result String
$display_string = "<table>";
$display_string .= "<tr>";
$display_string .= "<th>supplier</th>";
$display_string .= "<th>product</th>";
$display_string .= "<th>price</th>";
$display_string .= "</tr>";

// Insert a new row in the table for each person returned
while($row = mysql_fetch_array($qry_result)){
$display_string .= "<tr>";
$display_string .= "<td>$row[supplier]</td>";
$display_string .= "<td>$row[product]</td>";
$display_string .= "<td>$row[price]</td>";
$display_string .= "</tr>";

}
echo "Query: " . $query . "<br />";
$display_string .= "</table>";
echo $display_string;
?>

===================================================================================

// Create a function that will receive data sent from the server
ajaxRequest.onreadystatechange = function(){
if(ajaxRequest.readyState == 4){
var ajaxDisplay = document.getElementById('ajaxDiv');
ajaxDisplay.innerHTML = ajaxRequest.responseText;

}
}
var postcode = document.getElementById('postcode').value;
var product = document.getElementById('product').value;
var queryString = "?postcode=" + postcode + "&product=" + product;
ajaxRequest.open("GET", "-----.php" + queryString, true);
ajaxRequest.send(null);
}

//-->
</script>



<form name='myForm'>
Select your postcode: <input type='text' id='postcode' /> <br />

<br />
<br />
<br />
Select quantity required: <select id='product'>
<option>300</option>
<option>500</option>
<option>900</option>
</select>
<input type='button' onclick='ajaxFunction()' value='Get Prices' />
</form>
</body>
</html>


It is probably something really obvious, but I'm really stuck and need some help.

Regards, Sparatacus