Assign each value in array to separated variables

[kristian_gl]

Hello.

I need to get the the column names from a table called "answers", and assign each column name to a variable. The table is not static, so I need to do this dynamically (i.e the code needs to work if there is just one or hundreds of columns).

So far I've used SHOW COLUMNS the get all the column names, and when I echo out $column[0] (which I believe is an array) , I get a list over all the columns, but I can't seem to assign each value in $column[0] to separated variables.

 

CODE:

 

$sql = "SHOW COLUMNS from answers";
$result = mysql_query($sql);

while ($column= mysql_fetch_array($result))
{
echo  $column[0] . "<br/>";
}

 

Any help would be much appreciated

Kristian

[litebearer]

This may help...

 

http://docstore.mik.ua/orelly/webprog/pcook/ch05_05.htm

 

 

[kristian_gl]

maybe, it looks like it's related with my problem, but I can't seem to figure out how the exact syntax for my problem is (I'm a bit slow today), since none of the examples operates with an array in this form columns[0] (with the "[0]"). If I echo out $columns[1] I get the data types for the columns (i.e INT(4), VARCHAR(320), so it is only the values of $columns[0] that I need to convert to separate variables.

[litebearer]

Not really sure if this is what you are looking for.

 

presumption:

table has 3 columns named id, last_name, first_name

 

this will get the column names and create the following variables

 

$id -- the value of this variable at the time it is created is id (NOT the value of id BUT the letters i and d)

 

$last_name -- the value of this variable at the time it is created is last_name (NOT the value of last_name BUT the letters l, a, s, t  etc etc)

 

$first_name -- as as above

 

$sql = "SHOW COLUMNS from edselford_1963";
$result = mysql_query($sql);

while ($column= mysql_fetch_array($result)){
$arg="";
$val=$column[0];
${$arg . $val} = $val;
echo $val . "<br>";
}

does that help?

[dirkers]

Slightly better to store the column names in an array rather than to pollute the name space. Since you don't necessarily know the names of the variables, you might be overwriting something unintentionally. Also, using an associative array to fetch the results makes it a little clearer, I think.

 

$sql = "SHOW COLUMNS from answers";
$result = mysql_query($sql);

// array to store column names
$colNames = array();

// iterate over result set
while ($row = mysql_fetch_assoc($result)) {

        // append column name to array
        $colNames[] = $row['Field'];
}

// output column names for testing
print_r($colNames);

[kristian_gl]

thanks guys, I will try your suggestions tomorrow

[kristian_gl]

It wasn't exactly what I was looking for.  I need to give the different values in the array different variables, which only contain one of each value in the array

[dirkers]

Not sure what you mean. Can you give an example?

 

-D

[Mchl]

extract?

[kristian_gl]

yes, I have looked at extract(), but I can't seem to make it work. If somebody could show me exactly how to give each value in:

$columns[0] a different variable it would be really helpful. Remember that I dont know the what the values are, and how many of they there are at any given time, so it makes it a bit more complicated.

[litebearer]

can you show us a psuedo coded example of what you are looking to do

[kristian_gl]

Pseudo code:

 

1. get column names from table "answers"

2. put each column name in a variable (maybe $col1, $col2, $col3 and so on..)

3. use these variables in a SQL-query ( something like this: $sql = "INSERT INTO $db. `answers( `$col1`, `$col2``$col3`) VALUES (NULL, \'hei\', \'\', NULL);";

 

 

[PFMaBiSmAd]

the code needs to work if there is just one or hundreds of columns

 

Are you sure you are not really talking about rows? Because a table design that is dynamically  adding columns as the number of questions/answers changes would be a bad design and result in horrendous code to make it work (as has been demonstrated by the lack of progress in this thread.)

[PFMaBiSmAd]

In fact, I just reviewed your three threads and you are apparently dynamically adding tables (your first thread) and columns (your second and this thread.) Doing both of these things over complicates the process of managing data, takes more code to achieve, results in slower processing (you must execute multiple queries to just find the data), and results in a design that is not flexible.

 

What exactly is the overall goal you are trying to accomplish?

[kristian_gl]

yes I know that my design is not optimal, but it's just for a school project, and I can live with some flaws (better that then going back to change the whole setup). So if anybody could help me with:

1. get column names from table "answers"

2. put each column name in a variable (maybe $col1, $col2, $col3 and so on..)

 

it would be extremely helpful

 

 

[Mchl]

$query = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'YourDatabase' AND TABLE_NAME = 'YourTable'";
$result = mysql_query($query);

$columns = array();

while($row = mysql_fetch_row($result)) {
   $columns[] = $row[0];
}

var_dump($columns);

[kristian_gl]

thanks Mchl, just what I was looking for