MYSQL Function help needed to return a string comma separated

<?php // RAY_temp_matthewdacruz.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// SEE http://www.experts-exchange.com/Database/MySQL/Q_28331339.html#a39762002
// THIS SCRIPT DEMONSTRATES SOME OF THE BASICS OF MySQLi


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
, array( "fname" => "Ray"    , "lname" => "Capece"   )
, array( "fname" => "John"   , "lname" => "Paseur"   )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_fn  = $mysqli->real_escape_string($person['fname']);
    $safe_ln  = $mysqli->real_escape_string($person['lname']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }


    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$fname    = "RAY";
$safe_fn  = $mysqli->real_escape_string($fname);

// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLE
$sql = "SELECT id, fname, lname FROM my_table WHERE fname='$safe_fn' OR fname='Bill' ORDER BY lname, fname";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQLI FUNCTIONS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo PHP_EOL . 'USING MySQLi_Result::Fetch_<i>Object</i>(): ';
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;


// REUSE THE SAME QUERY RESULT SET
$res->data_seek(0);


// CREATE A COMMA-SEPARATED VALUE SET
$csv = 'RAY_temp_matthewdacruz.csv';
$fpw = fopen($csv, 'w');
if (!$fpw) trigger_error("CANNOT OPEN $csv", E_USER_ERROR);

// NOTE: ROW MUST BE FETCHED AS AN ARRAY
while ($row = $res->fetch_assoc())
{
    // COLLAPSE THE ROW INTO A CSV STRING AND WRITE THE FILE
    fputcsv($fpw, $row);
}
// SHOW A LINK TO THE CSV
echo '<a target="_blank" href="' . $csv . '">' . $csv . '</a>';
echo PHP_EOL;


// REUSE THE SAME QUERY RESULT SET
$res->data_seek(0);


// NOTE: ROW CAN BE FETCHED AS AN OBJECT FOR EASIER HEREDOC SYNTAX
$str = NULL;
while ($row = $res->fetch_object())
{
    // COLLAPSE THE ROW INTO XML
    $out = <<<EOD
  <person>
    <id>$row->id</id>
    <fn>$row->fname</fn>
    <ln>$row->lname</ln>
  </person>
EOD;

    $str .= $out;
}

// WRAP THE XML DOCUMENT
$str = <<<EOD
<?xml version="1.0" encoding="UTF-8"?>
<rezult>
$str
</rezult>
EOD;

// TEST TO SEE IF THE DOCUMENT WORKS
$obj = simpleXML_Load_String($str);
var_dump($obj);
echo PHP_EOL;

// WRITE THE XML STRING
$xml = 'RAY_temp_matthewdacruz.xml';
file_put_contents($xml, $str);

// SHOW A LINK TO THE XML
echo '<a target="_blank" href="' . $xml . '">' . $xml . '</a>';
echo PHP_EOL;


// REUSE THE OBJECT TO MAKE A JSON STRING
$jso = json_encode($obj);

// WRITE THE JSON STRING
$txt = 'RAY_temp_matthewdacruz.txt';
file_put_contents($txt, $jso);

// SHOW A LINK TO THE XML
echo '<a target="_blank" href="' . $txt . '">' . $txt . '</a>';
echo PHP_EOL;

Select all Open in new window