Solved

entering and extracting data in to mysql table using php

Posted on 2011-03-02
6
329 Views
Last Modified: 2012-05-11
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'");

0
Comment
Question by:ikon32
  • 3
  • 3
6 Comments
 
LVL 7

Expert Comment

by:StraySod
ID: 35027023
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
 

Author Comment

by:ikon32
ID: 35029614
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
 
LVL 7

Accepted Solution

by:
StraySod earned 500 total points
ID: 35030456
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Closing Comment

by:ikon32
ID: 35030868
Thank you for your advice. If you dont mind can you tell me some more about the index...
0
 
LVL 7

Expert Comment

by:StraySod
ID: 35031272
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
 

Author Comment

by:ikon32
ID: 35031861
thank you for the advice and comments.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question