~ Tiez Notes ~

Pada prinsipnya untuk membuat Stored Procedure dengan 1 atau lebih parameter sama saja. Untuk lebih jelasnya kita coba untuk membuat Stored Procedure nya terlebih dahulu.

CREATE PROCEDURE `usp_testCustomer2`(
   out cu1 varchar(18),
   out cu2 varchar(18)
)
BEGIN
   select min(customer_id) as minimum, max(customer_id) as maksimum
   into cu1, cu2
   from customer;
END

Lalu untuk menjalankan Stored Procedured tersebut sebagai berikut :

set @cust1 = '123';
set @cust2 = 'phonep';
CALL usp_testCustomer2(@cust1, @cust2);
select @cust1, @cust2;

Berikut ini catatan untuk membuat Stored Procedure di MySQL dan memanggil Stored Procedured tersebut.

Pertama kita buat Stored Procedure nya sbb :

CREATE PROCEDURE `usp_testCustomer`(IN custId varchar(18))
BEGIN
  select customer_id, customer_name, count(*) as jumlah
  from customer
  where customer_id = custID
  group by customer_id, customer_name;
END

Setelah itu kita eksekusi Stored Procedure tersebut dengan cara :

set @customer = '01080800051139';
call usp_testCustomer(@customer);

Output yang dihasilkan sbb :

customer_id               customer_name                          jumlah
01080800051139     R. HESTI ENDANG SIREGAR             1

Bila kita ingin parameter yang di-input juga berfungsi sebagai parameter untuk output, caranya adalah sebagai berikut.

Pertama kita buat Stored Procedure nya terlebih dahulu :

CREATE PROCEDURE `usp_testCustomer1`(INOUT custId varchar(18))
BEGIN
  DECLARE custId1 VARCHAR(18) DEFAULT custId;

  select customer_id, customer_name, count(*) as jumlah
  from customer
  where customer_id = custID
  group by customer_id, customer_name;

  set custId = custId1;
END

Setelah itu kita jalankan Stored Procedure tersebut :

set @customer = '01080800051139';
call usp_testCustomer1(@customer);
select @customer;

Output yang dihasilkan  sbb :

customer_id
01080800051139

Beberapa fungsi php yang berhubungan dengan url adalah sebagai berikut :

<?php
   echo "Mendapatkan path dari file yang aktif" . "<br>";
   echo $PHP_SELF . "<br><br>";

   $url=parse_url("http://localhost/ford/edit_employee.php?kode_employee=5");

   echo "Mendapatkan url scheme" . "<br>";
   echo $url[scheme] . "<br><br>";

   echo "Mendapatkan url host" . "<br>";
   echo $url[host] . "<br><br>";

   echo "Mendapatkan url path" . "<br>";
   echo $url[path] . "<br><br>";

   echo "Mendapatkan url query" . "<br>";
   echo $url[query] . "<br><br>";

   echo "Mendapatkan nama dari file yang aktif" . "<br>";
   echo basename($PHP_SELF) . "<br><br>";

   echo "Mendapatkan referer" . "<br>";
   echo $HTT

Output yang dihasilkan adalah sbb :

Mendapatkan path dari file yang aktif
/latihan/alamat_url_1.php

Mendapatkan url scheme
http

Mendapatkan url host
localhost

Mendapatkan url path
/ford/edit_employee.php

Mendapatkan url query
kode_employee=5

Mendapatkan nama dari file yang aktif
alamat_url_1.php

Mendapatkan referer

str_replace : menghapus semua karakter spasi

syntax :
str_replace("string_expression1", "string_expression2", "string_expression3")

Contoh :

<?php
   $text = "Jakarta Adalah Ibukota Indonesia";
   $hasil = str_replace(" ","", $text);

   $rawstring = "Your replacement is a pleasure to have!";
   $malestr = str_replace("replacement", "son", $rawstring);

   echo "Sebelum = " . $text . "<br>";
   echo "Sesudah = " . $hasil . "<br><br>";

   echo "Sebelum : Son = " . $rawstring . "<br>";
   echo "Sesudah : Son = " . $malestr . "<br>";
?>

Output :

Sebelum = Jakarta Adalah Ibukota Indonesia
Sesudah = JakartaAdalahIbukotaIndonesia

Sebelum : Son = Your replacement is a pleasure to have!
Sesudah : Son = Your son is a pleasure to have!

