Echo All Rows Without Fetch

[computermax2328]

Hey All,

 

I was wondering if there was a way to echo all the rows in a database table without having the fetch them. Pretty much, I have a group of tables that need to go individually on the same page, but display on the page according to different variables in the url. Displaying the content on the page according to the url works fine. I just need to figure our this one part.

 

Something like this without the fetch where it is indicated.

 

$table = $_GET['table'];
   $query = "SELECT * FROM $table";
   $go = mysql_query($query, $connection);
   # I need to do without the code from here...
   while($row = mysql_fetch_array($go)) {
       $name = $row['name'];
   }
   # to here, but with the while loop in it.......
   echo "<li>$name</li>";

 

The problem is that the tables are for different issues, but the different issues have different data to back them up, for example: labor rates and education scores. They are both two issues, but they have two different sets of data, which means they have different columns in the table.....

 

Thanks in advance,

[Zane]

There is no possible way of doing this with a fetch function. They were created explicitly to fetch the data from the dataset. I suppose you could get away with using a for loop and use mysql_num_rows for your threshold, but that is just ridiculous.

 

My advice to you is to not stray from the fetch approach. Since you say you plan to select different datasets and use them outside the loop, it is probably the best idea to create an array for each of your datasets... for instance

 

$query = "SELECT * FROM labor_rates";
$go[] = mysql_query($query, $connection);

$query = "SELECT * FROM ed_scores";
$go[] = mysql_query($query, $connection);

$query = "SELECT * FROM someTable";
$go[] = mysql_query($query, $connection);

$laborRates = array(); $ed_scores = array(); $someTable = array();

while($row = mysql_fetch_array($go[0])) $laborRates[] = $row;
while($row = mysql_fetch_array($go[1])) $ed_scores[] = $row;
while($row = mysql_fetch_array($go[2])) $someTable[] = $row;

echo "<b>$someTable[3]['name']</b>"; 

Then you can use a foreach to loop through those .. or you can access your data directly like I demonstrated at the end.

Edited November 14, 2012 by Zane

[computermax2328]

I am not the best with foreach. Foreach $row??

[Zane]

http://php.net/foreach

 

foreach($laborRates as $currentRow => $theRow) {
//Assuming you have a column called rate in your labor_rates table, this would echo all from that column
echo "Row Number $currentRow contains this" . $theRow['rate'] . "in the rate field.";
}

Simple as that..

Edited November 14, 2012 by Zane

[computermax2328]

Is there a way to maybe could identify all of the columns in a table and then get them as an array??

[Muddy_Funster]

OK, just trying to get my head arround your OP - you want to echo out information that is stored in a database table without actualy fetching that information from the database table first? Is that what you seriously want to try to do?

[computermax2328]

OK, just trying to get my head arround your OP - you want to echo out information that is stored in a database table without actualy fetching that information from the database table first? Is that what you seriously want to try to do?

 

No! What I need to do is identify the column names before I can query the table. Then use the results of that first query to query the content of the table.

 

Something like....

 

$data = "SELECT * FROM '$tabledata'";
  $dq = mysql_query($data, $connection);
  while($dr = mysql_fetch_field($dq)){
  $rowh = $dr->name;
  echo $rowh;
}

 

Maybe to get the column names??

[computermax2328]

Hello All,

 

Having some trouble with this loop. What I am trying to do is to fetch each column name from a database, then query the database based on that column name, loop around and do it again and finally stop after the last column

 

$data = "SELECT * FROM $tabledata";
$dq = mysql_query($data, $connection);
$add = 0;
while ($dq) {
$column = mysql_field_name($dq, $add)
$find = "SELECT * FROM $tabledata";
$fq = mysql_query($find, $connection);
while($fr = mysql_fetch_array($fq)) {
$fd = $fr['$column'];
}
$add++;
if ($column = empty()) {
$add = 0;
}
}

 

