-2

I have a button

<button onClick=sAve('save','."$id."']."')>SAVE</button>

Once I click it, I want to use AJAX to pass the parameters to the PHP page to perform the following task to MYSQL:

switch... case "Save":

$sql1 = "INSERT INTO permanent_table (id, user, email) 
         SELECT id, user, email 
         FROM temp_table WHERE id='".$_GET['id']."'";
$sql2 = "DELETE FROM temp_table WHERE id='".$_GET['id']."'";

Whereby permanent_table having same structure as temp_table. I tried to run both queries just like that but it is not working, so I guess that is not the right way.

In my case, my question is:

  1. What is the real pro way to use PHP/sql to perform this task?
  2. Is there any way I can simplify/combine it to a single query?
Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
P. Lau
  • 165
  • 1
  • 11
  • You can run multiple queries with [mysqli_multi_query](http://php.net/manual/de/mysqli.multi-query.php), but you should explain what is not working with you way, do you get an error or what is happening? – xander Aug 15 '17 at 13:02
  • read this: https://stackoverflow.com/questions/13980803/executing-multiple-sql-queries-in-one-statement-with-php/13982705#13982705 – Genaro Morales Aug 15 '17 at 13:03
  • I think the most important thing to advise, above other elements, is perhaps the use of a prepared statement. It looks like you're injecting a value into an SQL statement directly from the URL, with no sanitation. Fixing this should be a higher priority, which I think is closer to the answer of a better way to perform this task (even if it skirts around the optimisation issue). – Chris J Aug 15 '17 at 13:10
  • first of all: where do you want the phone value to go? You want to insert 4 values into 3 columns. Second: you could write a procedure to do this in 1 query. (a transaction could also be in place, should either of the queries fail) – Ivo P Aug 15 '17 at 13:02

1 Answers1

0

I think this is you want

<button onClick="$.post('/php file name/', {'save','."$id."'}, function(data){});">SAVE</button><br/>

Then you could run some query's in the PHP file.

$sql = $conn->prepare("INSERT INTO `permanent_table` (`id`, `user`, `email`) VALUES(?, ?, ?)";
$sql->bind_param("sss", $_GET['id'], "", "");
$sql->execute();

$sql = $conn->prepare("DELETE FROM `temp_table` WHERE `id`=?");
$sql->bind_param("s", $_GET['id']);
$sql->execute();

$sql = $conn->prepare("SELECT `id`, `user`, `email` FROM `temp_table` WHERE `id`=?");
$sql->bind_param("s", $_GET['id']);
$sql->execute();
//select last so that you can grab the results

Run them on your way (PDO or MYSQL)
I know a lot about query's so feel free to ask something to me!

Webdeveloper_Jelle
  • 2,868
  • 4
  • 29
  • 55
  • 1
    *I know a lot about query's so feel free to ask something to me!* - Then you'd be instructing them to use prepared statements instead of passing variables directly. It is preferred not to use inline events rather attach them to the element in question – Script47 Aug 15 '17 at 13:12
  • yeah I know but I give his answer on how he does it. – Webdeveloper_Jelle Aug 15 '17 at 13:13
  • Besides the point, you could improve your answer by giving them extra information regarding security, it won't do you any harm. – Script47 Aug 15 '17 at 13:14
  • 1
    Please do not give examples using an old deprecated API like mysql – Rotimi Aug 15 '17 at 13:15
  • 2
    **The `mysql` PHP extension is dead** -- Stop using the [`mysql` PHP extension](http://php.net/manual/en/function.mysql-connect.php). It is old, deprecated since PHP 5.5 and completely removed in PHP 7.0. Use [`mysqli`](http://php.net/manual/en/book.mysqli.php) or [`PDO_mysql`](http://php.net/manual/en/ref.pdo-mysql.php) instead. – axiac Aug 15 '17 at 13:15
  • Your syntax is wrong in your update, prepared variables don't need to be wrapped in singlequotes or doublequotes. – GrumpyCrouton Aug 15 '17 at 13:19
  • oops forgot about that ! – Webdeveloper_Jelle Aug 15 '17 at 13:21
  • In the question there is no `$_GET['user']` or `$_GET['email']`. Only a `$_GET['id']`. So the insert query could be done the way it is in the question. (apart from the prepared statement). And a check if the insert was successful before throwing away the temp information, would be handy. – Ivo P Aug 15 '17 at 13:30
  • thats true, but I added those values to show him what you possible can fill in – Webdeveloper_Jelle Aug 15 '17 at 13:31