If you use an ID column with AUTO_INCREMENT attribute, you can use the mysql_insert_id() function to get the last ID value assigned by the MySQL server, as shown in the sample script below:
include "mysql_connection.php";
$sql = "INSERT INTO fyi_users (name)"
. " VALUES ('John King')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
print("Last ID inserted: ".mysql_insert_id()."\n");
} else {
print("SQL statement failed.\n");
}
mysql_close($con);
?>
If you run this script, you will get something like this:
1 rows inserted.
Last ID inserted: 3
Free information sharing portal by Mayur Raiyani
Showing posts with label php. Show all posts
Showing posts with label php. Show all posts
How To Get the ID Column Auto-Incremented?
Many tables require an ID column to assign a unique ID number for each row in the table. For example, if you have a table to hold forum member profiles, you need an ID number to identify each member. To allow MySQL server to automatically assign a new ID number for each new record, you can define the ID column with AUTO_INCREMENT and PRIMARY KEY attributes as shown in the following sample script:
include "mysql_connection.php";
$sql = "CREATE TABLE fyi_users ("
. " id INTEGER NOT NULL AUTO_INCREMENT"
. ", name VARCHAR(80) NOT NULL"
. ", email VARCHAR(80)"
. ", time TIMESTAMP DEFAULT CURRENT_TIMESTAMP()"
. ", PRIMARY KEY (id)"
. ")";
if (mysql_query($sql, $con)) {
print("Table fyi_users created.\n");
} else {
print("Table creation failed.\n");
}
mysql_close($con);
?>
If you run this script, a new table will be created with ID column defined as auto-increment. The sample script below inserts two records with ID values assigned by MySQL server:
If you run this script, you will get something like this:
1 rows inserted.
1 rows inserted.
1, John King, 2006-07-01 23:02:39
2, Nancy Greenberg, 2006-07-01 23:02:39
include "mysql_connection.php";
$sql = "CREATE TABLE fyi_users ("
. " id INTEGER NOT NULL AUTO_INCREMENT"
. ", name VARCHAR(80) NOT NULL"
. ", email VARCHAR(80)"
. ", time TIMESTAMP DEFAULT CURRENT_TIMESTAMP()"
. ", PRIMARY KEY (id)"
. ")";
if (mysql_query($sql, $con)) {
print("Table fyi_users created.\n");
} else {
print("Table creation failed.\n");
}
mysql_close($con);
?>
If you run this script, a new table will be created with ID column defined as auto-increment. The sample script below inserts two records with ID values assigned by MySQL server:
If you run this script, you will get something like this:
1 rows inserted.
1 rows inserted.
1, John King, 2006-07-01 23:02:39
2, Nancy Greenberg, 2006-07-01 23:02:39
How To Query Multiple Tables Jointly?
If you want to query information stored in multiple tables, you can use the SELECT statement with a WHERE condition to make an inner join. Assuming that you have 3 tables in a forum system: "users" for user profile, "forums" for forums information, and "posts" for postings, you can query all postings from a single user with a script as shown below:
include "mysql_connection.php";
$userID = 101;
$sql = "SELECT posts.subject, posts.time, users.name,
. " forums.title"
. " FROM posts, users, forums"
. " WHERE posts.userID = ".$userID
. " AND posts.userID = users.id"
. " AND posts.forumID = forums.id";
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print($row['subject'].", ".$row['time'].", "
.$row['name'].", ".$row['title']."\n");
}
mysql_free_result($rs);
mysql_close($con);
?>
include "mysql_connection.php";
$userID = 101;
$sql = "SELECT posts.subject, posts.time, users.name,
. " forums.title"
. " FROM posts, users, forums"
. " WHERE posts.userID = ".$userID
. " AND posts.userID = users.id"
. " AND posts.forumID = forums.id";
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print($row['subject'].", ".$row['time'].", "
.$row['name'].", ".$row['title']."\n");
}
mysql_free_result($rs);
mysql_close($con);
?>
How To Build WHERE Criteria with Web Form Search Fields?
If your PHP script is linked to a Web form which takes search key words for multiple data fields. For example, your Web form asks your visitor to search for Website links with a URL search field, a Website title search field, a description search field, and a comment search field.
Now you have to build a nice WHERE criteria string that meets the following requirements:
Search fields with no data entered by visitors should not be included in the criteria.
Search values entered by visitors should be trimmed to remove leading and trailing space characters.
Empty search values after trimming should not be included in the criteria.
Single quote (') characters in search values should be protected.
backslash (\) characters in search values should be protected.
The tutorial script below shows you a good sample that meets the above requirements:
" Joe's brother\'s ", "description"=>"c:\windows\system ", "comment"=>" best "); $sql = "SELECT * FROM siteLinks WHERE 1=1"; $url = getFormParam("url"); $title = getFormParam("title"); $description = getFormParam("description"); $comment = getFormParam("comment"); if (strlen($url) > 0) $sql .= " AND url LIKE '%".$url."%'"; if (strlen($title) > 0) $sql .= " AND title LIKE '%".$title."%'"; if (strlen($description) > 0) $sql .= " AND description LIKE '%".$description."%'"; if (strlen($comment) > 0) $sql .= " AND comment LIKE '%".$comment."%'"; print("SQL statement:\n"); print($sql."\n"); function getFormParam($p) { if (isset($_REQUEST[$p])) { return str_replace("\\", "\\\\", str_replace("'", "''", trim($_REQUEST[$p]))); } else { return ""; } } ?>
If you run this script, you will get something like this:
SQL statement:
SELECT * FROM siteLinks WHERE 1=1
AND title LIKE '%Joe''s brother\\''s%'
AND description LIKE '%c:\\windows\\system%'
AND comment LIKE '%best%'
You should learn a couple of things in this script:
isset($_REQUEST[$p]) is used to detect if the visitor has actually entered any value or not to a field.
trim($s) is used to trim off leading and trailing space characters.
str_replace("'", "''",$s) is used to replace single quote (') characters with ('').
str_replace("\\", "\\\\",$s) is used to replace backslash (\) characters with (\\). You need to repeat backslashes because PHP string literals can not take backslashes as is.
getFormParam($p) is created do all the input value processing work in a single function. getFormParam($p) also makes sure that all input values are defined as strings, even if they are not defined.
The WHERE clause is initialized with a dummy condition "1=1", so that all other conditions can be prefixed with the key word "AND".
$_REQUEST() is created for testing purpose only. You need to remove it, when you move this script to a real Web page.
The final WHERE criteria generated in the output SQL statement seems to be correct.
Now you have to build a nice WHERE criteria string that meets the following requirements:
Search fields with no data entered by visitors should not be included in the criteria.
Search values entered by visitors should be trimmed to remove leading and trailing space characters.
Empty search values after trimming should not be included in the criteria.
Single quote (') characters in search values should be protected.
backslash (\) characters in search values should be protected.
The tutorial script below shows you a good sample that meets the above requirements:
" Joe's brother\'s ", "description"=>"c:\windows\system ", "comment"=>" best "); $sql = "SELECT * FROM siteLinks WHERE 1=1"; $url = getFormParam("url"); $title = getFormParam("title"); $description = getFormParam("description"); $comment = getFormParam("comment"); if (strlen($url) > 0) $sql .= " AND url LIKE '%".$url."%'"; if (strlen($title) > 0) $sql .= " AND title LIKE '%".$title."%'"; if (strlen($description) > 0) $sql .= " AND description LIKE '%".$description."%'"; if (strlen($comment) > 0) $sql .= " AND comment LIKE '%".$comment."%'"; print("SQL statement:\n"); print($sql."\n"); function getFormParam($p) { if (isset($_REQUEST[$p])) { return str_replace("\\", "\\\\", str_replace("'", "''", trim($_REQUEST[$p]))); } else { return ""; } } ?>
If you run this script, you will get something like this:
SQL statement:
SELECT * FROM siteLinks WHERE 1=1
AND title LIKE '%Joe''s brother\\''s%'
AND description LIKE '%c:\\windows\\system%'
AND comment LIKE '%best%'
You should learn a couple of things in this script:
isset($_REQUEST[$p]) is used to detect if the visitor has actually entered any value or not to a field.
trim($s) is used to trim off leading and trailing space characters.
str_replace("'", "''",$s) is used to replace single quote (') characters with ('').
str_replace("\\", "\\\\",$s) is used to replace backslash (\) characters with (\\). You need to repeat backslashes because PHP string literals can not take backslashes as is.
getFormParam($p) is created do all the input value processing work in a single function. getFormParam($p) also makes sure that all input values are defined as strings, even if they are not defined.
The WHERE clause is initialized with a dummy condition "1=1", so that all other conditions can be prefixed with the key word "AND".
$_REQUEST() is created for testing purpose only. You need to remove it, when you move this script to a real Web page.
The final WHERE criteria generated in the output SQL statement seems to be correct.
How To Perform Key Word Search in Tables?
The simplest way to perform key word search is to use the SELECT statement with a LIKE operator in the WHERE clause. The LIKE operator allows you to match a text field with a keyword pattern specified as '%keyword%', where (%) represents any number of any characters. Any single quote (') in the keyword needs to be protected by replacing them with two single quotes (''). The tutorial exercise below shows you how to search for records whose "notes" contains "e":
include "mysql_connection.php";
$key = "e";
$key = str_replace("'", "''", $key);
$sql = "SELECT id, url, notes FROM fyi_links"
. " WHERE notes LIKE '%".$key."%'";
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print($row['id'].", ".$row['url'].", "
. $row['notes']."\n");
}
mysql_free_result($rs);
mysql_close($con);
?>
If you run this script, you will get something like this:
102, dba.fyicenter.com, Nice site.
202, www.yahoo.com, It's another search engine!
301, netscape.com, Added long time ago!
302, myspace.com, Added today!
include "mysql_connection.php";
$key = "e";
$key = str_replace("'", "''", $key);
$sql = "SELECT id, url, notes FROM fyi_links"
. " WHERE notes LIKE '%".$key."%'";
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print($row['id'].", ".$row['url'].", "
. $row['notes']."\n");
}
mysql_free_result($rs);
mysql_close($con);
?>
If you run this script, you will get something like this:
102, dba.fyicenter.com, Nice site.
202, www.yahoo.com, It's another search engine!
301, netscape.com, Added long time ago!
302, myspace.com, Added today!
How To Display a Past Time in Days, Hours and Minutes?
You have seen a lots of Websites are displaying past times in days, hours and minutes. If you want to do this yourself, you can use the TIMEDIFF() SQL function. Note that the TIMEDIFF() function can only handle time range within 839 hours (about 33 days). So it works only for past times within one month or so.
The following tutorial exercise shows you how to use TIMEDIFF() to present a past time in days, hours, and minutes:
include "mysql_connection.php";
$pastTime = "2006-06-29 04:09:49";
$sql = "SELECT HOUR(timeDiff) AS hours,"
. " MINUTE(timeDiff) AS minutes FROM ("
. " SELECT TIMEDIFF(NOW(), '".$pastTime."')"
. " AS timeDiff FROM DUAL) subQuery";
print("SQL = $sql\n");
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print("$pastTime was ".$row['hours']." hours, "
. $row['minutes']." minutes ago.\n");
}
mysql_free_result($rs);
$sql = "SELECT (HOUR(timeDiff) DIV 24) AS days,"
. " (HOUR(timeDiff) MOD 24) AS hours,"
. " MINUTE(timeDiff) AS minutes FROM ("
. " SELECT TIMEDIFF(NOW(), '".$pastTime."')"
. " AS timeDiff FROM DUAL) subQuery";
print("SQL = $sql\n");
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print("$pastTime was ".$row['days']." days, "
. $row['hours']." hours, "
. $row['minutes']." minutes ago.\n");
}
mysql_free_result($rs);
mysql_close($con);
?>
If today is you run this script, you will get something like this:
SQL = SELECT HOUR(timeDiff) AS hours,
MINUTE(timeDiff) AS minutes FROM (
SELECT TIMEDIFF(NOW(), '2006-06-29 04:09:49')
AS timeDiff FROM DUAL) subQuery
2006-06-29 04:09:49 was 115 hours, 2 minutes ago.
SQL = SELECT (HOUR(timeDiff) DIV 24) AS days,
(HOUR(timeDiff) MOD 24) AS hours,
MINUTE(timeDiff) AS minutes FROM (
SELECT TIMEDIFF(NOW(), '2006-06-29 04:09:49')
AS timeDiff FROM DUAL) subQuery
2006-06-29 04:09:49 was 4 days, 19 hours, 2 minutes ago.
Warning again, this script only works if the past time is less than 33 days ago.
The following tutorial exercise shows you how to use TIMEDIFF() to present a past time in days, hours, and minutes:
include "mysql_connection.php";
$pastTime = "2006-06-29 04:09:49";
$sql = "SELECT HOUR(timeDiff) AS hours,"
. " MINUTE(timeDiff) AS minutes FROM ("
. " SELECT TIMEDIFF(NOW(), '".$pastTime."')"
. " AS timeDiff FROM DUAL) subQuery";
print("SQL = $sql\n");
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print("$pastTime was ".$row['hours']." hours, "
. $row['minutes']." minutes ago.\n");
}
mysql_free_result($rs);
$sql = "SELECT (HOUR(timeDiff) DIV 24) AS days,"
. " (HOUR(timeDiff) MOD 24) AS hours,"
. " MINUTE(timeDiff) AS minutes FROM ("
. " SELECT TIMEDIFF(NOW(), '".$pastTime."')"
. " AS timeDiff FROM DUAL) subQuery";
print("SQL = $sql\n");
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print("$pastTime was ".$row['days']." days, "
. $row['hours']." hours, "
. $row['minutes']." minutes ago.\n");
}
mysql_free_result($rs);
mysql_close($con);
?>
If today is you run this script, you will get something like this:
SQL = SELECT HOUR(timeDiff) AS hours,
MINUTE(timeDiff) AS minutes FROM (
SELECT TIMEDIFF(NOW(), '2006-06-29 04:09:49')
AS timeDiff FROM DUAL) subQuery
2006-06-29 04:09:49 was 115 hours, 2 minutes ago.
SQL = SELECT (HOUR(timeDiff) DIV 24) AS days,
(HOUR(timeDiff) MOD 24) AS hours,
MINUTE(timeDiff) AS minutes FROM (
SELECT TIMEDIFF(NOW(), '2006-06-29 04:09:49')
AS timeDiff FROM DUAL) subQuery
2006-06-29 04:09:49 was 4 days, 19 hours, 2 minutes ago.
Warning again, this script only works if the past time is less than 33 days ago.
How To Quote Date and Time Values in SQL Statements?
If you want to provide date and time values in a SQL statement, you should write them in the format of "yyyy-mm-dd hh:mm:ss", and quoted with single quotes ('). The tutorial exercise below shows you two INSERT statements. The first one uses a hard-code date value. The second one uses the date() function to return a date value.
include "mysql_connection.php";
$notes = "Added long time ago!";
$time = "1999-01-01 01:02:03";
$sql = "INSERT INTO fyi_links (id, url, notes, time)"
. " VALUES ("
. " 301, 'netscape.com', '".$notes."', '".$time."')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed.\n");
}
$notes = "Added today!";
$time = date("Y-m-d H:i:s");
$sql = "INSERT INTO fyi_links (id, url, notes, time)"
. " VALUES ("
. " 302, 'myspace.com', '".$notes."', '".$time."')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed.\n");
}
mysql_close($con);
?>
If you run this script, you will get something like this:
1 rows inserted.
1 rows inserted.
include "mysql_connection.php";
$notes = "Added long time ago!";
$time = "1999-01-01 01:02:03";
$sql = "INSERT INTO fyi_links (id, url, notes, time)"
. " VALUES ("
. " 301, 'netscape.com', '".$notes."', '".$time."')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed.\n");
}
$notes = "Added today!";
$time = date("Y-m-d H:i:s");
$sql = "INSERT INTO fyi_links (id, url, notes, time)"
. " VALUES ("
. " 302, 'myspace.com', '".$notes."', '".$time."')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed.\n");
}
mysql_close($con);
?>
If you run this script, you will get something like this:
1 rows inserted.
1 rows inserted.
How To Quote Text Values in SQL Statements?
Text values in SQL statements should be quoted with single quotes ('). If the text value contains a single quote ('), it should be protected by replacing it with two single quotes (''). In SQL language syntax, two single quotes represents one single quote in string literals. The tutorial exercise below shows you two INSERT statements. The first one will fail, because it has an un-protected single quote. The second one will be ok, because a str_replace() is used to replace (') with (''):
include "mysql_connection.php";
$notes = "It's a search engine!";
$sql = "INSERT INTO fyi_links (id, url, notes) VALUES ("
. " 201, 'www.google.com', '".$notes."')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed.\n");
}
$notes = "It's another search engine!";
$notes = str_replace("'", "''", $notes);
$sql = "INSERT INTO fyi_links (id, url, notes) VALUES ("
. " 202, 'www.yahoo.com', '".$notes."')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed.\n");
}
mysql_close($con);
?>
If you run this script, you will get something like this:
SQL statement failed.
1 rows inserted.
include "mysql_connection.php";
$notes = "It's a search engine!";
$sql = "INSERT INTO fyi_links (id, url, notes) VALUES ("
. " 201, 'www.google.com', '".$notes."')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed.\n");
}
$notes = "It's another search engine!";
$notes = str_replace("'", "''", $notes);
$sql = "INSERT INTO fyi_links (id, url, notes) VALUES ("
. " 202, 'www.yahoo.com', '".$notes."')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed.\n");
}
mysql_close($con);
?>
If you run this script, you will get something like this:
SQL statement failed.
1 rows inserted.
How To Delete Existing Rows in a Table?
If you want to remove a row from a table, you can use the DELETE statement with a WHERE clause to identify the row. The following sample script deletes one row:
include "mysql_connection.php";
$sql = "DELETE FROM fyi_links WHERE id = 1102";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows deleted.\n");
} else {
print("SQL statement failed.\n");
}
mysql_close($con);
?>
If you run this script, you will get something like this:
1 rows deleted.
include "mysql_connection.php";
$sql = "DELETE FROM fyi_links WHERE id = 1102";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows deleted.\n");
} else {
print("SQL statement failed.\n");
}
mysql_close($con);
?>
If you run this script, you will get something like this:
1 rows deleted.
How To Update Existing Rows in a Table?
Updating existing rows in a table requires to run the UPDATE statement with a WHERE clause to identify the row. The following sample script updates one row with two new values:
include "mysql_connection.php";
$sql = "UPDATE fyi_links SET notes='Nice site.', counts=8"
. " WHERE id = 102";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows updated.\n");
} else {
print("SQL statement failed.\n");
}
mysql_close($con);
?>
If you run this script, you will get something like this:
1 rows updated.
include "mysql_connection.php";
$sql = "UPDATE fyi_links SET notes='Nice site.', counts=8"
. " WHERE id = 102";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows updated.\n");
} else {
print("SQL statement failed.\n");
}
mysql_close($con);
?>
If you run this script, you will get something like this:
1 rows updated.
How To Break Output into Pages?
If you have a query that returns hundreds of rows, and you don't want to present all of them to your users on a single page. You can break output into multiple pages, and only present 10 rows per page like what Google Website is doing. To do this, you need to modify your query with the LIMIT clause to return rows starting from the correct row number, and limited to 10 rows in the output.
The tutorial exercise below shows you how to break output into pages with 2 rows per page. It also calcualtes the total number of pages with a simple query criteria and order by condition. As an example, rows for page number 2 are returned.
include "mysql_connection.php";
$rowsPerPage = 2;
$where = " WHERE url LIKE '%co%'";
$order = " ORDER BY time DESC";
$curPage = 2;
$start = ($curPage-1) * $rowsPerPage;
$sql = "SELECT COUNT(*) AS count FROM fyi_links" . $where . $order;
print("SQL = $sql\n");
$rs = mysql_query($sql, $con);
$row = mysql_fetch_assoc($rs);
$numberOfPages = $row['count'] / $rowsPerPage;
print("Number of pages = $numberOfPages\n");
mysql_free_result($rs);
$sql = "SELECT * FROM fyi_links" . $where . $order
. " LIMIT ".$start.", ".$rowsPerPage;
print("SQL = $sql\n");
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print($row['id'].", ".$row['url'].", "
. $row['notes'].", ".$row['time']."\n");
}
mysql_free_result($rs);
mysql_close($con);
?>
If you run this script, you will get something like this:
SQL = SELECT COUNT(*) AS count FROM fyi_links
WHERE url LIKE '%co%' ORDER BY time DESC
Number of pages = 3
SQL = SELECT * FROM fyi_links WHERE url LIKE '%co%'
ORDER BY time DESC LIMIT 2, 2
101, dev.fyicenter.com, , 2006-07-01 20:24:46
102, dba.fyicenter.com, Nice site., 2006-07-01 20:24:46
The tutorial exercise below shows you how to break output into pages with 2 rows per page. It also calcualtes the total number of pages with a simple query criteria and order by condition. As an example, rows for page number 2 are returned.
include "mysql_connection.php";
$rowsPerPage = 2;
$where = " WHERE url LIKE '%co%'";
$order = " ORDER BY time DESC";
$curPage = 2;
$start = ($curPage-1) * $rowsPerPage;
$sql = "SELECT COUNT(*) AS count FROM fyi_links" . $where . $order;
print("SQL = $sql\n");
$rs = mysql_query($sql, $con);
$row = mysql_fetch_assoc($rs);
$numberOfPages = $row['count'] / $rowsPerPage;
print("Number of pages = $numberOfPages\n");
mysql_free_result($rs);
$sql = "SELECT * FROM fyi_links" . $where . $order
. " LIMIT ".$start.", ".$rowsPerPage;
print("SQL = $sql\n");
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print($row['id'].", ".$row['url'].", "
. $row['notes'].", ".$row['time']."\n");
}
mysql_free_result($rs);
mysql_close($con);
?>
If you run this script, you will get something like this:
SQL = SELECT COUNT(*) AS count FROM fyi_links
WHERE url LIKE '%co%' ORDER BY time DESC
Number of pages = 3
SQL = SELECT * FROM fyi_links WHERE url LIKE '%co%'
ORDER BY time DESC LIMIT 2, 2
101, dev.fyicenter.com, , 2006-07-01 20:24:46
102, dba.fyicenter.com, Nice site., 2006-07-01 20:24:46
How To Query Tables and Loop through the Returning Rows?
The best way to query tables and loop through the returning rows is to run the SELECT statement with the mysql_query() function, catch the returning object as a result set, and loop through the result with the mysql_fetch_assoc() function in a while loop as shown in the following sample PHP script:
include "mysql_connection.php";
$sql = "SELECT id, url, time FROM fyi_links";
$res = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($res)) {
print($row['id'].",".$row['url'].",".$row['time']."\n");
}
mysql_free_result($res);
mysql_close($con);
?>
Using mysql_fetch_assoc() is better than other fetch functions, because it allows you to access field values by field names. If you run this script, you will see all rows from the fyi_links table are printed on the screen:
101, dev.fyicenter.com, 2006-07-01 22:29:02
102, dba.fyicenter.com, 2006-07-01 22:29:02
1101, dev.fyicenter.com, 2006-07-01 22:29:02
1102, dba.fyicenter.com, 2006-07-01 22:29:02
Don't forget to call mysql_free_result($res). It is important to free up result set objects as soon as you are done with them.
include "mysql_connection.php";
$sql = "SELECT id, url, time FROM fyi_links";
$res = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($res)) {
print($row['id'].",".$row['url'].",".$row['time']."\n");
}
mysql_free_result($res);
mysql_close($con);
?>
Using mysql_fetch_assoc() is better than other fetch functions, because it allows you to access field values by field names. If you run this script, you will see all rows from the fyi_links table are printed on the screen:
101, dev.fyicenter.com, 2006-07-01 22:29:02
102, dba.fyicenter.com, 2006-07-01 22:29:02
1101, dev.fyicenter.com, 2006-07-01 22:29:02
1102, dba.fyicenter.com, 2006-07-01 22:29:02
Don't forget to call mysql_free_result($res). It is important to free up result set objects as soon as you are done with them.
What Is a Result Set Object?
A result set object is a logical representation of data rows returned by mysql_query() function on SELECT statements. Every result set object has an internal pointer used to identify the current row in the result set. Once you get a result set object, you can use the following functions to retrieve detail information:
mysql_free_result($res) - Closes this result set object.
mysql_num_rows($res) - Returns the number rows in the result set.
mysql_num_fields($res) - Returns the number fields in the result set.
mysql_fetch_row($res) - Returns an array contains the current row indexed by field position numbers.
mysql_fetch_assoc($res) - Returns an array contains the current row indexed by field names.
mysql_fetch_array($res) - Returns an array contains the current row with double indexes: field position numbers and filed names.
mysql_fetch_lengths($res) - Returns an array contains lengths of all fields in the last row returned.
mysql_field_name($res, $i) - Returns the name of the field of the specified index.
mysql_free_result($res) - Closes this result set object.
mysql_num_rows($res) - Returns the number rows in the result set.
mysql_num_fields($res) - Returns the number fields in the result set.
mysql_fetch_row($res) - Returns an array contains the current row indexed by field position numbers.
mysql_fetch_assoc($res) - Returns an array contains the current row indexed by field names.
mysql_fetch_array($res) - Returns an array contains the current row with double indexes: field position numbers and filed names.
mysql_fetch_lengths($res) - Returns an array contains lengths of all fields in the last row returned.
mysql_field_name($res, $i) - Returns the name of the field of the specified index.
How To Insert Multiple Rows with a SELECT Statement?
If want to insert rows into a table based on data rows from other tables, you can use a sub-query inside the INSERT statement as shown in the following script example:
include "mysql_connection.php";
$sql = "INSERT INTO fyi_links"
. " SELECT id+1000, url, notes, counts, time"
. " FROM fyi_links";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed.\n");
}
mysql_close($con);
?>
If you run this script, you will get something like this:
2 rows inserted.
include "mysql_connection.php";
$sql = "INSERT INTO fyi_links"
. " SELECT id+1000, url, notes, counts, time"
. " FROM fyi_links";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed.\n");
}
mysql_close($con);
?>
If you run this script, you will get something like this:
2 rows inserted.
How To Fix the INSERT Command Denied Error?
The reason for getting the "1142: INSERT command denied" error is that your user accound does not have the INSERT privilege on the table or database. To resolve this error, you need to ask your MySQL DBA to grant you the right privilege.
Your DBA will need to run commands like these to grant you enough privileges :
Once you got the right privileges, run the following tutorial scrip again:
Your DBA will need to run commands like these to grant you enough privileges :
GRANT INSERT ON fyi.* TO dev; GRANT UPDATE ON fyi.* TO dev; GRANT DELETE ON fyi.* TO dev; GRANT SELECT ON fyi.* TO dev; GRANT CREATE ON fyi.* TO dev; GRANT DROP ON fyi.* TO dev;
Once you got the right privileges, run the following tutorial scrip again:
include "mysql_connection.php";
$sql = "INSERT INTO fyi_links (id, url) VALUES ("
. " 101, 'dev.fyicenter.com')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed with error:\n");
print(mysql_errno($con).": ".mysql_error($con)."\n");
}
$sql = "INSERT INTO fyi_links (id, url) VALUES ("
. " 102, 'dba.fyicenter.com')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed with error:\n");
print(mysql_errno($con).": ".mysql_error($con)."\n");
}
mysql_close($con);
?> The script should return this:
1 rows inserted. 1 rows inserted.
How To Insert Data into an Existing Table?
If you want to insert a row of data into an existing table, you can use the INSERT INTO statement as shown in the following sample script:
Remember that mysql_query() returns integer/FALSE on INSERT statements. If you run this script, you could get something like this:
include "mysql_connection.php";
$sql = "INSERT INTO fyi_links (id, url) VALUES ("
. " 101, 'dev.fyicenter.com')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed with error:\n");
print(mysql_errno($con).": ".mysql_error($con)."\n");
}
mysql_close($con);
?>Remember that mysql_query() returns integer/FALSE on INSERT statements. If you run this script, you could get something like this:
SQL statement failed with error: 1142: INSERT command denied to user 'dev'@'localhost' for table 'fyi_links'
How To Get the Number of Rows Selected or Affected by a SQL Statement?
There are two functions you can use the get the number of rows selected or affected by a SQL statement:
- mysql_num_rows($res) - Returns the number of rows selected in a result set object returned from SELECT statement.
- mysql_affected_rows() - Returns the number of rows affected by the last INSERT, UPDATE or DELETE statement.
How To Create a New Table?
If you want to create a table, you can run the CREATE TABLE statement as shown in the following sample script:
include "mysql_connection.php";
$sql = "CREATE TABLE fyi_links ("
. " id INTEGER NOT NULL"
. ", url VARCHAR(80) NOT NULL"
. ", notes VARCHAR(1024)"
. ", counts INTEGER"
. ", time TIMESTAMP DEFAULT CURRENT_TIMESTAMP()"
. ")";
if (mysql_query($sql, $con)) {
print("Table fyi_links created.\n");
} else {
print("Table creation failed with error:\n");
print(mysql_errno($con).": ".mysql_error($con)."\n");
}
mysql_close($con);
?> Remember that mysql_query() returns TRUE/FALSE on CREATE statements. If you run this script, you will get something like this: Table fyi_links created.
How to assign a virtual local domain name to your localhost
How To Assign a Virtual Local Domain Name To Your Localhost
Step by Step Instructions to Assign Multiple Virtual Domain Names and Websites to Your Localhost
With the help of this tutorial you can assign a virtual domain name to your localhost.
You can access your local website with your favourite name like http://mysite.web instead of http://localhost and also you can assign different virtual domain names to different local webistes.
Requirements:
• Apache webserver installed. It would be nice if you have WAMP server. This tutorial is based on WAMP server. But it works on any Apache server.
Procedure:
Part 1:
1. First go to location "C:\WINDOWS\system32\drivers\etc" directory.(or where you installed windows).
Then open "hosts" file with simple text editor like notepad.
2. You'll see "127.0.0.1 localhost" at the end of the file.
In the next line add your virtual domain name like the example shown below.
Here mysite.web is just my example. You can use anything you like.
127.0.0.1 mysite.web #this is virtual domain name.
3. Now save the hosts file. mysite.web is just an example. You can add anything like mywebsite.local and you can use any extension or no extension at all.
You can simply add mysite also.
4. Now test your virtual domain. Just type "http://mysite.web" You must see your wamp page or webservers defalut page. If not go through the process again.
NOTE: Don't use any real domain name like www.google.com or your own domain name if you have any. If you did so, you cannot that access the original remote site. This is because, 127.0.0.1 is loopback address, anything with that address will never leave your computer.
Part 2- Now some difficult part. Assigning this virtual domain name to your web site in your local server.
1. Open your httpd.conf file in conf directory of Apache webserver folder.
If you are using WAMP click on WAMP icon, go to Apache menu and select httpd.conf there.
2. Create a new folder mysite in your C:\>. And create a new page index.html. These are for testing purposes.
If you have a local website, specify the full path of website in below code.
3. Now add the following code at the end of the httpd.conf file.
NOTE: PLEASE TYPE IN THE CODE MANUALLY without comments, DON'T COPY AND PASTE.
NameVirtualHost 127.0.0.1
ServerName localhost
DocumentRoot "C:/wamp/www" #this is default wamp root for websites,
ServerName mysite.web #your virtual domain name
DocumentRoot "C:/mysite" #location of your site, no extenison needed.
#again location of your website
Order Allow,Deny
Allow from all
Save this file. Restart your WAMP server. Now type http://mysite.web You'll see the index page of mysite.
If you want another website, first add another virtual domain in hosts file as shown in part1.
And then copy and paste the following code at the end of httpd.conf file. Just change the virtual domain name, and locations of website.
ServerName mywebsite.web #change this virtual domain name
DocumentRoot "C:/mywebsite" #location of your site, change this.
#again location of your website, change this
Order Allow,Deny
Allow from all
You can add as many websites as you wish. Just repeat the above procedure. And access all your local websites with your favorite name. It will be fun and will save lot of your time.
Step by Step Instructions to Assign Multiple Virtual Domain Names and Websites to Your Localhost
With the help of this tutorial you can assign a virtual domain name to your localhost.
You can access your local website with your favourite name like http://mysite.web instead of http://localhost and also you can assign different virtual domain names to different local webistes.
Requirements:
• Apache webserver installed. It would be nice if you have WAMP server. This tutorial is based on WAMP server. But it works on any Apache server.
Procedure:
Part 1:
1. First go to location "C:\WINDOWS\system32\drivers\etc" directory.(or where you installed windows).
Then open "hosts" file with simple text editor like notepad.
2. You'll see "127.0.0.1 localhost" at the end of the file.
In the next line add your virtual domain name like the example shown below.
Here mysite.web is just my example. You can use anything you like.
127.0.0.1 mysite.web #this is virtual domain name.
3. Now save the hosts file. mysite.web is just an example. You can add anything like mywebsite.local and you can use any extension or no extension at all.
You can simply add mysite also.
4. Now test your virtual domain. Just type "http://mysite.web" You must see your wamp page or webservers defalut page. If not go through the process again.
NOTE: Don't use any real domain name like www.google.com or your own domain name if you have any. If you did so, you cannot that access the original remote site. This is because, 127.0.0.1 is loopback address, anything with that address will never leave your computer.
Part 2- Now some difficult part. Assigning this virtual domain name to your web site in your local server.
1. Open your httpd.conf file in conf directory of Apache webserver folder.
If you are using WAMP click on WAMP icon, go to Apache menu and select httpd.conf there.
2. Create a new folder mysite in your C:\>. And create a new page index.html. These are for testing purposes.
If you have a local website, specify the full path of website in below code.
3. Now add the following code at the end of the httpd.conf file.
NOTE: PLEASE TYPE IN THE CODE MANUALLY without comments, DON'T COPY AND PASTE.
NameVirtualHost 127.0.0.1
ServerName localhost
DocumentRoot "C:/wamp/www" #this is default wamp root for websites,
ServerName mysite.web #your virtual domain name
DocumentRoot "C:/mysite" #location of your site, no extenison needed.
Order Allow,Deny
Allow from all
Save this file. Restart your WAMP server. Now type http://mysite.web You'll see the index page of mysite.
If you want another website, first add another virtual domain in hosts file as shown in part1.
And then copy and paste the following code at the end of httpd.conf file. Just change the virtual domain name, and locations of website.
ServerName mywebsite.web #change this virtual domain name
DocumentRoot "C:/mywebsite" #location of your site, change this.
Order Allow,Deny
Allow from all
You can add as many websites as you wish. Just repeat the above procedure. And access all your local websites with your favorite name. It will be fun and will save lot of your time.
Subscribe to:
Posts (Atom)