When coding in PHP you may at some point need to run queries in a MySQL database. Just like many other database applications, MySQL has several types of queries to perform adding, updating and removing of data.
All of the various types of queries are processed by the mysql_query() function.
To run an insert query:
$sql = "INSERT INTO `users` (`UserID`, `name`, `email`) VALUES (NULL, '$name', '$email');"; mysql_query($sql) or die('Unable to process query: '.mysql_error());
Here we are placing the query into the $sql variable, the second line then processes the query. If the query could not be run, the script exits and shows the error that was produced. We are inserting this data into the users table which has columns of UserID, name, and email.
To update a record in the users table you would run:
$sql = "UPDATE `users` SET `name` = '$name', `email` = '$email' WHERE `UserID` = $_POST[id] LIMIT 1"; mysql_query($sql) or die("Unable to process query: " . mysql_error());
Here you specify the table right after the UPDATE and then specify all fields you wish to update after the SET. The last half of the query we are specifying which record we wish to update and limiting the update to only one record.
Now to delete a record or multiple records, you would use:
$sql = "DELETE FROM users WHERE UserID = '$UserID' LIMIT 1"; mysql_query($sql);
This is a very short query that shows the table which we would like to delete a record from and the criteria that we would like to remove. Since we only wish to remove one record, LIMIT 1 is added to make sure we only remove one record.
Anytime that you run a query, you need to make sure that you escape all of your user input variables. If you fail to do this, you risk the chance of somebody performing an SQL injection on your database. One of the easiest ways to perform this is to use the mysql_escape_string() function which preps the variables for use in your query.
(2 votes, average: 4.00 out of 5)
Loading ...
July 25th, 2009 at 4:34 am
why name it $sql when you can use the same name over and over and write it shorter, so it not only loads the string faster but takes up less room when editing the script?
$q = “SELECT * FROM `table`”;
$res = mysql_query($q);
or just run the query
mysql_query($q);
Also i have a question:
When limiting to one row a example of this would be authentication right?
IF a user is authenticated we will have a query that selects from the usertable.
$q = “SELECT * FROM `users`
WHERE `username`=’EXAMPLE’ AND `password`=’EXAMPLE’”;
(LIMIT 1) ?
$q = “SELECT * FROM `users`
WHERE `username`=’EXAMPLE’ AND `password`=’EXAMPLE’ LIMIT 1″;
// SELECT 1, ROW FROM `users` (WHERE)
Username & PASSWORD ARE EQUAL TO OUR POST DATA?
July 31st, 2009 at 1:59 pm
Writing some variables like $sql for queries is just something I have done for a while. It is just something I know exactly what it is and where it will be used. I reserve certain variables for certain types of code and that is just one of them I use personally.
Yes when creating a user authentication script you would want to limit 1 in your query since once you have found your record you don’t need any others.