mysql help

**Barely Human** · 05-11-2005, 10: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..

Code:

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

&lt;body>
&lt;?php
if&#40;isset&#40;$_POST&#91;'save'&#93;&#41;&#41;
&#123;
$product = $_POST&#91;'product'&#93;;
$description = $_POST&#91;'discription'&#93;;
$price = $_POST&#91;'price'&#93;;
$techsheet = $_POST&#91;'techsheet'&#93;;

if&#40;!get_magic_quotes_gpc&#40;&#41;&#41;
&#123;
$product = addslashes&#40;$product&#41;;
$description = addslashes&#40;$description&#41;;
$price = addslashes&#40;$price&#41;;
$techsheet = addslashes&#40;$techsheet&#41;;
&#125;
include 'library/config.php';
include 'library/opendb.php';

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

echo "Product '$product' added";
&#125;
?>
&lt;form method="post">
&lt;table width="700" border="0" cellpadding="2" cellspacing="1" class="box" align="center">
&lt;tr> 
&lt;td width="100">Category&lt;/td>
&lt;td>&lt;select name="category" cols="50" rows="10" class="" id="category">&lt;/select>&lt;/td> 
&lt;/tr>
&lt;tr>
&lt;td width="100">Manufacturer&lt;/td>
&lt;td>&lt;select name="manufacturer" cols="50" rows="10" class="" id="manufacturer">&lt;/select>&lt;/td> 
&lt;/tr>
&lt;tr>
&lt;td width="100">Product&lt;/td>
&lt;td>&lt;textarea name="product" cols="50" rows="10" class="box" id="product">&lt;/textarea>&lt;/td>
&lt;/tr>
&lt;tr>
&lt;td width="100">Description&lt;/td>
&lt;td>&lt;textarea name="description" cols="50" rows="10" class="box" id="description">&lt;/textarea>&lt;/td>
&lt;/tr>
&lt;tr>
&lt;td width="100">Price&lt;/td>
&lt;td>&lt;textarea name="price" cols="50" rows="10" class="box" id="price">&lt;/textarea>&lt;/td>
&lt;/tr>
&lt;tr>
&lt;td width="100">Techsheet URL&lt;/td>
&lt;td>&lt;textarea name="techsheet" cols="50" rows="10" class="box" id="techsheet">&lt;/textarea>&lt;/td>
&lt;/tr>
&lt;tr>    
&lt;td width="100">&amp;nbsp;&lt;/td>
&lt;td>&amp;nbsp;&lt;/td>
&lt;/tr>
&lt;tr> 
&lt;td colspan="2" align="center">&lt;input name="save" type="submit" class="box" id="save" value="Save Article">&lt;/td>
&lt;/tr>
&lt;/table>
&lt;/form>
&lt;/body>
&lt;/html>

**schlongfingers** · 08-11-2005, 12:52 AM

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.

Code:

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

&lt;?
$q="select distinct category, id from product_cat order by category asc";
$r=mysql_query&#40;$q&#41;;
$c=mysql_num_rows&#40;$r&#41;;
for &#40;$i=0;$i&lt;$c;$i++&#41;&#123;
        $a=mysql_fetch_array&#40;$r&#41;;
        echo '&lt;option value="'.$a&#91;'id'&#93;.'">'.$a&#91;'category'&#93;.'&lt;/option>';
&#125;
?>

&lt;/select>&lt;/td>
&lt;/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, 02:33 PM

Have a look at using the PEAR DB extension as well... saves a lot of time.

**Barely Human** · 09-11-2005, 09: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, 11:05 AM

Change the addition handling part of your code, following in the same structure you are currently using it should change to this:

Code:

if&#40;isset&#40;$_POST&#91;'save'&#93;&#41;&#41;
&#123;
$product = $_POST&#91;'product'&#93;;
$description = $_POST&#91;'discription'&#93;;
$price = $_POST&#91;'price'&#93;;
$techsheet = $_POST&#91;'techsheet'&#93;;
$catId = $_POST&#91;'category'&#93;;

if&#40;!get_magic_quotes_gpc&#40;&#41;&#41;
&#123;
$product = addslashes&#40;$product&#41;;
$description = addslashes&#40;$description&#41;;
$price = addslashes&#40;$price&#41;;
$techsheet = addslashes&#40;$techsheet&#41;;
$catId = addslashes&#40;$catId&#41;;
&#125;
include 'library/config.php';
include 'library/opendb.php';

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

echo "Product '$product' added";
&#125;

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, 01: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, 03: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

Thread: mysql help

Thread Tools

Display

mysql help

Posting Permissions