I can tell you that $tabledata does exist and mysql_fetch_field does work with a zero in it. It returns the first column if I echo it.

 

Any suggestions??

Edited November 14, 2012 by computermax2328

[AyKay47]

This code seamlessly does nothing, $fd is being overwritten every iteration and nothing is accomplished. So what is the goal here?

[PFMaBiSmAd]

You also should NOT be executing queries inside of loops.

 

If you post your table definition and some sample data and what result you are tying to achieve for that data, someone can point you in the proper direction (i.e. the implication of what you are doing indicates an improper table design.)

[PFMaBiSmAd]

Also, this is a continuation of a existing thread (where you do provide some small information about what you are trying to do.) Two topics merged...

 

Finally, the mysql extension is being discouraged for new code. php.net suggests using the mysqli extension.

Edited November 15, 2012 by PFMaBiSmAd

[Andy-H]

From what I can gather, you're not asking to use the data without it being fetched, but you want the column names (i.e. associative key names)

 

bad practices aside, I think you're asking for something along these lines?

 

/*
  TABLE - users
  username - James
  password - somehash
  ip       - 127.0.0.1
*/
$query = "SELECT * FROM users";
$result = mysql_query($query);
while( $row = mysql_fetch_assoc($result) ) {
 $new_query = "SELECT `". implode('`, `', array_keys($row)) ."` FROM othertable"; // effectively "SELECT `username`, `password`, `ip` FROM othertable";
}

 

So basically, if you're asking what I think you're asking, you need array_keys?

[Andy-H]

I think I understand what you're asking for now:

 

$table = $_GET['table'];
$query = "SELECT * FROM `". mysql_real_escape_string($table) ."`"; // limit 1?
$result = mysql_query($query);
$content = '';
while ( $row = mysql_fetch_assoc($result) ) {
$content .= '<li>'. implode('</li><li>', $row) .'</li>'. PHP_EOL;
}
echo $content; // '<li>Field 1 Value</li><li>Field 2 value</li>'...

 

Or with limit 1

 

 

$table = $_GET['table'];
$query = "SELECT * FROM `". mysql_real_escape_string($table) ."` LIMIT 1";
$result = mysql_query($query);
$content .= '<li>'. implode('</li><li>', mysql_fetch_assoc($result)) .'</li>'. PHP_EOL;
echo $content; // '<li>Field 1 Value</li><li>Field 2 value</li>'...

 

For a result like this:

 

$name = $row['name'];

 

See the extract function, but be sure that if your'e extracting from an unknown source (i.e. $_GET/$_POST) you use EXTR_SKIP or EXTR_PREFIX_ALL i.e.

 

extract($_GET, EXTR_SKIP); // only extract key names to variables if they don't already exist
extract($_POST, EXTR_PREFIX_ALL, 'incase_it_already_exists'); // $already_exists_name

Edited November 15, 2012 by Andy-H

[PFMaBiSmAd]

Escaping the table name being dynamically put into the query doesn't protect against sql injection in it because the table name isn't string data, which is the only thing that mysql_real_escape_string is for, it's an identifier and its not in a place in the query (between single-quotes) where there's anything that it needs to be prevented from escaping from.

[PFMaBiSmAd]

Best guess about what the OP wants is to be able to query different tables and display the arbitrary columns from that table by retrieving the actual column names that the query returned, for something like a general purpose sortable data-grid or general purpose crud application.

 

If so, the answer is simple, just use ONE query to get the rows you are interested in, then fetch the field names from that result set, followed by fetching the data in the rows.

[computermax2328]

The process would be identify the table, identify the column names and query those columns. The problem is that the tables are all different. The data in the table has a child relationship to another table with information about the topic.

 

So for example there is a table called education that has information about education, but there is another table that I am trying to query called education data. education and labor have the same table structure, but education data and labor data have a different structure because there is different data for both of them. Make sense??

[Muddy_Funster]

nope, still don't see why you can't just return the column names and data in the same normal query