Page 1 of 1

Problem Updating two tables at the same time..

Posted: Fri Apr 08, 2005 3:58 am
by vittelite
Hi guys, I'm having a small problem updating two tables at a time and would really appreciate some help.

Basically what I have is two tables named "books" and "course", they both have a common column named "isbn".

For example I have a page that loads up as a form with textfields, and the textfields display the current details of a book for example.



My queries look something like this:

Code: Select all

if ($n && $a && $p && $y && $pr && $l && $m && $s && $c && $in) {

$query = "UPDATE books SET name='$n', author='$a', publisher='$p', year='$y', price='$pr' WHERE books.isbn='$i'";
$query1 = "UPDATE course SET level='$l', module='$m', semester='$s', code='$c', image_name='$in' WHERE course.isbn='$i'";


$result = mysql_query ($query) or die(mysql_error()); // Run query.
$result = mysql_query ($query1) or die(mysql_error()); // Run second query.
I have declared all the variables correct and they correspond to each textfield entry. When I hit the Submit button at the bottom of the page all I get is a white screen.

Anyone have any ideas? Let me know if you need more script, thanks.

Re: Problem Updating two tables at the same time..

Posted: Fri Apr 08, 2005 5:52 am
by anjanesh
vittelite wrote:When I hit the Submit button at the bottom of the page all I get is a white screen.
For your 8 line code, it'll give a blank screen because theres no ouput.

Code: Select all

$result  = mysql_query ($query) or die(mysql_error()); // Run query.
$result2 = mysql_query ($query1) or die(mysql_error()); // Run second query.
echo 'db Updated !';

Posted: Fri Apr 08, 2005 5:54 am
by Chris Corbyn
You get a white screen? Where's the rest of the script? Like the bit it should output instead of a white page? :wink:

Post some more code.

Posted: Fri Apr 08, 2005 6:04 am
by vittelite
Hi,
here is the complete script.

Code: Select all

include ('admin_header.html');



echo '<hr>';


if (isset($_GET['bid'])) { // Make sure there is a Book ID

$query = "SELECT * FROM books, course WHERE books.isbn = course.isbn AND course.book_id = {$_GET['bid']}";
$result = mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_ASSOC);

if (isset($_POST['submit'])) { // Form Handling.

require_once ('mysql_connect.php'); // Connect to the DB.


// Checks for a book name.
if (eregi ("^[[].' -]{2,80}$", stripslashes(trim($_POST['name'])))) {
	$n = escape_data($_POST['name']);
	} else {
	$n = FALSE;
	echo '<p><font color="red">Please enter a book name!</font></p>';
	}
	
// Checks for an author name.
if (eregi ("^[[].' -]{2,80}$", stripslashes(trim($_POST['author'])))) {
	$a = escape_data($_POST['author']);
	} else {
	$a = FALSE;
	echo '<p><font color="red">Please enter an author\'s name!</font></p>';
	}
	
	// Checks for a publisher name.
if (eregi ("^[[].' -]{2,40}$", stripslashes(trim($_POST['publisher'])))) {
	$p = escape_data($_POST['publisher']);
	} else {
	$p = FALSE;
	echo '<p><font color="red">Please enter a publisher!</font></p>';
	}

//Checks for a Year.
if (eregi ("^[[]_]{2,4}$", stripslashes(trim($_POST['year'])))) {
	$y = escape_data($_POST['year']);
	} else {
	$y = FALSE;
	echo '<p><font color="red">Please enter a year!</font></p>';
	}
	
//Checks for a price.
if (eregi ("^[[]][a-z0-9_.-]+\.[a-z0-9]{2,7}$", stripslashes(trim($_POST['price'])))) {
	$pr = escape_data($_POST['price']);
	} else {
	$pr = FALSE;
	echo '<p><font color="red">Please enter a price!</font></p>';
	}	

//Checks for a level.
	if (eregi ("^[[]_]{1,2}$", stripslashes(trim($_POST['level'])))) {
	$l = escape_data($_POST['level']);
	} else {
	$l = FALSE;
	echo '<p><font color="red">Please enter a level!</font></p>';
	}	
		
	// Checks for a module name.
if (eregi ("^[[].' -]{2,80}$", stripslashes(trim($_POST['module'])))) {
	$m = escape_data($_POST['module']);
	} else {
	$m = FALSE;
	echo '<p><font color="red">Please enter a module name!</font></p>';
	}

	// Checks for a semester.
if (eregi ("^[[].' -]{2,6}$", stripslashes(trim($_POST['semester'])))) {
	$s = escape_data($_POST['semester']);
	} else {
	$s = FALSE;
	echo '<p><font color="red">Please enter a semester!</font></p>';
	}

//Checks for a module code.
	if (eregi ("^[[]_]{2,8}$", stripslashes(trim($_POST['code'])))) {
	$c = escape_data($_POST['code']);
	} else {
	$c = FALSE;
	echo '<p><font color="red">Please enter a module code!</font></p>';
	}	
	
//Checks for an image name.
	if (eregi ("^[[]_]{2,30}$", stripslashes(trim($_POST['image_name'])))) {
	$in = escape_data($_POST['image_name']);
	} else {
	$in = FALSE;
	echo '<p><font color="red">Please enter an image name!</font></p>';
	}
	
if ($n && $a && $p && $y && $pr && $l && $m && $s && $c && $in) {

$query = "UPDATE books SET name='$n', author='$a', publisher='$p', year='$y', price='$pr' WHERE books.isbn='$i'";
$query1 = "UPDATE course SET level='$l', module='$m', semester='$s', code='$c', image_name='$in' WHERE course.isbn='$i'";


$result = mysql_query ($query) or die(mysql_error()); // Run query.
$result = mysql_query ($query1) or die(mysql_error()); // Run second query.


if ($result) { 
	echo '<h1 align="center">The book has been updated. Use the header to check the update.</h1>';
	include('admin_footer.html');
	exit(); 
	} else {
	echo '<p><font color="red">The book could not be updated due to a system error.</font></p>';
	}
	
	mysql_close();
}
}

Posted: Fri Apr 08, 2005 6:13 am
by anjanesh
mysql_query() doesnt not return true is successful - returns false if it fails though.
From php's manual :
mysql_query() returns a resource on success, and FALSE on error.

Code: Select all

if (!$result) { 
    echo '<p><font color="red">The book could not be updated due to a system error.</font></p>';
    } else {
    echo '<h1 align="center">The book has been updated. Use the header to check the update.</h1>';
    include('admin_footer.html');
    exit(); 
    }

Posted: Fri Apr 08, 2005 6:16 am
by Chris Corbyn
You're overwriting $result.

Change the second $result to $result2 like anjanesh said.

Then:

Code: Select all

if ($result && $result2) {
    // Output
}
That wont fix you're problem though if you say you get a blank white page :?

Doesn't the else stament even execute?

Code: Select all

error_reporting(E_ALL); //On the very top line of your script and post the output to us

Posted: Fri Apr 08, 2005 6:18 am
by harsha
Hi
vitt

Try this out I AM NOT QUITE SURE WHETHER IT WORKS OR NOT BUT WHATS WRONG IN TRYING OUT

Code: Select all

<?
$query = "UPDATE books, course SET books.name='$n', books.author='$a', 
books.publisher='$p', books.year='$y', 
books.price='$pr', course.level='$l', course.module='$m', 
course.semester='$s', course.code='$c', course.image_name='$in' 
WHERE books.isbn='$i', course.isbn='$i'";

$result = mysql_query ($query) or die(mysql_error()); 
?>
I AM VERY POOR IN SQL HE HE HE
;-)

