[SOLVED] php msql query field name problem

[jonaHill87]

edit: the forum seems to be putting some quotes and semicolons in my code a lil strange. They are attached to the statements in my code I dont know why they look like that in here.

Hi all. Nice set of forums your got here. I was hoping maybe I could get some help on a php/mysql query I'm having. I'm trying to retrieve some field names and then put them into an html form as checkboxes. I'm echoing the field names fine but when I try to put them into the form it doesn't turn out right. I'm pretty sure the values are passing fine because it outputs the correct number of boxes for how many field names there are. But after the closing input tag, I'd like to echo the names as well but only echos the name of the function I'm using. I think I might have to use an array but I'm not sure, or maybe some other wierd thing in my code. I'm still pretty new to this stuff.

 

Here's my code with the form commented out just to show you that the field names are echoing properly.

<?php

$test = $_POST["data"];

//test is just the table name string from my earlier page which I incorporated into my query.

$query2 = "SELECT * FROM ".$test."";

$result3 = mysql_query($query2);

$fields = mysql_num_fields($result3);

for ($i = 0; $i < $fields; $i++) {

echo mysql_field_name($result3, $i)."<br>";

//echo "<form method='post' action='page3.php'>";

//echo "<input type='checkbox' value='mysql_field_name($result3, $i)' name='fieldNames'>mysql_field_name($result3, $i)<br>";

//echo "<input type='submit' value='submit' name='submit'><br>";

//echo "</form>";

}

?>

[jonaHill87]

Hey guys sorry I didnt post my code right and was trying to edit but the forum wouldnt let me.

Here's the correct code.

<?php

$test = $_POST["data"];

//test is just the table name string from my earlier page which I incorporated into my query.

$query2 = "SELECT * FROM ".$test."";

$result3 = mysql_query($query2);

$fields = mysql_num_fields($result3);

//echo "<form method='post' action='page3.php'>";

for ($i = 0; $i < $fields; $i++) {

echo mysql_field_name($result3, $i)."<br>";

//echo "<input type='checkbox' value='mysql_field_name($result3, $i)' name='fieldNames'>mysql_field_name($result3, $i)<br>";

}

//echo "<input type='submit' value='submit' name='submit'><br>";

//echo "</form>";

 

?>

[trq]

Please use the provided [ code ][/ code ] tags (without spaces).

[jonaHill87]

I'm sorry. I will from now on and when I'm allowed to edit this post I'll do the same. It doesn't look like I have permission to edit right now.

[jonaHill87]

Any ideas on help anyone?

[PHP_PhREEEk]

You've done the query, but...

 

>I'm pretty sure the values are passing fine because it outputs the correct number of boxes

 

You don't have the items, you asked MySQL for the number of rows available in the record set, and you're using that for your loop counter. num_rows is just an integer and contains no data...

 

So, you haven't actually asked for the data yet...

 

Instead of a FOR loop, you will need to iterate over the record set with WHILE. I'm just stepping out to do some more XMas shopping, so hopefully that will be enoguh information to help you try another route. There are at least 5 - 10 examples per page of posts here on this board with examples of iterating over a result set to extract the data. Shouldn't be hard to figure out, so consider yourself challenged! Now have a go at it...! = )

 

PhREEEk

[jonaHill87]

hmmm. I'm still not quite sure what to do. Like I said I still new to this stuff. But anyway, you're right. All I'm getting is the resource id for the field names and not actually getting the names as values or data. That's what fooled me because the field names were echoing fine with echo mysql_field_name($result3, $i). So as for a while loop to iterate over the result set. I'm not quite sure what you mean. I know how to use a while loop but I'm not sure what to put in it in this case.  Can you help out a little more? After you xmas shopping of course:) I'll try looking around the boards a bit more too.

[raku]

Hi,

 

Try something like:

 

while($row = mysql_fetch_array($result))
{
// get information from the database by accessing $row
$info = $row['info'];
}

 

Hope this helps!

[jonaHill87]

Well. All that seems to do is give me the row data and not the field names. For example, one of my tables on has 2 fields. If I use that while loop in my code instead of the for loop, I get an array with 2 index's full of table data but no field names. Am I doing something wrong? I'm assuming that fetch_array is pretty much the same as fetch_row?

 

 

[PHP_PhREEEk]

Back from the mayhem.. appreciate your patience = )

 

<?php
$db_server = 'localhost';
$db_user = 'root';
$db_pass = 'password';
$db_name = 'database_name';

@$con = mysql_connect($db_server, $db_user, $db_pass);

if ( !mysql_select_db($db_name, $con) ) {
   die('MySQL Error: ' . mysql_error());
}
$test = mysql_real_escape_string($_POST['data']);
$query2 = "SHOW COLUMNS FROM `" . $test . "`";
if ( !$result3 = mysql_query($query2) ) {
   die('MySQL Error: ' . mysql_error());
}
if ( mysql_num_rows($result3) > 0 ) {
   echo "<form method=\"post\" action=\"page3.php\">\n";
   while ( $row = mysql_fetch_assoc($result3) ) {
       echo "<input type=\"checkbox\" value=\"{$row['Field']}\" name=\"fieldNames[]\">{$row['Field']}\n";
   }
   echo "<input type=\"submit\" value=\"submit\" name=\"submit\">\n";
   echo "</form>\n";
} else {
   echo "No fields found in table $test!";
}
// script continues
?>

 

