Get column names from a mySql query even when no results are found

Question

I'm using laravel to execute dynamic query strings sent by the front-end.

This is a dev environment used to speed up a development process.

Since I don't know what the queries will look like, I can't really use laravel query builder.

So this

$users = DB::table('look_up_lists')
    ->leftJoin('look_up_list_values', 'look_up_lists.id', '=', 'look_up_list_values.look_up_list_id')
    ->where('look_up_list_values.id', '=', 99)
    ->get();

WIll actually look like this

select * from `look_up_lists` left join `look_up_list_values` on `look_up_lists`.`id` = `look_up_list_values`.`look_up_list_id` where `look_up_list_values`.`id` = 99

In PHPmyAdmin, even when no results are thrown, you can see the columns involved.

This is exactly what I need, and I can't figure out a way to do this. I managed to get the columns involved as long as there are results, but not when I don't have any.

I tried this:

 $sth =  DB::getPdo()->prepare($query);
    $sth->execute();
    return  $sth->fetchALl(\PDO::FETCH_OBJ);

again, works as long as there are results.

I'm stuck for days with this, can anyone help?

How are these queries being created? Are the tables involved known or can they be derived from the query? — Peppermintology, Jun 27 '21 at 17:18

Heinz · Answer 1 · 2021-06-27T17:36:05.557

In case the result set is empty, I added a count(*) as ignore this will force an output of just one line and give you the column names, or you dump the empty result in a temp table then you can use the simple select on teh tmep table. Good luck.

      <?php
            include_once "../include/connect.pdo.php";
            
            $q = "select * from ttt";
            $xx = $connect_pdo->prepare($q);
            $xx->execute([]);
            $rows = $xx->fetchAll(PDO::FETCH_OBJ);
            $n = count($rows);
            
            echo var_dump($rows);
            
           $q = "select *,count(*) as ingnor from ttt";
           $xx = $connect_pdo->prepare($q);
           $xx->execute([]);
           $rows = $xx->fetchAll(PDO::FETCH_OBJ);
           $n = count($rows);
           echo "<p>". var_dump($rows);


// dump unknown query in temp-table then use the simple select with count

        $q = "CREATE TEMPORARY TABLE IF NOT EXISTS t1temp as (select * from ttt)";
        $xx = $connect_pdo->prepare($q);
        $xx->execute([]);
        $q = "select *,count(*) from t1temp";
        $xx = $connect_pdo->prepare($q);
        $xx->execute([]);
        $rows = $xx->fetchAll(PDO::FETCH_OBJ);
        $n = count($rows);
        $q = "drop temporary table t1temp";
        $xx = $connect_pdo->prepare($q);
        $xx->execute([]);
        echo "<p>" . var_dump($rows);
   

// The output
 
        F:\xampp-htdocs\project_template\user\test.php:11:
array (size=0)
  empty
F:\xampp-htdocs\project_template\user\test.php:24:
array (size=1)
  0 => 
    object(stdClass)[3]
      public 'id' => null
      public 'i0' => null
      public 'i1' => null
      public 't1' => null
      public 'c1' => null
      public 'e1' => null
      public 'u1' => null
      public 's1' => null
      public 'x1' => null
      public 'rt' => null
      public 'i2' => null
  public 'count(*)' => string '0' (length=1)

Hey! thanks for the answer, unfortunately in my case it wouldn't work, since I never know the structure of the query that the front-end will send, especially with complex queries. — Santiago A. DeHeza, Jun 27 '21 at 16:53
In my answer I added an other way to see the column names wne no result set. — Heinz, Jun 27 '21 at 17:37
Interesting option to create a temporary table for every query! Anyone, how do you assess the performance of this approach? — Ifedi Okonkwo, May 08 '23 at 16:05
As for the trick of adding "count(*) as ignore" to the query, I guess that would automatically group the result set; which means you must then go back and provide a "GROUP BY" clause. Right? — Ifedi Okonkwo, May 08 '23 at 16:07

score -1 · Answer 2 · answered Jun 21 '23 at 16:32

-1

Check out PDOStatement::getColumnMeta

answered Jun 21 '23 at 16:32

ragol

527
3
11

Get column names from a mySql query even when no results are found

2 Answers2