Data not saved between sessions

[wallyweb]

Set up: MySQL 4.1.7, PHP 5.0.2.2, Apache 2.0.52, localhost, Windows98 SE

Objective:
- Page 1 - Using PHP to view data (ID, name, contact) from a MySql table (bands)
- Link on Page 1 opens Page 2 in new window
- Page 2 - Using PHP and form to add another name and contact to bands

Problem:
- Data added via consol persists and displays properly on both Page 1 and Page 2
- Data added via Page 2 appears on Page 2 as having been added with a properly incremented ID
- Refresh Page 1 does not show data added via Page 2
- Open Page 2 to add more data again appears to work as intended but data previously entered does not show up, nor does it appear via consol.
- ID is incremented in second visit to Page 2, but previous ID is skipped:

ID1 name contact from consol input persists
ID2 name contact from consol input persists
ID3 name contact from Page 2 input appears in Page 2, then does not persist
ID4 name contact from Page 2 input appears in Page 2, then does not persist and ID3 and related name and contact have disappeared.

Page 1 Code:

<?php
$dbcnx = mysqli_init();
mysqli_options($dbcnx, MYSQLI_INIT_COMMAND, "SET AUTOCOMMIT=0");
mysqli_options($dbcnx, MYSQLI_OPT_CONNECT_TIMEOUT, 5);
mysqli_real_connect($dbcnx, 'localhost', 'root', 'psswrd', 'dbname');
if (mysqli_connect_errno()) {
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}
echo "<table cellpadding=2 border=1 align=center>";
echo "<tr>";
echo "<th>ID #</th>";
echo "<th>NAME</th>";
echo "<th>CONTACT</th>";
echo "</tr>";
$query = "SELECT * FROM bands";
$result = mysqli_query($dbcnx, $query) or die ("Error in query: $query. " . mysqli_error());
if (mysqli_num_rows($result) > 0) { 
   while($row = mysqli_fetch_row($result)) {
        echo "<tr>";
        echo "<td>" . $row[0] . "</td>";
        echo "<td>" . $row[1] . "</td>";
        echo "<td>" . $row[2] . "</td>";
        echo "</tr>"; 
   }
}
else { 
   echo "No rows found!";
}
mysqli_free_result($result);
mysqli_close($dbcnx);
echo "<tr>";
echo "<td colspan=3><a href='#'  class='bodylink' onclick=\"window.open('addband.php','addband','height=400,width=700,top=10,left=10,scrollbars=yes,toolbar=no,status=no,menubar=no,location=no,resizable=yes')\">Add a new band</a></td>";
echo "</tr>"; 
echo "</table>";
?>

Page 2 code:

<?php
if (!isset($_POST['submit'])) { 		
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
   Band Name: <input type="text" name="name"><br>
   Contact: <input type="text" name="contact"><br>
   <input type="submit" name="submit" value="Add band">
</form>
<?php
}
else {
   $dbcnx = mysqli_init();
   mysqli_options($dbcnx, MYSQLI_INIT_COMMAND, "SET AUTOCOMMIT=0");
   mysqli_options($dbcnx, MYSQLI_OPT_CONNECT_TIMEOUT, 5);
   mysqli_real_connect($dbcnx, 'localhost', 'root', 'psswrd', 'dbname');
   if (mysqli_connect_errno()) {
      printf("Connect failed: %s\n", mysqli_connect_error());
      exit();
   }
   $name = $_POST['name'];
   $name = mysqli_real_escape_string($dbcnx, $name);
   $contact = $_POST['contact'];
   $contact = mysqli_real_escape_string($dbcnx, $contact);
   echo $name . " - - " . $contact;
   $query = "INSERT INTO bands (name, contact) VALUES ('$name','$contact')";
   $result = mysqli_query($dbcnx, $query) or die ("Error in query: $query. " . mysqli_error());
   echo "New record inserted with ID " . mysqli_insert_id($dbcnx);
   echo "<table cellpadding=2 border=1 align=center>";
   echo "<tr>";
   echo "<th>ID #</th>";
   echo "<th>NAME</th>";
   echo "<th>CONTACT</th>";
   echo "</tr>";
   $query = "SELECT * FROM bands";
   $result = mysqli_query($dbcnx, $query) or die ("Error in query: $query. " . mysqli_error());
   if (mysqli_num_rows($result) > 0) { 
      while($row = mysqli_fetch_row($result)) {
         echo "<tr>";
         echo "<td>" . $row[0] . "</td>";
         echo "<td>" . $row[1] . "</td>";
         echo "<td>" . $row[2] . "</td>";
         echo "</tr>"; 
      }
   }
   else { 
      echo "No rows found!";
   }
echo "<tr>";
echo "<td colspan=3><a href='#'  class='bodylink' onclick='self.close()'>CLOSE</a></td>";
echo "</tr>"; 
echo "</table>";
}
mysqli_free_result($result);
mysqli_close($dbcnx);
?>

Question 1 : What's wrong?
Question 2 : Is the issue with my scripting or could it be with a configuration (my.ini, php.ini)?

[wallyweb]

Alright folks, my post has been up for almost 24 hours and nobody has responded although several have viewed it.

This seems to indicate my issue is a bit of a poser and for me it is all the more so as I am new to MySQL and PHP.

So let me rephrase the question a bit.

The PHP seems to work just fine. Consol input is accepted as verified by the return from the query on Page 1. This also seems to indicate that the MySQL set up is good too. The database databases as intended and PHP reads from it as intended.

On Page 2, the form posts the data to PHP and PHP seems to send the data to the database, as verified the query, or does it? A page refresh as well as select * from bands done on the console seem to indicate that teh data actually was not inserted.

