Solved

entering and extracting data in to mysql table using php

Posted on 2011-03-02
6
327 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

864 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now