Low cost ecommerce web development India
flash website design
How do I add or modify data in my database?
Again, the answer is simple with PHP: use the mysql_query function combined
with SQL commands INSERT and UPDATE. INSERT is used to create new rows in
a table, while UPDATE is used to modify existing rows.
Inserting a Row
A simple INSERT, using the articles table defined at the start of this chapter,
looks like this:
File: 7.php (excerpt)
// A query to INSERT data
$sql = "INSERT INTO
articles
SET
title = '$title',
body = '$body',
author = '$author'";
// Run the query, identifying the connection
if (!$queryResource = mysql_query($sql, $dbConn)) {
trigger_error('Query error ' . mysql_error() . ' SQL: ' . $sql);
}
Updating a Row
Before you can use an UPDATE query, you need to be able to identify which row(s)
of the table to update. In this example, I’ve used a SELECT query to obtain
the
unique article_id value for the article entitled “How to insert data”:
File: 8.php (excerpt)
// A query to select an article
$sql = "SELECT article_id FROM articles
WHERE title='How to insert data'";
if (!$queryResource = mysql_query($sql, $dbConn)) {
trigger_error('Query error ' . mysql_error() . ' SQL: ' . $sql);
}
// Fetch a single row from the result
$row = mysql_fetch_array($queryResource, MYSQL_ASSOC);
// A new title
$title = 'How to update data';
$sql = "UPDATE
articles
SET
title='$title'
WHERE
article_id='" . $row['article_id'] . "'";
if (!$queryResource = mysql_query($sql, $dbConn)) {
trigger_error('Query error ' . mysql_error() . ' SQL: ' . $sql);
}
In the above example, we used the SELECT query to find the ID for the row we
wanted to update.
In practical Web applications, the UPDATE might occur on a page which relies on
input from the Web browser, after the user has entered the value(s) using an
HTML form, for example. It is possible that strings in this data might contain
apostrophes, which would break the SQL, and impact upon security. In light of
this, make sure you read “How do I solve database errors caused by quotes/apostrophes?”,
which covers SQL injection attacks.
Beware Global Updates
Be careful with UPDATE and remember to use a WHERE clause to indicate
which rows to change.
For example, consider this query:
UPDATE articles SET title = 'How NOT to update data'
This will update every row of the table!
website designer freelance ASP PHP ecommerce web developer
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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110