How to do dynamic query on null column with mySQL and php

How to do dynamic query on null column with mySQL and php

If you have to query null able column dynamically you have to use the like keyword

“select  emp_name from emp where emp_name like ‘%$emp_name%’;

How every this works fine if the emp_name column is not null able. But this will return no rows if the emp_name has null values in it. So we have to tune the query more we have to put one more condition.

“select  emp_name from emp where (emp_name like ‘%$emp_name%’ or emp_name is null);”

The above query works fine but will return every row from the table because it is always true.  So we have to use the third option which is using  ?: operator.

So we build the query this way.

$emp_name  = mysql_escape_string($_REQUEST['emp_name']);

$sqlstr=” select  emp_name from emp where 1=1 “;

$sqlstr= $sqlstr.($emp_name? " and emp_name like '%{$emp_name}%' ":"");

So as you can see if the user supply null in the emp_name field the query will be

select  emp_name from emp where 1=1

and this will return every thing in the table

and if the user supply value for emp_name the result will be according the query matching the emp_name.

you may ask what is the difference between this

1- “select  emp_name from emp where emp_name like ‘%%’;

2- “select  emp_name from emp where emp_name like ‘%smith%’;

The first query will not work if the column has null value and the second query will return every match to *smith*.

So I use  this and it is working fine with my dynamic selection form

$emp_name  = mysql_escape_string($_REQUEST['emp_name']);

$sqlstr=” select  emp_name from emp where 1=1 “;

$sqlstr= $sqlstr.($emp_name? " and emp_name like '%{$emp_name}%' ":"");

How to change varchar field to decimal in mysql

To alter column in mySQL from varchar to DECIMAL I used this syntax.

Alter table my_tbl  modify my_col decimal(6,2);

MySQL uses the Round routines of the host.


On a Windows box

16.125 = 16.13

16.135 = 16.14



On a Linux/Unix box

16.125 = 16.12

16.135 = 16.14

Oracle/PLSQL: Sort a varchar2 field as a numeric field

Question: I have a field defined in Oracle as a varchar2, but it contains numbers. When I use an "order by" clause, the records are sorted ascending by character. But I want to sort it numerically without changing the datatype from varchar2 to numeric. Is there any solution?




Answer: To sort your field numerically, there are two possible solutions:

Solution #1



This first solution only works if the varchar2 field contains all numbers. To do this, you will need to add another field to your "order by" clause that evaluates to a number, but you don't have to include this new field in your "select" portion of the SQL.



For example,

You may have a supplier table defined as:



create table supplier

(

supplier_id varchar2(10) not null,

supplier_name varchar2(60)

);



When you perform the following select statement, the supplier_id field sorts alphabetically, even though it contains numbers.



select * from supplier

order by supplier_id;



However, you could execute the following SQL, which will return a numerically sorted supplier_id:



select * from supplier

order by to_number(supplier_id);



This SQL converts the supplier_id field to a numeric value and then sorts the value in ascending order. This solution returns an error if not all of the values in the supplier_id field are numeric.

Solution #2 (more eloquent solution)



We'd like to thank Kamil for suggesting this solution.



This solution will work even if the varchar2 field contains non-numeric values.



Again, we'll demonstrate this solution on the supplier table, defined as:



create table supplier

(

supplier_id varchar2(10) not null,

supplier_name varchar2(60)

);



Remember that our goal is to sort the supplier_id field in ascending order (based on its numeric value). To do this, try using the LPAD function.



For example,



select * from supplier

order by lpad(supplier_id, 10);



This SQL pads the front of the supplier_id field with spaces up to 10 characters. Now, your results should be sorted numerically in ascending order.



Please note that if your numbers in the supplier_id field are longer than 10 digits, you may need to increase the second parameter on the LPAD function.

How to DROP an Oracle Database

You can do it at the OS level by deleting all the files of the database. The files to be deleted can be found using:

1) select * from dba_data_files;
2) select * from v$logfile;
3) select * from v$controlfile;
4) archive log list
5) initSID.ora
6) In addition you can clean the UDUMP, BDUMP, scripts etc

Clean up the listener.ora and the tnsnames.ora. make sure that the oratab entry is also removed

However this is not a good way to drop an Oracle database. you should use dbca utility to drop the database. or for Oracle 10g and higher you can use drop database command

SHUTDOWN IMMEDIATE;

STARTUP RESTRICT MOUNT;
DROP DATABASE;