[SOLVED] Create a MySQL INSERT from a previous SELECT with 2 values changed

[SoWhy]

Hi there.

 

I want to do something that sounds easy in my mind but I cannot figure out the correct code for it.

What I want is this:

Use

SELECT * FROM table WHERE uid = xxx

and get a row in return.

 

In this row, I want to change the second value to the first value and then the first value to '' (auto-increment) and then create a

 

INSERT INTO table VALUES(...)

where "..." is the previously selected values with those small changes.

 

I hope that made some sense and someone can help me with a code to do that 

 

TIA

SoWhy

 

[Potatis]

By auto increment, do you mean increase by 1? or more? Is it a number? Do you really want to INSERT a new row with the new data, or UPDATE the existing row?

[SoWhy]

I mean that the first column in MySQL is set to auto increment (i.e. increases by 1 with each new row), so I do not want to insert a row that has a value for this field (thus leaving it blank - ''). But I want this value, that the SELECT query reported to replace the second value in this row (which is for a column not set to auto increment).

Yes, I want to insert a new row with the new data that is exactly like the previous row except it will get assigned a new uid (unique id due to auto increment) and the second column's value for this row will be the uid from the originally selected row.

 

Regards

SoWhy

[Ken2k7]

I think you're talking about UPDATE. Google MySQL UPDATE.

 

Although, INSERT can do that with ON DUPLICATE KEY UPDATE.

[SoWhy]

No, I'm not. I want to insert a new row which is like the old row but slightly different, so I know the MySQL part is correct. In the end, I want to have two rows.

 

Maybe I should elaborate a bit. Currently, I have to do these steps manually (values chosen randomly):

 

1. I open phpMyAdmin

2. I run

SELECT * FROM table WHERE uid = 14785

3. I go to export and export this query into a *.sql file which will then contain something like

INSERT INTO table VALUES (14785, 3669, barfoo, 124, foo, 558, 41114778, bar)

4. I manually change 3669 to 14785 and change 14785 to ''

5. I feed back the new query that looks like this

INSERT INTO table VALUES ('', 14785, barfoo, 124, foo, 558, 41114778, bar)

into phpMyAdmin

 

That's complicated and annoying to do and I think it should be easy to do with php doing those steps for me. I just cannot figure out how.

 

Regards

SoWhy

 

[premiso]

Ok, so what exactly are you asking here?

 

$result = mysql_query("SELECT * FROM table_name WHERE uid = " . $userid);

while ($row = mysql_fetch_row($result)) {
    $newSQL = "INSERT INTO table VALUES ('', {$row[0]}, '{$row[2]}', {$row[3]}, '{$row[4]}', {$row[5]}, {$row[6]}, '{$row[7]}')";
    mysql_query($newSQL);
}

 

Is that what you are looking to do?

[SoWhy]

Yes, that is approximately the thing I need. But I need it to work with multiple tables, which each have different amount of columns and this solution will only work if I write it manually for every single table, thus making it not really more efficient than doing it manually (as described above). Also, the tables have 80 or more columns so it would end in a very very long code (i.e. {$row[0]}, '{$row[2]}', {$row[3]}, [...] '{$row[80]}',  '{$row[81]}')

 

Regards

SoWhy

[fenway]

Then push an array and join it at the end.. and PLEASE don't omit the column names.

[SoWhy]

Then push an array and join it at the end.. and PLEASE don't omit the column names.

Could you elaborate that push-suggestion to a PHP beginner like me?

[premiso]

You should really post exactly what you want and what you have tried in the future. As it seems you are asking us to write the code for you.

 

If your table has that many columns then you can dynamically generate the SQL like so, as far as fenway's suggestion about columns I would agree.

 

$vals = array();
$cols = array();
while ($row = mysql_fetch_assoc($result)) {
    foreach ($row as $col => $val) {
         if (!is_numeric($val)) {
             $val = "'" . $val . "'";
         }

         $vals[] = $val;
         $cols[] = $col;
    }

    $newSQL = "INSERT INTO table (" . implode("`, `", $cols) . ") VALUES (" . implode(", ", $vals) . ")";
    mysql_query($newSQL);
}

 

Should generate your SQL for all the columns in that table. If you want to do the value switching you will need to add some logic inside the foreach. The above is untested.

[Ken2k7]

He just meant to put the values into an array and then convert it to a string of values so you don't have to manually type out $row[0] to $row[81] or whatever number it ends at.

 

By the way, if you have 81 columns, then you need to read up on DB normalization.

 

Edit: premiso, wouldn't your code leave an extra ", `" at the end of implode?

[fenway]

Actually, there are missing opening/closing backticks -- not that they should be there to begin with.

[SoWhy]

He just meant to put the values into an array and then convert it to a string of values so you don't have to manually type out $row[0] to $row[81] or whatever number it ends at.

 

By the way, if you have 81 columns, then you need to read up on DB normalization.

 

Edit: premiso, wouldn't your code leave an extra ", `" at the end of implode?

Well, couldn't that be solved with a

$newSQL = substr($newSQL, 0, -3);

It's not nice but that was the way I did it in my try as well.

 

@premiso: Sorry, I'm new here. Yeah, I have some code I tried but my way did not result in the correct number of cols. I can post it tomorrow when I'm back at work.

[premiso]

Edit: premiso, wouldn't your code leave an extra ", `" at the end of implode?

 

Nope. It will not. Give it a try.

 

However, I did set it up wrong. This would be the proper way to do that:

 

    $newSQL = "INSERT INTO table (`" . implode("`, `", $cols) . "`) VALUES (" . implode(", ", $vals) . ")";

 

Note the backticks after the ( and before the )

 

Or if you choose to omit the back ticks:

    $newSQL = "INSERT INTO table (" . implode(", ", $cols) . ") VALUES (" . implode(", ", $vals) . ")";

 

Should work as long as you are not using a reserved word for a column name.

[Ken2k7]

Wow, I am out of it today. I should get some rest soon. Sorry about that.

[SoWhy]

Thanks for the help, premiso. Your version worked perfectly (well, I had to change  mysql_fetch_row to  mysql_fetch_assoc but apart from that). I've use a simple dirty coding to do the value switching now I had an array to work with

$vals[1] = $vals[0];
$vals[0] = "''";

Works like a charm now, so thanks again for all the help guys :-)

 

Regards

SoWhy