[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to get all row details at once

Posted on 2011-05-03
13
Medium Priority
?
264 Views
Last Modified: 2012-05-11
Hello Experts,

At the moment I use script much like the standard mysql select script here http://www.w3schools.com/php/php_mysql_select.asp

However it would be handy to extract all the details... ie not just echo $row['FirstName'] . " " . $row['LastName']; - without having to literally write every line of code... for example...

$first_name =  $row['FirstName']
$middle_name =  $row['MiddleName']
$last_name =  $row['lastName']
etc etc

is there anyway of doing this??
0
Comment
Question by:maccaj51
  • 3
  • 3
  • 3
  • +4
13 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 35514926
"$row[]" is an array so you can use any array methods to get the data from it.  It is not just a single item though so you can't treat it like it is.
0
 

Author Comment

by:maccaj51
ID: 35515247
But as soon as you close the "while" .. like so...

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }


you cant use it again... is there any way round this?

0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35515437
Dave's right. You can use it even inside a function, but check if this is what you want:

$myarray = array();
while($row = mysql_fetch_assoc($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  $myarray[] = $row;
  }

print_r($myarray);

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 31

Accepted Solution

by:
Marco Gasi earned 668 total points
ID: 35516694
You can use extract() function: http://www.php.net/manual/en/function.extract.php

A little example got from above page:

<?php
$sql=query("SELECT * FROM contact WHERE contact_id='$invoicedata_clientcontact'");
        if ($tmp=fetch_array($sql))
        {
            extract($tmp);
        }
?>

Can this make sense for you?
0
 
LVL 9

Expert Comment

by:crazedsanity
ID: 35689758
I wrote a library, cs-webapplibs [ https://cs-webapplibs.svn.sourceforge.net/svnroot/cs-webapplibs/trunk ] that has an abstraction layer for databases, including MySQL; it has a method called "farray_fieldnames()" that will build an associative array representing all rows & all columns, with an index field of your choice (must be unique, such as the ID of a database).  You can stuff that into an array and just run a foreach() on it to get the contents, and you'll still be able to reference any part of it later.

EXAMPLE CODE::: https://cs-webapplibs.svn.sourceforge.net/svnroot/cs-webapplibs/trunk/0.4/db_types/cs_phpDB__mysql.class.php
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35690750
This kind of construct might do what you want:

// GET THE ROWS FROM THE RESULTS SET
while ($row = mysql_fetch_assoc($res))
{
    // ITERATE OVER THE $row ARRAY AND PRINT THE KEYS => VALUES
    foreach ($row as $k => $v)
    {
        echo "<br/>$k => $v" . PHP_EOL;
    }
}
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 35698403
Can you give some feedback, please?
0
 

Author Comment

by:maccaj51
ID: 35698471
Sorry MarqusG... I am up to my neck in it... cease posting and I will have a look at all your comments!

Many thanks to all!
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 664 total points
ID: 35699354
Sorry... Just noticed this part of the question...

But as soon as you close the "while" .. like so...

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

you cant use it again... is there any way round this?


Yes! (the row numbers start at zero)
http://php.net/manual/en/function.mysql-data-seek.php
0
 
LVL 9

Assisted Solution

by:Erdinç Güngör Çorbacı
Erdinç Güngör Çorbacı earned 668 total points
ID: 35711816
The exact solution is extract as margusG mentioned. You don't have to use while or something similar if you know how many rows pulled from db ... and usage as below is simpler i think;


$query=mysql_query($sql_query_string);
$results=mysql_fetch_assoc($query);
//uncomment line below if there is only one row 
//extract($results[0],EXTR_PREFIX_ALL,"the");
// or if there are several rows expected...
$i=0;
foreach($results as $result){
extract($result[$i],EXTR_PREFIX_ALL,"the_$i");
$i++;
}
// this will produce variables from your column names after 
// prefixing them with the_ or the_##_ . Please note that any 
// variable set with the same name before will be overwritten.

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35711901
One last comment then I'll sign off on this one, as it has been well answered.

Regarding this kind of code:

$first_name =  $row['FirstName']
$middle_name =  $row['MiddleName']
$last_name =  $row['lastName']

That is not really a value-added activity.  All it does is copy data from one place to another in storage.  If you can figure out how to use the data in the $row array, you can save yourself the trouble of copying all the elements into separate variables (a process that is sure to lead to typographical errors).  Some techniques that might make this easier would include mapping the names of HTML form input controls to the names of the columns in your data base.  There is something wonderful about a one-to-one correspondence in the $_POST array and the $row array.  Missing or extraneous keys are easy to spot, etc.

Just a thought,
~Ray
0
 

Author Closing Comment

by:maccaj51
ID: 35716609
Many thanks for all your input. I used all your advice... hence the split in points. Im not technical enough to choose THE best answer!

Thanks Again
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 35716703
Don't worry, maccaj51, you've done right: extract is the shorter way, mysql_data_seek is probably the more correct and elegant, and the erdincgc's suggestion is the better explained solution. So I think we all can be happy about your decision. Glad to helped you. Bye.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month17 days, 22 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question