Posted: Fri Apr 08, 2005 6:22 am
by Chris Corbyn
anjanesh wrote:mysql_query() doesnt not return true is successful - returns false if it fails though.
From php's manual :
mysql_query() returns a resource on success, and FALSE on error.

Code: Select all

if (!$result) { 
    echo '<p><font color="red">The book could not be updated due to a system error.</font></p>';
    } else {
    echo '<h1 align="center">The book has been updated. Use the header to check the update.</h1>';
    include('admin_footer.html');
    exit(); 
    }
If a resource is returned this evaluates to TRUE by default.

Code: Select all

$var = 'string'; //This is TRUE even though it's not $var = TRUE
if ($var) {
   echo $var.' is TRUE';
}

Posted: Fri Apr 08, 2005 6:25 am
by Chris Corbyn
harsha wrote:Hi
vitt

Try this out I AM NOT QUITE SURE WHETHER IT WORKS OR NOT BUT WHATS WRONG IN TRYING OUT

Code: Select all

<?
$query = "UPDATE books, course SET books.name='$n', books.author='$a', 
books.publisher='$p', books.year='$y', 
books.price='$pr', course.level='$l', course.module='$m', 
course.semester='$s', course.code='$c', course.image_name='$in' 
WHERE books.isbn='$i', course.isbn='$i'";

