Quick Reach
The mysql_query() function
After establishing a connection to MySQL and its specific database, it is time to work with database records. Mostly we need to Insert, Update, retrieve or select and Delete records from the database tables.
PHP provides built-in function mysql_query() to achieve these tasks along with others. This chapter will explain how to use mysql_query() function to do these operations.
Syntax of mysql_query()
mysql_query(‘sql statement’,connection)
Where:
- SQL statement = There you will place either direct or a string variable containing an SQL statement.
- Connection = You can also place connection object here if a connection is not already established with a database.
Examples of using mysql_query()
Before we go through the examples, we are assuming that:
- You have knowledge of SQL insert, delete, update statements.
- You have installed MySQL database.
- You have created a DB name = testdb.
- You have created a user = testuser.
- You have assigned this password to above user = testpassword.
- You have created a table = tblstaff.
To learn about SQL insert, delete and update statements go to SQL or MySQL tutorials.
PHP mysql_query to insert data
This example enters a record into the tblstaff in testdb database
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
<?php
$dbhostname = 'localhost';
$dbusername = 'testuser';
$dbpassword = 'testpassword';
$conn = mysql_connect($dbhostname, $dbusername, $dbpassword);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
echo 'MySQL Connected successfully'."<BR>";
mysql_select_db("testdb") or die(mysql_error());
echo "Connected to Database"."<BR>";
$sql_statemanet = 'INSERT INTO tblstaff '.
'(employee_name,employee_add, employee_joined, employee_sal) '.
'VALUES ( "Mike", "dummy address", NOW() ,5000 )';
//mysql_select_db('test_db');
$rec_insert = mysql_query( $sql_statemanet);
if(! $rec_insert )
{
die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfullyn";
mysql_close($conn);
?>
|
This should be the output:
MySQL Connected successfully
Connected to Database
Entered data successfully
This is a basic example of how to use mysql_query to insert data into a table of MySQL database.
In real time applications, this will be selected by users/visitors of your website through a user interface in HTML or some other front end. In that case, you have to capture values from the front end like HTML form, store data into PHP variables (temporarily), make an SQL statement and use the above example approach.
PHP MySQL to Update data
This example updates/modify a record of tblstaff in testdb database.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
<?php
$dbhostname = 'localhost';
$dbusername = 'testuser';
$dbpassword = 'testpassword';
$conn = mysql_connect($dbhostname, $dbusername, $dbpassword);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
echo 'MySQL Connected successfully'."<BR>";
mysql_select_db("testdb") or die(mysql_error());
echo "Connected to Database"."<BR>";
$emp_salary = 7000;
$emp_id = 3;
$sql_statemanet = "UPDATE tblstaff ".
"SET employee_sal = $emp_salary ".
"WHERE ID = $emp_id" ;
//mysql_select_db('test_db');
$rec_update = mysql_query( $sql_statemanet);
if(! $rec_update )
{
die('Could not update data: ' . mysql_error());
}
echo "Record updated successfullyn";
mysql_close($conn);
?>
|
Output:
MySQL Connected successfully
Connected to Database
Record updated successfully
mysql_query to Select data and displaying by mysql_fetch_array
This example selects or retrieves data from the tblstaff table in the testdb database. After fetching data from tblstaff, it also displays the records.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
<?php
$dbhostname = 'localhost';
$dbusername = 'testuser';
$dbpassword = 'testpassword';
$conn = mysql_connect($dbhostname, $dbusername, $dbpassword);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("testdb") or die(mysql_error());
$emp_salary = 7000;
$emp_id = 3;
$sql_statemanet = "select * from tblstaff";
$rec_select = mysql_query( $sql_statemanet);
if(! $rec_select )
{
die('Could not retrieve data: ' . mysql_error());
}
//Displaying fetched records to HTML table
echo "<table border='1'>";
echo "<tr> <th>Employee Name</th> <th>Employee Salary</th> </tr>";
// Using mysql_fetch_array() to get the next row until end of table rows
while($row = mysql_fetch_array( $rec_select )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['employee_name'];
echo "</td><td>";
echo $row['employee_sal'];
echo "</td></tr>";
}
mysql_close($conn);
?>
|
Output:
This example will output HTML table in the browser showing all records fetched from the tblstaff table in testdb database.
Note: The mysql_query PHP function is deprecated from the PHP 5.5.0, source.
Leave A Comment?