INSERT
<?php
//connect to the DB using the hostname,username and password
$conn = mysql_connect('localhost','root','');
//select the database
mysql_select_db('db');
$query ="INSERT INTO mytable(name) VALUES('john')";
//execute the query
$data = mysql_query($query) or die("Prob with query");
?>
SELECT<?php
$conn = mysql_connect('localhost','root','');
mysql_select_db('new_db');
$query ="SELECT * FROM comments";
$data = mysql_query($query) or die("Prob with query");
while($rows = mysql_fetch_array($data))//An associative array is returned for each row every time while is true
{
foreach($rows as $row)
echo $row."<br/>";
}
?>
There are better ways of connecting to the Db called MySQLi(improved) and PDO(PHP Data Objects). They use objects for connecting and
there is also the option of using prepared statements which are much more secure.
MySQLi
//Create a new MySQLi class and a connection using the credentials and DB name
$conn = new mysqli('localhost','root','','new_db') or die("Error connecting to the DB");
$sql = "INSERT INTO comments(name,email,comments) VALUES('new','new','new')";
$result = $conn->query($sql) or die("Prob with query");
SELECT$conn = new mysqli('localhost','root','','new_db') or die("Error connecting to the DB");
$sql = "SELECT * FROM comments";
$result = $conn->query($sql) or die("Prob with query");
while($rows=$result->fetch_assoc())
{
echo "<br/>";
foreach($rows as $row)
echo $row."\n";
}
Prepared statements
A prepared query allows us to reuse the query. It is safer as well as more flexible.
$mysqli = new Mysqli('localhost','username','password','db_name') or die("Could not connect to the Db");
$stmt = $mysqli->prepare("SELECT name from Products where id = ?") or die("Error with the query");
$stmt->bind_param("i",$id);
$stmt->execute();
$stmt->bind_result($name);
$stmt->fetch();
$stmt->close();
1. A new connection to the mysqli class is made. In case of error, display the die stmt.2. Prepare the query. Prepare the query gives a compiled form of the query that can be reused with different values in place of the ?
3. Bind the paramters. i represents integer.
4. Execute the query
5. Bind the result to the variable.
6. Fetch the binded result.
PDO
PDO is in many ways advanced than MySQLi in that it supports many RDMSes. So if you want to shift to some other RDMS over time, using
PDO is your best bet.
<?php
$dsn = “mysql:dbname=mydatabase”;
$username = “root”;
$password = “ ”;
//PDO connections and queries are always done inside try catch statements to catch the errors.
try {
$conn = new PDO( $dsn, $username, $password );
$conn- > setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
} catch ( PDOException $e ) {
echo “Connection failed: “ . $e- > getMessage();
}
$sql = “INSERT INTO table VALUES (1,'indy)”;
try {
$conn- > query( $sql );
} catch ( PDOException $e ) {
echo “Query failed: “ . $e- > getMessage();
}
?>
search details from the user entry using prepared statements <?php
if(isset($_POST['submit']))
{
$conn = new mysqli('localhost','root','','new_db') or die("Error connecting to the DB");
$sql = "SELECT email FROM comments WHERE name=?";
$stmt = $conn->prepare($sql) or die("Prob with query");
$name=$_POST['name'];
$stmt->bind_param("s", $name);
$stmt->execute();
$stmt->bind_result($name);
$stmt->fetch();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title></title>
<style type="text/css">
</style>
</head>
<body>
<form method="post">
Name:<input type="text" name="name"/>
<input type="submit" name="submit" value="Seatch For Email"/>
</form>
<?php
if($name==$_POST['name'])
echo "Sorry, the name is not in the DB";
else
echo "The email id of ". $_POST['name']." is ".$name;
?>
</body>
</html>
