Problem Updating My DB

tappiocca · May 4, 2011, 4:45pm

Ok, so I am trying to figure out why this will not updating on my website, but will on my computer, here is a copy of my code…
My CSS file:

body { background-color: #CCCCCC; color: #000000; font-family: Arial, Verdana, sans-serif; margin: 0;} 

h1 { text-align: center; font-weight: bold; }

p2 { font-size: 100%; }

.content { padding: 10px 10px 0 10px; }

.footer { position: absolute; bottom: 5px; left: 400px; font-style: italic; font-size: 75%; text-align: center; }

.new { font-weight: bold; font-style: italic; }
		
#box { background-color: #999999; width: 100%; min-width: 800px; padding-left: 10px; }

#center { margin-left: 100px; background-color: #CCCCCC; }

#left { float: left; width: 100px; background-color: #999999; border: none; font-size: 100%; font-weight: bold; }

#left a { text-decoration: none; color: #000000; margin: 15px; display: block; }
	
#left a:visited { color: #000000; }

#left a:hover { color: #000000; background-color: #CCCCCC; }

#logo { background-color: #999999; font-weight: bold; text-align: center; font-size: 80px; }

#right { float: right; width: 200px; background-color: #999999; border: none; padding-left: 10px; }

My main page:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
		"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!-- Open HTML -->
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<!-- Open HEAD -->
<head>
<title>List of Comics</title>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
				
<link href="../tapp.css" rel="stylesheet" type="text/css" />
</head>
<!-- Close HEAD -->

<!-- Open BODY-->
<body>
	
<!-- Open BOX -->	
<div id="box">
	
<!-- Open LOGO -->
<div id="logo">Robs Internet Comic Database</div>
<!-- Close LOGO -->

<!-- Open LEFT -->	
<div id="left">
<p><a href="?addcomic">Add Comic</a></p>
<p><a href="?addauthor">Add Author</a></p>
</div>
<!-- Close LEFT -->
<br />

<!-- Open Right -->
<div id="right">
</div>
<!-- Close Right -->
<!-- Open Center -->
<div id="center">

<div class="content">
<p>Here is a list of Author's and ID's</p>	
<!-- Create Table for Author List -->
<table border="1" cellpadding="2">
<tr>
<th>Author</th>
<th>Id</th>
<th>Remove</th>
</tr>
<!-- Call PHP containing Author INFO -->		
<?php foreach ($Comic as $Splat): ?>
<form action="?deleteauthor" method="post">
<tr>
<td><?php echo htmlspecialchars($Splat['ComicAuthor'], ENT_QUOTES,
'UTF-8'); ?></td>
<td><?php echo htmlspecialchars($Splat['ID'], ENT_QUOTES,
'UTF-8'); ?></td>
<td><input type="hidden" name="ID" value="
<?php echo $Splat['ID']; ?>"/>
<input type="submit" value="Delete"/> </td>
</tr>
<?php endforeach; ?>
<!-- Close PHP containing Author INFO -->
</table>
<!-- Close Table for Author List -->

<p>Here are all the comics in the database:</p>
		
<!-- Create Table for Comic List -->
<table border="1" cellpadding="2">
<tr>
<th>Number</th>
<th>Name</th>
<th>Title</th>
<th>Date</th>
<th>Author</th>
<th>Remove</th>
</tr>
<!-- Call PHP containing Comic INFO -->
<?php foreach ($Comics as $Gizmo): ?>
<form action="?deletecomic" method="post"> 
<tr>
<td><?php echo htmlspecialchars($Gizmo['ComicNumber'], ENT_QUOTES,
								'UTF-8'); ?></td>
<td><?php echo htmlspecialchars($Gizmo['ComicName'], ENT_QUOTES,
								'UTF-8'); ?></td>
<td><?php echo htmlspecialchars($Gizmo['ComicTitle'], ENT_QUOTES,
								'UTF-8'); ?></td>
<td><?php echo htmlspecialchars($Gizmo['ComicDate'], ENT_QUOTES,
								'UTF-8'); ?></td>
<td><?php echo htmlspecialchars($Gizmo['ComicAuthor'], ENT_QUOTES,
								'UTF-8'); ?></td>
<td><input type="hidden" name="id" value="
<?php echo $Gizmo['id']; ?>"/>
<input type="submit" value="Delete"/> </td>
						
</tr>
</form>
<!-- Close PHP containing Comic INFO -->
<?php endforeach; ?>
</table>
<!-- Close Table for Comic List --></div>
<!-- Close Content -->
<!-- Open Footer -->
<div class="footer">Questions?&nbsp;<a href=mailto:admin">Email Webmaster!</a><br />
Last Update 05/03/2011<br />
&copy;2011 Another Tappiocca Production
</div>
<!-- Close Footer -->
</div>
<!-- Close BOX -->
</div>
<!-- Close Center -->		
</body>
<!-- Close BODY-->

</html>
<!-- Close HTML -->

The page to add info:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Add Author</title>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
		
<!-- Create Internal CSS -->
<style type="text/css">
textarea { display: block; width: 10%; 	}
</style>
<!-- Close Internal CSS -->

<!-- Call External CSS -->
<link href="../tapp.css" rel="stylesheet" type="text/css" />
</head>
<!-- Open Body -->
<body>	
<!-- Open Box -->
<div id="box">
	
<!-- Open Logo -->
<div id="logo">Rob's Internet Comic Database</div>
<!-- Close Logo -->

<!-- Open Left/internal links -->
<div id="left">
<a href="../index.html">Home</a>
<a href="index.php">Database</a>
<!-- Create add comic/author link -->
<p><a href="?addauthor">Add Author</a></p>
<p><a href="?addcomic">Add Comic</a></p>
</div>
<!-- Close Left/internal links -->

<br />

<!-- Open Right/News Feed -->
<div id="right">
</div>
<!-- Close Right/News Feed -->

<!-- Open Center -->
<div id="center">
	
<!-- Open Content -->
<div class="content">
<!-- Call PHP form to create ID/Author -->
<form action="?" method="post">
<div>
<!-- Create Author ID -->
<label for="ID">Author ID:</label>
<textarea id="ID" name="ID" rows="1" cols="10"></textarea>
<!-- Create Author Name -->
<label for="ComicAuthor">Author Name:</label>
<textarea id="ComicAuthor" name="ComicAuthor" rows="1" cols="10"></textarea>
</div>
<div><input type="submit" value="Add"/></div>
</form>
<!-- Close PHP form to create ID/Author -->
	
</div>
<!-- Close Content -->

<!-- Open Footer -->
<div class="footer">Questions?&nbsp;<a href="mailto:admin"> Email Webmaster!</a> <br />
Last Update 05/03/2011<br /> 
&copy;2011 Another Tappiocca Production
</div>
<!-- Close Footer -->

</div>
<!-- Close Center -->

</div>
<!-- Close Box -->

	</body>
<!-- Close Body -->

</html>
<!-- Close HTML -->

and the PHP itself:

&lt;?php
if (get_magic_quotes_gpc())
{
function stripslashes_deep($value)
{
$value = is_array($value) ?
array_map('stripslashes_deep', $value) :
stripslashes($value);

return $value;
}

$_POST = array_map('stripslashes_deep', $_POST);
$_GET = array_map('stripslashes_deep', $_GET);
$_COOKIE = array_map('stripslashes_deep', $_COOKIE);
$_REQUEST = array_map('stripslashes_deep', $_REQUEST);
}

if (isset($_GET['addcomic']))
{
include 'form.html.php';
exit();
}

if (isset($_GET['addauthor']))
{
include 'form2.html.php';
exit();
}

$link = mysqli_connect('localhost', 'root', 'root');
if (!$link)
{
$error = 'Unable to connect to the database server: '. mysqli_error($link);
include 'error.html.php';
exit();
}

if (!mysqli_set_charset($link, 'utf8'))
{
$output = 'Unable to set database connection encoding: '. mysqli_error($link);
include 'output.html.php';
exit();
}

if (!mysqli_select_db($link, 'icdb'))
{
$error = 'Unable to locate the comic database: '. mysqli_error($link);
include 'error.html.php';
exit();
} 
if (isset($_POST['ComicNumber']))  
{   
foreach ($_POST as $key =&gt; $val) 
{ 
$_POST[$key] = mysqli_real_escape_string($link, $_POST[$key]); 
} 
      
$ComicNumber = $_POST['ComicNumber'];   
$ComicName   = $_POST['ComicName'];   
$ComicTitle  = $_POST['ComicTitle'];   
$ComicDate   = $_POST['ComicDate'];   
$AuthorId    = $_POST['AuthorId'];   
              
 $sql = "INSERT INTO `Comics` (`ComicNumber`, `ComicName`, `ComicTitle`, `ComicDate`, `AuthorId`) 
 VALUES ($ComicNumber, '$ComicName', '$ComicTitle', '$ComicDate', $AuthorId)";    
      
if (!mysqli_query($link, $sql))   
{   
$error = 'Error adding submitted Comic: ' . mysqli_error($link);   
include 'error.html.php';   
exit();   
}   

header('Location: .');   
exit();   
} 

if (isset($_POST['ComicAuthor']))  
{   
foreach ($_POST as $key =&gt; $val) 
{ 
$_POST[$key] = mysqli_real_escape_string($link, $_POST[$key]); 
} 
      
$ID = $_POST['ID'];   
$ComicAuthor   = $_POST['ComicAuthor'];  
              
$sql = "INSERT INTO `ComicAuthor` (`ID`, `ComicAuthor`) 
VALUES ($ID, '$ComicAuthor')";    
      
if (!mysqli_query($link, $sql))   
{   
$error = 'Error adding submitted Author: ' . mysqli_error($link);   
include 'error.html.php';   
exit();   
}   

header('Location: .');   
exit();   
}

if (isset($_GET['deletecomic']))
{
$id = mysqli_real_escape_string($link, $_POST['id']);
$sql = "DELETE FROM Comics WHERE id='$id'";
if (!mysqli_query($link, $sql))
{
$error = 'Error deleting comic: ' . mysqli_error($link);
include 'error.html.php';
exit();
}

header('Location: .');
exit();
}

if (isset($_GET['deleteauthor']))
{
$ID = mysqli_real_escape_string($link, $_POST['ID']);
$sql = "DELETE FROM ComicAuthor WHERE ID='$ID'";
if (!mysqli_query($link, $sql))
{
$error = 'Error deleting comic author: ' . mysqli_error($link);
include 'error.html.php';
exit();
}

header('Location: .');
exit();
}

$result = mysqli_query($link,
'SELECT Comics.id, ComicNumber, ComicName, ComicTitle, ComicDate, ComicAuthor FROM Comics INNER JOIN ComicAuthor ON AuthorId ComicAuthor.id');
if (!$result)
{
$error = 'Error fetching Comics: ' . mysqli_error($link);
include 'error.html.php';
exit();
}

while ($row = mysqli_fetch_array($result))
{
$Comics[] = array('id' =&gt; $row['id'], 'ComicNumber' =&gt; $row['ComicNumber'],
'ComicName' =&gt; $row['ComicName'], 'ComicTitle' =&gt; $row['ComicTitle'],
'ComicDate' =&gt; $row['ComicDate'], 'ComicAuthor' =&gt; $row['ComicAuthor']);
}

$result1 = mysqli_query($link, 'SELECT ID, ComicAuthor FROM ComicAuthor');
if (!$result1)
{
$error = 'Error fetching Comics: ' . mysqli_error($link);
include 'error.html.php';
exit();
}

while ($row = mysqli_fetch_array($result1))
{
$Comic[] = array('ID' =&gt; $row['ID'], 'ComicAuthor' =&gt; $row['ComicAuthor']);
}
include 'comics.html.php';
?&gt;

StarLion · May 4, 2011, 4:49pm

You’ve got about 5 queries in there.

Which one isnt working?

tappiocca · May 4, 2011, 4:56pm

I just wanted to get my code out of the way 1st…

So on my personal computer, when I ADD a Author/ID it auto-updates and shows the new change; but when I do the same function via my website, it will not… Now, it WAS working before I added all the CSS code (which is why I linked that also).

I also tried to separate the stuff, so you would know what was what.

StarLion · May 4, 2011, 5:02pm

So we’re clear:


if (isset($_POST['ComicAuthor']))   
{    
foreach ($_POST as $key =&gt; $val)  
{  
$_POST[$key] = mysqli_real_escape_string($link, $_POST[$key]);  
}  
       
$ID = $_POST['ID'];    
$ComicAuthor   = $_POST['ComicAuthor'];   
               
$sql = "INSERT INTO `ComicAuthor` (`ID`, `ComicAuthor`)  
VALUES ($ID, '$ComicAuthor')";     
       
if (!mysqli_query($link, $sql))    
{    
$error = 'Error adding submitted Author: ' . mysqli_error($link);    
include 'error.html.php';    
exit();    
}    

header('Location: .');    
exit();    
}

is the section you’re referring to.

I have no idea why you have that header in there. Forcing a reload of the page? Why?

Anyway. Basic Debugging Step #1: Is my code going into the loop.


if (isset($_POST['ComicAuthor']))   
{ 
echo "Debug 1&lt;br /&gt;";   
foreach ($_POST as $key =&gt; $val)  
{  
echo "Debug 2 - ".$key."&lt;br /&gt;";
$_POST[$key] = mysqli_real_escape_string($link, $_POST[$key]);  
}  
echo "Debug 3&lt;br /&gt;";          
$ID = $_POST['ID'];    
$ComicAuthor   = $_POST['ComicAuthor'];   
               
$sql = "INSERT INTO `ComicAuthor` (`ID`, `ComicAuthor`)  
VALUES ($ID, '$ComicAuthor')";     
       
if (!mysqli_query($link, $sql))    
{    
echo "Debug 4&lt;br /&gt;";   
$error = 'Error adding submitted Author: ' . mysqli_error($link);    
include 'error.html.php';    
exit();    
}    
echo "Debug 5&lt;br /&gt;";   
//header('Location: .');    
//exit();    
}

What do you get out?

tappiocca · May 4, 2011, 5:14pm

I am sorry let me be more specific in the “It does not work” reference… the main page shows my information
ie. StarLion/1 (Author/ID)
and
ie. PHP for Dummies/2011 (Title/Year)
Now, I want to add a new Author… ie Kevin. So I click the link to goto the Add Author.

I type in Kevin/2 and hit enter… The header points me back to index.php (which by defaults displays the comic.html.php) ONLY … There is no new update on the page (as it still only displays StarLion/1), yet there is a change inside of the DB itself. So I know the code is working to add/delete people, it is just simply not updating properly… If you want, I can give you the link to the site itself so you can try it yourself.

tappiocca · May 4, 2011, 5:23pm

StarLion:

So we’re clear:


if (isset($_POST['ComicAuthor']))   
{    
foreach ($_POST as $key =&gt; $val)  
{  
$_POST[$key] = mysqli_real_escape_string($link, $_POST[$key]);  
}  
       
$ID = $_POST['ID'];    
$ComicAuthor   = $_POST['ComicAuthor'];   
               
$sql = "INSERT INTO `ComicAuthor` (`ID`, `ComicAuthor`)  
VALUES ($ID, '$ComicAuthor')";     
       
if (!mysqli_query($link, $sql))    
{    
$error = 'Error adding submitted Author: ' . mysqli_error($link);    
include 'error.html.php';    
exit();    
}    

header('Location: .');    
exit();    
}

is the section you’re referring to.

I have no idea why you have that header in there. Forcing a reload of the page? Why?

Why would you not want the page to reload to the main page? I understood that was the purpose of the header(‘Location: .’), to take one back to the index.php page. Also, there is a header(‘Location: .’) on ever other insert/delete spot above/below it…

Also, this is what your code spits out…
DEBUG 1
DEBUG 2 - ID
DEBUG 2 - ComicAuthor
DEBUG 2
DEBUG 5

StarLion · May 4, 2011, 6:18pm

I assume the third Debug 2 should actually have been a Debug 3.
So the code is executing correctly;

Step #2: Echo out your $sql value, and run it directly on the database to make sure it inserts a row.

As for ‘why would I not want the page to reload to the main page’… because i would be displaying the main page without the additional pageload.


<?php
if(isset($_POST['dosomething'])) {
  //Do something
}
//Main Display here
?>

Again, that’s me.

tappiocca · May 4, 2011, 6:40pm

Actually I went back and checked, I had one duplicate number, here is the output again:
DEBUG 1
DEBUG 2 - ID
DEBUG 2 - ComicAuthor
DEBUG 3
DEBUG 5
and the code:

if (isset($_POST['ComicAuthor']))  
{   
    echo "DEBUG 1&lt;br /&gt;";
    foreach ($_POST as $key =&gt; $val) 
    { 
    echo "DEBUG 2 - ".$key."&lt;br /&gt;";
        $_POST[$key] = mysqli_real_escape_string($link, $_POST[$key]); 
    } 
     echo "DEBUG 3&lt;br /&gt;";     
    $ID = $_POST['ID'];   
    $ComicAuthor   = $_POST['ComicAuthor'];  
              
    $sql = "INSERT INTO `ComicAuthor` (`ID`, `ComicAuthor`) 
    		              VALUES ($ID, '$ComicAuthor')";    
      
    if (!mysqli_query($link, $sql))   
    {   
        echo "DEBUG 4&lt;br /&gt;";
        $error = 'Error adding submitted Author: ' . mysqli_error($link);   
        include 'error.html.php';   
        exit();   
    }   
    echo "DEBUG 5&lt;br /&gt;";
//    header('Location: .');   
  //  exit();   
}

StarLion · May 4, 2011, 6:43pm

Which indicates the script is correctly entering the loop, so if there isnt an update in the table something isnt quite right on the mySQL side - hence step 2 being to try it directly and see what happens.

tappiocca · May 4, 2011, 6:46pm


<?php
if(isset($_POST['dosomething'])) {
  //Do something
}
//Main Display here
?>

So does that mean I do not need the header(‘Location: .’); when I am on a different page.? When do you want to use that?

StarLion · May 4, 2011, 6:49pm

Header (Location) is for when you want to send the browser somewhere different. If I were designing what you’re describing, I wouldnt have been moving around to begin with, so i wouldnt need to go anywhere different.

tappiocca · May 4, 2011, 6:49pm

What do you mean, directly? I know it is updating the server b/c if you try and use the same ID number twice, it tells you NO; for example: Error adding submitted Author: Duplicate entry ‘22’ for key ‘PRIMARY’

StarLion · May 4, 2011, 6:55pm

Okay, so the problem is… that your display isnt displaying the entries… so lets see your display code.

tappiocca · May 4, 2011, 6:57pm

<form action="?" method="post">

Does this tell the server to return to where ? originated from, i mean because the link to get to this page was called by addauthor?

tappiocca · May 4, 2011, 6:59pm

<!-- Create Table for Author List -->
<table border="1" cellpadding="2">
<tr>
<th>Author</th>
<th>Id</th>
<th>Remove</th>
</tr>
<!-- Call PHP containing Author INFO -->		
<?php foreach ($Comic as $Splat): ?>
<form action="?deleteauthor" method="post">
<tr>
<td><?php echo htmlspecialchars($Splat['ComicAuthor'], ENT_QUOTES,
'UTF-8'); ?></td>
<td><?php echo htmlspecialchars($Splat['ID'], ENT_QUOTES,
'UTF-8'); ?></td>
<td><input type="hidden" name="ID" value="
<?php echo $Splat['ID']; ?>"/>
<input type="submit" value="Delete"/> </td>
</tr>
<?php endforeach; ?>
<!-- Close PHP containing Author INFO -->
</table>
<!-- Close Table for Author List -->

<p>Here are all the comics in the database:</p>
		
<!-- Create Table for Comic List -->
<table border="1" cellpadding="2">
<tr>
<th>Number</th>
<th>Name</th>
<th>Title</th>
<th>Date</th>
<th>Author</th>
<th>Remove</th>
</tr>
<!-- Call PHP containing Comic INFO -->
<?php foreach ($Comics as $Gizmo): ?>
<form action="?deletecomic" method="post"> 
<tr>
<td><?php echo htmlspecialchars($Gizmo['ComicNumber'], ENT_QUOTES,
								'UTF-8'); ?></td>
<td><?php echo htmlspecialchars($Gizmo['ComicName'], ENT_QUOTES,
								'UTF-8'); ?></td>
<td><?php echo htmlspecialchars($Gizmo['ComicTitle'], ENT_QUOTES,
								'UTF-8'); ?></td>
<td><?php echo htmlspecialchars($Gizmo['ComicDate'], ENT_QUOTES,
								'UTF-8'); ?></td>
<td><?php echo htmlspecialchars($Gizmo['ComicAuthor'], ENT_QUOTES,
								'UTF-8'); ?></td>
<td><input type="hidden" name="id" value="
<?php echo $Gizmo['id']; ?>"/>
<input type="submit" value="Delete"/> </td>
						
</tr>
</form>
<!-- Close PHP containing Comic INFO -->
<?php endforeach; ?>
</table>
<!-- Close Table for Comic List --></div>

Now, like I said, it was working before I added all the HTML/CSS code to the file.

StarLion · May 4, 2011, 7:01pm

? goes to the same page that the form is on, and has an empty GET array.

StarLion · May 4, 2011, 7:02pm

<?php foreach ($Comic as $Splat): ?>

and where is $Comic filled?

<?php foreach ($Comics as $Gizmo): ?>

same question.

tappiocca · May 4, 2011, 7:07pm

while ($row = mysqli_fetch_array($result))
{ $Comics[] = array('id' => $row['id'], 'ComicNumber' => $row['ComicNumber'], 
'ComicName' => $row['ComicName'], 'ComicTitle' => $row['ComicTitle'], 
'ComicDate' => $row['ComicDate'], 'ComicAuthor' => $row['ComicAuthor']);
} 
-----------------------------------------
while ($row = mysqli_fetch_array($result1))
{ $Comic[] = array('ID' => $row['ID'], 'ComicAuthor' => $row['ComicAuthor']);
}

StarLion · May 4, 2011, 7:10pm

… and the query used to get that result?

tappiocca · May 4, 2011, 7:11pm

But how does the GET know what its getting, and you are saying that ? auto points back to where ever the form is called?

Topic		Replies	Views
Is this valid code? PHP	45	2745	April 29, 2011
Updating a Database PHP	17	1194	October 8, 2014
UPDATE is the bane of my existence PHP	34	2797	January 29, 2015
I can't figure out what is wrong with my edit page PHP	12	1524	April 25, 2015
How to code submit button to update database...URGENT :( PHP	21	68727	October 8, 2014

Problem Updating My DB

Related topics