Get the column names and field values for one record using MySqli

Question

I am trying to get the column names and field values from a table for ONE record. If a column name was 'sellers' and the field value was 'Bob', the desired output would be: seller Bob

The output will actually be used for a script like this: $fields['sellers']->setValue($sellers);

Where 'sellers' is the column name and $sellers is the field value.

There are dozens of columns in the table.

The script below only outputs the column names - not the field values.

Any help is appreciated.

$sql = "SELECT * FROM tbl_pdfform WHERE trans_id = '$trans_id' ";
$sql_result = mysqli_query($db, $sql);

for($i = 0; $i < mysqli_num_fields($sql_result); $i++) {
    $field_info = mysqli_fetch_field($sql_result);
    $col = "{$field_info->name}";
    echo $col . ' ';
         while ($row = mysqli_fetch_array($sql_result)) {
            $data = $row[$col];
            echo $data."<br>";
    }
}

Every time you call `mysqli_fetch_array()` it tries to get the next row of results. Since the query only returns one row, all the subsequent calls return `false`. — Barmar, Feb 04 '16 at 21:34

WheatBeak · Accepted Answer · 2016-02-04T21:36:39.057

7

Not sure if I exactly understand what you're trying to do but would using mysqli_fetch_assoc() and a foreach loop give your desired result?

$sql = "SELECT * FROM tbl_pdfform WHERE trans_id = '$trans_id' ";
$sql_result = mysqli_query($db, $sql);

   $row = mysqli_fetch_assoc($sql_result);
   foreach($row as $column => $value) {
          echo $column . " " . $value;
   }

edited Feb 04 '16 at 21:36

answered Feb 04 '16 at 21:29

WheatBeak

1,036
6
12

Simple solution gives just what I needed. Thank you. – WGS Feb 05 '16 at 12:19

score 1 · Answer 2 · answered Feb 04 '16 at 21:37

You should just call mysqli_fetch_array() once, not in a loop for each column. Because every time you call it, it moves to the next row of results, it doesn't re-fetch the old row. Since you only have one row of results, the repeated calls just return false.

$row = mysqli_fetch_array($sql_result);
for($i = 0; $i < mysqli_num_fields($sql_result); $i++) {
    $field_info = mysqli_fetch_field($sql_result);
    $col = "{$field_info->name}";
    echo $col . ' ' . $row[$col];
}

But there isn't really any need to use mysqli_fetch_field() to get the field names. Since $row is an associative array, the field names are just the keys of the array. However, it would be better to use mysqli_fetch_assoc(), because mysqli_fetch_array() returns an array that contains both named and numbered elements; mysqli_fetch_assoc() just returns the named elements. So the answer by WheatBeak is how most would do what you want.

score 0 · Answer 3 · answered Feb 04 '16 at 21:22

0

I think that there's a problem with the interpolation.

$sql = "SELECT * FROM tbl_pdfform WHERE trans_id = '$trans_id' ";

Using variables between apostrophes sends the string "$trans_id" in the SQL query instead of the value which $trans_id contains. Try this:

$sql = "SELECT * FROM tbl_pdfform WHERE trans_id = '" . $trans_id . "' ";

answered Feb 04 '16 at 21:22

CupRacer

149
7

Even if I manually add the trans_id value I get the same results. – WGS Feb 04 '16 at 21:26
1

Variables are expanded inside double quotes. The single quotes inside the double quotes don't change that. – Barmar Feb 04 '16 at 21:31
@Barmar In some rare cases, they do. Seen it quite a few times. – Funk Forty Niner Feb 04 '16 at 21:51
@Fred-ii- I'm certain you're mistaken. – Barmar Feb 04 '16 at 21:52
@Barmar I'm afraid I'm not. Although I can't give you an exact example, have seen quite a few questions on Stack, where the OP had to use an alternate method of quoting. – Funk Forty Niner Feb 04 '16 at 21:54
1

@Fred-ii- They were probably doing something else wrong, like using a complex variable without putting it in curly braces. Read the PHP documentation on strings http://php.net/manual/en/language.types.string.php or http://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php – Barmar Feb 04 '16 at 22:00
I have always used like this: $sql = "SELECT * FROM tbl_pdfform WHERE trans_id = '$trans_id' "; and it works for me. – WGS Feb 05 '16 at 14:25

Get the column names and field values for one record using MySqli

3 Answers3