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

Class / Forums / mysql101 Refresh the current page Week #4 Basic To-Do Assignment
 

 (#26 2003-08-29 14:02:28) Post Reply

TDavid
php mySQL Perl C/C++
Principal


Enrolled: Mar 2000
Posts: 193
AP: 1

 Your Week #4 Basic TO-DO assignment should be posted here in this thread only. If you have specific questions about the week 4 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:

Create interfaces to Add Relationships, Delete Relationships and Display the information in Your database in a useful way. Share the source code you are using in the body of your post.
 

 (#47 2003-10-06 22:03:32) Post Reply

lizabit
Need To Set


Enrolled: Jul 2003
Posts: 17
AP: 1

 Hi Logspirit,
I'm still here however, work is taking up alot of time that I really need to spend working this stuff out. However, I haven't given up, just going very very slow.
I was able to create the relations table ok but am having trouble with the script to delete relationships. It seems to not like the 'AND' in the $query_delete or something. I don't know but I just can't get it to work. The GET link highlights all the links when I click on one link. In the address bar the ?del_w doesn't appear.
CODE:
*********************************************
<?php
// Connect to Your MySQL server (use YOUR parameters):
// Note: @ suppresses standard error reporting
$db = @ mysql_connect("xxxxxx","xxxx", "xxxx")
or die("Could not connect:". mysql_error());
// Connect to database:
@ mysql_select_db("MySponsors",$db)
or die("Could not Connect to database:". mysql_error());

$query_delete = "DELETE FROM website_sponsor
WHERE ws_w_id = '".$_GET['del_w']."'
AND ws_s_id = '".$_GET['del_s']."'
";
// Send query, check affected rows:
if(@mysql_query($query_delete,$db) && @mysql_affected_rows() == 1){
// query sent, set display status:
$status = '<font color="#009900">Relationship Deleted</font><br>';
} else { // Problem sending query, set display status:
$status = '<font color="#663399">Problem Deleting Relationship</font><br>';
}//End if(@ mysql_query


$query = "SELECT `ws_w_id`,`ws_s_id`,`w_name`,`s_name`
FROM `sponsors`,`websites`,`website_sponsor`
WHERE `s_id`= `ws_s_id` AND `w_id` = `ws_w_id`
";
?>
<html>
<body>
<table>
<tr>
<td>
<?php
$result = mysql_query($query, $db);
// loop through data result
while($row = mysql_fetch_array($result)){
// create delete links to send id's through GET:
echo '<a href='.$_SERVER['PHP_SELF'].'?
del_w='.$row['ws_w_id'].'&del_s='.$row['ws_s_id'].'>'.$row['w_name'].
' · '.$row['s_name'].'</a><br>';
}
echo $status;
?>
</td>
</tr>
</table>
</body>
</html>
**************************
Any help?
Regards
Liz
 

 (#48 2003-10-07 03:33:59) Post Reply

logspirit
Need To Set


Enrolled: Oct 2001
Posts: 63
AP: 1

 Liz,
Glad You are still there...

I think the problem is that You are not controlling the sequence properly by checking to see if the $_GET variables have been sent yet. The teaching script has:

// Check to see if a delete link has been
// clicked, sending variables by GET:
if(isset($_GET['del_w']) && isset($_GET['del_s'])){
... do delete processing ...
}

Your script goes right into the delete queries without any supplied variables, so then the rest of the script displays problems.

See if adding the if() conditional helps.

logspirit
 

 (#49 2003-10-07 18:37:06) Post Reply

lizabit
Need To Set


Enrolled: Jul 2003
Posts: 17
AP: 1

 Hi Logspirit,
Gee, thanks for replying so quick! Much appreciate it as always.
Anyway, I tried the conditional if as you suggested but it still didn't work. I then cut and pasted your code direct into my editor and I had the same problems using the course example. Is it some fundamental setup problem on my system do you think?
(Its starting to warm up down here. And it looks like the drought broke finally. Plenty of rain although the dams are still down 50%. Oh, well, more water restrictions...)
Regards
Liz
 

 (#50 2003-10-07 20:55:51) Post Reply

lizabit
Need To Set


Enrolled: Jul 2003
Posts: 17
AP: 1

 Hi Logspirit,
Its me again. I found the problem. It nearly drove me to distraction but I found it. It was in the line
*********************************************
echo '<a href='.$_SERVER['PHP_SELF'].'?del_w='.$row['ws_w_id'].'&del_s='.$row['ws_s_id'].'>'.$row['w_name'].
' · '.$row['s_name'].'</a><br>';
**********************************************
I took the line break after the question mark out so it all sits on one line and now it works ok. It seems to at this stage anyway.
Programming can be soooo frustrating.
Regards
Liz
 

 (#51 2003-10-08 00:57:01) Post Reply

logspirit
Need To Set


Enrolled: Oct 2001
Posts: 63
AP: 1

 Liz,
Right... sometimes the line breaks created by
simple copies of html rendered pages won't work when strings are broken into two lines of code without proper cancantenation (programmatical string joins).
That's why it's always best to write the code in by hand or get exact copies of the scripts.
(like in the zip files available in week 5)

Glad You are doing well.

logspirit
 

 (#52 2003-10-08 21:26:00) Post Reply

lizabit
Need To Set


Enrolled: Jul 2003
Posts: 17
AP: 1

 /****************************************
**Basic assignment
*****************************************/
<?php
/** functions **
*************************/

//sel() selects list option

function sel($row_id,$frm) {
if($frm == 'w') { //see which form
if($row_id ==$_POST['website_list']) { //see if id matches posted value
echo 'selected';
}
}else if($frm =='s') {
if($row_id ==$_POST['sponsor_list']) { //see which form
echo 'selected'; //see if id matches posted value
}
}
}
function report_status($status) {
switch($status) {
case 'qry_ins' :
echo '<font color=red>Error Inserting into Database';
break;
case 'qry_ck' :
echo '<font color=red>Error Checking for Relationship';
break;
case 'rel_ex' :
echo '<font color=red>This Relationship Already Exists';
break;
case 'rel_add' :
echo '<font color=blue>Added Relationship to Database';
break;
case 'empty_w' :
echo '<font color=red>No website Selected';
break;
case '_empty_s' :
echo '<font color=red>No sponsor Selected';
break;
case 'empty_w_empty_s' :
echo '<font color=red>No website or sponsor Selected';
break;
} //end switch
if(isset($status)) {
echo '<br><br></font>';
}
} //end function

/** logic sequence **
***********************/

//connect to Mysql server:
$db = @ mysql_connect("localhost","lgrace","nicho6uz")
or die("Could not connect:". mysql_error());
// echo "Connected Successfully to MySQL server.\n\r";
//Connect to db
@mysql_select_db("MySponsors", $db)
or die("Could not connect to db:". mysql_error());
// echo "Connected to Database.\n\r\n\r";

//Determine if form has been submitted:
if(isset($_POST['Submit_WS'])) {

//check if values exist from both lists:
if(!empty($_POST['website_list']) && !empty($_POST['sponsor_list'])) {
//write query, determine if duplicating relationship:
$query_check = "SELECT * FROM website_sponsor
WHERE ws_w_id = '".$_POST['website_list']."'
AND ws_s_id = '".$_POST['sponsor_list']."'
;";
//Do duplicate query:
if($result = @mysql_query($query_check, $db)) {
//if number of rows is one or more...
//Note that mysql_num_rows is used for SELECT queries
if(mysql_num_rows($result) >=1) {
//This relationship already exists in the database:
$status = 'rel_ex';
} else { //OK to insert data...
//write insert query
$query_insert = "INSERT INTO `website_sponsor`
Set ws_w_id = '".$_POST['website_list']."',
ws_s_id = '".$_POST['sponsor_list']."'
";
// Do query, check if one row affected:
//Note that mysql_affected rows is used for INSERT queries
if($result = @mysql_query($query_insert,$db)
&& @mysql_affected_rows() == 1) {
//Report Success:
$status = 'rel_add';
} else {
//error Inserting result into database
$status = 'qry_ins';
} //end Do insert query/else error
} // end else OK to insert data
} else {//Duplicate query
// Error in getting result from query
$status = 'qry_ck';
} //end else duplicate query
} else { //End check for both list values
// One or both lists NOT selected:
if(empty($_POST['website_list'])) {
$status = 'empty_w';
}
if(empty($_POST['sponsor_list'])) {
// Note use of the .= to add string to $status variable
$status .='_empty_s';
}
} //end else check for both list values
} // end if form submitted
/********************************************************************
//Start Delete Stuff
********************************************************************/
if(isset($_GET['del_w']) && isset($_GET['del_s'])){
$query_delete = "DELETE FROM website_sponsor
WHERE ws_w_id = '".$_GET['del_w']."'
AND ws_s_id = '".$_GET['del_s']."'
";
// Send query, check affected rows:
if(@mysql_query($query_delete,$db) && @mysql_affected_rows() == 1){
// query sent, set display status:
$rel_delete = '<font color="#ff0000">Relationship Deleted</font><br>';
} else { // Problem sending query, set display status:
$rel_delete = '<font color="#663399">Problem Deleting Relationship</font><br>';
}//End if(@ mysql_query
}
// Check if link has sent sorting choice by GET:
// Note ternary conditional operator: A?B:C If A true do B else do C
isset($_GET['sort']) ? $sort = $_GET['sort'] : $sort = 'w';
// set join queries depending on sorting choice:
if($sort == 'w'){
$query = "SELECT `ws_w_id`,`ws_s_id`,`w_name`,`s_name`
FROM `sponsors`, `websites`, `website_sponsor`
WHERE `s_id` = `ws_s_id` AND `w_id` = `ws_w_id`
ORDER BY `w_name`,`s_name`
";
}else if($sort == 's'){
$query = "SELECT `ws_w_id`,`ws_s_id`,`w_name`,`s_name`
FROM `sponsors`, `websites`, `website_sponsor`
WHERE `s_id` = `ws_s_id`
AND `w_id` = `ws_w_id`
ORDER BY `s_name`,`w_name`
";
}

?>
<html>
<head>
<title>website sponsor Table Form</title>
</head>
<body>
<table bordercolor="663399" border="2" bgcolor="#003366" cellpadding="3" cellspacing="4">
<tr>
<td align="center" width="50%" height="19" bgcolor="#B552AD"><strong>Add Website Sponsor relationship</strong></td>
<td align="center" width="50%" height="19" bgcolor="#B552AD"><strong>Delete Relationships</strong></td>
</tr>
<tr>
<td width="60%" height="180" rowspan="2">
<form name="website_sponsor" method="post"
action="<?php echo $_SERVER['PHP_SELF']; ?>">
<table width ="300" align="center"
cellpadding="2" cellspacing="1" bgcolor="#336699" rules="none">
<tr align="center" valign="Middle">
<td width="240"><strong>Websites</strong></td>
<td width="240"><strong>Sponsors</strong></td>
</tr>
<tr align="center" valign="middle">
<td>
<select name="website_list" size="10" id="website_list" style="background-color: #336699">
<?php
//create websites query:
$query_websites = "SELECT w_id, w_name FROM `websites`;";
//Do query obtain result:
$result = mysql_query($query_websites, $db);
// Get and display data one row at a time
while($row = mysql_fetch_array($result)) {
// Create option, use sel() to show if selected:
echo '<option value="'.$row['w_id'].'"'; sel($row['w_id'],'w');
echo '>'.$row['w_name'].'</option>';
}
?>
</select>
</td>
<td>
<select name="sponsor_list" size="10" id="sponsor_list" style="background-color: #336699">

<?php
//create websites query:
$query_sponsors = "SELECT s_id, s_name FROM `sponsors`;";
//Do query obtain result:
$result = mysql_query($query_sponsors, $db);
// Get and display data one row at a time
while($row = mysql_fetch_array($result)) {
// Create option, use sel() to show if selected:
echo '<option value="'.$row['s_id'].'"'; sel($row['s_id'],'s');
echo '>'.$row['s_name'].'</option>';
}
?>
</select>
</td>
</tr>
<tr align="center" valign="middle">
<td colspan="2">
<?php
report_status($status);
?>
<input type="submit" name="Submit_WS"
value="« Add New Relationship »">
</td>
</tr>
</table>
</form>
</td>
<td width="40%" height="160" bordercolor="white" bgcolor="#008080">
<?php
$result = mysql_query($query, $db);
// loop through data result
while($row = mysql_fetch_array($result)){
// create delete links to send id's through GET:
echo '<a href='.$_SERVER['PHP_SELF'].'?del_w='.$row['ws_w_id'].'&del_s='.$row['ws_s_id'].'>'.$row['w_name'].
' · '.$row['s_name'].'</a><br>';
}
?>
<?php echo $rel_delete; ?>
</td>
</tr>
<tr>
<td width="50%" bgcolor="#008080">

Sort By:    
<!-- Create links to sort data -->
<a href="<?php echo ''.$_SERVER['PHP_SELF'].'?sort=w' ?>">websites</a>
   
<a href="<?php echo ''.$_SERVER['PHP_SELF'].'?sort=s' ?>">sponsors</a>
</td>
</tr>
</table>

</body>
</html>
Regards
Liz
 

 (#53 2003-10-08 21:26:04) Post Reply

lizabit
Need To Set


Enrolled: Jul 2003
Posts: 17
AP: 1

 /****************************************
**Basic assignment
*****************************************/
<?php
/** functions **
*************************/

//sel() selects list option

function sel($row_id,$frm) {
if($frm == 'w') { //see which form
if($row_id ==$_POST['website_list']) { //see if id matches posted value
echo 'selected';
}
}else if($frm =='s') {
if($row_id ==$_POST['sponsor_list']) { //see which form
echo 'selected'; //see if id matches posted value
}
}
}
function report_status($status) {
switch($status) {
case 'qry_ins' :
echo '<font color=red>Error Inserting into Database';
break;
case 'qry_ck' :
echo '<font color=red>Error Checking for Relationship';
break;
case 'rel_ex' :
echo '<font color=red>This Relationship Already Exists';
break;
case 'rel_add' :
echo '<font color=blue>Added Relationship to Database';
break;
case 'empty_w' :
echo '<font color=red>No website Selected';
break;
case '_empty_s' :
echo '<font color=red>No sponsor Selected';
break;
case 'empty_w_empty_s' :
echo '<font color=red>No website or sponsor Selected';
break;
} //end switch
if(isset($status)) {
echo '<br><br></font>';
}
} //end function

/** logic sequence **
***********************/

//connect to Mysql server:
$db = @ mysql_connect("localhost","lgrace","nicho6uz")
or die("Could not connect:". mysql_error());
// echo "Connected Successfully to MySQL server.\n\r";
//Connect to db
@mysql_select_db("MySponsors", $db)
or die("Could not connect to db:". mysql_error());
// echo "Connected to Database.\n\r\n\r";

//Determine if form has been submitted:
if(isset($_POST['Submit_WS'])) {

//check if values exist from both lists:
if(!empty($_POST['website_list']) && !empty($_POST['sponsor_list'])) {
//write query, determine if duplicating relationship:
$query_check = "SELECT * FROM website_sponsor
WHERE ws_w_id = '".$_POST['website_list']."'
AND ws_s_id = '".$_POST['sponsor_list']."'
;";
//Do duplicate query:
if($result = @mysql_query($query_check, $db)) {
//if number of rows is one or more...
//Note that mysql_num_rows is used for SELECT queries
if(mysql_num_rows($result) >=1) {
//This relationship already exists in the database:
$status = 'rel_ex';
} else { //OK to insert data...
//write insert query
$query_insert = "INSERT INTO `website_sponsor`
Set ws_w_id = '".$_POST['website_list']."',
ws_s_id = '".$_POST['sponsor_list']."'
";
// Do query, check if one row affected:
//Note that mysql_affected rows is used for INSERT queries
if($result = @mysql_query($query_insert,$db)
&& @mysql_affected_rows() == 1) {
//Report Success:
$status = 'rel_add';
} else {
//error Inserting result into database
$status = 'qry_ins';
} //end Do insert query/else error
} // end else OK to insert data
} else {//Duplicate query
// Error in getting result from query
$status = 'qry_ck';
} //end else duplicate query
} else { //End check for both list values
// One or both lists NOT selected:
if(empty($_POST['website_list'])) {
$status = 'empty_w';
}
if(empty($_POST['sponsor_list'])) {
// Note use of the .= to add string to $status variable
$status .='_empty_s';
}
} //end else check for both list values
} // end if form submitted
/********************************************************************
//Start Delete Stuff
********************************************************************/
if(isset($_GET['del_w']) && isset($_GET['del_s'])){
$query_delete = "DELETE FROM website_sponsor
WHERE ws_w_id = '".$_GET['del_w']."'
AND ws_s_id = '".$_GET['del_s']."'
";
// Send query, check affected rows:
if(@mysql_query($query_delete,$db) && @mysql_affected_rows() == 1){
// query sent, set display status:
$rel_delete = '<font color="#ff0000">Relationship Deleted</font><br>';
} else { // Problem sending query, set display status:
$rel_delete = '<font color="#663399">Problem Deleting Relationship</font><br>';
}//End if(@ mysql_query
}
// Check if link has sent sorting choice by GET:
// Note ternary conditional operator: A?B:C If A true do B else do C
isset($_GET['sort']) ? $sort = $_GET['sort'] : $sort = 'w';
// set join queries depending on sorting choice:
if($sort == 'w'){
$query = "SELECT `ws_w_id`,`ws_s_id`,`w_name`,`s_name`
FROM `sponsors`, `websites`, `website_sponsor`
WHERE `s_id` = `ws_s_id` AND `w_id` = `ws_w_id`
ORDER BY `w_name`,`s_name`
";
}else if($sort == 's'){
$query = "SELECT `ws_w_id`,`ws_s_id`,`w_name`,`s_name`
FROM `sponsors`, `websites`, `website_sponsor`
WHERE `s_id` = `ws_s_id`
AND `w_id` = `ws_w_id`
ORDER BY `s_name`,`w_name`
";
}

?>
<html>
<head>
<title>website sponsor Table Form</title>
</head>
<body>
<table bordercolor="663399" border="2" bgcolor="#003366" cellpadding="3" cellspacing="4">
<tr>
<td align="center" width="50%" height="19" bgcolor="#B552AD"><strong>Add Website Sponsor relationship</strong></td>
<td align="center" width="50%" height="19" bgcolor="#B552AD"><strong>Delete Relationships</strong></td>
</tr>
<tr>
<td width="60%" height="180" rowspan="2">
<form name="website_sponsor" method="post"
action="<?php echo $_SERVER['PHP_SELF']; ?>">
<table width ="300" align="center"
cellpadding="2" cellspacing="1" bgcolor="#336699" rules="none">
<tr align="center" valign="Middle">
<td width="240"><strong>Websites</strong></td>
<td width="240"><strong>Sponsors</strong></td>
</tr>
<tr align="center" valign="middle">
<td>
<select name="website_list" size="10" id="website_list" style="background-color: #336699">
<?php
//create websites query:
$query_websites = "SELECT w_id, w_name FROM `websites`;";
//Do query obtain result:
$result = mysql_query($query_websites, $db);
// Get and display data one row at a time
while($row = mysql_fetch_array($result)) {
// Create option, use sel() to show if selected:
echo '<option value="'.$row['w_id'].'"'; sel($row['w_id'],'w');
echo '>'.$row['w_name'].'</option>';
}
?>
</select>
</td>
<td>
<select name="sponsor_list" size="10" id="sponsor_list" style="background-color: #336699">

<?php
//create websites query:
$query_sponsors = "SELECT s_id, s_name FROM `sponsors`;";
//Do query obtain result:
$result = mysql_query($query_sponsors, $db);
// Get and display data one row at a time
while($row = mysql_fetch_array($result)) {
// Create option, use sel() to show if selected:
echo '<option value="'.$row['s_id'].'"'; sel($row['s_id'],'s');
echo '>'.$row['s_name'].'</option>';
}
?>
</select>
</td>
</tr>
<tr align="center" valign="middle">
<td colspan="2">
<?php
report_status($status);
?>
<input type="submit" name="Submit_WS"
value="« Add New Relationship »">
</td>
</tr>
</table>
</form>
</td>
<td width="40%" height="160" bordercolor="white" bgcolor="#008080">
<?php
$result = mysql_query($query, $db);
// loop through data result
while($row = mysql_fetch_array($result)){
// create delete links to send id's through GET:
echo '<a href='.$_SERVER['PHP_SELF'].'?del_w='.$row['ws_w_id'].'&del_s='.$row['ws_s_id'].'>'.$row['w_name'].
' · '.$row['s_name'].'</a><br>';
}
?>
<?php echo $rel_delete; ?>
</td>
</tr>
<tr>
<td width="50%" bgcolor="#008080">

Sort By:    
<!-- Create links to sort data -->
<a href="<?php echo ''.$_SERVER['PHP_SELF'].'?sort=w' ?>">websites</a>
   
<a href="<?php echo ''.$_SERVER['PHP_SELF'].'?sort=s' ?>">sponsors</a>
</td>
</tr>
</table>

</body>
</html>
Regards
Liz
 

 (#54 2003-10-10 01:51:58) Post Reply

logspirit
Need To Set


Enrolled: Oct 2001
Posts: 63
AP: 1

 Liz,
I see that it may still be a problem to simply take even the zip files and just use them as scripts if strings are broken into two lines...
I hope everyone is now aware of this and can debug their code properly...
We all learn from each other...
In future courses all lines that might cause problems with strings, no matter how long, will be on 'one' line in the zip files, or will be broken into proper concatenations.

Liz, I hope you are getting some real understanding from this course and will be able to write Your own programs. Hope You'll stay cool through this Summer... I've seen some pictures of some beautiful cold streams in wonderful forested places in south coastal Australia... just sit by one with a laptop and code on... oh, and please keep an eye peeled for those salty Crocks... I don't want to loose my hardest working student!

From a swamp deep in Florida,
where the Alligators rule...
logspirit
 

 

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