There are several ways to go about pulling the fields from a particular table and putting them in form check boxes. This is one way. I saw no connection info in your code, and without a database actually selected, selecting a table using $_POST['data'] would result in an error. So I added the connection info, and a variable for the database name. This script will run (it was tested) once you fill in the top variables with your MySQL info. As you can see, we used SHOW COLUMNS to get the field names, and a WHILE loop to iterate over them and assign them to individual check boxes. The name of the check box 'group' is fieldNames[], which will return an array (since multiple check boxes can be selected). On the receiving end, if you print_r($_POST['fieldNames']), you will see whatever was selected. You can use count($_POST['fieldNames']) to determine how many fields were selected, then use a FOR loop to iterate over them to do whatever else you need to.

 

Happy Holidays!

 

PhREEEk

[jonaHill87]

edit:

Thank you very much phfreeek. Your code works great. But now I'm having another problem that I need to solve. I put the $_POST["fieldNames"] into another array(not sure if that was needed or not) and now I'm trying to create a new query to display the data within the selected fields. I'm using implode() on my fieldArray to do this but I don't seem to get any results and I'm not sure what the problem is. Here's my code for page3.php(my final page).

I think maybe the problem is my $test var, it contains $_POST["data"] from the previous page which is the table name.

$fieldArray = $_POST["fieldNames"];
$query3 = "SELECT ".implode (", ", $fieldArray)." FROM ".$test;
$result4 = mysql_query($query3);
while ($rows = mysql_fetch_row($result4)) {
foreach ($rows as $entry) {
echo $entry;
}
}

no need for any fancy display for $entry. Once I know I can display the data, I will format the display.

[jonaHill87]

just bumping the thread*

[PHP_PhREEEk]

Ok, well here you go... if we go much further with this, we are going to just be recreating phpMyAdmin = )

 

This script is neither how I would do things, nor is exactly how you want it for your purposes. It's written this way so that I can test it on my localhost and still maintain some of your code format. It is here solely for you to see how to set up the logic behind your querys, and how to extract that data for display. That being said, if you save this script as test.php and fill in the proper DB variables at the top, then this script will 100% run/work. It will take the last $db_ value for a table and display all of the fields with check boxes (for that purpose, I hard coded in a $_POST value for 'data'). After selecting one or more check boxes, it will build a table and display those fields and the data for ALL records in the record set. Enjoy...

 

<?php
$db_server = 'localhost';
$db_user = 'root';
$db_pass = 'password';
$db_name = 'database_name';
$db_table = 'table_name';

@$con = mysql_connect($db_server, $db_user, $db_pass);

if ( !mysql_select_db($db_name, $con) ) {
    die('MySQL Error: ' . mysql_error());
}
$_POST['data'] = $db_table;
$test = mysql_real_escape_string($_POST['data']);
if ( isset($_POST['submit']) && $_POST['submit'] == 'submit' ) {
    $queryFields = '';
    foreach ($_POST['fieldNames'] as $fields) {
        $queryFields .= '`' . mysql_real_escape_string($fields) . '`, ';
        $tableHeader[] = "<td><b>" . mysql_real_escape_string($fields) . "</b></td>";
    }
    $queryFields = substr($queryFields, 0, strlen($queryFields)-2); // strip last white space and comma
    $query3 = "SELECT $queryFields FROM `" . $test . "`";
    if ( !$result3 = mysql_query($query3) ) {
        die('MySQL Error: ' . mysql_error());
    }
    echo "<table border=\"1\" cellspacing=\"0\" cellpadding=\"2\">
  <tr>
    <td><b>Record #</b></td>\n";
    for ( $x=0; $x < count($tableHeader); $x++ ) {
        echo "    $tableHeader[$x]\n";
    }
    $count = 1;
    while ( $row = mysql_fetch_assoc($result3) ) {
        echo "  <tr>\n";
        echo "    <td>$count</td>\n";
        foreach ($row as $key => $value) {
            if ( empty($value) ) {
                $value = ' ';
            }
            echo "    <td>$value</td>\n";
        }
        echo "  </tr>\n";
        $count++;
    }
    echo "</table>";
} else {
    $query2 = "SHOW COLUMNS FROM `" . $test . "`";
    if ( !$result3 = mysql_query($query2) ) {
        die('MySQL Error: ' . mysql_error());
    }
    if ( mysql_num_rows($result3) > 0 ) {
        echo "<form method=\"post\" action=\"test.php\">\n";
        while ( $row = mysql_fetch_assoc($result3) ) {
            echo "<input type=\"checkbox\" value=\"{$row['Field']}\" name=\"fieldNames[]\">{$row['Field']}\n";
        }
        echo "<input type=\"submit\" value=\"submit\" name=\"submit\">\n";
        echo "</form>\n";
    } else {
        echo "No fields found in table $test!";
    }
}
// script continues
?>

 

PhREEEk

[jonaHill87]

Thanks again phreeeek. I'm not going to use this code on my site because that would kind of defeat the purpose of learning php. I'll just keep the code as reference and study it so I can get the jist of how it works. thanks again.