Page 1 of 1

Newbie In Need of Assistance - PHP & SQL

Posted: Wed Mar 16, 2011 11:20 am
by aal9_programmer
Hey all! I'm recently new to programming and am working on a small basic personal timetable project using PHP and SQL. I have a web-page where a user wants to update data in one of the database tables. I've been through sources online and my 'php for dummies' book(!) and i'm still unable to solve my problem. I'll try to post my code below and hope someone will be able to point out what i'm doing wrong:

Code: Select all

<?php
session_start();
$hostname_logon = "***.*.*.*" ;   
$database_logon = "aal9" ;  
$username_logon = "root" ;  
$password_logon = "" ;

$coursenametext = '';

$connections = mysql_connect($hostname_logon, $username_logon, $password_logon) or die ( "Unable to connect to the database" );

mysql_select_db($database_logon) or die ( "Unable to select database!" );
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Edit Courses</title>
<link href="Aaron.css" rel="stylesheet" type="text/css" />
</head>
<div class="container">
<h1><a href="http://www.bbc.co.uk"><img src="logo.gif" alt="Logo" width="224" height="62" id="ol_logo" align="left"/></a>Update Courses</h1>
</div>
<div class="sidebar1">
    <ul class="nav">
      <li><a href="homepage.php">Homepage</a></li>
      <li><a href="modules.php">My Modules</a></li>
      <li><a href="update.php">Update Database</a></li>
      <li><a href="googles1.php">Sem 1 Google</a></li>
      <li><a href="timetable2.php">Sem 2 Timetable</a></li>
      <li><a href="googles2.php">Sem 2 Google</a></li>
      <li><a href="logout.php">Logout</a></li>
    </ul>
    <p><a href="javascript:window.print()">Print This Page</a></p>
    <p> Lorem ipsum dolor sit amet, consectetur adipiscing elit. Integer nec odio. Praesent libero. Sed cursus ante dapibus diam. Sed nisi. Nulla quis sem at nibh elementum imperdiet. Duis sagittis ipsum. Praesent mauris. Fusce nec tellus sed augue semper porta. Mauris massa. Vestibulum lacinia arcu eget nulla. </p>
    <p>Welcome To Timetable Management. Please Find All Necessary Links Above.</p>
</div>
<div class="content">
<form action="editcourses.php" method="post">
<table border="1" cellspacing="1" align="center" bordercolor="#FFFFFF">
<tr>
<td>Course Name</td>
<td>Edit</td>
<td>Submit</td>
</tr>
<?php
$query = "SELECT * FROM Courses ";
$result = mysql_query ($query);
if(mysql_num_rows($result)>0){
		while($row = mysql_fetch_array($result)) {
		echo('<tr>' .
		'<td>' . $row['CourseName'] . '</td>' .
		'<td><textarea name="coursenametext" rows="1"></textarea>' . '</td>' .
		'<td>' . '<input type="Submit" name="Submit" value="Submit">' . '</td>' .
		'</tr>');
	}
}
$query1 = "UPDATE courses SET courseName = $coursenametext";
$result1 = mysql_query ($query1); 
  
?>
</table>    
</form>
<br>
</div>
<div class="footer">
<p><a href="mailto:********" class="Email">Click Here To Email For Help With Faults</a></p>
</div>
</div>
</body>
</html>
hope to hear some feedback soon, many thanks Aaron :)

Re: Newbie In Need of Assistance - PHP & SQL

Posted: Wed Mar 16, 2011 11:33 am
by Mini
Hey,
It looks like your trying to update the database with a empty variable.

For post variables use $_POST.

$query1 = "UPDATE courses SET courseName = $coursenametext";

->

$query1 = "UPDATE courses SET courseName = '".$_POST['coursenametext']."'";

Hope that helps :).

Re: Newbie In Need of Assistance - PHP & SQL

Posted: Wed Mar 16, 2011 12:05 pm
by aal9_programmer
thanks for your reply Mini, I made the changes but now have an error:

Notice: Undefined index: coursenametext in C:\wamp\www\Timetable\editcourses.php on line 158

i've defined coursenametext at the top of my webpage so don't know what else to do?

Re: Newbie In Need of Assistance - PHP & SQL

Posted: Wed Mar 16, 2011 12:30 pm
by aal9_programmer
I managed to hide the undefined index error but now when I hit the submit button in the table not only does it not update the data in the database, it sets all the courseName's to empty strings in the database :? any advice would be great :)

Re: Newbie In Need of Assistance - PHP & SQL

Posted: Wed Mar 16, 2011 12:56 pm
by Mini
If you want to send data using a form like:
<form method="post"><textarea name="mytxt"></textarea><input type="submit" name="submit" value="submit"/><form>
you get the values of the form using $_POST['html_name_field'].

If you visit the page without submitting the form the $_POST values are undifined. And you'll get that notise :P.

So you have to check if the form is submitted.
For example:

