SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast wallyweb's Avatar
    Join Date
    Oct 2003
    Location
    Saint John, NB, Canada
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Data not saved between sessions

    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:

    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:

    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)?

  2. #2
    SitePoint Enthusiast wallyweb's Avatar
    Join Date
    Oct 2003
    Location
    Saint John, NB, Canada
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast wallyweb's Avatar
    Join Date
    Oct 2003
    Location
    Saint John, NB, Canada
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wallyweb
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast wallyweb's Avatar
    Join Date
    Oct 2003
    Location
    Saint John, NB, Canada
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. ;-)

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks for the congrats, but i'm a vikings/lions/colts/chargers/jets fan
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast wallyweb's Avatar
    Join Date
    Oct 2003
    Location
    Saint John, NB, Canada
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if(!argos){echo "ok!"}]

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually it's more like
    Code:
    select team from nfl order by rand() limit 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast wallyweb's Avatar
    Join Date
    Oct 2003
    Location
    Saint John, NB, Canada
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Eureka!

    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.:

    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 {
            /*======== 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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •