Mysql query to get field name and result as array key and array value

Question

I have this code below and I am trying to get results from query as array containing array key to be table field name and value to be the result from the field. So far I have this:

$query='select      
en_product_name,de_product_name,fr_product_name,ru_product_name    
from products where id="'.$pid.'"';    
$result=mysql_query($query) or die('Mysql Error:'.mysql_error().'<br /> Query:'.$query);    
$num_rows=mysql_num_rows($result);    
$row = mysql_fetch_array($result);    
$columns = mysql_num_fields($result);    
$fields =array();    
for($i = 0; $i < $columns; $i++) {     
echo $fields = mysql_field_name($result,$i).'<br />';
}

this $fields returnes only the field name.. How can I have the result as: Array ( [en_product_name] => New en product name, [de_product_name] => New de product name) and etc.. Thank you for any help and suggestions

mysql_* has been deprecated. Please use PDO (php.net/manual/en/book.pdo.php) or mysqli_* instead. Otherwise there are security problems. I would recommend PDO. — Patrick Geyer
– Patrick Geyer, Commented Feb 12, 2014 at 10:27

jogesh_pi · Accepted Answer · 2014-02-12 10:38:46Z

3

Try with this

$query='select en_product_name,de_product_name,fr_product_name,ru_product_name    
    from products where id="'.$pid.'"';    
$result=mysql_query($query) or die('Mysql Error:'.mysql_error().'<br /> Query:'.$query);    
$num_rows=mysql_num_rows($result);    
$row = mysql_fetch_array($result);    
$columns = mysql_num_fields($result);    
$fields =array();    
for($i = 0; $i < $columns; $i++) {     
    $field = mysql_field_name($result,$i);
    $fields[$field] = $row[$field];
}  

print_r($fields);

NOTE:

mysql_* has been deprecated. Please use PDO (php.net/manual/en/book.pdo.php) or mysqli_* instead. Otherwise there are security problems.

edited Feb 12, 2014 at 10:38

answered Feb 12, 2014 at 10:31

jogesh_pi

9,7725 gold badges40 silver badges65 bronze badges

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

2 Comments

thecore7 Over a year ago

jogesh_pi - good job ! Accepted as correct ! Thank you all guys !! Good day to all of you !

jogesh_pi Over a year ago

@thecore7 glad to help you, to understand a bit more take a look on codepad.org/w0dGw78r it is slimier to your problem.

Andrew Mackrodt · Accepted Answer · 2014-02-12 10:34:38Z

0

Consider replacing mysql_fetch_array with mysql_fetch_assoc.

From the documentation for mysql_fetch_assoc:

Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead. mysql_fetch_assoc() is equivalent to calling mysql_fetch_array() with MYSQL_ASSOC for the optional second parameter. It only returns an associative array.

Updated code:

$query='select en_product_name,de_product_name,fr_product_name,ru_product_name    
    from products where id="'.$pid.'"';    
$result=mysql_query($query) or die('Mysql Error:'.mysql_error().'<br /> Query:'.$query);    
$num_rows=mysql_num_rows($result);    
$row = mysql_fetch_assoc($result);

answered Feb 12, 2014 at 10:34

Andrew Mackrodt

1,82614 silver badges10 bronze badges

Comments

Mandeep Gill · Accepted Answer · 2014-02-12 10:35:35Z

0

So you want to access key => value, i am using while statement for clearing the Answer.

You can use these functions and parameters:

while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"];
    echo $row["fullname"];
    echo $row["userstatus"];
}

Or

$row = mysql_fetch_array($result, MYSQL_ASSOC);

It should be solve your problem.

answered Feb 12, 2014 at 10:35

Mandeep Gill

3905 silver badges12 bronze badges

Comments

BenMorel · Accepted Answer · 2014-05-22 20:05:40Z

Try to avoid use of mysql_* function as it is deprecated from php 5.5 and will be removed in future.So use mysqli_* function.like this

$con = mysqli_connect("localhost","username","pwd","dbname");
$array = array();
$equery1223 = "SHOW COLUMNS FROM products ";
$eresults1223 = mysqli_query($con,$equery1223) or die(mysqli_error($con));
$i=0;
while($rows3 = mysqli_fetch_array($eresults1223))   
   {
      if($rows3['Field'] ="en_product_name" || $rows3['Field'] ="de_product_name" || $rows3['Field'] ="fr_product_name" || $rows3['Field'] ="ru_product_name" ) 
      {  $array[$i]=$rows3['Field'];$i++; }
   }
$array2 = array();$i=0;
$equery1224 = "select * FROM products ";
$eresults1224 = mysqli_query($con,$equery1224) or die(mysqli_error($con));
$i=0;
while($rows4 = mysqli_fetch_array($eresults1224))   
   {
      $array2[$array[$i]]=$rows4[$array[$i]];$i++;
   }

Collectives™ on Stack Overflow

Mysql query to get field name and result as array key and array value

4 Answers 4

NOTE:

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

NOTE:

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related