0

I've pretty much never coded in PHP and MySQL before, so you'll have to excuse my god awful code.

I have a 3 columns in my database. ID, Class, and Need.

Tables

Basically, I'm trying to find the easiest way for me to code a page in order to allow users to change the "Need" value of each row from 0 to 1, or 1 to 0.

Currently I have it working where I have 2 buttons that run 2 separate but almost identical scripts whose only purpose is to change a specific row's "Need" value to 1 or 0 (shown below)

<?php
mysql_connect("localhost", "muffins", "sugarcookies") or die(mysql_error());
mysql_select_db("crackers_cheese") or die(mysql_error());

mysql_query("UPDATE recruitment SET Need=1
WHERE ID='1'");

mysql_close($con);

header("location: /admin.html"); 
?>

The inverse .php file is identical except for "SET Need=0"

So, what can I do instead of having 22 individual .php files whos only purpose is setting each specific row to 0 or 1.

Thanks!

Community
  • 1
  • 1
Cassette
  • 65
  • 1
  • 7

2 Answers2

1

Have one of your buttons send an HTTP parameter to indicate whether the field should be 0 or 1. Also, look at using the PDO class instead of mysql_* functions.

Here's an example of how it can be done using PDO functions:

PHP code

<?php

if(!isset($_POST['need'])) {
    die('Missing need parameter');
} elseif($_POST['need'] !== '0' && $_POST['need'] !== '1') {
    die('Invalid need values');
}

if(!isset($_POST['id']) || !is_numeric($_POST['id'])) {
    die('Missing ID');
}

$db = new \PDO('mysql:dbname=crackers_cheese;host=localhost', 'muffins', 'sugarcookies');
$statement = $db->prepare("UPDATE `recruitment` SET `Need` = :need WHERE `ID`= :id");

$statement->bindValue(':need', $_POST['need'], \PDO::PARAM_INT);
$statement->bindValue(':id', $_POST['id'], \PDO::PARAM_INT);

$statement->execute();

header("location: /admin.html"); 
?>

Note that since you are using a prepared statement here, the data does not need to be escaped using real_escape_string or anything like that, thus mitigating SQL injection attacks.

It is good practice to use parameter binding instead of concatenating a query to prevent SQL injection attacks. For more information, see: Are PDO prepared statements sufficient to prevent SQL injection?

HTML form

<form method='POST' action='update.php'>
    <label for='id'>ID:</label>
    <input type='number' name='id' id='id'>

    <input type='radio' name='need' value='0' id='needfalse'> <label for='needfalse'>Need = 0</label>
    <input type='radio' name='need' value='1' id='needtrue'> <label for='needtrue'>Need = 1</label>

<button type='submit'>Submit</button>

This isn't the best form designed. I've used radio buttons here, it would be slightly more complex to do it with actual buttons, but this should be sufficient to get you started.

Community
  • 1
  • 1
rink.attendant.6
  • 44,500
  • 61
  • 101
  • 156
  • 1
    It's not the best idea to use GET requests for [unsafe actions](http://stackoverflow.com/questions/1100366/get-vs-post-does-it-really-really-matter), and this definitely could use an authorization check (including any necessary [CSRF](http://en.wikipedia.org/wiki/Cross-site_request_forgery) protection) to prevent Googlebot from deleting or overwriting everything. At least there is protection against SQL injection. – PleaseStand Jan 19 '13 at 08:34
  • @PleaseStand You're right, I've updated my answer to use HTTP POST in accordance with the HTTP specifications. – rink.attendant.6 Jan 19 '13 at 08:37
  • I feel like I'm missing something simple. I replaced my php file with the one above, but whenever I go to www.xxx.com/insert.php?id=1&need=0, it returns with a server error. Am I formatting the URL wrong? – Cassette Jan 19 '13 at 08:42
  • I've updated my example to use HTTP POST, so you would need to be `POST`ing from an HTML form (you did mention that you were clicking buttons, so I'm assuming an HTML form). I'll update my example with some HTML code. – rink.attendant.6 Jan 19 '13 at 08:43
  • Neither presence nor absence of real_escape_string will cause or mitigate injection attacks. It's irrelevant matters. One can speak only of a good practice but I wouldn't call manual binding one. – Your Common Sense Jan 19 '13 at 08:48
  • @YourCommonSense From the answer posted at http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection , I'm under the impression that using prepared statements with parameter binding will defend from SQL injection attacks. Am I interpreting something wrong here? – rink.attendant.6 Jan 19 '13 at 08:55
0

Assuming you just want to toggle (set to 1 if it is 0 or set to 0 if it is 1) you could use the following SQL update statement:

update table_name
set need = abs(need - 1)
where id = 1
Verma
  • 956
  • 6
  • 21