-1

One of my MySQL columns contains a hyphen. While the query works fine when tested through a mysql browser, it returns the key rather than the value when using using php mysqli_fetch_array($result).

The query I am running looks like this:

if($test_base_name==='isolation-mer') {
    $test_name="`".$ds_channel[$i]."_isolation-mer`";
}
else {
    $test_name=$ds_channel[$i]."_isolation-mer";
}
$query="select serial_number, $test_name from table_name";
if($result=mysqli_query($dbc,$query)) {
    while($row=mysqli_fetch_arrya($result) {
       $sid=$row['serial_number'];
       $pass_fail=$row[$test_name];
       ...

The serial number is retrieved successfully. However, the $pass_fail variable always retrieves nothing. The test name is embedded with quotes. Even if I hardwire the key name within all kinds of quotes, it always retrieves the key and not the value.

This is an old version of PHP and I wonder if that is the issue. Perl has no issues with this.

β.εηοιτ.βε
  • 33,893
  • 13
  • 69
  • 83
user3470332
  • 99
  • 1
  • 8
  • *"The test name is embedded with quotes."* You need show us the actual query's syntax here. What exactly do you mean by quotes? Regular ones like `'`, or a tick `\``? Two different animals here. – Funk Forty Niner Feb 18 '18 at 21:38
  • *"This is an old version of PHP and I wonder if that is the issue."* - I don't think it's "dinosaur" old; not if you're running this under the mysqli_ api which you've posted just the one function. – Funk Forty Niner Feb 18 '18 at 21:40
  • If you are present, I'd like to get some answers from my asking for clarification. However, if you left the question, then don't be surprised if your question was closed with a duplicate or more. You then will need to consult those and apply it to your code. If that still fails (which I tend to doubt), update your post with the new code under the old one, and include any errors, given you are with error reporting and `mysqli_error($connection)` to the query. – Funk Forty Niner Feb 18 '18 at 21:44

2 Answers2

0

Using ...

select serial_number, 'isolation-noise' from table_name

means that 'isolation-noise' is a literal value which is selected and will return a result set of (e.g.)

1234,'isolation-noise'
1235,'isolation-noise'

whereas...

select serial_number, `isolation-noise` from table_name

using backticks, will return the actual value of the column.

Update: When doing the assignment - you definitely shouldn't have backticks in the name of the field, so 

$test_name=$ds_channel[$i]."_isolation-mer";
$query="select serial_number, `$test_name` from table_name";
if($result=mysqli_query($dbc,$query)) {
    while($row=mysqli_fetch_arrya($result) {
       $sid=$row['serial_number'];
       $pass_fail=$row[$test_name];

So this always puts backticks round column name in the select statement and uses the raw name in fetching the data from the result set.

Nigel Ren
  • 56,122
  • 11
  • 43
  • 55
  • I've tried all types:single,double and backquote. Also 'isolation-noise' is a column in the table. Running this query from the browser works fine. I even tried using an Alias for the column name. – user3470332 Feb 18 '18 at 22:24
  • Can you add the code where you set $test and where you build the sql statement – Nigel Ren Feb 18 '18 at 22:35
  • I have another module that I wrote in Perl. It works fine with ticks around the test name. I'm probably not seeing something trivial. – user3470332 Feb 18 '18 at 22:45
  • if($test_base_name==='isolation-mer') { $test_name="`".$ds_channel[$i]."_isolation-mer`"; } – user3470332 Feb 18 '18 at 22:51
  • As we can't see your full code, we probably can't help either. Although you should probably need quotes at $row['serial_number'] – Nigel Ren Feb 18 '18 at 22:51
  • I added the previous code lines. Note that quotes are optional if the key is optional when there are no special characters. It's probably something trivial that I cannot see. Thanks. – user3470332 Feb 18 '18 at 23:10
  • When you do `$pass_fail=$row[$test_name];` you definitely shouldn't have any form of quote in the `$test_name`, so try adding extra code above to your assignment (bottom of answer). – Nigel Ren Feb 19 '18 at 07:48
0

PHP reads the below code as a variable and not as the name of your database column:

$pass_fail=$row[$test_name];

The below code should work:

$pass_fail=$row['$test_name'];
Tom
  • 324
  • 4
  • 11
  • The quotes are embedded within the variable. Placing single quotes around the variable name will not allow interpolation of the variable. So I think your example is wrong. – user3470332 Feb 18 '18 at 22:41
  • The contents of the $test_name variable contain the column name.This is not the problem. Thanks. – user3470332 Feb 18 '18 at 22:48