Inserting data into a sql query from a text box in a html form

Question

SELECT
ticketDescription,ticketTimeDate,ticketCategory,ticketServerity,user.userLocation,user.userFirstNames,user.userSurname,user.userDepartment,user.userContactNumber,user.userEmailAddress
FROM ticket
LEFT JOIN ticketHistory ON
ticket.ticketID = ticketHistory.ticketID
LEFT JOIN technician ON
ticketHistory.technicianID = technician.technicianID
LEFT JOIN user ON
ticket.userID = user.userID
WHERE ticketResolved = '0'
AND technician.technicianID = '1'

I am trying to replace the integer where the sql query is returning results based on the technician ID. (the last line). I want to replace the '1' with a value from a text box on a html form which the user will fill in and click submit. Then the html form's action would put the value from the text box into the AND technician.technicianID = '**' where the text box's value would replace the **.

Seems simple enough, but has completely stumped me!

it is not the right way. See some basic of php and html fom tutorial by googling — Awlad Liton, May 06 '14 at 11:23
And tag your questions with the database you are actually using. SQL Server and MySQL are two different products. — Gordon Linoff, May 06 '14 at 11:24
check this: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Tanner, May 06 '14 at 11:26
Note that your WHERE condition nullifies the outerliness of your outer join ;-) — Strawberry, May 06 '14 at 11:27
Have you tried on of the hundreds of MySQLi/PDO tutorials that use prepared statements? — CodeZombie, May 06 '14 at 11:30
you can store your textbox result into a variable like '@input1' and you can use this variable @input1 in place of 1. — Zafar Malik, May 06 '14 at 11:34
It appears I am going about this in wrong direction. Oops. Could I replace the criteria with a session variable that contains the technician ID from when they log in? — murry_minton, May 06 '14 at 11:35
@Zafar Malik sounds exactly what I am trying to do, how would I format this within a SQL query, do I just use AND technician.technicianID = '@variable_name' — murry_minton, May 06 '14 at 11:44

score 0 · Accepted Answer · answered May 06 '14 at 11:49

You can do like below:

set @input1=1;

SELECT
ticketDescription,ticketTimeDate,ticketCategory,ticketServerity,user.userLocation,user.userFirstNames,user.userSurname,user.userDepartment,user.userContactNumber,user.userEmailAddress
FROM ticket
LEFT JOIN ticketHistory ON
ticket.ticketID = ticketHistory.ticketID
LEFT JOIN technician ON
ticketHistory.technicianID = technician.technicianID
LEFT JOIN user ON
ticket.userID = user.userID
WHERE ticketResolved = '0'
AND technician.technicianID = @input1;

Inserting data into a sql query from a text box in a html form

1 Answers1