-2

I have issue on INSERT INTO query, In the registration form, when user enter username and select the department/center as bursary, I want to insert that UserName into table bursary.

What I tried is;

$query14 = "INSERT INTO bursary (UserName) VALUES                   
('$UserName') WHERE DepartCent='Bursary'";
$result14=mysql_query($query14);

My table name is bursary, and it's look like ;

UserID      UserName

  1        (        ) <---- I want only UserName that choose department/center as bursary

Please anyone help me to solve this, appreciate that.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
satish
  • 41
  • 4
  • 2
    **WARNING**: This is terrifyingly insecure because those parameters are not [properly escaped](http://bobby-tables.com/php). You should **NEVER** put `$_POST` data directly into the query: it creates a gigantic [SQL injection bug](http://bobby-tables.com/). `mysql_query` is an obsolete interface and should not be used, it's being removed from PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). A guide like [PHP The Right Way](http://www.phptherightway.com/) explains best practices. – tadman Dec 01 '14 at 19:43
  • 3
    Are you certain you want to use an insert and not update? – CBredlow Dec 01 '14 at 19:43
  • 2
    An `INSERT` statement has no `WHERE` clause because by definition it is only for adding new rows, not modifying existing ones. You are looking for `UPDATE bursary SET Username = '$UserName' WHERE DepartCent = 'Bursary'` – Michael Berkowski Dec 01 '14 at 19:44
  • @MichaelBerkowski I tried the query but failed sir :( – satish Dec 01 '14 at 19:50
  • If it failed, you must check `mysql_error()` to find out why. Likely it is due to faulty escaping. As mentioned above, the SQL injection vulnerability already present can cause plain old malfunction too. See [how can I prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) -- now is the time to begin learning to use PDO with `prepare()/execute()` because the `mysql_*()` extension has been deprecated. New code should not be written with it. – Michael Berkowski Dec 01 '14 at 19:53

2 Answers2

0

You can either:

  • insert new rows in a database table
  • update existing rows in a database table

Only when you are trying to change values of existing rows does it make sense to specify a WHERE clause to let the database know which values you want to change. EDIT: Could you explain again what it is exactly that you are trying to achieve? Do you want to insert only some users filling out your form? Namely those that choose bursary as their department? Why don't you change your table structure to something like: Table DEPARTMENT: Department Name, Department ID

Table USER: User Name, Department ID (foreign key to Department)

EDIT Nr. 2: The way to do that is by normalizing your Table structure. Suppose one of the following things happened:

  • You want to add an additional department later on - you would not want to create a new table for that every time.
  • You want to change the name of a department - do you want to rename your tables? change your code,... I doubt that

So the way to go is to design your tables in a way that they separate the different "things" in your program. One type of thing you are working with are departments (bursary,...) another type of thing are Users. As a rough starting point try to make a table for each and try to connect the tables with so called foreign keys. Read it like this:

  • Every department has a unique department ID
  • Every User is associated to a department by this users Department-ID

You can then later on join these tables to find out all users of department X,...

Select u.userName,d.departmentName from User u INNER JOIN Department d ON u.departmentId=d.departmentId

This would show you the names of all your users and their associated departmentName

markus
  • 1,631
  • 2
  • 17
  • 31
  • Actually there is a lot of department and center, and I want the username being separated according to their department. – satish Dec 01 '14 at 19:54
0

INSERT queries imply adding data that isn't there already. If you want to insert a value derived from a query you need to use an actual query to get it. The WHERE clause fails because there no row to examine until you insert one.

There's really not enough info to figure out what you're trying to do, but if we just go on this part of your question:

"I want only UserName that choose department/center as bursary"

Then you are probably looking for an INSERT..SELECT (assumes you have already inserted the data into some other user table 'your_user_table')

INSERT INTO bursary(UserName) 
SELECT UserName from your_user_table
WHERE DepartCent='Bursary';
Gregory Patmore
  • 1,154
  • 11
  • 11
  • Its working but the issue is, everytime a registration is done, the username is creating duplicates UserName which is already is in the bursary tables – satish Dec 01 '14 at 20:07
  • This is because you are simply creating new rows in your bursary table everytime you execute the statement. You need to think about when you want INSERT's to happen and control that in your program – markus Dec 01 '14 at 20:10
  • I wasn't advocating this to be a working query, only to populate the table initially (sorry if I misinterpreted). – Gregory Patmore Dec 05 '14 at 17:15