Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Regular Coder
    Join Date
    Feb 2005
    Location
    Tokyo, Japan
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    removing a record from a result set - SESSIONS?

    I'm not sure if I'm posting this in the right forum, but I'll give it a shot...

    Currently I have a php program that preforms a query on a MySQL db and returns the values to an .html file.

    A typical example of the SELECT statments I've been using look like this:
    PHP Code:
    $sql_select "SELECT Genre, Title, Director, Year FROM movies WHERE Genre ='".$_POST['genre']."'"
    the result is then stored in a result/record set:
    PHP Code:
    $rs mysql_query($sql_select); 
    The entire contents of the result set ($rs) is displayed in an html <table>. After the <table> is printed, the user may select rows that they would like to remove. Once the "remove" button is pressed that record is deleted from the .html table but NOT the db.

    How can I remove a record(s) (and the associated <tr> (table row)) from the .html file but NOT the db?

    Any suggestions?

    Thanks

    Yak

    - Let me know if I should post this to the MySQL forum.
    Last edited by Yakisoba; 06-10-2005 at 02:26 AM.

  • #2
    Regular Coder
    Join Date
    Feb 2005
    Location
    West Midlands, UK
    Posts
    623
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You mean you want to somehow mark the row in the db so that on future queries it doesn't show up in the html? If so, you'd probably need to add another field to the database, like an "active" field and either set it to yes or no, then in your query just do:

    PHP Code:
    $sql_select "SELECT Genre, Title, Director, Year FROM movies WHERE Genre ='".$_POST['genre']."' AND active='yes'"
    Then when you remove rows from the html, just do an update on those rows in the database and change their active field to 'no'.

  • #3
    Regular Coder
    Join Date
    Feb 2005
    Location
    Tokyo, Japan
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts
    not quite...

    I don't want the data in the db to be manipulated in any way. Only the data that is contained in the result set.

    For example:
    SELECT * FROM table_name; - the results of this query are stored in a variable ($rs).

    each row of the $rs is displayed in tabular form. Each row of the <table> also has a "remove" button.

    when the user clicks on the "remove" button the row is removed from $rs and the table is refreshed (without re-running the original query).

    Is this possible?

    Thanks

    Yak

  • #4
    New to the CF scene
    Join Date
    Jun 2005
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    To be honest

    To be honest I reallt think you should do a variable for $_POST['genre'] like :
    PHP Code:
    $genre $_REQUEST['genre'];
    $sql_select "SELECT Genre, Title, Director, Year FROM movies WHERE Genre ='$genre'";
    $rs mysql_query($rs); 

  • #5
    Regular Coder
    Join Date
    Feb 2005
    Location
    West Midlands, UK
    Posts
    623
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Yakisoba
    each row of the $rs is displayed in tabular form. Each row of the <table> also has a "remove" button.

    when the user clicks on the "remove" button the row is removed from $rs and the table is refreshed (without re-running the original query).
    Oh. Well, maybe sessions would be the way to do this, if you read all the data from the database into sessions then, when the user selects a row to remove you could pass the id for the row in the url then unset the row for the passed id, for instance (let's assume you have stored all the id's in a session array called rowdata):
    PHP Code:
    if(isset($_GET['id']) && isset($_SESSION['rowdata'][$_GET['id']])) {
        unset(
    $_SESSION['rowdata'][$_GET['id']]);

    Then just iterate through the session and create the table using the remaining stored row data.

  • #6
    Regular Coder
    Join Date
    Feb 2005
    Location
    Tokyo, Japan
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Interesting...

    delinear - that sounds like what I'm trying to do, unfortunatly I don't know much about session variables but they are high on my list of things to learn. No time like the present...

    Would anyone be able to point me to some good web tutorials (regarding php sessions)?

    Also, some examples on this matter would be appreciated.

    Here is my code so far: (Note: everything worked fine until I started messing around with the session variables)


    This file (select.php) runs the initial query and echo's the results into a <table>. In the first column of each row there is a "remove" button, when the user clicks the button it will run "remove.php" (onClick="remove.php"; i will probably change this to an href later)
    PHP Code:
    error_reporting(E_ALL); 

    session_start();
    session_register('rs');

    include 
    'db_connect_movies.php';

    //SELECT statment
    $sql_select "SELECT Genre, Title, Director, Year FROM $db_table";

    //result set
    $rs mysql_query($sql_select);

    //creating the table /w headers

         
    echo "<html><body>";
         echo 
    "<table border='1' cellspacing='0'><tr><td><img src='../images/close.jpg' /></td><td>Genre</td><td>Title</td><td>Director</td><td>Year</td></tr>";
        
        
        
    //row for each record
         
    while ($row mysql_fetch_array($rs)) {
                     echo 
    "<tr><td><img src='../images/close.jpg' onClick='remove.php'/></td><td>" $row['Genre'] . "</td><td>" $row['Title'] . "</td><td>" $row['Director'] . "</td><td>" $row['Year'] . "</td></tr>";
               }  
        
         echo 
    "</table>";
         echo 
    "</body></html>";


    //free memory
    mysql_free_result($rs);

    //close the db
    mysql_close(); 

    When "remove.php" is executed the row (that the user clicked on) is removed from the session variable and the table is regenerated using the remaining values in the recordset.

    remove.php
    PHP Code:
    session_start();


    if(isset(
    $_GET['ID']) && isset($_SESSION['rs'][$_GET['ID']])) {
        unset(
    $_SESSION['rs'][$_GET['ID']]);
      

         echo 
    "<html><body>";
         echo 
    "<table border='1' cellspacing='0'><tr><td><img src='../images/close.jpg' /></td><td>Genre</td><td>Title</td><td>Director</td><td>Year</td></tr>";
        
        
         while (
    $row mysql_fetch_array($rs)) {
                     echo 
    "<tr><td><img src='../images/close.jpg' onClick='remove.php'/></td><td>" $row['Genre'] . "</td><td>" $row['Title'] . "</td><td>" $row['Director'] . "</td><td>" $row['Year'] . "</td></tr>";
               }  
        
         echo 
    "</table>";
         echo 
    "</body></html>";

         } 

    I know remove.php is totally screwed up at the moment, any suggestions?

    Thanks,

    Yak

    P.S. - markman
    To be honest I reallt think you should do a variable for $_POST['genre'] like :
    PHP Code:
    $genre = $_REQUEST['genre'];
    $sql_select = "SELECT Genre, Title, Director, Year FROM movies WHERE Genre ='$genre'";
    $rs = mysql_query($rs);
    Why?

  • #7
    Regular Coder
    Join Date
    Feb 2005
    Location
    West Midlands, UK
    Posts
    623
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, a few things going on here. Firstly the example I suggested uses an auto-increment field id to identify the row that needs to be removed. I notice from your sql query that you don't make any call to an id. While it is possible to do this by passing the other fields to the url, it's much simpler if you use an id (and if you're not assigning an auto-incrementing id as your primary key in the database I'd highly recomment that you do so).

    Next, you would only query the database when you load the page IF the session data doesn't already exist, otherwise you will just overwrite the session data each time which will defeat the purpose of having the session data.

    Also, when you initially make the database call, you have to store the data as session data, you're not currently doing that.

    Finally, in the onClick event you need to pass the id of the item you want to remove. Without passing that id you will just reload the page without removing anything.

    Making those changes you should end up with something like...

    PHP Code:
    error_reporting(E_ALL);  

    session_start(); 

    // check whether to pull the data from the database
    if(!isset($_SESSION['rs'])) {
        
        include 
    'db_connect_movies.php';
        
    $sql_select "SELECT movie_id, Genre, Title, Director, Year FROM $db_table"// requires an autoincrementing movie_id field in the database
        
    $rs mysql_query($sql_select);
        
    $numrows mysql_num_rows($rs);
        for(
    $i=0$i $numrows$i++) {
            
    $row[$i] = mysql_fetch_assoc($rs);
            
    $_SESSION['rs'][$i] = mysql_fetch_assoc($rs);
        }
    }

    // check whether to remove a row of data
    if(isset($_GET['id'])) {
        unset(
    $_SESSION['rs'][$_GET['id']]);
    }

    //creating the table /w headers 
    echo "<html><body>"
    echo 
    "<table border='1' cellspacing='0'><tr><td><img src='../images/close.jpg' /></td><td>Genre</td><td>Title</td><td>Director</td><td>Year</td></tr>"
         
    //row for each record 
         
    for($i=0$i count($_SESSION['rs']); $i++) {
        echo 
    '<tr><td><img src="../images/close.jpg" onClick="location.href=\'' basename($_SERVER['PHP_SELF']) . '?id=' $_SESSION['rs'][$i]['movie_id'] . '\'/></td><td>' $_SESSION['rs'][$i]['Genre'] . '</td><td>' $_SESSION['rs'][$i]['Title'] . '</td><td>' $_SESSION['rs'][$i]['Director'] . '</td><td>' $_SESSION['rs'][$i]['Year'] . '</td></tr>'
    }
         
    echo 
    "</table>"
    echo 
    "</body></html>"


    //free memory 
    mysql_free_result($rs); 

    //close the db 
    mysql_close(); 
    I think that should do it although I have no way to test it right now so, any problems let me know and I'll take another look.

    Just a couple of things to bear in mind - firstly as I said this code will only work if you have a movie_id field in your database so you will either need to create one or, if you already have one, you'll need to change the references to movie_id in the above code to point to your id field.

    Secondly, I'm not 100% sure the javascript is right for the onClick although I'm reasonably sure it is.

    Hope that helps a little.

  • #8
    Regular Coder
    Join Date
    Feb 2005
    Location
    Tokyo, Japan
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I do use an AUTO_INCREMENT id field as a primary key in my db, so that won't be a problem...

    When trying to run the code you provided the following happend:

    (lets say my table has 6 records)

    - A table with 6 rows was created, however only every second row was displayed. (first three rows of the table contained data, the last 3 were empty. Row #'s 2, 4, and 6 were displayed).

    -The remove function does not seem to do anything at the moment.

    I will screw around with it a little more tonight...

    I appreciate the help.

    Thanks

    Yak

  • #9
    Regular Coder
    Join Date
    Aug 2004
    Location
    The US of A
    Posts
    767
    Thanks
    1
    Thanked 0 Times in 0 Posts
    PHP Code:
            $row[$i] = mysql_fetch_assoc($rs);
            
    $_SESSION['rs'][$i] = mysql_fetch_assoc($rs); 
    That is the problem with the displaying every second row. Perosnally, this how I would have done that....

    PHP Code:
        $rs mysql_query($sql_select);
        
    $i 1;
        while(
    $row mysql_fetch_assoc($rs))
        {
            
    $_SESSION['rs'][$i] = $row;
            
    $i++;
        } 
    But that's me.

  • #10
    Regular Coder
    Join Date
    Feb 2005
    Location
    West Midlands, UK
    Posts
    623
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oops, yeah I originally wrote it to output to $row then I realised it was quicker to put it directly into the session, must have forgot to remove the previous line, d'oh

  • #11
    Regular Coder
    Join Date
    Feb 2005
    Location
    West Midlands, UK
    Posts
    623
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try changing this:

    PHP Code:
    if(isset($_GET['id'])) { 
        unset(
    $_SESSION['rs'][$_GET['id']]); 

    To:

    PHP Code:
    if(isset($_GET['id'])) {
        for(
    $i=0$i count($_SESSION['rs']); $i++) {
            if(
    $_GET['id'] == $_SESSION['rs'][$i]['movie_id']) {
                unset(
    $_SESSION['rs'][$i];
            }
        }

    And change this:

    PHP Code:
    echo '<tr><td><img src="../images/close.jpg" onClick="location.href=\'' basename($_SERVER['PHP_SELF']) . '?id=' $_SESSION['rs'][$i]['movie_id'] . '\'/></td><td>' $_SESSION['rs'][$i]['Genre'] . '</td><td>' $_SESSION['rs'][$i]['Title'] . '</td><td>' $_SESSION['rs'][$i]['Director'] . '</td><td>' $_SESSION['rs'][$i]['Year'] . '</td></tr>'
    to this:

    PHP Code:
    echo '<tr><td><a href="' basename($_SERVER['PHP_SELF']) . '?id=' $_SESSION['rs'][$i]['movie_id'] . '"><img src="../images/close.jpg" border="0" /></a></td><td>' $_SESSION['rs'][$i]['Genre'] . '</td><td>' $_SESSION['rs'][$i]['Title'] . '</td><td>' $_SESSION['rs'][$i]['Director'] . '</td><td>' $_SESSION['rs'][$i]['Year'] . '</td></tr>'
    Hopefully that will fix all the stupid

  • #12
    Regular Coder
    Join Date
    Feb 2005
    Location
    Tokyo, Japan
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh man...getting close.

    Here is what's happening now:

    Kurashu - Your suggestion worked beautifully, I only had to change one thing. In your code you initialized the counter to 1 ($i = 1;), this worked, however it would skip the first row of the database (it was incremented to 2 before record 1 was printed).
    I just initialized the counter to 0 ($i = 0;).

    delinear - still having a little trouble with the "isset" code. For some reason "isset" always causes me grief.

    I got the below piece of code to work (somewhat), for some reason when I clicked on the "remove" button it would remove the record below. Once the record was removed it would leave a blank row in the table (as apposed to regenerating the table minus that row). Also, the (deleted) record reappears when I attempt to remove another record.
    PHP Code:
     if(isset($_GET['id'])) { 
        unset(
    $_SESSION['rs'][$_GET['id']]); 

    I made a little change to the above code and now the proper record is removed, however the other two problems remain.

    This is what I changed the above code to (nothing fancy);
    PHP Code:

    $id 
    $_GET['id'] - 1;

    if(isset(
    $id)) {
        unset(
    $_SESSION['rs'][$id]); 
    I know it's a band-aid solution, but it works (for the moment).

    I tried messing around with this...
    PHP Code:

     
    if(isset($_GET['id'])) {
        for(
    $i=0$i count($_SESSION['rs']); $i++) {
            if(
    $_GET['id'] == $_SESSION['rs'][$i]['ID']) {
                unset(
    $_SESSION['rs'][$i];
            }
        }

    ...but have had no luck. Whenever I use this piece of code I can't get the table to print at all.

    there has be something that I am overlooking.

    Thanks again for the assistance.

    Yak

  • #13
    Regular Coder
    Join Date
    Feb 2005
    Location
    West Midlands, UK
    Posts
    623
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ah, yeah sorry - it is right but the problem with the empty table row is caused because the numeric keys aren't reset, ie if you have rows 1,2,3,4,5 and you unset session 3 you will then have rows 1,2,4,5 but the for loop that draws the table still tries to print the empty row 3. All we have to do to fix that is reset the array keys for the session, array_values() is good for that. Here is the amended code:

    PHP Code:
    error_reporting(E_ALL);   
    session_start();  

    // check whether to pull the data from the database 
    if(!isset($_SESSION['rs'])) { 
     
        include 
    'db_connect_movies.php'
        
    $sql_select "SELECT movie_id, Genre, Title, Director, Year FROM $db_table"// requires an autoincrementing movie_id field in the database 
        
    $rs mysql_query($sql_select); 
        
    $numrows mysql_num_rows($rs); 
      
        for(
    $i=0$i $numrows$i++) { 
            
    $_SESSION['rs'][$i] = mysql_fetch_assoc($rs);
        } 


    if(isset(
    $_GET['id'])) { 
        for(
    $i=0$i count($_SESSION['rs']); $i++) { 
            if(
    $_GET['id'] == $_SESSION['rs'][$i]['movie_id']) { 
                unset(
    $_SESSION['rs'][$i]); 
            } 
        }
        
    $_SESSION['rs'] = array_values($_SESSION['rs']); 
    }

    //creating the table /w headers 
    echo "<html><body>";  
    echo 
    "<table border='1' cellspacing='0'><tr><td><img src='../images/close.jpg' /></td><td>Genre</td><td>Title</td><td>Director</td><td>Year</td></tr>";  
          
    //row for each record 
    for($i=0$i count($_SESSION['rs']); $i++) { 
            echo 
    '<tr><td><a href="' basename($_SERVER['PHP_SELF']) . '?id=' $_SESSION['rs'][$i]['movie_id'] . '"><img src="../images/close.jpg" border="0" /></a></td><td>' $_SESSION['rs'][$i]['Genre'] . '</td><td>' $_SESSION['rs'][$i]['Title'] . '</td><td>' $_SESSION['rs'][$i]['Director'] . '</td><td>' $_SESSION['rs'][$i]['Year'] . '</td></tr>';  

          
    echo 
    "</table>";  
    echo 
    "</body></html>";  

    //free memory 
    mysql_free_result($rs);  

    //close the db 
    mysql_close(); 

  • #14
    Regular Coder
    Join Date
    Feb 2005
    Location
    Tokyo, Japan
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts
    almost...

    When running the program for the first time the table generates with all values (as expected. I'll stick with the 6 row example). Then, I can click on the "remove" button on any row and that row dissappears (SWEET!!).

    - Now I'm down to 5 rows -

    But, after the first click things get a little crazy.

    It appears that I am only allowed to remove one row from the <table>.

    When I click to remove another row from the table that row dissapears however a 5 row table is regenerated. (and the value I previously removed reappears).



    Yak

  • #15
    Regular Coder
    Join Date
    Feb 2005
    Location
    West Midlands, UK
    Posts
    623
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hmm... odd. That would suggest that the session data isn't maintaining for some reason, since it's obviously generating it anew from the database every time the page is refreshed. It was working fine this side, I set up some test data and it let me click one at a time until the whole table was removed. I don't know why the sessions would reset, I can't see anything in the code that might cause it

    Try creating a script with this code:
    PHP Code:
    <?php
    session_start
    ();
    if(!isset(
    $_SESSION['test'])) {
       
    $_SESSION['test'] = 0;
    } else {
        echo 
    $_SESSION['test'];
        
    $_SESSION['test']++;
    }
    ?>
    Then run the script and refresh the page a few times, does the number increment or does it stick at 0 or 1?

    The only thing I can see that ought to be changed (but shouldn't cause the problem you're having) is that the mysql_free_result and mysql_close are always being run whether they are needed or not, they ought to be moved inside the initial data retrieval loop like so:

    PHP Code:
    if(!isset($_SESSION['rs'])) {  

        include 
    'db_connect_movies.php';  
        
    $sql_select "SELECT movie_id, Genre, Title, Director, Year FROM $db_table"// requires an autoincrementing movie_id field in the database 
        
    $rs mysql_query($sql_select);  
        
    $numrows mysql_num_rows($rs);  
       
        for(
    $i=0$i $numrows$i++) {  
            
    $_SESSION['rs'][$i] = mysql_fetch_assoc($rs); 
        }  

        
    //free memory 
        
    mysql_free_result($rs);   
        
        
    //close the db 
        
    mysql_close(); 

    But like I said, that's just incidental it shouldn't be causing this behaviour.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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