Mysql unique field

 

Query Select Left Join with sum

 a

select sia_kelas.*, sia_dagur.idx, sia_dagur.nama,lk,pr 

      from sia_kelas

      left join (select kelas,

      SUM(CASE WHEN kelamin='Laki-laki' THEN 1 ELSE 0 END) as lk,

      SUM(CASE WHEN kelamin='Perempuan' THEN 1 ELSE 0 END) as pr

      from sia_dasis where owner='$owner->owner' and kelas!='' group by kelas) dasis

      on sia_kelas.kelas=dasis.kelas

      LEFT JOIN sia_dagur 

      ON sia_dagur.idx=sia_kelas.wali

      where sia_kelas.owner='$owner->owner' 

      and sia_kelas.ta='$tahun'

      order by nmr ASC

b

Show Column Name and Columns comment from mysql table

SELECT COLUMN_NAME, COLUMN_COMMENT
FROM information_schema.`COLUMNS`
WHERE table_name = 'des_daduk'

Left Join With Select

select sia_dasis.*,tax from sia_dasis left join (select sum(tax) as tax,b from sia_bill_list where owner='$owner->owner' group by b) tagih on sia_dasis.b = tagih.b where sia_dasis.owner='$owner->owner' and sia_dasis.b like '$a->b%' and sia_dasis.c like '%$a->c%' order by $urut ASC limit 50

SELECT b,c,d,f,g,h,nokk,kk

from des_daduk

left join

(select d as nokk,c as kk from des_daduk where q='1') tbkk

on des_daduk.d=tbkk.nokk

where des_daduk.c like '%masturi%'

Array

Convert Assosiative Array to Numeric Array

 <? // $r=array_values($r)?>
 <? $i=2?>

Pukul<? f_txt($rw,"ljam",$rw->ljam);?> function f_txt($rw,$field,$default){ global $db; echo "<input type='text' name='a[$field]' value='"; if($rw->$field)echo $rw->$field;else echo $default; echo "'>"; } Alasan Pindah<? f_slc($rw,"kode",(object) array( '1' => 'Pekerjaan', '2' => 'Pendidikan', '3' => 'Keamanan', '4' => 'Kesehatan', '5' => 'Perumahan', '6' => 'Keluarga', '7' => 'Lainya'));?> function f_slc($rw,$field,$arr){ echo "<select name='a[$field]' id='a[$field]'>"; foreach ($arr as $key => $value) { if ($rw->$field == $key)$selected=' selected'; else $selected=''; echo "<option value='$key' $selected>$value</option>"; } echo "</select>"; }

mysql_fetch_field

<?php
$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$conn) {
    die('Could not connect: ' . mysql_error());
}mysql_select_db('database');$result = mysql_query('select * from table');
if (!$result) {
    die('Query failed: ' . mysql_error());
}/* get column metadata */$i = 0;
while ($i < mysql_num_fields($result)) {
    echo "Information for column $i:<br />\n";
    $meta = mysql_fetch_field($result, $i);
    if (!$meta) {
        echo "No information available<br />\n";
    }
    echo "<pre>
blob:         $meta->blob
max_length:   $meta->max_length
multiple_key: $meta->multiple_key
name:         $meta->name
not_null:     $meta->not_null
numeric:      $meta->numeric
primary_key:  $meta->primary_key
table:        $meta->table
type:         $meta->type
unique_key:   $meta->unique_key
unsigned:     $meta->unsigned
zerofill:     $meta->zerofill
</pre>";
    $i++;
}mysql_free_result($result);?>

source: http://php.net/manual/en/function.mysql-num-fields.php

Multiple Count Statements With Different Criteria

Table:

Code:  Status  Cancel_Date
-----------------------------------
AAA    X       2012-02-01
AAA
BBB    X       2012-02-01
AAA    D       2012-01-01
AAA
BBB    
BBB    D       2012-02-01
BBB    X       2012-01-01

Example result (based on the above data):

Code:  TotalNotXorD     TotalXorD
------------------------------------
AAA    2                1
BBB    1                2

in SQL SERVER

SELECT  Code,
        SUM(CASE WHEN status NOT IN ('X','D') OR status IS NULL THEN 1 ELSE 0 END) TotalNotXorD,
        SUM(CASE WHEN status IN ('X','D') AND cancel_date >= '2012-02-01' THEN 1 ELSE 0 END) TotalXorD  
FROM    tableName
GROUP   BY Code

in MS ACCESS

 SELECT
    Sum(IIf(status<>'X' and status<>'D',1,0)) AS TotalNotXorD,
    Sum(IIf(status='X' and status='D',1,0)) AS TotalXorD
FROM tableName
GROUP   BY Code