Need help in MySQL and PHP

[EM @MAJ.com]

I have a field name contentdata with a hex data of AA560D72DA4AB20AAA using a type char.

In MySQL, I can run this command:
SELECT SUBSTR(hex(contentdata),15,2) FROM table1

the output is:
0A

How can I code "SELECT SUBSTR(hex(contentdata),15,2) FROM table1" in PHP so I can bring the same result.

MySQL create php code gave me this recommendation but I can't show the value.
$sql = "SELECT SUBSTR(hex(payload),15,2) FROM device_status LIMIT"

Let me know what I'm missing here?
Thanks in advance.

Em

 

[katherine]

$sql = "SELECT SUBSTR(hex(payload),15,2) FROM table1 LIMIT"
table1 is the table name
LIMIT is used to fetch only the N first records, optionally from an offset. It's for pagination.
For example LIMIT 10 or LIMIT 10,10. LIMIT must have at least one argument or remove it from the autogenerated code if you have no need for it.
This statement is not correct and I think you have error reporting turned off in PHP.
You will need a few more lines of code to display the results. Make the connection to the dabase, execute the SQL, then loop on the results etc.

 

[EM @MAJ.com]

I've got the solution, add "AS hex_value" courtesy by Katherine.

 

[katherine]

And what is the error ?

It would be a good idea to use aliases to rename the fields resulting from expressions, for example rename 'SUBSTR(hex(contentdata),15,2)' to hex_value or something else

SELECT SUBSTR( hex( contentdata ) , 15, 2 ) AS hex_value
FROM table1
ORDER BY id DESC 
LIMIT 1

Then use that alias in your PHP code too. Use aliases that make sense and accurately describe the result.

 

[EM @MAJ.com]

Hi Katherine,

The addition of using alias from the query expression e.g. "AS hex_value" to my php codes work for me. Excellent advice!

Do you know how to save a '00001111' data? php/mysql is only saving 1111 removing the first four 0s.

thank you,
em

 

[katherine]

You mean retaining the leading zeros in an integer value ? Look at the zerofill property in field definition.

 

[EM @MAJ.com]

That's what I thought, using the zerofill but my data is coming from HEX to binary in php script.

Hex data is 0A, when I use the following code.

$hexadecimal = '$data';
echo base_convert($data, 16, 2);
echo "<br />";

the output is 1010 instead of a 00001010

another examples.
if the hex data is AA
the out is 10101010 <-- this correct.

if the hex data is 0B
the output is 1011 <-- incorrect, it should be 00001011

thanks in advance.

 

[EM @MAJ.com]

do you think this will work? I will try this one.

$formatted_value = sprintf("%08d", $data);

 

[katherine]

You can use the str_pad function in PHP to display the value with leading zeros.
It depends on your needs:
do you need to store the value as-is
or you just want it to be formatted for display

I'm not sure what these hex values stand for and what you are trying to achieve.

But mysql has binary fields too:
http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html

 

[EM @MAJ.com]

I need to store the value as-is e.g. 00001010 not 1010

As alternative approach, I created a $dummy = 100000000 as a global variables, then add '$data', so I can get a consistent value.

from
1010

new output after the global $100000000
100001010
100001100

I thought there is an easy way to retain the leading zero's.

Thanks.

 

[katherine]

I need to store the value as-is e.g. 00001010 not 1010

Where do you store the binary value ?
I understand you have an hex value stored and it is converted to binary. Do you need to store the binary value as well ?
If the reference value is the hex, better to keep it like that and use the str_pad function to display the binary like you want.

 

[EM @MAJ.com]

Hi Katherine,

Thank you very much for taking the time to answer my questions.

Cheers to you,
em @ king.net