show fields in db table

How do I get all the existing fields in the db table mysql by php?

PHP Code:

---

$query = "select  * from table"; 


---

all of the fields? as in field names? run the query:

SHOW FIELDS FROM table_name;

Quote:

---

Originally Posted by kows

all of the fields? as in field names? run the query:

SHOW FIELDS FROM table_name;

---

Ok What if I just want the fields? Because I want to insert the following info automatically:

PHP Code:

---

$sql = "INSERT INTO ".$acronym." (".fields().") VALUES (".$strLine[$line].")"; 


---

I think ID should be left out though.

the SQL I gave you does only return the fields.. it returns a result set with all of the field names for your table.

Quote:

---

Originally Posted by kows

the SQL I gave you does only return the fields.. it returns a result set with all of the field names for your table.

---

How do I place it in function?

PHP Code:

---

function fields(){
    $sql = "SHOW FIELDS FROM ".$acronym;
    $result = mysql_query($sql);
    $i = 0;
    
    while ($row = mysql_fetch_array($result)) {
        return $row['Field']."<br />\n";
    }
}
echo fields(); 


---

uhh, if you wanted to use it in the way you described above, why would you be returning HTML and new lines? you obviously want it to return something like ("field1, field2, field3"), so you could either build a string and add commas yourself, or build an array and use implode() to add the commas for you. quick example (you'll have to implement these into your type of loop, it should be fairly straight forward):

PHP Code:

---

$array = array("field1", "field2", "field3");
$str = '';
foreach($array as $value){
  //add a comma before the value if $str is not empty
  if($str)
    $str .= ", ";

  //add the value
  $str .= $value;
}
echo $str;
//echos "field1, field2, field3" 


---

or:

PHP Code:

---

$array = array("field1", "field2", "field3");
$str = implode(", ", $array);
echo $str;
//echos "field1, field2, field3" 


---

either way, you don't seem to know what scope is. the variable $acronym is not available to the function you're calling because you haven't defined it yet. it's best to think of a function as its own program -- it has no idea what's going on in the rest of the script unless you let it know; you can pass variables to the function with parameters, or you could simply add a variable to the function's scope using the global keyword. in this case, I would probably just pass it the table name as a parameter. your function definition would look like:

PHP Code:

---

function fields($table){

} 


---

then, you can use the variable $table instead of $acronym inside of the function. you would call this function like so (in your SQL query), assuming $acronym holds the table name: fields($acronym)

and, finally, a function will stop operating as soon as it has a value to return. so, this means that in your function, the first iteration of your while() loop causes the function to stop because you're already returning a value. this is why you'll need to build a string during your loop, and then return that string after the loop has finished.

Quote:

---

Originally Posted by kows

uhh, if you wanted to use it in the way you described above, why would you be returning HTML and new lines? you obviously want it to return something like ("field1, field2, field3"), so you could either build a string and add commas yourself, or build an array and use implode() to add the commas for you. quick example (you'll have to implement these into your type of loop, it should be fairly straight forward):

PHP Code:

---

$array = array("field1", "field2", "field3");
$str = '';
foreach($array as $value){
  //add a comma before the value if $str is not empty
  if($str)
    $str .= ", ";

  //add the value
  $str .= $value;
}
echo $str;
//echos "field1, field2, field3" 


---

or:

PHP Code:

---

$array = array("field1", "field2", "field3");
$str = implode(", ", $array);
echo $str;
//echos "field1, field2, field3" 


---

either way, you don't seem to know what scope is. the variable $acronym is not available to the function you're calling because you haven't defined it yet. it's best to think of a function as its own program -- it has no idea what's going on in the rest of the script unless you let it know; you can pass variables to the function with parameters, or you could simply add a variable to the function's scope using the global keyword. in this case, I would probably just pass it the table name as a parameter. your function definition would look like:

PHP Code:

---

function fields($table){

} 


---

then, you can use the variable $table instead of $acronym inside of the function. you would call this function like so (in your SQL query), assuming $acronym holds the table name: fields($acronym)

and, finally, a function will stop operating as soon as it has a value to return. so, this means that in your function, the first iteration of your while() loop causes the function to stop because you're already returning a value. this is why you'll need to build a string during your loop, and then return that string after the loop has finished.

---

Ok what I meant is how to return the field names into an array. $acronym is defined very early in my code. I haven't mentioned it in the forum.

Something like this?

PHP Code:

---

    $fields = array();
    $sql = "SHOW FIELDS FROM ".$acronym;
    $result = mysql_query($sql);
    $i = 0;
    $fields[]= "(";
    while ($row = mysql_fetch_array($result)) {
        $fields[].= $row['Field'].", ";
    }
    $fields[].= ")";
    print_r($fields); 


---

the point in my saying that $acronym wasn't defined was because you were using it within a function that did not have it defined. if $acronym is defined within your main script, but not within your function, then that function cannot access $acronym. this is called variable scope.

are you even running this code? that would most likely help in solving your problems, so that you can see what you're actually doing. if you only want to store all of the field names in an array, you don't need to add commas or brackets. you seem to be confused with what you want to do :/ ...

this will store all of your fields in an array:

PHP Code:

---

$fields = array();
while($row = ....){
  $fields[] = $row['field'];
} 


---

effectively, a print_r for $fields would then print out:

Code:

---

$fields = array (
  [0] => field1
  [1] => field2
  [2] => field3
  ...
)

---

however, having this array still won't do what you were originally asking for unless you apply one of the methods I demonstrated in my last post (either looping through and adding commas, or using implode() to add commas).

ok i see what you mean.

PHP Code:

---

$fields = array();
$sql = "SHOW FIELDS FROM ".$acronym;
$result = mysql_query($sql);
$i = 1; // to skip the id field
while ($row = mysql_fetch_array($result)) {
    $fields[]= $row['Field'];
}
$str = implode(", ", $fields);
echo $str; 


---

Now the first field will be the id. how do I skip the 1st field?

then how are you calling that function (your code doesn't have any place where it calls it)? and what did it produce, if you were calling it already? did you get any errors?

I'm not going to do all of the work for you.

Quote:

---

Originally Posted by kows

then how are you calling that function (your code doesn't have any place where it calls it)? and what did it produce, if you were calling it already? did you get any errors?

I'm not going to do all of the work for you.

---

Don't worry I have the rest of the code ready. All I need to know is how to skip the id field:

PHP Code:

---

$fields = array();
$sql = "SHOW FIELDS FROM ".$acronym;
$result = mysql_query($sql);
$i = 1; // to skip the id field
while ($row = mysql_fetch_array($result)) {
    $fields[]= $row['Field'];
}
$str = implode(", ", $fields);
echo $str;

function recValues(){
    $contents_of_page = file_get_contents('bible.htm');
    preg_match_all("#<td.*>(.+)</td#Ui", $contents_of_page, $tdInnerHTML);
    $totaltds = count($tdInnerHTML[1]);
    $line = 0;
    $strLine = array();
    for($k=0; $k < $totaltds; $k++){
        if($k &#37; 10 == 0){
            print("line ");
            $strLine[$line] = "";
        }
        if($k % 10 != 0){
            $j = 0;
            $tdValues = array();
            $tdValues[$j] = $tdInnerHTML[1][$k];
            $strLine[$line] .= "'";
            //print("'");
            $strLine[$line] .= $tdValues[$j];
            //print("<span style='font-weight: bold; color: red'>".$tdValues[$j]."</span>");
            //print($tdInnerHTML[1][$k]);
            if($k % 10 == 9){
                $strLine[$line] .= "'";
                //print("'<br />\n");
            }else{
                $strLine[$line] .= "', ";
                //print("', ");
            }
        }
        
        print($strLine[$line]."<br />\n");
        //return $strLine[$line];
        $j++;
        if($k % 9 == 0){
            
            $sql = "INSERT INTO ".$acronym." (".$str.") VALUES (".$strLine[$line].")";
            print("<span style='color: red;'>".$k." ".$sql."</span><br />\n");
            $line++;
        }
        
    }
    
}
echo recValues();*/
mysql_close($con); 


---

This is where it's going to fit:
$sql = "INSERT INTO ".$acronym." (".$str.") VALUES (".$strLine[$line].")";

I want to get all the td tags 9/record excluding the ID field inserted.

add a where clause to your SQL.

SHOW FIELDS FROM table_name WHERE `Field` NOT LIKE 'id';

however, from the looks of your code, you're again ignoring variable scope. your recValues() function isn't being passed $str and hasn't made $str global, and so your $sql variable within recValues() won't know what $str is. furthermore, you're not actually querying the database within that function, either... so, if you're querying the database later on with your $sql variable, it won't be set to anything either, because $sql is defined within a function.

Quote:

---

Originally Posted by kows

however, from the looks of your code, you're again ignoring variable scope. your recValues() function isn't being passed $str and hasn't made $str global, and so your $sql variable within recValues() won't know what $str is. furthermore, you're not actually querying the database within that function, either... so, if you're querying the database later on with your $sql variable, it won't be set to anything either, because $sql is defined within a function.

---

Yes you're right. I broke down the function. I will have to look into it.