entering and extracting data in to mysql table using php

hi experts, I need an example code in order to enter category_id and product_id into the third table below:

// first table
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
mysql_query("CREATE TABLE product_tbl (
            p_id int(8) NOT NULL,
            p_name varchar(50) NOT NULL,
            p_color varchar(50) NOT NULL,
            p_price int(4) NOT NULL,
            p_caption varchar(255) NOT NULL,
            p_supplier varchar(50) NOT NULL,
            PRIMARY KEY(p_id),
            UNIQUE KEY (p_id))");
mysql_query("SET NAMES 'utf8'");

//  second table
mysql_query("CREATE TABLE category_tbl (c_id int(4) NOT NULL auto_increment,
category varchar(50) NOT NULL,
PRIMARY KEY(c_id),
UNIQUE KEY (category))");
mysql_query("SET NAMES 'utf8'");


//   third table -- here I have the p_id and the category_name but not the category_id.
//   how do I look in to the second table get the category_id and enter it in to this table?
//  And then how do I  query for all products that are bind with a category_name if I dont have
//  the category_id

mysql_query("CREATE TABLE product_category (
            c_id int(4) PRIMARY KEY,
            p_id int(8) PRIMARY KEY)");
mysql_query("SET NAMES 'utf8'");

ikon32Asked:
Who is Participating?
 
StraySodCommented:
1. to retrieve the category_id regarding to it's name try the following statement:

$res = mysql_query("select c_id from category_tbl where category_name = '$cat_name'");

note: you should build an index on the column category_name, you can use phpMyAdmin

2. pt, ct and pc are aliases used to make the statement more transparent. It's not necessary to use it, it could even look like this:

"select product_tbl.* from category_tbl inner join product_category on product_category.c_id = category_tbl.c_id inner join product_tbl on product_tbl.p_id = product_category.p_id where category_tbl.c_id = $c_id"

the result will be the same, the statement in this form is a bit more complicated. When using joins on tables containing the same column names, they must be distinguished by the table name OR an alias of the table followed by a dot and column name.
0
 
StraySodCommented:
Hi,

I don't understand your question. In the product_category table I can see both, c_id, p_id and you write there's no any c_id only c_name.

for inserting a row in your product_category table you can simply use a INSERT statement:

mysql_query("insert into product_category (c_id, p_id) values ($c_id, $p_id)");

in order to retrieve all the products of a certain category you need to use something like this:

$res = mysql_query("select pt.* from category_tbl ct inner join product_category pc on pc.c_id = ct.c_id inner join product_tbl pt on pt.p_id = pc.p_id where ct.c_id = $c_id");

I assume one product can belong to more categories since you are using 3 tables.

If this wasn't what you are looking for, please be more specific and provide more details. Is it necessary to create tables using php code? As this is a one time action I'd recommend to use some MySQL client application, phpMyAdmin is mostly available.
0
 
ikon32Author Commented:
Thank you for your comment I will try to explain that the best I can please look code below:

// my variables as I get them from the form
 $prod_id=$_POST['prod_id'];  
$prod_name=$_POST['prod_name'];  
$prod_category=$_POST['prod_category'];  
$prod_color=$_POST['prod_color'];  
$prod_price=$_POST['prod_price'];  
$prod_caption=$_POST['prod_caption'];  
$prod_supplier=$_POST['prod_supplier'];

// on the table I have the category_id and category_name
category_tbl  
c_id,  category_name

// my first problem...
// I have the category_name variable from the form
// and need to find the category_id from the table category_tbl (as above)
// how do i find the c_id if i only have the category_name?

//  the second  problem you just provided the answer but I am a bit confused

$res = mysql_query("select pt.* from category_tbl ct inner join product_category pc on pc.c_id = ct.c_id inner join product_tbl pt on pt.p_id = pc.p_id where ct.c_id = $c_id");

//sorry but I have to ask this even if it seems self explanatory
// select pt.* from category_tbl -- what is 'pt'
//  and same for  'ct'  'pc'  

// you are correct about the third table. A product can belong to more than 1 category and 1 category
  can belong to more than 1 product, also that way I can have the category edited and keep the same
category_id.

Thanks so much for your advice.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
ikon32Author Commented:
Thank you for your advice. If you dont mind can you tell me some more about the index...
0
 
StraySodCommented:
you're welcome.

the best way to find out what index is and how it works is to check the official documentation:
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
it's worth reading.

it's a topic which can't be explained in a forum thread. Shortly: index is build upon one or more columns to boost the searching of certain column values. There are several types of indexes (see the documentation).
In your case without an index on category_name the DB server would need to search for the value by visiting every record until it finds the match. With an index it will be able to find it without visiting every record. Basically without index you need N visits to find the match with an index only log(N) visits are necessary, that's a remarkable difference specialy on big tables.
This is a complex topic, so the above is really only a basic explanation.

to build an index use the create index syntax:
http://dev.mysql.com/doc/refman/5.0/en/create-index.html

or do it in phpMyAdmin: in table structure you should see the Index section.
0
 
ikon32Author Commented:
thank you for the advice and comments.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.