Member Avatar for awo

I just started using both PHP and MYSQL and i have this SQL statement that get record from three tables ie

Select name,age, datejoind, active, avrage_time_in,avreagework_duration from Personalrecord Union Activestaff Union Timetable

but i need to be able to get a the list of Returnd field name into a PHP array so that i can daynamically load into a CSV file. I know i would have used show columns but that works only with a single table so i hope any of you web Geeks could help me as i am helpless and i am to present a term paper and this is the only thing delaying me

  • 4 Contributors
  • 6 Replies
  • 195 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

You actually want the column names that the SELECT returns, so the cleanest approach is to control those names in the query (use AS aliases) and then read the resultset metadata in PHP. That is exactly what was getting at — give predictable labels in your SELECT so the CSV header is reliable. (dev.mysql.com)

Use a modern API (mysqli or PDO) rather than the old mysql_* calls. With mysqli you can read the field metadata and then write a CSV header with fputcsv. Example (mysqli OOP style):

$mysqli = new mysqli('localhost','user','pass','db');
if ($res = $mysqli->query($sql)) {
    $fields = $res->fetch_fields();
    $headers = array();
    foreach ($fields as $f) $headers[] = $f->name;

    $out = fopen('php://output','w');
    fputcsv($out, $headers);
    while ($row = $res->fetch_row()) {
        fputcsv($out, $row);
    }
    fclose($out);
    $res->free();
}

See the mysqli field-metadata function and PHP CSV writer for details: mysqli_result::fetch_fields and fputcsv. (php.net)

If you prefer PDO you can use PDOStatement::getColumnMeta to read names, but note some drivers/versions differ in behavior — test it in your environment. Also, do not rely on the deprecated ext/mysql functions (mysql_*) shown in older answers — migrate to mysqli or PDO. (php.net)

Practical checklist derived from the thread: 1) explicitly alias each selected expression (AS) so headers are meaningful; 2) avoid duplicate column names from joins; 3) if you follow and infer headers from the first associative row, handle empty resultsets first; 4) set the connection charset (utf8mb4) to avoid multibyte/length surprises; 5) use fputcsv to handle quoting/escaping for CSV output. These steps make the CSV export robust and maintainable. (php.net)

Recommended Answers

All 6 Replies

Member Avatar for zortec

You can use mysql_fetch_array() or mysqli_fetch_array() to return the result as an array.

commented: Time for you to be green again. :) +9
Member Avatar for Member #120589

UNION means that all the returned fields in the tables must be the same, e.g.
TABLE 1
id name description
TABLE 2
id name description
TABLE 3
id name description

If they are called something different, you'll have to give them aliases with the 'AS' keyword.

Edited by Member #120589 because: n/a
Member Avatar for awo

UNION means that all the returned fields in the tables must be the same, e.g.
TABLE 1
id name description
TABLE 2
id name description
TABLE 3
id name description

If they are called something different, you'll have to give them aliases with the 'AS' keyword.

sorry about that i mean join

Member Avatar for Member #120589

I thought perhaps you did. So what is the query you want to check? I can't build one for you.

Edited by Member #120589 because: n/a
Member Avatar for hielo 
$connection=mysql_connect("localhost","username","password") or die(mysql_error());

mysql_select_db("databasename") or die( mysql_error());

$result = mysql_query("Select field1,field2,... from Table") or die(mysql_error());

if( !mysql_num_rows($result) )
{
 echo "No records  found";
}
else
{
  $row=mysql_fetch_assoc($result);

  /* here $fieldNames is an array that contains the field names of the records returned by your query */
  $fieldNames = array_keys($row);
echo "<table border='1'> ";
echo "<tr><th>" . implode( "</th><th>", $fieldNames) . "</th></tr>";
do{
  echo "<tr><td>" . implode("</td><td>", array_values($row) ) . "</td></tr>";
 }
 while( $row=mysql_fetch_assoc($result) );
echo "</table>";
}
Member Avatar for Member #120589

Sorry, I'm still none the wiser.

Returnd field name into a PHP array so that i can daynamically load into a CSV file

Which fields from which tables? Have you checked out the MySQL manual w.r.t CSV files?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.