...

View Full Version : Automatically Delete NULL values with PHP



stevenryals
11-06-2012, 06:04 PM
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..


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

Fou-Lu
11-06-2012, 07:26 PM
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.

stevenryals
11-06-2012, 07:37 PM
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.. ??

Fou-Lu
11-06-2012, 07:55 PM
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)?

stevenryals
11-06-2012, 08:01 PM
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..

Fou-Lu
11-06-2012, 08:06 PM
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.

stevenryals
11-06-2012, 08:15 PM
Thanks for that..

I added this:



$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

stevenryals
11-06-2012, 08:59 PM
Can't seem to get it working ..

Fou-Lu
11-06-2012, 09:33 PM
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.

stevenryals
11-06-2012, 09:38 PM
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




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());
}

Fou-Lu
11-06-2012, 10:13 PM
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.

stevenryals
11-07-2012, 01:24 PM
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.. :)

stevenryals
11-07-2012, 01:26 PM
That was the trick sir.. good catch.. it's always the simple things.. lol



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum