Solved

entering and extracting data in to mysql table using php

Posted on 2011-03-02
6
331 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
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 video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

695 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