5 Replies - 1134 Views - Last Post: 30 September 2011 - 08:31 AM

#1 shabbaranks   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 55
  • Joined: 08-August 11

Is it possible to get php/mysql query to return fields

Posted 30 September 2011 - 02:10 AM

Hi,

Im using a perticular mysql table as a list, so there isnt any data stored within this perticular table. Im looking to list the field description from each row and display it in a drop down list on a page. Could anyone tell me if this is possible please?

Thanks
:)
Is This A Good Question/Topic? 0
  • +

Replies To: Is it possible to get php/mysql query to return fields

#2 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Is it possible to get php/mysql query to return fields

Posted 30 September 2011 - 03:39 AM

Hey.

You can use the SHOW COLUMNS command to get details about the columns in a table.
Was This Post Helpful? 1
  • +
  • -

#3 shabbaranks   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 55
  • Joined: 08-August 11

Re: Is it possible to get php/mysql query to return fields

Posted 30 September 2011 - 05:56 AM

Great, I understand that. The shows all the columns within that table. Would I then echo the result of the first column i.e "the field" as this is what I am trying to populate within the drop down list?

Thanks
:)
Was This Post Helpful? 0
  • +
  • -

#4 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Is it possible to get php/mysql query to return fields

Posted 30 September 2011 - 06:34 AM

Yes. Just execute the query as you would a normal SELECT query and grab the "Field" field (or whichever field you need) to echo into your <option> elements.

For example, using PDO:
<?php
try 
{
    $db = new PDO('mysql:host=localhost;dbname=test', 'usr', 'pwd');
    
    $sql = "SHOW COLUMNS FROM `example_table`";
    $stmt = $db->prepare($sql);
    if ($stmt->execute())
    {
        echo "<select name=\"field_select\">\n";
        while ($row = $stmt->fetchObject())
        {
            echo "\t<option>{$row->Field} [{$row->Type}]</option>\n";
        }
        echo "</select>\n";
    }
    else
    {
        $ei = $stmt->errorInfo();
        trigger_error('Failed to execute query: ' . $ei[2], E_USER_ERROR);
    }
}
catch (PDOException $e) 
{
    trigger_error('PDO Error: ' . $e->getMessage(), E_USER_ERROR);
}
?>


Was This Post Helpful? 0
  • +
  • -

#5 shabbaranks   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 55
  • Joined: 08-August 11

Re: Is it possible to get php/mysql query to return fields

Posted 30 September 2011 - 07:46 AM

Apologies, Im totally lost with all this. I have my table which has 3 columns - id, field and data. Im trying to get the data from the field column on each row. Ive amended the example you gave me above but it doesnt seem to work - what am I doing wrong? Thanks

<?php
$username="username";
$password="pass";
$database="database";

mysql_connect('localhost',$username,$password) or trigger_error( mysql_error() . " Unable to connect to database");
mysql_select_db($database) or trigger_error( mysql_error() . " Unable to select database");
?>  


<?php
    $sql = "SHOW COLUMNS FROM `WorkActivity`";
    $stmt = $db->prepare($sql);
    if ($stmt->execute())
    {
        echo "<select name=\"field_select\">\n";
        while ($row = $stmt->fetchObject())
        {
            echo "\t<option>{$row->Field} [{$row->Type}]</option>\n";
        }
        echo "</select>\n";
    }
    else
    {
        $ei = $stmt->errorInfo();
        trigger_error('Failed to execute query: ' . $ei[2], E_USER_ERROR);
    }
}
catch (PDOException $e) 
{
    trigger_error('PDO Error: ' . $e->getMessage(), E_USER_ERROR);
}
?>



Was This Post Helpful? 0
  • +
  • -

#6 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Is it possible to get php/mysql query to return fields

Posted 30 September 2011 - 08:31 AM

First, regarding the code. You are mixing together the old mysql_* functions with the PDO example I posted. You can't do that; you'll want to pick either one or the other. I strongly recommend PDO!

View Postshabbaranks, on 30 September 2011 - 02:46 PM, said:

I have my table which has 3 columns - id, field and data. Im trying to get the data from the field column on each row.

I'm confused. I tough you said there wasn't any data in the table?

What I've been talking about is showing details about the structure of the table, not showing data from within the table. If you want to do the latter you can do that with a normal SELECT query.

To update my previous example to do that, all you would have to do is replace the SQL query:
$sql = "SELECT `field` FROM `example_table`";

And the part that echoes the data into the <option> box.
echo "\t<option>{$row->field}</option>\n";

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1