Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

entering and extracting data in to mysql table using php

Posted on 2011-03-02
6
Medium Priority
?
334 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

610 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