Code: Select all

if(isset($_POST['submit']))
{
  //your sql to update the fields
}
And yes it will update all the fields because your saying: UPDATE courses SET courseName = $coursenametext. That will update all the rows in the table. You have to use something like: UPDATE table SET x = 'y' WHERE id = '$id' to only update the rows with the given id. Therefor you will need to send the id or name or whatever unique field you have in the table with the form. You can use a hidden input for it :).

Re: Newbie In Need of Assistance - PHP & SQL

Posted: Wed Mar 16, 2011 3:28 pm
by aal9_programmer
thank you so much for your help so far mini! I am still having trouble and perhaps I am misunderstanding something. I will try to make my problem clearer. So in my 'Courses' table I have 2 fields, ID and courseName. I have 6 entries in the table. In the webpage, I have a table with the headings CourseName (displaying the current courseNames in the database) , edit (displaying a textarea for the user to edit the courseName) and submit, where I have a submit button for each row. All I want to do is for the user to write in the textareas, hit a submit button and it updates the database with whatever the input was and updates the webpage.

At the top of my webpage I have the following:

Code: Select all

<?php
session_start();
$hostname_logon = "***.*.*.*" ;   
$database_logon = "aal9" ;  
$username_logon = "root" ;  
$password_logon = "" ;

$coursenametext = '';
$id = '';
//error_reporting (E_ALL ^ E_NOTICE);

$connections = mysql_connect($hostname_logon, $username_logon, $password_logon) or die ( "Unable to connect to the database" );
mysql_select_db($database_logon) or die ( "Unable to select database!" );
?>
and then the PHP I'm using for my table is now:

Code: Select all

<?php
$query = "SELECT * FROM Courses ";
$result = mysql_query ($query);
if(mysql_num_rows($result)>0){
		while($row = mysql_fetch_array($result)) {
		echo('<tr>' .
		'<td>' . $row['CourseName'] . '</td>' .
		'<td><textarea name="coursenametext" rows="1"></textarea>' . '</td>' .
		'<td>' . '<input type="Submit" name="Submit" value="Submit">' . '</td>' .
		'</tr>');
	}
}
if(isset($_POST['coursenametext']))
{
$query1 = "UPDATE courses SET courseName = '".$_POST['coursenametext']."' WHERE ID = '$id'";
$result1 = mysql_query ($query1);
}
?>
currently, the webpage loads, but none of the textareas are updating the database. I should also add I am coding in Dreamweaver CS5. If someone could point out definitively where I've gone wrong and provide me with the code I need, I would be ever so grateful. Sorry for being such a pain! Thanks in advance, Aaron

Re: Newbie In Need of Assistance - PHP & SQL

Posted: Wed Mar 16, 2011 4:08 pm
by Mini
Add a hidden html field with the id. Then use that id to update the correct row in the database. ;)
Like this:

Code: Select all

<?php
$query = "SELECT * FROM Courses ";
$result = mysql_query ($query);
if(mysql_num_rows($result)>0){
                while($row = mysql_fetch_array($result)) {
                echo('<tr>' .
                '<td>' . $row['CourseName'] . '</td>' .
                '<td><input type="hidden" name="id" value="' . $row['ID'] . '"/><textarea name="coursenametext" rows="1"></textarea>' . '</td>' .
                '<td>' . '<input type="Submit" name="Submit" value="Submit">' . '</td>' .
                '</tr>');
        }
}
if(isset($_POST['coursenametext']))
{
$query1 = "UPDATE courses SET courseName = '".$_POST['coursenametext']."' WHERE ID = '".$_POST['id']."'";
$result1 = mysql_query ($query1);
}
?>

Re: Newbie In Need of Assistance - PHP & SQL

Posted: Wed Mar 16, 2011 4:49 pm
by aal9_programmer
it is working, but only for the last textarea in the table. my table looks like:

CourseName Edit Submit

Comp Science (textarea) (submit button)
Computing (textarea) (submit button)
Management (textarea) (submit button)
Masters (textarea) (submit button)
MComp (textarea) (submit button)
Staff (textarea) (submit button)

so when i edit ONLY the textarea for Staff and press submit, it works, if i edit any of the other textareas it doesn't update and clears the Staff courseName. what must I change?

Re: Newbie In Need of Assistance - PHP & SQL

Posted: Thu Mar 17, 2011 3:42 pm
by Mini
You have to make a new form for each row. The fields override eachother because they have the same name, so the last field is send with the post data.

Re: Newbie In Need of Assistance - PHP & SQL

Posted: Fri Mar 18, 2011 11:55 am
by miki
textarea should be in different form

Code: Select all

<form><textarea name="a"></textarea><input type="Submit" name="Submit" value="Submit"></form>
<form><textarea name="a"></textarea><input type="Submit" name="Submit" value="Submit"></form>
<form><textarea name="a"></textarea><input type="Submit" name="Submit" value="Submit"></form>
...