Difficulty querying two tables in mysql with php

Question

I have a database in MYSQL and it contains 2 tables:

Table: political_party
+----------+--------------------+-------+
| party_id | party_abbreviation | party |
+----------+--------------------+-------+

Table: polling_party_result
+----+------+-----------------+
| id | p_id | number_of_votes |
+----+------+-----------------+

I am writing a PHP program that outputs a form, which UPDATES the political_party_result table WHERE the id runs from 1 to X.. The problem I'm facing is that on the form, the id relates to the party_abbreviation column in the above political_party table.

That is 1(in the political_party_result table) should bring out AP(from the political_party table) 2 = ADC
3 = PDP etc..

Here is my HTML code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>HTML INEC</title>
</head>

<body>
<form action="inechp.php" method="post" name="form1">
ID: <input name="id" type="text" /> <br /> <br />
Polling Unit: <input name="pid" type="text" /> <br /> <br />
Number of Votes: <input name="votes" type="text" /> <br /> <br />
<input type="submit" /> <br />
</form>

<form action="inechp.php" method="post" name="form2">
Polling Unit: <input name="unit" type="text" /> <br />
Number of Votes: <input name="nov" type="text" /> <br />
<input type="submit" />
</form>
</body>
</html>

Here is my PHP code:

<?php
$con = mysqli_connect("localhost", "root", "", "inec_results");

mysqli_query($con, "UPDATE inec_results.polling_party_result SET p_id ='$_POST[pid]' , number_of_votes = '$_POST[votes]' WHERE id = '$_POST[id]'");
?>

Forgive me if my explanations weren't satisfactory as I'm bit new to PHP.

Not an answer to your question, but you're using mysqli and concatenating $_POST variables directly into the SQL string - that's insecure. You should look at using a prepared statement and bound variables instead. — andrewsi, May 14 '13 at 15:50
See if http://stackoverflow.com/questions/9588423/sql-server-inner-join-when-updating helps... — Colas, May 14 '13 at 15:55
It's easier to use OO mysqli imho. $mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME); if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } $admin = "SELECT * FROM users WHERE uname =\"". $_POST["username"] ."\" AND pwd = \"".$_POST["password"]."\" AND `admin`=1"; $isadmin = $mysqli->query($admin) or die($mysqli->error); — rcbevans, May 14 '13 at 16:08
o0rebelious0o your answer is showing a bad way to code. Your introducing a SQL injection vulnerability. — David Hirst, May 14 '13 at 16:16
You probably want to make a pull down menu in your form to let people `select` one of the available parties. In the processing script, you might want to _check_ that the returned value is valid (ie. an _integer_ between `1` and `X`), and then use a properly parameterized query. Please look up both concepts, try implementing them, and come back with a more specific question if you get stuck later on. — didierc, May 14 '13 at 16:17
You're welcome. There are a lot of questions and answers here you could draw some inspiration from. Please look on the right side of the page for questions dealing with similar topics. — didierc, May 14 '13 at 16:29

score 0 · Answer 1 · edited May 23 '17 at 11:49

mysqli_query($con, "UPDATE inec_results.polling_party_result SET p_id ='$_POST[pid]' , number_of_votes = '$_POST[votes]' WHERE id = '$_POST[id]'");

This is bad for a number of reasons.

Firstly your not validating that the form data you have received is valid. E.g I enter an email address for your ID field and you just pass that directly to your database. This also leads onto the second big issue.

You should never place post data directly into SQL, as andrew mentions this is very insecure and a malicious user could quite easily drop your entire database by SQL injection.

Having said that I am not sure you are actually passing your post variables (its been a little while since I touched PHP) because the entire statement is in double quotes. This means those '$_POST[var]' parts are actually just being read in as part of the string literal. (Meaning your always passing the value $_POST[var] instead of the value from your form. You would need to brace or fullstop the vars '{$_POST[var]}' or '.$_POST[var].'(BUT AGAIN THIS IS BAD!).

What you should do is as mentioned used a prepared statement after you have validated your data. You can see a good example here How can I prevent SQL injection in PHP?

Difficulty querying two tables in mysql with php

1 Answers1