Page 1 of 1

Tables in MySQL

Posted: Tue Dec 03, 2002 7:40 am
by don_assi
Hi Guys,

I'm a novice at PHP/MySQL and would appreciate any help I can get...

Lets say I have 2 tables:
Table 1: Category

Category | CategoryId
-----------------------
Cakes | 1

Table 2: Items

ItemName | Category
---------------------
Chocolate | ?

My question is if I want to select a category for Table 2 that is in Table 1 how do I do it?

MySQL> INSERT INTO Items SET
-> ItemName = "Chocolate"
-> Category = ?????????
->;
I tried putting the actual word and I tried the assigned CategoryId number '1' but got an error for both...

Is there a command line to insert a field from one table into another?

One other question... I seem to have a problem with the transfer of info from the script to the database - what does it mean when I submit an entry in PHP and MySQL recognises that it is a new entry but adds a blank entry? Has anyone come across that at all? I have not been able to find out what is wrong...

Thanks for your help ...

Don_assi

Posted: Tue Dec 03, 2002 7:52 am
by mydimension
for your items table in the category field, your are gonna want to put the category id defined in the Category table. now, although you didn't say what error you got (which would have been very helpful) i think i can make a guess that is was because you didn't use a comma between your field/value pairs: (note the comma after 'Chocolate')

Code: Select all

INSERT INTO Items SET ItemName='Chocolate',Category=1
now this is where relational databases become, well relational. in your select query:

Code: Select all

SELECT * FROM Category, Items WHERE Items.Category=Category.CategoryId
it is in your select statements that you relate information from one table to information in another table, not your insert queries. those just put information in there for later use.

creating tables

Posted: Tue Dec 03, 2002 8:16 am
by don_assi
Thanks for that.. very quick reply.. I will check it out... when I get to my server (at work at the moment)...
About the second question:
I have attached a couple of codes for files the first one links to the 2nd and uses that as the central file to access the database on MySQL but it only seems to add 'blank' entries... funny thing is... if I add an entry through MysQl directly I can see it through the script.. its like a one way channel?
Can you help?

File 1: addCategory.php

Code: Select all

<? 
require("/home/gassi01/public_www/Cart/Cart.php"); 
Brand(); 
Root(); 
commonHeader("$Company","Add A Category"); 

blueFont("Arial","Type your new category into the box, and then SUBMIT!<br><br>"); 

echo "<TABLE BORDER="0" CELLPADDING="10" CELLSPACING="10"><tr>"; 
echo "<FORM ACTION="./addCategoryResponse.php" METHOD="POST">"; 
echo "<tr><td>"; 
blueFont("Arial","Category"); 
echo "</td><td>"; 
echo "<input type="text" name="Category" size="50">"; 
echo "</td></tr>"; 

echo "<tr><td><INPUT TYPE="submit" NAME="Submit" VALUE="Submit">"; 
echo "</td></tr></TABLE>"; 

adminFooter($Relative); 
?>

File 2: Cart.php

Code: Select all

<? 
/* Replace these variables with information about your company */ 
$Company ="Your Company"; 
$Address1="555 Any St."; 
$Address2="PO Box 3"; 
$City    ="AnyTown"; 
$State   ="OR"; 
$Zip     ="97777"; 
$Phone   ="(777) 555-1212"; 
$Web     ="http://www.yoursite.com/"; 
$Email   ="contact@you.com"; 
$NoShipping = "Shipping Extra";function Brand() &#123; 
global $Company,$Address1,$Address2,$City,$State,$Zip,$Phone,$Web,$Email,$NoShipping; 
&#125; 

/* Replace these variables with information for connecting to your 
database server */ 
$DBHost="hermes"; 
$DBUser="gassi01"; 
$DBPass=""; 
$DBName="gassi01db"; 
function DBInfo() &#123; 
global $DBHost,$DBUser,$DBPass,$DBName; 
&#125; 

/* Replace these variables with the absolute and relative paths to your 
MyCart scripts */ 
$WebRoot="/home/gassi01/public_www/Cart"; 
$Relative="/Cart"; 
$WebHost="http://hermes.dcs.bbk.ac.uk"; 
function Root() &#123; 
global $WebRoot,$Relative,$WebHost; 
&#125; 

function redFont($font,$text) &#123; 
echo "<FONT FACE="$font" COLOR="red">$text</FONT>"; 
&#125; 

function blueFont($font,$text) &#123;echo "<FONT FACE="$font" COLOR="blue">$text</FONT>"; 
&#125; 

function colorFont($color,$font,$text) &#123;echo "<FONT FACE="$font" COLOR="$color">$text</FONT>"; 
&#125; 

function fontFace($font,$text) &#123; 
echo "<FONT FACE="$font">$text</FONT>"; 
&#125; 

function fontSize($size,$color,$font,$text) &#123; 
echo "<FONT FACE="$font" COLOR="$color" SIZE="$size">$text</FONT>"; 
&#125; 

function commonHeader($Company,$title) &#123; 
echo "<HTML><TITLE>$Company Shopping Cart - $title</TITLE><BODY BGCOLOR="#FFFFFF">"; 
&#125; 

function receiptHeader($Company,$title) &#123; 
echo "<HTML><TITLE>$Company Receipt - $title</TITLE><BODY BGCOLOR="#FFFFFF">"; 
&#125; 

function commonFooter($Relative,$UID) &#123; 
echo "<center><br><br><hr width="70%"><br>"; 
echo "<b><a href="$Relative/checkout.php?UID=$UID">Go To The Check Out Stand</a></b><br><br>"; 
echo "<b><a href="$Relative/viewCart.php?UID=$UID">View The Contents Of Your Cart</a></b><br><br>"; 
fontFace("Arial","<a href="$Relative/index.php?UID=$UID">Our Catalog</a>"); 
fontFace("Arial"," | <a href="/">Home</a>"); 
echo "<br><br></center></BODY></HTML>"; 
&#125; 

function adminFooter($Relative) &#123; 
echo "<center><br><br><hr width="70%"><br>"; 
fontFace("Arial"," | <a href="$Relative">Our Catalog</a>"); 
fontFace("Arial"," | <a href="$Relative/admin/">Shopping Cart Admin</a>"); 
fontFace("Arial"," | <a href="http://modems.rosenet.net/mysql/">MySQL Utilities</a>"); 
fontFace("Arial"," | <a href="http://www.rosenet.net/">Rosenet</a>"); 
echo "<br><br></center></BODY></HTML>"; 
&#125; 

function receiptFooter($Company) &#123; 
echo "<br><br><center>"; 
fontFace("Arial","Thank you for ordering from <b>$Company</b>"); 
echo "</center><br><br>"; 
&#125; 

?>
Can you see where the problem lies?

Thanks for your help...

don_assi