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?
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
<h1 align="e;center"e;>Change Book Details</h1>
<form action="e;<?php echo $_SERVERї'PHP_SELF']; ?>"e; method="e;post"e;>
<fieldset>
<table width="e;300"e; border="e;1"e; align="e;center"e; bgcolor="e;#CCCCCC"e;>
<tr bordercolor="e;#FFFFFF"e; bgcolor="e;#FFFFFF"e;>
<td width="e;240"e; height="e;30"e;>
<div align="e;center"e;><font color="e;#000099"e; size="e;4"e; face="e;Times New Roman, Times, serif"e;>ISBN:</font></div></td>
<td><?php echo $rowї'isbn']; ?></td>
</tr>
<tr bordercolor="e;#FFFFFF"e; bgcolor="e;#FFFFFF"e;>
<td width="e;240"e; height="e;30"e;>
<div align="e;center"e;><font color="e;#000099"e; size="e;4"e; face="e;Times New Roman, Times, serif"e;>Name:</font></div>
</td>
<td> <input type="e;text"e; name="e;name"e; id="e;name"e; size="e;60"e; maxlength="e;80"e; value="e;<?php echo $rowї'name']; if (isset($_POSTї'name'])) echo $_POSTї'name']; ?>"e; /> </td>
</tr>
<tr bordercolor="e;#FFFFFF"e; bgcolor="e;#FFFFFF"e;>
<td width="e;240"e; height="e;30"e;>
<div align="e;center"e;><font color="e;#000099"e; size="e;4"e; face="e;Times New Roman, Times, serif"e;>Author:</font></div>
</td>
<td> <input type="e;text"e; name="e;author"e; id="e;author"e; size="e;60"e; maxlength="e;80"e; value="e;<?php echo $rowї'author']; if (isset($_POSTї'author'])) echo $_POSTї'author']; ?>"e; /> </td>
</tr>
<tr bordercolor="e;#FFFFFF"e; bgcolor="e;#FFFFFF"e;>
<td width="e;240"e; height="e;30"e;>
<div align="e;center"e;><font color="e;#000099"e; size="e;4"e; face="e;Times New Roman, Times, serif"e;>Publisher:</font></div>
</td>
<td> <input type="e;text"e; name="e;publisher"e; id="e;publisher"e; size="e;40"e; maxlength="e;40"e; value="e;<?php echo $rowї'publisher']; if (isset($_POSTї'publisher'])) echo $_POSTї'publisher']; ?>"e; /> </td>
</tr>
<tr bordercolor="e;#FFFFFF"e; bgcolor="e;#FFFFFF"e;>
<td width="e;240"e; height="e;30"e;>
<div align="e;center"e;><font color="e;#000099"e; size="e;4"e; face="e;Times New Roman, Times, serif"e;>Year:</font></div>
</td>
<td> <input type="e;text"e; name="e;year"e; size="e;4"e; maxlength="e;4"e; value="e;<?php echo $rowї'year']; if (isset($_POSTї'year'])) echo $_POSTї'year']; ?>"e; /> </td>
</tr>
<tr bordercolor="e;#FFFFFF"e; bgcolor="e;#FFFFFF"e;>
<td width="e;240"e; height="e;30"e;>
<div align="e;center"e;><font color="e;#000099"e; size="e;4"e; face="e;Times New Roman, Times, serif"e;>Price: <small>e.g. 24.99</small></font></div>
</td>
<td> <input type="e;text"e; name="e;price"e; size="e;6"e; maxlength="e;6"e; value="e;<?php echo $rowї'price']; if (isset($_POSTї'price'])) echo $_POSTї'price']; ?>"e; /> </td>
</tr>
<tr bordercolor="e;#FFFFFF"e; bgcolor="e;#FFFFFF"e;>
<td width="e;240"e; height="e;30"e;>
<div align="e;center"e;><font color="e;#000099"e; size="e;4"e; face="e;Times New Roman, Times, serif"e;>Level:</font></div>
</td>
<td> <input type="e;text"e; name="e;level"e; size="e;1"e; maxlength="e;1"e; value="e;<?php echo $rowї'level']; if (isset($_POSTї'level'])) echo $_POSTї'level']; ?>"e; /> </td>
</tr>
<tr bordercolor="e;#FFFFFF"e; bgcolor="e;#FFFFFF"e;>
<td width="e;240"e; height="e;30"e;>
<div align="e;center"e;><font color="e;#000099"e; size="e;4"e; face="e;Times New Roman, Times, serif"e;>Module:</font></div>
</td>
<td> <input type="e;text"e; name="e;module"e; size="e;60"e; maxlength="e;80"e; value="e;<?php echo $rowї'module']; if (isset($_POSTї'module'])) echo $_POSTї'module']; ?>"e; /> </td>
</tr>
<tr bordercolor="e;#FFFFFF"e; bgcolor="e;#FFFFFF"e;>
<td width="e;240"e; height="e;30"e;>
<div align="e;center"e;><font color="e;#000099"e; size="e;4"e; face="e;Times New Roman, Times, serif"e;>Semester:</font></div>
</td>
<td> <input type="e;text"e; name="e;semester"e; size="e;6"e; maxlength="e;6"e; value="e;<?php echo $rowї'semester']; if (isset($_POSTї'semester'])) echo $_POSTї'semester']; ?>"e; /> </td>
</tr>
<tr bordercolor="e;#FFFFFF"e; bgcolor="e;#FFFFFF"e;>
<td width="e;240"e; height="e;30"e;>
<div align="e;center"e;><font color="e;#000099"e; size="e;4"e; face="e;Times New Roman, Times, serif"e;>Code:</font></div>
</td>
<td> <input type="e;text"e; name="e;code"e; size="e;8"e; maxlength="e;8"e; value="e;<?php echo $rowї'code']; if (isset($_POSTї'code'])) echo $_POSTї'code']; ?>"e; /> </td>
</tr>
<tr bordercolor="e;#FFFFFF"e; bgcolor="e;#FFFFFF"e;>
<td width="e;240"e; height="e;30"e;>
<div align="e;center"e;><font color="e;#000099"e; size="e;4"e; face="e;Times New Roman, Times, serif"e;>Image Name:</font></div>
</td>
<td> <input type="e;text"e; name="e;image_name"e; size="e;30"e; maxlength="e;30"e; value="e;<?php echo $rowї'image_name']; if (isset($_POSTї'image_name'])) echo $_POSTї'image_name']; ?>"e; /> </td>
</tr>
</table>
</fieldset>
<div align="e;center"e;><input type="e;submit"e; name="e;submit"e; value="e;Update"e; /></div>
</form>
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 ||
Have you tried echoing $_GET['bid']...
EDIT | Your form action does not include the GET variable $_GET['bid']

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.