How to substring WHERE in SELECT php mysql

Question

I have a column kd_barang2 as an id in my table. The values is like this: 010000101.

I want to select the first 7 number (0100001) and then show it.

The script to select is like this:

$queryalat1="select * from tb_master
  where kd_barang2 = '".$data1['kd_barang2']."'
  && jenis_barang='alat habis pakai'
  Group by merk_barang";

and then I tried this kind of code

$queryalat1="select * from tb_master
  where SUBSTRING(kd_barang2,0,7) = '".substr($data1['kd_barang2'],0,7)."'
  && jenis_barang='alat habis pakai'
  Group by merk_barang";

but it failed, because it didn't show any data in my table.

Try $queryalat1="select * from tb_master where kd_barang2 = '".substr($data1['kd_barang2'],0,7)."' && jenis_barang = 'alat habis pakai' Group by merk_barang"; — Narendrasingh Sisodia
– Narendrasingh Sisodia, Commented Apr 14, 2015 at 9:07
NEVER concatenate strings into SQL queries! Learn how to use parameters. — Sami Kuhmonen
– Sami Kuhmonen, Commented Apr 14, 2015 at 9:08
Is the ID field numeric or textual? If the ID field is numeric, substring will return nothing. — Sami Kuhmonen
– Sami Kuhmonen, Commented Apr 14, 2015 at 9:11
not true, php will happily substr a number and give the desired result: echo substr(12345678, 1, 5) == 23456 — pala_
– pala_, Commented Apr 14, 2015 at 9:12
@pala_ And he's using also MySQL's SUBSTRING, if you read the actual query. Actually the problem is with base: PHP is zero-based, MySQL one-based, so my comment was wrong. — Sami Kuhmonen
– Sami Kuhmonen, Commented Apr 14, 2015 at 9:16

Sami Kuhmonen · Accepted Answer · 2015-04-14 09:17:17Z

3

You're using MySQL's SUBSTRING function with the wrong start value. In PHP substr() will start with zero, but in MySQL it starts with 1. So you need to use SUBSTRING(kd_barang2, 1, 7)

answered Apr 14, 2015 at 9:17

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ujang Hardman Over a year ago

ohh i see, your solution really work.. :D I didn't know mysql's substring start from 1... -_- Thank you so much, guess i have to learn more...

Soorajlal K G · Accepted Answer · 2015-04-14 09:14:38Z

2

Try

$queryalat1="select * from tb_master where LEFT(kd_barang2,8) = '".substr($data1['kd_barang2'],0,7)."' && jenis_barang='alat habis pakai' Group by merk_barang";

answered Apr 14, 2015 at 9:14

Soorajlal K G

7869 silver badges20 bronze badges

1 Comment

Barmar Over a year ago

It should be LEFT(kd_barang2, 7)

ajit · Accepted Answer · 2015-04-14 09:35:20Z

2

Try this..
$queryalat1="select * from tb_master
where left(kd_barang2,7) = '".substr($data1['kd_barang2'],0,7)."'
&& jenis_barang='alat habis pakai'
Group by merk_barang";

answered Apr 14, 2015 at 9:35

ajit

866 bronze badges

Collectives™ on Stack Overflow

How to substring WHERE in SELECT php mysql

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related