Page 1 of 1
Problems with NULL value in MySQL data when using PHP Form
Posted: Fri Jun 21, 2002 5:19 pm
by sknelson
I have a PHP form that users can enter a value in one or all of three fields to search for information. The problem is that the code I use to convert the data from MS Access to MySQL places "NULL" in the field. So, if one of the search fields has a "NULL" value, it won't pull up the record. The only records that are returned are those with data all of three of the search fields. I have an idea of what I need to do because I do something similar in a SQL statement in Access, but I can't figure out how to do it with MySQL and PHP. Any help would be greatly appreciate! Thanks in advance. Here is my code:
Code: Select all
$sql = "SELECT * , DATE_FORMAT(DATE_IN, '%m/%d/%Y') as DATE_IN ,
DATE_FORMAT(DELIVERY_D, '%m/%d/%Y') as DELIVERY_D
FROM tblwebdata WHERE " .
"SELLER_LAS LIKE '%".$SELLER."%' AND " .
"BUYER_LAST LIKE '%".$BUYER."%' AND " .
"LEGAL_DESC LIKE '%".$LEGAL."%' " .
"ORDER BY DATE_IN DESC";
Posted: Sat Jun 22, 2002 6:47 am
by Pekka
NULL is that, it is nothing. You can't describe it in joins.
Your query returns a row only if all three fields match. If one has no match or NULL you get no result. There is no way around this on one query.
I'd split the search into three separate queries and then use arrays to combine the results by id's (you are using unique id's right?).
PS. If you intend to build a big system of this, the database should really be normalized so that you make e.g. table "names" with name, address and auto id. Legal texts should be on separate tables with unique id's and then all relations are done by master table which has only the id's: name id used as buyer id and/or seller id, legal id and the date. This way you save disk space, code is faster and data is reusable among other benefits.
Posted: Sat Jun 22, 2002 10:41 am
by sam
^ NAH... Just replace the AND's with OR's
Code: Select all
$sql = "SELECT * , DATE_FORMAT(DATE_IN, '%m/%d/%Y') as DATE_IN ,
DATE_FORMAT(DELIVERY_D, '%m/%d/%Y') as DELIVERY_D
FROM tblwebdata WHERE " .
"SELLER_LAS LIKE '%".$SELLER."%' OR " .
"BUYER_LAST LIKE '%".$BUYER."%' OR " .
"LEGAL_DESC LIKE '%".$LEGAL."%' " .
"ORDER BY DATE_IN DESC";
Cheers Sam
Posted: Tue Jun 25, 2002 5:52 pm
by sknelson
Replacing the AND's with OR's just returns all the records, so that didn't work.
I have tried splitting the query a couple different ways, but never got it to work. That is how I run a similar query in MS Access. The database is fairly small, only about 400 records at any one time. It is just the work in progress and the work that has been delivered in the last 30 days, so it isn't likely to grow much. There are only 18 fields in the table. I run a query in MS Access that gives me the data I need for the website. I have two sets of code that I can run to export the data to MySQL. One code places NULL in fields that are empty, so I have the problem running the query. The other leaves the fields empty. The query works fine with this code but creates a problem when the query results are displayed. Since it is empty, MySQL changes the Delivery Date to 00/00/0000. What I really want is for that to show up blank. I tried some code that someone suggested a while back, but I haven't been able to make that work. Here is that code:
Code: Select all
if ($date_variable != "0000-00-00") {
list($year, $month, $day) = split('ї-./]', $date_variable);
$date_variable = date('m/d/Y',mktime(0,0,0,$month,$day,$year));
}
else {
$date_variable = "";
}
I really appreciate everyone's suggestions!
Posted: Wed Jun 26, 2002 6:38 pm
by Mahmoud
try the following code
Code: Select all
if (isset($SELLER) || isset($BUYER) || isset($LEGAL)) {
if (isset($SELLER)) {
$where = 'SELLER_LAS LIKE "%'.$SELLER.'%';
}elseif (isset($BUYER)) {
if (isset($where) {
$where = $where.' AND BUYER_LAST LIKE %'.$BUYER'.'%';
}else{
$where = 'BUYER_LAST LIKE %'.$BUYER'.'%';
}
}elseif (isset($LEGAL)) {
if (isset($where) {
$where = $where.' AND LEGAL_DESC LIKE %'.$LEGAL'.'%';
}else{
$where = 'LEGAL_DESC LIKE %'.$LEGAL'.'%';
}
}
// database connection command here and select table
$sql = "SELECT * , DATE_FORMAT(DATE_IN, '%m/%d/%Y') as DATE_IN ,
DATE_FORMAT(DELIVERY_D, '%m/%d/%Y') as DELIVERY_D
FROM tblwebdata WHERE " .$where;
}
although there might be a better way
I think I've figured it out
Posted: Thu Jun 27, 2002 3:47 pm
by sknelson
I decided to stick with the Access module that left all the values empty rather than the one that placed NULL in the fields. This allowed my query to work. I added the following to the end of the code in the Access module:
Code: Select all
Print #1, "UPDATE " & tname & " SET DELIVERY_D=NULL WHERE DELIVERY_D=0000-00-00;"
Now, when I insert the text file that the module creates into MySQL, ONLY the values in the DELIVERY_D field are changed to NULL. When the query results are displayed in the PHP form, if the work hasn't been delivered yet, it leaves it blank, rather than 00-00-0000, which is what I was after.
Thank you all for your suggestions and assistance! It was greatly appreciated and got me thinking in different directions.