1

I had a problem where I couldn't insert data into a table because the columns were not named correctly. They were simply column1, column2, etc. Instead of Date, Address, City, etc. I did not design this database and there is no way for me to change how these tables are made so I decided to insert the names of the columns in the first row below them. Now I have a corresponding piece of data to link them together. So now I am making an array of all the column names, and an array of all the values in row 1 so I can combine them into one associative array. I will later use this as a key for my insert statement.

That may of been confusing so here is what my table looks like

id | column1 | column2 | column3
 1 |   Date  | Address |  City

However the next time it could have more columns, with different names so it would look like this

id | column1 | column2 | column3 | column4 | column5 
 1 |  Apple  | Orange  | Tomato  |   Pear  | Banana

Now here is the code I'm using to try and make my Associative Array:

$col_keys = array();
$col_values = array();

$columns = "SELECT column_name FROM information_schema.columns WHERE table_name = 'mytablename'";
$columns_info = "SELECT * FROM 'mytablename' WHERE 'id' = '1'";    

if ($query_run11 = mysql_query($columns)) {
    if (mysql_num_rows($query_run11) == NULL) {             
            $response["success"] = 0;         
            echo $response;
    } else {        
        while ($row = mysql_fetch_assoc($query_run11)) {            
            $col_keys['key_name'] = $row['column_name'];                        
        }           
    } 
}

if ($query_run12 = mysql_query($columns_info)) {
    if (mysql_num_rows($query_run12) == NULL) {             
            $response["success"] = 0;         
            echo $response;
    } else {        
        while ($row = mysql_fetch_assoc($query_run12)) {            
            $col_values['value_name'] = $row['column_name']; // PROBLEM                     
        }           
    } 
}

$final = array_combine($col_keys, $col_values);
//echo '<pre>'; print_r($final); echo '</pre>

As commented in the code the line below is a problem. 

$col_values['value_name'] = $row['column_name'];

It's a problem because 'column_name' is not the name of the column. Obviously that was the whole problem to begin with, there's no way to know what the column name is. It could be column1 - column 67.

So my question is how would I do this?

Also if there is an easier way to do all of this I'm all ears. This is a poor solution but it was the best I could come up with so far.

EDIT

There seems to be a lot of confusion on my overall goal. It's understandable as this database design sucks and its a complex problem. The column names are actually the value of the question. The rows underneath them are the answers to those questions. So eventually this is my goal. I will have an Associative Array that looks like this

column1 => Date
column2 => Address
column3 => City
...

That way I can write an INSERT that inserts 06-22-2013 (the answer to Date) into column1 because column1 => Date.

If more explanation is needed I will add to this.

Improve this question
9
  • Why do you not know the name of the columns, aren't they column1 and so on? Commented Jun 22, 2013 at 23:32
  • @silkfire they are, but they will be column1, column2, etc. So it's not like I can say $row['column1'];. If I'm wrong let me know. Also the whole reason behind all of this is these tables are full of questions, and the questions will be different each time. So what the column names should be will be different each time. Next time the table might have column names of "apple, banana, pear". Which is why I need to create this associative array each time before I make my insert statement otherwise I will never know where to put the values Commented Jun 22, 2013 at 23:37
  • Is it different number of columns for each time, besides different names? Commented Jun 22, 2013 at 23:43
  • @silkfire yes, It could be 10 questions the first time, which would be 11 columns (including the 'id' column) and then 650 the next time. However the number of columns, and the number of questions are always the same. So an associative array makes sense because both array will always be the same length Commented Jun 22, 2013 at 23:44
  • 1
    Like $row['column' . $i], providing you know when to stop, but see my answer down also. Commented Jun 23, 2013 at 0:25

3 Answers 3

Reset to default
1

First, switch to PDO or MySQLi, see big warning here; this is PDO code:

$dbh = new PDO('mysql:host=localhost;dbname=my_name', $user, $pass);
$stmt = $dbh->query($columns_info);
$result = $stmt->fetch();
$col_values = $result;

After that you can combine them:

$final = array_combine($col_keys, $col_values);

But inserting different kind of data into a same column... looks like a problem. Just populate a separate table like:

+------------+-------+
| column_id  |  name |
+------------+-------+

every time, and clear it after use. column_id could be 1,2,3... with AUTO_INCREMENT

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I tried using PDO and I can't get it to work. It never actually performs the queries this way. I think it's the database I'm working with.
That's actually a really good idea about the separate table though. I didn't think of that. Thanks!
PDO will connect same way, see here, your db has nothing special.
1

Just perform a regular INSERT, specifying null as the first value so that the id column auto increments correctly.

Improve this answer

9 Comments

My whole problem is the column names represent questions. The insert statement represents the answers to those questions. So Weight would be the column name where as 177.5lbs or 391.2kg would be the answer inside the row. If I perform a normal insert it has no idea what order to put the data in and it will stick Phone Number under the weight column and any other number of whacky outputs
You do know that the whole table design is royally screwed, it is the completely wrong way of building a table...
Now that being said, once this associative array is complete, I will perform an INSERT based on column1 => Weight, so INSERT 177.5lbs into column1
Oh yes my friend, I know all too well how poorly this was designed =). This database is a nightmare. I had no idea what I was getting into when I started to design my Android App.
Hmm, and there's no API you can use? It doesn't make sense that the column name is a question. You can't put any characters you want in a column name, you know =S
|
1

the column names are already in array.

$sql = "SELECT * FROM 'mytablename' WHERE 'id' = '1'";    
$query = mysql_query($sql);
if (mysql_num_rows($query) == NULL)
{
echo 0; 
}
else
{

while ($row = mysql_fetch_assoc($query)) { 
$id=$row['id'];

// you build your out in here.

$id[$id]=$id;
$date[$id]=$row['column1'];
$address[$id]=$row['column2'];
$city[$id]=$row['column3'];


}


}

http://www.php.net/manual/en/book.pdo.php

https://www.php.net/manual/en/mysqlinfo.api.choosing.php

Improve this answer

2 Comments

by this logic then all I need to do is have take $columns and $columns_info and just combine them? like this - $final = array_combine($columns, $columns_info); ???
what do you want to do with the data?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.