School Map:  Home / Enroll $ Student Records; Class  
$ News @ LIVE Tech Radio * Support/FAQ | Store | FORUMS

Class / Forums / mysql101 Refresh the current page Week #2 Advanced To-Do Assignment
 

 (#18 2003-08-15 13:24:20) Post Reply

TDavid
php mySQL Perl C/C++
Principal


Enrolled: Mar 2000
Posts: 193
AP: 1

 Your Week #2 Advanced TO-DO assignment should be posted here in this thread only. If you have specific questions about the week 2 course text then please also post them in this thread so that other students can learn from the information and we can collectively build a knowledge base.

Here is the details on what you need to do in this assignment:

While doing the Basic Assignment add a description Field to the Sponsors Table and add a FULLTEXT index for it which includes the url and name Fields. Create an additional Table called 'Hosts' and relate it to the Websites table. Make it a One-To-Many relationship for many possible Websites on each Host server... Or, for an even more advanced challenge, assume that you are using multiple hosts to mirror your websites, and make it a Many-To-Many relationship. Consider the path and sequence to find the hosts of a particular Sponsor.
 

 (#22 2003-08-20 23:00:36) Post Reply

lizabit
Need To Set


Enrolled: Jul 2003
Posts: 17
AP: 1

 Ok Here's mine. (Is anyone else doing this?)

******code to add a new field*****

<?php
//Connect to MySQL server:
$db = mysql_connect("localhost", "xxxxxxx", "xxxxxx")
or die("Could not connect:". mysql_error());

//now connect to database
mysql_select_db("MySponsors", $db)
or die("Could not Connect to database". mysql_error());

//add field to table
$query_addto_table = "ALTER TABLE sponsors ADD COLUMN (s_description VARCHAR(255)
DEFAULT '' NOT NULL,
FULLTEXT KEY s_search(s_name, s_url, s_description))";

//execute query
$result_addto_table = mysql_query($query_addto_table,$db)
or die("Could not add field: ". mysql_error());
print("New field added:");

?>

Although the hosts table works, I don't think I succeeded in getting the schema right.

*****New one to many hosts table*******
***************************************

<?php
//Connect to MySQL server:
$db = mysql_connect("localhost", "xxxxx", "xxxx")
or die("Could not connect:". mysql_error());

//now connect to database
mysql_select_db("MySponsors", $db)
or die("Could not Connect to database". mysql_error());

//create table
$query_create_table = "CREATE TABLE `hosts` (
`h_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`h_url` VARCHAR(255) NOT NULL DEFAULT '',
`h_w_id` MEDIUMINT(8) NOT NULL,
KEY (`h_w_id`)
) TYPE=MyISAM;";

//execute query
$result_create_table =
mysql_query($query_create_table,$db)
or die("Could not create table: ". mysql_error());
print("Table created");
?>

If its ok, I'll try the many to many.
Regards
Liz
 

 (#25 2003-08-28 03:58:04) Post Reply

logspirit
Need To Set


Enrolled: Oct 2001
Posts: 63
AP: 1

 Liz,

As we discussed in the Tuesday Workshop... Don't use more than one AUTO_INCREMENT PRIMARY KEY for a relationship link in Your database. Only one Table should create the AUTO_INCREMENT key. Then use the number it creates for a record to make matching keys to that record in the records in other Tables You want to link it to. PHP provides a MySQL function called: mysql_insert_id() which returns the last integer created in the AUTO_INCREMENT key field when doing INSERT operations. You should get the value immediately after an INSERT query, it will be overwritten on the next one. You can use that integer to create Your matching keys for records in other tables. Of course You can also SELECT the id at a later time, if You have another 'handle' to the record like a search for a Name, or if You list records and create links using the id - and then link new records to the original.

You're doing well, keep going!
logspirit
 

 (#56 2003-10-13 12:37:36) Post Reply

Donnie
Need To Set


Enrolled: Aug 2003
Posts: 41
AP: 1

 Week 2 advanced - 2 files. Worked on local host - no server to upload to.



<?php

// Refer to week #1 to create a database before implementing this code.
// Connect to Your MySQL server (use YOUR parameters):

$db = mysql_connect("localhost","username","password")
or die("Could not connect:". mysql_error());
print("Connected successfully");

// Connect to database:
mysql_select_db("ssMySQLclass",$db)
or die("Could not Connect to database:". mysql_error());
print("Connected to Database");

// Create php variable containing Table Creation SQL Query:
$query_create_table = 'CREATE TABLE `Sponsors` (
`s_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`s_name` VARCHAR(255) NOT NULL DEFAULT "",
`s_url` VARCHAR(255) NOT NULL DEFAULT "",
`s_desc` TEXT NOT NULL DEFAULT "",
`s_staturl` VARCHAR(255) DEFAULT NULL,
`s_email` VARCHAR(255) DEFAULT NULL,
`s_uname` VARCHAR(255) DEFAULT NULL,
`s_pass` VARCHAR(255) DEFAULT NULL,
`s_date` DATE DEFAULT NULL,
`s_status` ENUM("Pending","Active","Inactive","Defunct") NOT NULL DEFAULT "Pending",
FULLTEXT KEY `w_search` (`s_name`,`s_url`,`s_desc`)
) TYPE=MyISAM;';

// Execute Query:
$result_create_table =
mysql_query($query_create_table,$db)
or die("Could not Create Table:". mysql_error());
print("Table Created");
?>



<?php
/* Connect to MySQL server */
$db=mysql_connect("localhost", "username", "password")
or die ("Could not connect:".mysql_error());
print("Connected Successfully");

/* Connect to database */
mysql_select_db("ssMySQLclass",$db)
or die("Could not connect to database:".mysql_error());
print("Connected to Database");

/* Create php vaiable containing Table Creation SQL Query */
/* Relational table where h_ws_id = h_id */
$query_create_table = 'CREATE TABLE `Host_Website` ( `h_ws_id` MEDIUMINT(8) NOT NULL, `ws_w_id` MEDIUMINT(8) NOT NULL, PRIMARY KEY (`h_ws_id`, `ws_w_id`) );';

/* Execute Query */
$result_create_table =
mysql_query($query_create_table, $db)
or die ("Could not create table:".mysql_error());
print("Table Created");

/* Create php vaiable containing Table Creation SQL Query */
/* Create Hosts Table */
$query_create_table2 = 'CREATE TABLE `Hosts` ( `h_name` MEDIUMINT(8) NOT NULL, `h_id` MEDIUMINT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY);';

/* Execute Query */
$result_create_table =
mysql_query($query_create_table2, $db)
or die ("Could not create table:".mysql_error());
print("Table Created");
?>



 

 (#57 2003-10-14 20:05:03) Post Reply

logspirit
Need To Set


Enrolled: Oct 2001
Posts: 63
AP: 1

 Hello Donnie,
Welcome...
Looks OK

logspirit - author of course
 

 

View Previous Thread
Print this page Print This Page

View Next Thread

School Map:  Home / Enroll $ Student Records; Class  
$ News @ LIVE Tech Radio * Support/FAQ | Store

Advertising | Link To Us | Privacy 
Copyright 2000-2002 Script School Productions / KMR Enterprises 
No part of this website may be reproduced, copied and/or distributed in any medium 
without express written permission