Dynamic query results to array in php5

Question

So I have a function that handles queries:

function cfquery($query){
    // Connection's Parameters
    $db_host="localhost";
    $db_name="d";
    $username="u";
    $password="p";
    $db_con=mysql_connect($db_host,$username,$password);
    $connection_string=mysql_select_db($db_name);
    // Connection
    mysql_connect($db_host,$username,$password);
    mysql_select_db($db_name);

    // Query
    $sql = $query;
    $results = mysql_query($sql);
    return $results; 
}

And I'm trying to write a function that will take the results and put them into an array, but this function is generating a 'resource', and everything I'm finding on the internet doesn't work for that data type, or isn't generated dynamically - like you have to know the column names in order to get to the data.

For instance:

while ($row = mysql_fetch_array($results)){
    echo $row['name'] . ',' ;
    echo $row['email'] . ',' ;
    echo $row['city'] . ',' ;
}

I'm looking for something that will get the column names from the results. Like this:

$array = new array();
loop rows index x {
    loop column_names index y {
        $array[x][y] = $results[rows][column_names];
    }
}

If you can't tell, I'm transitioning from ColdFusion, so I have only a vague idea of what a "resource" is (after reading some documentation), and no idea why I can't get an array out of it.

It is not recommended to use the old mysql extension for new development, as it has been deprecated as of PHP 5.5.0 and will be removed in the future. See MySQL: Choosing an API — Amal
– Amal, Commented Jan 26, 2014 at 13:48

Community · Accepted Answer · 2023-11-17 20:24:57Z

2

If you can use MySQLi: Use mysqli_fetch_field_direct() to get field metadata.

The field name can be accessed like so (within your results loop):

$metadata = mysqli_fetch_field_direct($row, $field_index);
$name = $metadata->name;

If you are stuck using the deprecated (as of 5.5) mysql functions: then you can use mysql_field_name($result, $field_index) instead in the same manner although it will directly return the name as a string and not an object.

You can read up on MySQLi and it's implementation here: https://www.php.net/mysqli

edited Nov 17, 2023 at 20:24

CommunityBot

11 silver badge

answered Jan 26, 2014 at 13:50

George Reith

13.5k18 gold badges82 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simone Nigro · Accepted Answer · 2014-01-26 13:59:45Z

0

mysql_* is deprecated... use mysqli_* or pdo

In MySQL...

function cfquery($query)
{
    $return = array();

    $cn = mysql_connect("localhost","u","p") or die('connection error');
    mysql_select_db("d", $cn) or die('select db error');

    $results = mysql_query($query, $cn) or die(mysql_error($cn));

    while ( $row = mysql_fetch_array($results) )
        $return[] = $row;

    mysql_free_result($results);
    mysql_close($cn);

    return $return;
}

edited Jan 26, 2014 at 13:59

answered Jan 26, 2014 at 13:52

Simone Nigro

4,9475 gold badges46 silver badges81 bronze badges

3 Comments

George Reith Over a year ago

You used the deprecated functions in your answer.

Simone Nigro Over a year ago

User use mysql_* function... I have warned

George Reith Over a year ago

Your top comment makes it sound like you hadn't. I'd explicitly state that your example uses the deprecated functions to prevent confusion.

Collectives™ on Stack Overflow

Dynamic query results to array in php5

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related