$result = mysql_query ($query) or die(mysql_error()); 
?>
I AM VERY POOR IN SQL HE HE HE
;-)
That would be AND not ", " in the WHERE clause.

Code: Select all

<?
$query = "UPDATE books, course SET books.name='$n', books.author='$a', 
books.publisher='$p', books.year='$y', 
books.price='$pr', course.level='$l', course.module='$m', 
course.semester='$s', course.code='$c', course.image_name='$in' 
WHERE books.isbn='$i' AND course.isbn='$i'";

$result = mysql_query ($query) or die(mysql_error()); 
?>

Posted: Fri Apr 08, 2005 6:36 am
by vittelite
Hi,
thanks for the help guys but I'm still getting a white screen and no execution of the queries. Here is the complete script if you have time to go through it, many thanks!

Code: Select all

error_reporting(E_ALL); 
include ('admin_header.html');
include_once ('mysql_connect.php');


echo '<hr>';


if (isset($_GET['bid'])) { // Make sure there is a Book ID

$query = "SELECT * FROM books, course WHERE books.isbn = course.isbn AND course.book_id = {$_GET['bid']}";
$result = mysql_query ($query);
$row = mysql_fetch_array ($result, MYSQL_ASSOC);

if (isset($_POST['submit'])) { // Form Handling.

require_once ('mysql_connect.php'); // Connect to the DB.


// Checks for a book name.
if (eregi ("^[[].' -]{2,80}$", stripslashes(trim($_POST['name'])))) {
	$n = escape_data($_POST['name']);
	} else {
	$n = FALSE;
	echo '<p><font color="red">Please enter a book name!</font></p>';
	}
	
// Checks for an author name.
if (eregi ("^[[].' -]{2,80}$", stripslashes(trim($_POST['author'])))) {
	$a = escape_data($_POST['author']);
	} else {
	$a = FALSE;
	echo '<p><font color="red">Please enter an author\'s name!</font></p>';
	}
	
	// Checks for a publisher name.
if (eregi ("^[[].' -]{2,40}$", stripslashes(trim($_POST['publisher'])))) {
	$p = escape_data($_POST['publisher']);
	} else {
	$p = FALSE;
	echo '<p><font color="red">Please enter a publisher!</font></p>';
	}

//Checks for a Year.
if (eregi ("^[[]_]{2,4}$", stripslashes(trim($_POST['year'])))) {
	$y = escape_data($_POST['year']);
	} else {
	$y = FALSE;
	echo '<p><font color="red">Please enter a year!</font></p>';
	}
	
//Checks for a price.
if (eregi ("^[[]][a-z0-9_.-]+\.[a-z0-9]{2,7}$", stripslashes(trim($_POST['price'])))) {
	$pr = escape_data($_POST['price']);
	} else {
	$pr = FALSE;
	echo '<p><font color="red">Please enter a price!</font></p>';
	}	

//Checks for a level.
	if (eregi ("^[[]_]{1,2}$", stripslashes(trim($_POST['level'])))) {
	$l = escape_data($_POST['level']);
	} else {
	$l = FALSE;
	echo '<p><font color="red">Please enter a level!</font></p>';
	}	
		
	// Checks for a module name.
if (eregi ("^[[].' -]{2,80}$", stripslashes(trim($_POST['module'])))) {
	$m = escape_data($_POST['module']);
	} else {
	$m = FALSE;
	echo '<p><font color="red">Please enter a module name!</font></p>';
	}

	// Checks for a semester.
if (eregi ("^[[].' -]{2,6}$", stripslashes(trim($_POST['semester'])))) {
	$s = escape_data($_POST['semester']);
	} else {
	$s = FALSE;
	echo '<p><font color="red">Please enter a semester!</font></p>';
	}

//Checks for a module code.
	if (eregi ("^[[]_]{2,8}$", stripslashes(trim($_POST['code'])))) {
	$c = escape_data($_POST['code']);
	} else {
	$c = FALSE;
	echo '<p><font color="red">Please enter a module code!</font></p>';
	}	
	
//Checks for an image name.
	if (eregi ("^[[]_]{2,30}$", stripslashes(trim($_POST['image_name'])))) {
	$in = escape_data($_POST['image_name']);
	} else {
	$in = FALSE;
	echo '<p><font color="red">Please enter an image name!</font></p>';
	}
	
if ($n && $a && $p && $y && $pr && $l && $m && $s && $c && $in) {

$query = "UPDATE books SET name='$n', author='$a', publisher='$p', year='$y', price='$pr' WHERE books.isbn='$i'";
$query1 = "UPDATE course SET level='$l', module='$m', semester='$s', code='$c', image_name='$in' WHERE course.isbn='$i'";


$result = mysql_query ($query) or die(mysql_error()); // Run query.
$result2 = mysql_query ($query1) or die(mysql_error()); // Run second query.


if (!$result && !$result2) { 
	
	echo '<p><font color="red">The book could not be updated due to a system error.</font></p>';
	} else {
	echo '<h1 align="center">The book has been updated. Use the header to check the update.</h1>';
	include('admin_footer.html');
	exit(); 
	}
	
	mysql_close();
}
}

Code: Select all

&lt;h1 align=&quote;center&quote;&gt;Change Book Details&lt;/h1&gt;
&lt;form action=&quote;&lt;?php echo $_SERVER&#1111;'PHP_SELF']; ?&gt;&quote; method=&quote;post&quote;&gt;

&lt;fieldset&gt;

&lt;table width=&quote;300&quote; border=&quote;1&quote; align=&quote;center&quote; bgcolor=&quote;#CCCCCC&quote;&gt;
          &lt;tr bordercolor=&quote;#FFFFFF&quote; bgcolor=&quote;#FFFFFF&quote;&gt; 
            &lt;td width=&quote;240&quote; height=&quote;30&quote;&gt;
              &lt;div align=&quote;center&quote;&gt;&lt;font color=&quote;#000099&quote; size=&quote;4&quote; face=&quote;Times New Roman, Times, serif&quote;&gt;ISBN:&lt;/font&gt;&lt;/div&gt;&lt;/td&gt;
            
    &lt;td&gt;&lt;?php echo $row&#1111;'isbn']; ?&gt;&lt;/td&gt;
      
          &lt;/tr&gt;
          &lt;tr bordercolor=&quote;#FFFFFF&quote; bgcolor=&quote;#FFFFFF&quote;&gt; 
            
    &lt;td width=&quote;240&quote; height=&quote;30&quote;&gt;
      &lt;div align=&quote;center&quote;&gt;&lt;font color=&quote;#000099&quote; size=&quote;4&quote; face=&quote;Times New Roman, Times, serif&quote;&gt;Name:&lt;/font&gt;&lt;/div&gt;
      &lt;/td&gt;
    &lt;td&gt; &lt;input type=&quote;text&quote; name=&quote;name&quote; id=&quote;name&quote; size=&quote;60&quote; maxlength=&quote;80&quote; value=&quote;&lt;?php echo $row&#1111;'name']; if (isset($_POST&#1111;'name'])) echo $_POST&#1111;'name']; ?&gt;&quote; /&gt; &lt;/td&gt;
          &lt;/tr&gt;
          &lt;tr bordercolor=&quote;#FFFFFF&quote; bgcolor=&quote;#FFFFFF&quote;&gt; 
            
    &lt;td width=&quote;240&quote; height=&quote;30&quote;&gt;
      &lt;div align=&quote;center&quote;&gt;&lt;font color=&quote;#000099&quote; size=&quote;4&quote; face=&quote;Times New Roman, Times, serif&quote;&gt;Author:&lt;/font&gt;&lt;/div&gt;
      &lt;/td&gt;
    
	&lt;td&gt; &lt;input type=&quote;text&quote; name=&quote;author&quote; id=&quote;author&quote; size=&quote;60&quote; maxlength=&quote;80&quote; value=&quote;&lt;?php echo $row&#1111;'author']; if (isset($_POST&#1111;'author'])) echo $_POST&#1111;'author']; ?&gt;&quote; /&gt; &lt;/td&gt;
          &lt;/tr&gt;
          &lt;tr bordercolor=&quote;#FFFFFF&quote; bgcolor=&quote;#FFFFFF&quote;&gt; 
            
    &lt;td width=&quote;240&quote; height=&quote;30&quote;&gt;
      &lt;div align=&quote;center&quote;&gt;&lt;font color=&quote;#000099&quote; size=&quote;4&quote; face=&quote;Times New Roman, Times, serif&quote;&gt;Publisher:&lt;/font&gt;&lt;/div&gt;
      &lt;/td&gt;
    &lt;td&gt; &lt;input type=&quote;text&quote; name=&quote;publisher&quote; id=&quote;publisher&quote; size=&quote;40&quote; maxlength=&quote;40&quote; value=&quote;&lt;?php echo $row&#1111;'publisher']; if (isset($_POST&#1111;'publisher'])) echo $_POST&#1111;'publisher']; ?&gt;&quote; /&gt; &lt;/td&gt;
          &lt;/tr&gt;
		  &lt;tr bordercolor=&quote;#FFFFFF&quote; bgcolor=&quote;#FFFFFF&quote;&gt;
	
	&lt;td width=&quote;240&quote; height=&quote;30&quote;&gt;
      &lt;div align=&quote;center&quote;&gt;&lt;font color=&quote;#000099&quote; size=&quote;4&quote; face=&quote;Times New Roman, Times, serif&quote;&gt;Year:&lt;/font&gt;&lt;/div&gt;
      &lt;/td&gt;
	&lt;td&gt; &lt;input type=&quote;text&quote; name=&quote;year&quote; size=&quote;4&quote; maxlength=&quote;4&quote; value=&quote;&lt;?php echo $row&#1111;'year']; if (isset($_POST&#1111;'year'])) echo $_POST&#1111;'year']; ?&gt;&quote; /&gt;  &lt;/td&gt;	  
		  &lt;/tr&gt;
		  &lt;tr bordercolor=&quote;#FFFFFF&quote; bgcolor=&quote;#FFFFFF&quote;&gt;
	
	&lt;td width=&quote;240&quote; height=&quote;30&quote;&gt;
     &lt;div align=&quote;center&quote;&gt;&lt;font color=&quote;#000099&quote; size=&quote;4&quote; face=&quote;Times New Roman, Times, serif&quote;&gt;Price: &lt;small&gt;e.g. 24.99&lt;/small&gt;&lt;/font&gt;&lt;/div&gt;
      &lt;/td&gt;
	&lt;td&gt; &lt;input type=&quote;text&quote; name=&quote;price&quote; size=&quote;6&quote; maxlength=&quote;6&quote; value=&quote;&lt;?php echo $row&#1111;'price']; if (isset($_POST&#1111;'price'])) echo $_POST&#1111;'price']; ?&gt;&quote; /&gt;  &lt;/td&gt;	  
		  &lt;/tr&gt;
		  &lt;tr bordercolor=&quote;#FFFFFF&quote; bgcolor=&quote;#FFFFFF&quote;&gt;
	
	&lt;td width=&quote;240&quote; height=&quote;30&quote;&gt;
      &lt;div align=&quote;center&quote;&gt;&lt;font color=&quote;#000099&quote; size=&quote;4&quote; face=&quote;Times New Roman, Times, serif&quote;&gt;Level:&lt;/font&gt;&lt;/div&gt;
      &lt;/td&gt;
	&lt;td&gt; &lt;input type=&quote;text&quote; name=&quote;level&quote; size=&quote;1&quote; maxlength=&quote;1&quote; value=&quote;&lt;?php echo $row&#1111;'level']; if (isset($_POST&#1111;'level'])) echo $_POST&#1111;'level']; ?&gt;&quote; /&gt;  &lt;/td&gt;	  
		  &lt;/tr&gt;
		  &lt;tr bordercolor=&quote;#FFFFFF&quote; bgcolor=&quote;#FFFFFF&quote;&gt;
	
	&lt;td width=&quote;240&quote; height=&quote;30&quote;&gt;
      &lt;div align=&quote;center&quote;&gt;&lt;font color=&quote;#000099&quote; size=&quote;4&quote; face=&quote;Times New Roman, Times, serif&quote;&gt;Module:&lt;/font&gt;&lt;/div&gt;
      &lt;/td&gt;
	&lt;td&gt; &lt;input type=&quote;text&quote; name=&quote;module&quote; size=&quote;60&quote; maxlength=&quote;80&quote; value=&quote;&lt;?php echo $row&#1111;'module']; if (isset($_POST&#1111;'module'])) echo $_POST&#1111;'module']; ?&gt;&quote; /&gt;  &lt;/td&gt;	  
		  &lt;/tr&gt;
		  &lt;tr bordercolor=&quote;#FFFFFF&quote; bgcolor=&quote;#FFFFFF&quote;&gt;
	
	&lt;td width=&quote;240&quote; height=&quote;30&quote;&gt;
      &lt;div align=&quote;center&quote;&gt;&lt;font color=&quote;#000099&quote; size=&quote;4&quote; face=&quote;Times New Roman, Times, serif&quote;&gt;Semester:&lt;/font&gt;&lt;/div&gt;
      &lt;/td&gt;
	&lt;td&gt; &lt;input type=&quote;text&quote; name=&quote;semester&quote; size=&quote;6&quote; maxlength=&quote;6&quote; value=&quote;&lt;?php echo $row&#1111;'semester']; if (isset($_POST&#1111;'semester'])) echo $_POST&#1111;'semester']; ?&gt;&quote; /&gt;  &lt;/td&gt;	  
		  &lt;/tr&gt;
		  &lt;tr bordercolor=&quote;#FFFFFF&quote; bgcolor=&quote;#FFFFFF&quote;&gt;
	
	&lt;td width=&quote;240&quote; height=&quote;30&quote;&gt;
      &lt;div align=&quote;center&quote;&gt;&lt;font color=&quote;#000099&quote; size=&quote;4&quote; face=&quote;Times New Roman, Times, serif&quote;&gt;Code:&lt;/font&gt;&lt;/div&gt;
      &lt;/td&gt;
	&lt;td&gt; &lt;input type=&quote;text&quote; name=&quote;code&quote; size=&quote;8&quote; maxlength=&quote;8&quote; value=&quote;&lt;?php echo $row&#1111;'code']; if (isset($_POST&#1111;'code'])) echo $_POST&#1111;'code']; ?&gt;&quote; /&gt;  &lt;/td&gt;	  
		  &lt;/tr&gt;
		  &lt;tr bordercolor=&quote;#FFFFFF&quote; bgcolor=&quote;#FFFFFF&quote;&gt;
	
	&lt;td width=&quote;240&quote; height=&quote;30&quote;&gt;
      &lt;div align=&quote;center&quote;&gt;&lt;font color=&quote;#000099&quote; size=&quote;4&quote; face=&quote;Times New Roman, Times, serif&quote;&gt;Image Name:&lt;/font&gt;&lt;/div&gt;
      &lt;/td&gt;
	&lt;td&gt; &lt;input type=&quote;text&quote; name=&quote;image_name&quote; size=&quote;30&quote; maxlength=&quote;30&quote; value=&quote;&lt;?php echo $row&#1111;'image_name']; if (isset($_POST&#1111;'image_name'])) echo $_POST&#1111;'image_name']; ?&gt;&quote; /&gt;  &lt;/td&gt;	  
		  &lt;/tr&gt;
		  
        &lt;/table&gt;

&lt;/fieldset&gt;

&lt;div align=&quote;center&quote;&gt;&lt;input type=&quote;submit&quote; name=&quote;submit&quote; value=&quote;Update&quote; /&gt;&lt;/div&gt;

&lt;/form&gt;

Code: Select all

echo '<hr>';
include_once ('admin_footer.html');
} 
exit();

Posted: Fri Apr 08, 2005 6:49 am
by Chris Corbyn
Since you've negated your IF statement Change && to || 8)

Have you tried echoing $_GET['bid']...

EDIT | Your form action does not include the GET variable $_GET['bid'] :wink:

Posted: Fri Apr 08, 2005 10:44 am
by vittelite
Still no luck, any chance you might give me a hand with the script?
Thanks.

Posted: Fri Apr 08, 2005 12:31 pm
by Jade
Have you tried printing out all the variables? Its possible that something is going wrong before it even gets to the script. I know when I'm stumped printing out all the variable values is sometimes the best solution.