PDA

View Full Version : mysql help


Barely Human
05-11-2005, 09:48 PM
Ok, im building a site. I have a submit product form which links to a table in my database and that works fine. I have 2 other tables, one called product_man and another called product_cat. I want a couple of drodown menus on the submit product form which will retrieve the data from the said tables. Im not sure how to do this. I also need the code to save the selected dropdown data somewhere on the database for retrival later on so i can show results for manufacturer, or catagory. I dont know the best way to do this though. i assume the the id number for the product on all 3 tables should be the same, but how do i do this?

Anyways, here is my submit form. I need 2 dropdowns at the top which bring up data from these tables and rows -

table= product_cat, row= category
table= product_man, row= manufacturer

Any help at all would be f$cking exelent..


<html>
<head>
<title>Add a Product</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
.box {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
border: 1px solid #000000;
}
-->
</style>
</head>

<body>
<?php
if(isset($_POST['save']))
{
$product = $_POST['product'];
$description = $_POST['discription'];
$price = $_POST['price'];
$techsheet = $_POST['techsheet'];

if(!get_magic_quotes_gpc())
{
$product = addslashes($product);
$description = addslashes($description);
$price = addslashes($price);
$techsheet = addslashes($techsheet);
}
include 'library/config.php';
include 'library/opendb.php';

$query = "INSERT INTO product_details (product, description, price, techsheet) VALUES ('$product', '$description', '$price', '$techsheet')";
mysql_query($query) or die('Error ,query failed');
include 'library/closedb.php';

echo "Product '$product' added";
}
?>
<form method="post">
<table width="700" border="0" cellpadding="2" cellspacing="1" class="box" align="center">
<tr>
<td width="100">Category</td>
<td><select name="category" cols="50" rows="10" class="" id="category"></select></td>
</tr>
<tr>
<td width="100">Manufacturer</td>
<td><select name="manufacturer" cols="50" rows="10" class="" id="manufacturer"></select></td>
</tr>
<tr>
<td width="100">Product</td>
<td><textarea name="product" cols="50" rows="10" class="box" id="product"></textarea></td>
</tr>
<tr>
<td width="100">Description</td>
<td><textarea name="description" cols="50" rows="10" class="box" id="description"></textarea></td>
</tr>
<tr>
<td width="100">Price</td>
<td><textarea name="price" cols="50" rows="10" class="box" id="price"></textarea></td>
</tr>
<tr>
<td width="100">Techsheet URL</td>
<td><textarea name="techsheet" cols="50" rows="10" class="box" id="techsheet"></textarea></td>
</tr>
<tr>
<td width="100"> </td>
<td> </td>
</tr>
<tr>
<td colspan="2" align="center"><input name="save" type="submit" class="box" id="save" value="Save Article"></td>
</tr>
</table>
</form>
</body>
</html>
schlongfingers
07-11-2005, 11:52 PM
If i'm understanding you correctly, you want the following code, there should be a unique, auto incrementing key field to both the product_cat and product_man tables - i'm assuming it is 'id', if it isn't then just replace id below.


<tr>
<td width="100">Category</td>
<td><select name="category" cols="50" rows="10" class="" id="category">

<?
$q="select distinct category, id from product_cat order by category asc";
$r=mysql_query($q);
$c=mysql_num_rows($r);
for ($i=0;$i<$c;$i++){
$a=mysql_fetch_array($r);
echo '<option value="'.$a['id'].'">'.$a['category'].'</option>';
}
?>

</select></td>
</tr>

product_man works in exactly the same way, just replace ''product_cat' with 'product_man'. 'category' with 'manufacturer'

you need to modify the product table and add columns for product_man and product_cat - in these cols you store the 'id' reference in order to enable you to cross reference the product table with the relevant result in the product_man and product_cat tables. You store the unique id field rather than the text value because it is forcibly unique and immoveable - when you perform updates on the text value of this row (eg changing the name of a category) all products will still point correctly because it is linked via id rather than the text value at the time of adding the product.

hope i haven't misunderstood your question, and that this is some help.
TechMouse
08-11-2005, 01:33 PM
Have a look at using the PEAR DB extension as well... saves a lot of time.
Barely Human
09-11-2005, 08:27 PM
Cheers man! Works a treat.

Now onto the next hurdle. I have a row called "cat_id" in my "product_details" table. How do i get the id of the catagory selected in the dropdown to save to this row?

Techmouse - Whats PEAR DB extension used for?

Thanks again :clap:
schlongfingers
10-11-2005, 10:05 AM
Change the addition handling part of your code, following in the same structure you are currently using it should change to this:



if(isset($_POST['save']))
{
$product = $_POST['product'];
$description = $_POST['discription'];
$price = $_POST['price'];
$techsheet = $_POST['techsheet'];
$catId = $_POST['category'];

if(!get_magic_quotes_gpc())
{
$product = addslashes($product);
$description = addslashes($description);
$price = addslashes($price);
$techsheet = addslashes($techsheet);
$catId = addslashes($catId);
}
include 'library/config.php';
include 'library/opendb.php';

$query = "INSERT INTO product_details (product, description, price, techsheet, cat_id) VALUES ('$product', '$description', '$price', '$techsheet','$catId')";
mysql_query($query) or die('Error ,query failed');
include 'library/closedb.php';

echo "Product '$product' added";
}


PEAR is a collection of php classes that simplify common tasks, it's really heavily used and well worth looking into. Personally I don't use it but thats cos i like reinventing the wheel and am a stick-in-the-mud :D
TechMouse
10-11-2005, 12:31 PM
PEAR takes a lot of the hassle out of writing code for Databases by providing some ready made objects to do 90% of what you want to do, plus it allows you to (in theory) support multiple database backends. Though you wouldn't know it given the amount of ****ing work we've just had to do to get our applications talking to Oracle!

The DB module is just one part of pear - it has loads of other stuff in it to do other things too... http://pear.php.net

The MDB2 abstraction layer looks really good too, but I can't be arsed going back and re-writing all our shit to deal with it!
MangaFish
24-12-2005, 02:49 PM
writing code for databases is about as easy as coding get though tbh. i cant see why you would want a program to do the coding for you
278d7e64a374de26f==