How to execute mysql on button click

Question

PHP:
How would I execute SQL when a button is clicked? Would I do it with the JavaScript onClick() function, or some other way? I am trying to do this inside of a loop, and only execute the sql on the row that the button is clicked on... Thanks!

Code for @PHPnoOb to help: Okay, so now I have all of that sorted out, but when the button is clicked it executes once for each row... not just once for the row the button was clicked on! I only want the row that the button was clicked on to be queried... my code:

while($row = mysqli_fetch_array($result)) {

//FAULTY CODE!!! EXECUTES ONCE FOR EACH ROW!
//I WANT IT TO ONLY EXECUTE FOR THE ROW THE
//BUTTON WAS CLICKED ON

if(isset($_POST['delete'])){
echo "You clicked on: ".$row['subject'];
//eventually i will have sql query up here
}
//echo all the results into a table... messy
echo"

<tr><td><div align='center'><font color='grey'>".$row['date']."</font></div></td><td><div align='center'> ".$row['sender']."</div></td><td><div align='center'> ".$row['subject']."</div></td><td><div align='center'><input type='button' value='open' onClick='window.alert(\"On ".$row['date']." ".$row['sender']." wrote:\\n".$row['message']."\")'/></div></td><td><div align='center'><form  method='post'><input type='submit' value='delete' name='delete'/></form></div></td></tr>

";

}

echo '</table>'; //ends the table.

Solution 1

You can do something like this to save a value from an input/submit btn.

<!-- your html form -->
<form action="POST">
    <input type='text' name='username' />
    <input type='text' value='submit' />
</form>



<?php 

// your php code

if($_POST && isset($_POST['username'])){

    $db = new \PDO('......'); // enter your db details

    $stmt = $db->prepare("INSERT INTO table (username) VALUES (?)");
    $result = $stmt->execute(array($_POST['username']);

    echo $result->rowCount() ? 'Username saved in db' : 'Unknown error occured'; 

}

Solution 2

Update: 2017

This answer is outdated, please use better library like PDO to accomplish the below feature.

---

Ok, just copy/paste the whole code in a plain PHP text. It works, I tried it just now. All you need is a table called test, with fileds id,username, or you can costumize the script, however you like. and don't forget to change databse password, username details..

<form action='' method='POST'>
<?php

mysql_connect('localhost', 'root', '');
mysql_select_db('test');
$query  = "SELECT * FROM users";
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
    echo "  To delete user   <b>" . $row['username'] . "</b>  Click on the number <input type='submit' name='delete' value='" . $row['id'] . "' /><br/>";
}

if (isset($_POST['delete'])) {
    $user = $_POST['delete'];
    $delet_query = mysql_query("DELETE FROM users WHERE id = $user ") or die(mysql_error());

    if ($delet_query) {
        echo 'user with id ' . $user . ' is removed from your table, to refresh your page, click' . '<a href=' . $_SERVER["PHP_SELF"] . ' > here </a>';
    }
}
?>
</form>

Solution 3

Use an AJAX library to issue a request to a server-side script that executes your query. Put this as your onClick() handler in Javascript, and presto, Bob's your uncle!

Comments

[pattyd]

stackoverflow.com/questions/12166494/… does not help, I do not want to submit the whole form...

[Qantas 94 Heavy]

You'd need to send an HTTP POST/GET request to be able to tell the PHP server when the button is clicked, and have PHP call any database functions. Most of the time this is achieved using a HTML form, but not necessarily.

[pattyd]

Why was this closed? It is not difficult to understand

[pattyd]

oh! that looks right! thanks, testing now!

[pattyd]

and this works on the same page as the button, correct?

[samayo]

@pattyd as long as your button name is submit yes, it will, just try writting anything inside the field and echo it inside the if(isset)

[pattyd]

ok, above the button, or below?

[samayo]

The both. You fill something in the above, and press the submit button

[pattyd]

gotcha, one minute please

[pattyd]

that worked! thanks! I will accept the answer as soon as Stack Overflow allows! Thanks Again!!!

[samayo]

No problem. Feel free to ask me more if you have anything on this subject, maybe one more question.. I love helping :)

[pattyd]

Okay I have one more..... I do this inside a loop okay? so i want to delete the row that the button is clicked on.... but it deletes all of the records, not just the one record that is clicked! I may have to sign off, but I can post more soon, if needed! Thanks so much!

[samayo]

You can do the same, inside the if statement, create another if, and check if something is pressed with specific name(button name) then, use that value to delete a record. I didn't clearly undestand the question though

[pattyd]

I mean I am looping over the table rows result, printing all the rows to the screen. Beside each row is a button that says delete.... when i press it i tell it to execute echo "hi"; and it echoes that as many times are there are rows.... I only want the function to be called once... once for the row it is pressed on.... does that give better detail? Or should I share more?

[samayo]

Give me some codes, as I am answering another question, I am not in focus. I undestood some, but not all. sorry

[pattyd]

okay.... i will add in an edit

[pattyd]

PHP Noob: its in the edit, thx!

[samayo]

give me a minute, never built a delete by row query, but it is simple possible to create a quick one.

[samayo]

@pattyd check my other answer, for clarity sake, I have made another one. I hope it gives you some idea, if not you can re-ask by use the code, and some SO experts will help you out. just make sure to post clear questions

[pattyd]

Thanks alot! That is exactly what I meant! I haven't tested this yet, but It looks right! Thanks!

[pattyd]

testing now.... there are some errors in your code: change eles to else and mysq to mysql testing now,...

[samayo]

Ok, sorry about that.

[pattyd]

ok this seriously isnt working..... could you please incorperate this into my code that is in my question? I really appreciate it.

[pattyd]

thanks for your help so far, i cant get it to work with my code, could you please show with my code? thanks

[samayo]

@pattyd ok, but give me some time. I will try to create it in my localhost, and i'll give you when I finish.

[samayo]

@pattyd Ok, I just remembered like 10 minutes ago, and tested this script before I sleep. It works, but, I may build something better and that works with Javascript/Ajax tomorrow and will give you, if ur still interested. cause, I enjoy make those little scripts.

[pattyd]

ok thanks! All i need is something that works, i definatly would prefer PHP, even if it is a big mess, AJAX/js works too thx!

[pattyd]

PHPNOOB IS THE MAN! THX SOOOOO MUCH DUDE! The only error in that code was that the delete button had to be nested in a form: <form method='post'>buttonhere</form> THANKS AGAIN!!!! I hope to see you again on another SO forum!

[pattyd]

I will keep the old answer as the accepted answer though, it doesnt really make a difference cuz they are both yours! Thanks!

[pattyd]

also, if you want the button value to be something other than the ID you can turn the button into a hidden field, and have a new submit button with a value of Delete, or what ever you want it to say.

[samayo]

glad it worked for you :),try to add a message saying 'are you sure u want to delete this/that user' functionality, and it will be good, I have an empty room on ajax :( drop me a message, if u want

[pattyd]

okay thanks! I don't think i will add the confirmation yet. How do i send you a message? Sorry, kinda new to SO!

[samayo]

I am not sure, if you have enough reps to chat, but you can follow the link i gave you above, and just post message there, if not u have to wait a little bit, until you earn more reps

[pattyd]

yeah i can chat, what link are you talking about?

[samayo]

click right here