The data was sent but not received. Where did it get lost?

The ID increments so it is controlled from outside the table.
Optimize Table does reset the ID.
Is the data stored in some temporary location before being committed to the table?

Unfortunately I don't know enough about the inner workings of MySQL so any hints would be greatly appreciated. For instance, is this the right forum for this question? It does appear to be a MySQL issue ... or is it?

Thanks folks.

[r937]

it's not really a mysql question, which is perhaps why you didn't get an answer in this forum

i mean, there's nothing wrong with SELECT * FROM bands

and INSERT INTO bands (name, contact) VALUES ('$name','$contact') looks okay too

[wallyweb]

Thanks ... I found some additional info from the Apache error log:

[client 127.0.0.1] PHP Notice: Undefined variable: result in I:\\Apache Group\\Apache2\\htdocs\\olearys\\addband.php on line 342, referer: http://localhost:8080/olearys/olearyssiteadmin.php
[client 127.0.0.1] PHP Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, null given in I:\\Apache Group\\Apache2\\htdocs\\olearys\\addband.php on line 342, referer: http://localhost:8080/olearys/olearyssiteadmin.php
[client 127.0.0.1] PHP Notice: Undefined variable: dbcnx in I:\\Apache Group\\Apache2\\htdocs\\olearys\\addband.php on line 343, referer: http://localhost:8080/olearys/olearyssiteadmin.php
[client 127.0.0.1] PHP Warning: mysqli_close() expects parameter 1 to be mysqli, null given in I:\\Apache Group\\Apache2\\htdocs\\olearys\\addband.php on line 343, referer: http://localhost:8080/olearys/olearyssiteadmin.php

Page 1 is olearyssiteadmin.php, the referer.
Page 2 is addband.php
line 342: mysqli_free_result($result);
line 343: mysqli_close($dbcnx);

The errors appear when Page 2 is loaded.
Of note:
PHP Notice: Undefined variable: result
and
PHP Notice: Undefined variable: dbcnx

I agree this does not appear to be a MySql issue but rather with PHP.
How can I move this thread to PHP?

[r937]

How can I move this thread to PHP?

wait for one of the moderators (seanf, freakysid, redemption, dhtmlgod, Nicky D, Helge) to notice, and if none does within, say, a day, you may send one of them a private message

[wallyweb]

Thanks again.

If you meet up with any of them, please give them a heads up, ok?
PS. Hope you don't mind ... congrats re the Argos, but I'm an Als fan. ;-)

[r937]

thanks for the congrats, but i'm a vikings/lions/colts/chargers/jets fan

[wallyweb]

if(!argos){echo "ok!"}]

[r937]

actually it's more like

select team from nfl order by rand() limit 1

[wallyweb]

Eureka! After 24 cartons (!packs) of cigarettes, 55 gallons of Tim Horton's coffee, 5 PHP manual searches, 10 ^ 99 additions to Apache's error.log and some genial banter with Rudy, I solved it.

The problem was in Page 2.

For those who need it, here's the new code commented with the changes.:

<?php
	if (!isset($_POST['submit'])) { 		
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
	Band Name: <input type="text" name="name"><br>
	Contact: <input type="text" name="contact"><br>
	<input type="submit" name="submit" value="Add band">
</form>
<?php
	}
	else {
        /*======== Changed connection script ============ */
		$dbcnx = mysqli_connect('localhost', 'root','psswrd', 'dbname');
		if (mysqli_connect_errno()) {
			printf("Connect failed: %s\n", mysqli_connect_error());
			exit();
		}
        /*=================================*/
		$name = $_POST['name'];
		$name = mysqli_real_escape_string($dbcnx, $name);
		$contact = $_POST['contact'];
		$contact = mysqli_real_escape_string($dbcnx, $contact);
       /*===== Changed INSERT query to an if and dropped redundant $result */
		if (mysqli_query($dbcnx, "INSERT INTO bands (name, contact) VALUES ('$name','$contact')") === TRUE) {
			printf("New record inserted with ID " . mysqli_insert_id($dbcnx));
		}
       /*=================================*/
		echo "<table cellpadding=2 border=1 align=center>";
		echo "<tr>";
		echo "<th>ID #</th>";
		echo "<th>NAME</th>";
		echo "<th>CONTACT</th>";
		echo "</tr>";
		$query = "SELECT * FROM bands";
    /* ===== Changed $result to an if ========= */
		if ($result = mysqli_query($dbcnx, $query)){
    /*=================================*/
			if (mysqli_num_rows($result) > 0) { 
				while($row = mysqli_fetch_row($result)) {
					echo "<tr>";
					echo "<td>" . $row[0] . "</td>";
					echo "<td>" . $row[1] . "</td>";
					echo "<td>" . $row[2] . "</td>";
					echo "</tr>"; 
				}
			}
			else { 
				echo "No rows found!";
			}
			echo "<tr>";
			echo "<td colspan=3><a href='#'  class='bodylink' onclick='self.close()'>CLOSE</a></td>";
			echo "</tr>"; 
			echo "</table>";
   /*========  Moved this inside the {} of the if that referenced $result ====*/
			mysqli_free_result($result); 
		}
   /* ======== Moved this inside the {} of the if that referenced $dbcnx ====*/
		mysqli_close($dbcnx);
	}
?>

Only one question ... why does the original connection script work in Page 1 but seem to prevent the form from updating the db in page 2 ?

Obviously still a PHP topic ... be good if this thread still goes over to PHP for them to consider it.

Many thanks ... sometimes just talking it out gets the solution.
And a big
for Rudy.