-- Data Position utk materna
select cu.customer_id, cu.customer_name, cu.customer_phone, pl.product_id, ch.child_expected_birthday, ca.calling_strike_id
from customer cu, calling ca, product_log pl, children ch
where cu.customer_id = ca.customer_id
          and ca.validation_id = '1'
          and cu.customer_id = pl.customer_id
          and cu.customer_id = ch.customer_id
          and pl.product_id = 'materna'
          and pl.consumed_by = cu.customer_id
          and cu.followup_status = '1'
          and cu.loyalty1_by <> " "
          -- and ca.calling_strike_id <> '12'       
          and ca.calling_result_id = '1'
          and ca.calling_for_id = '3'
          -- and ca.calling_strike_id in ('4','5','10','11','13','14','15')
          and (select ca2.calling_strike_id from calling ca2 where ca2.customer_id = cu.customer_id and ca2.phone_date = (select max(ca1.phone_date) from calling ca1 where ca1.customer_id = cu.customer_id) limit 1) not in ('12', '17')
          and ch.child_expected_birthday <> '0000-00-00'
          and cu.submit_date <= curdate()
          -- and cu.customer_id = '01080800030023'
group by cu.customer_id
order by cu.customer_id
-- limit 100

-- Data Position Anak dgn Produk
-- select aa.umur_anak, aa.product_id, count(aa.umur_anak) as jumlah
-- from
-- (
select ch.child_id, cu.customer_id, ch.child_name, ch.child_birthday,
         -- PERIOD_DIFF(DATE_FORMAT('2010-11-30','%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) as umur_anak,
         ((datediff(curdate(), ch.child_birthday)) / 30)  as umur_anak,
         -- (case when PERIOD_DIFF(DATE_FORMAT(curdate(),'%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) between 0 and 6 then 'IF1'
         --         when PERIOD_DIFF(DATE_FORMAT(curdate(),'%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) between 7 and 12 then 'IF2'
         --         when PERIOD_DIFF(DATE_FORMAT(curdate(),'%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) between 13 and 36 then 'ESS3'
         -- end) as product_id,
         -- (case when ((datediff(curdate(), ch.child_birthday)) / 30)  >= 0 and ((datediff(curdate(), ch.child_birthday)) / 30)  <= 6  then 'IF1'
         --          when ((datediff(curdate(), ch.child_birthday)) / 30)  > 6   and ((datediff(curdate(), ch.child_birthday)) / 30)  <= 12 then 'IF2'
         --          when ((datediff(curdate(), ch.child_birthday)) / 30)  > 12 and ((datediff(curdate(), ch.child_birthday)) / 30)  <= 36 then 'ESS3'
         --          when ((datediff(curdate(), ch.child_birthday)) / 30)  > 36 then 'ESS4'
         -- end) as product_id,
         pl.product_id,
         cl.calling_result_id,
         cu.customer_address, cu.customer_phone, cu.customer_phone_2
from children ch
inner join  customer cu  on ch.customer_id = cu.customer_id
inner join  calling cl on cl.customer_id = cu.customer_id
inner join product_log pl on pl.customer_id = cu.customer_id
where ((ch.child_birthday >= (date_sub(curdate(), interval 37 month)))
and (ch.child_birthday <= (date_sub(curdate(), interval 0 month))))
and (ch.child_birthday<> '0000-00-00' or ch.child_birthday like '0001-00-00')
and cl.validation_id='1'
and cu.followup_status = '1'
and cl.calling_result_id = '1'
and pl.consumed_by = ch.child_id
and (select ca2.calling_strike_id from calling ca2 where ca2.customer_id = cu.customer_id and ca2.phone_date = (select max(ca1.phone_date) from calling ca1 where ca1.customer_id = cu.customer_id) limit 1) not in ('6', '12', '16')
and pl.product_id in ('if1', 'if2', 'ess3', 'ess4')
group by ch.child_id
order by ch.child_id, ch.child_birthday
-- limit 60000,60000
-- ) aa
-- group by aa.umur_anak, aa.product_id
-- order by aa.umur_anak

strcasecmp : membandingkan 2 string yang tidak case sensitive

syntax :
strcasecmp (string $str1, string $str2)

akan menghasilkan < 0 jika str1 < str2
                                     > 0 jika str1 > str2
                                     = 0 jika data sama

Contoh :

<?php
$kata1 = "Jakarta";
$kata2 = "jaKarta";

if (strcasecmp($kata1,$kata2) == 0)
{
    echo "Kata antara " . $kata1 . " dan " . $kata2 . " sama ...";
}
else
{
    echo "Kata tidak sama ...";
}
?>

Output yang dihasilkan adalah sbb :

Kata antara Jakarta dan jaKarta sama ...