Writing to m

[forumlogger]

I am confused !!.. ( quickness's easy really )

My insert query doesnt work

    $copy_id = $_POST['id'];

    $query = "CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM clients WHERE id=" . $copy_id . " ; ";
    $query .= "  UPDATE tmptable_1 SET id = NULL;";
    $query .= "  INSERT INTO clients SELECT * FROM tmptable_1;";
    $query .= "  DROP TEMPORARY TABLE IF EXISTS tmptable_1;";

$myfile = fopen("newfile.txt", "w") ;
$txt = $query;
fwrite($myfile, $txt);
fclose($myfile);

    $cpy   = db::query($query);

 if I look at the newfile.txt it says

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM clients WHERE id=37 ;   UPDATE tmptable_1 SET id = NULL;  INSERT INTO clients SELECT * FROM tmptable_1;  DROP TEMPORARY TABLE IF EXISTS tmptable_1;

If I run that in mysql..voila works great

 

but the db::query does nothing ?

What am I doing wrong

[Barand]

You need an individual $db->query() for each of the 4 queries.

[requinix]

Why are you using a temporary table to, effectively, copy a single record #37? Why not just do it normally?

[gizmola]

3 hours ago, requinix said:

Why are you using a temporary table to, effectively, copy a single record #37? Why not just do it normally?

Yeah, really makes me wonder what this is for.  I'm guessing it's some sort of home grown development/test tool to clone an existing row.  Under the circumstances, I don't see a better way of doing it, as there's no non-trivial way of getting all the columns other than id that doesn't involved either something like this, or querying table definition from the information schema and using that to generate a sql statement.

I'd suggest adding ENGINE=Memory for the temp table definition.

Also you don't have to drop the table, as it will be dropped when the connection is closed.  That is the point of declaring a table as temporary.

 

[requinix]

2 hours ago, gizmola said:

Under the circumstances, I don't see a better way of doing it, as there's no non-trivial way of getting all the columns other than id that doesn't involved either something like this, or querying table definition from the information schema and using that to generate a sql statement.

Doesn't have to be complicated: SELECT *, overwrite the ID with null in the returned array, and insert back - without naming columns. You don't know how many columns or what order they come in, but that doesn't matter because the code simply repeats it back to the server.

[gizmola]

I didn't think about doing it wholly with PHP, but yeah, that is a good way to do it in this circumstance. 

Just 2 queries, even though you have to read the result -- it's 1 row. 

The main concern I would have, never trying this, is that the columns must be in the correct order.  You'd also have to escape the strings.  It's a bit tricky, unless I'm missing something.

[requinix]

Columns in the correct order isn't an issue, as long as you're careful with the array from the first SELECT - as in overwriting values (eg, the ID to be null) and never removing/adding values (eg, unset the ID and re-create it). It sounds riskier than it is. Worst case, though, fetching in an associative mode will give you the column names so you could do a "normal" INSERT that names the columns.

Quoting isn't an issue since you can do a prepared statement, albeit with a variable number of placeholders, and then feed the values array directly into it. Again, worst case you escape all the string things.

Anyway, this all feels like a one-off type of job, in which case you might as well just write the code specifically for that table and structure. And besides, this sort of copy operation tends to have nuances that can't be solved in a general-purpose way: are there other fields in the clients table that should not be copied verbatim, and/or are there rows in other tables that should also be copied?

[gizmola]

Yeah, certainly it's typical to have a whole set of relations you want to clone in most cases, so the entire premise here is simplistic, but an interesting challenge as is.  DBAL provides an interesting option, where it will expand parameters.  I'm playing with that at the moment, just to see how simple an app could be to do this somewhat generically.

[gizmola]

I made an app that uses DBAL to do this, as a quick and dirty proof of concept.  Anyone with an interest can check it out here:  row cloner

Took less than a day start to finish, including learning enough about Bulma to get a decent looking UI put together.