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.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Automatically Delete NULL values with PHP

    Hi,

    I have what seems to be a simple problem... but i'm coming up a bit short..

    I'm displaying all rows from a database in a table.

    I am looking to either:
    a) automatically delete entries that "price IS NULL" or
    b) add a simple submit button to "remove null values"

    Any ideas?

    here's my current script, not that it's anything complicated..

    Code:
    <html>
    <head> 
    
        <script src="sorttable.js" type="text/javascript"></script>
    
    <title>Python Flight Search Results</title>
    
    </head>
    
    <body>
    
    <br>
    
    <?php
    $db_host = 'x.x.x';
    $db_user = 'x_xx';
    $db_pwd = 'x';
    $database = 'x_x';
    $table = 'xx';
    if (!mysql_connect($db_host, $db_user, $db_pwd))
        die("Can't connect to database");
    if (!mysql_select_db($database))
        die("Can't select database");
    // sending query
    $result = mysql_query("SELECT * FROM {$table} where price is not NULL ");
    if (!$result) {
       die("Query to show fields from table failed");
    }
    
    $fields_num = mysql_num_fields($result);
    
    echo "<h1>pyflighsearch Results</h1>";
    echo "<table border='1' class=sortable><tr>";
    echo "<td>ID</td><td>URL</td><td>Price</td><td>Route</td><td>Dates</td><td>detail</td><td>Timestamp</td>";
    
    echo "</tr>\n";
    
    // printing table rows
    
    while($row = mysql_fetch_row($result))
    
    {
        echo "<tr>";
        // $row is array... foreach( .. ) puts every element
        // of $row to $cell variable
        foreach($row as $cell)
            echo "<td border='1' bordercolor=black>$cell</td>";
        echo "<td>Edit</td>";
        echo "</tr>";
    
    }
    
    mysql_free_result($result);
    ?>
    </body>
    </html>

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Issuing a delete with a IS NULL check would remove any associated records where the field is null. If you don't need them, toss them. If you want to use PHP, then thats fine too, just delete where all records price is null and away you go.

  • #3
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Issuing a delete with a IS NULL check would remove any associated records where the field is null. If you don't need them, toss them. If you want to use PHP, then thats fine too, just delete where all records price is null and away you go.

    thats what i'm trying to figure out how to do on page load..

    i did have "DELETE * FROM $table WHERE price IS NULL"
    but it didnt work.. ??

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    You sure the field value is null? It may be empty or 0 as well. Does issuing a SELECT * FROM yourtable WHERE price IS NULL create results (best do it in a mysql client or use phpmyadmin for it)?

  • #5
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    You sure the field value is null? It may be empty or 0 as well. Does issuing a SELECT * FROM yourtable WHERE price IS NULL create results (best do it in a mysql client or use phpmyadmin for it)?

    You are correct.. the value is "0"
    so I need to remove values of "0" , empty 'price' fields & NULL price values..

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Then you'll need a condition for each: WHERE price IS NULL OR price = 0 OR price = "". Although you shouldn't end up with both 0 and an empty string, assuming price is a double or variant type.

  • #7
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks for that..

    I added this:


    Code:
    $sql = mysql_query("DELETE * FROM {$table} WHERE price IS NULL OR price = 0 OR price = ''" );
    if (!$sql) {
        die("can not delete NULL values");
    }
    Not working for me yet... :/ hmmmmm

  • #8
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Can't seem to get it working ..

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    What is the error you receive? Is it successfully deleting nothing, or failing to execute the query? Make sure you put the mysql_error() within the die.

  • #10
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    What is the error you receive? Is it successfully deleting nothing, or failing to execute the query? Make sure you put the mysql_error() within the die.


    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM tablename WHERE price IS NULL OR price = 0 OR price = ''' at line 1



    Code:
    if (!mysql_connect($db_host, $db_user, $db_pwd))
        die("Can't connect to database");
    
    if (!mysql_select_db($database))
        die("Can't select database");
    $sql = mysql_query("DELETE * FROM {$table} WHERE price IS NULL OR price = 0 OR price = ''" );
    if (!$sql) {
        die(mysql_error());
    }
    Last edited by stevenryals; 11-06-2012 at 09:41 PM.

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Ah, there's no field property in delete. Its just DELETE FROM, not DELETE * FROM. The field is not necessary since the delete will remove the entire record, not a field in the record.

  • #12
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Ah, there's no field property in delete. Its just DELETE FROM, not DELETE * FROM. The field is not necessary since the delete will remove the entire record, not a field in the record.

    If you look at the code i pasted in the last post, i do have the "*" in there..
    could it be something else maybe?

    edit::

    it's early.. just woke up.. lol but after re-reading.. i think i may have misunderstood.. lol let me try to removed the '*' and see what happens..

  • #13
    New Coder
    Join Date
    Jul 2012
    Posts
    73
    Thanks
    4
    Thanked 0 Times in 0 Posts
    That was the trick sir.. good catch.. it's always the simple things.. lol


  •  

    Posting Permissions

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