Using Tables in PHP
I'm sorry if this question frustrates anyone ... I am truly a beginner to PHP + MYSQL but I would love to make some progress on this one!
My Goal:
- To create a table that I can view in a web page.
- To be able to add data (rows) to this table and still have the data be there the next time the page is loaded.
Step 1: Creating the table
<html>
<body>
<?php
// Conect to MYSQL
$con = mysql_connect("localhost", "My_Username", "My_Password") or die(mysql_error());
echo "Connected to MYSQL </br>";
mysql_select_db("My_Database") or die(mysql_error());
echo "Connected to Database";
// Create a MySQL table in the selected database (called ExampleTable)
$sql = "CREATE TABLE ExampleTable
(
// Set the primary key (personID)
personID 开发者_Python百科int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(personID),
ColumnOne varchar(15)
ColumnTwo varchar(15)
ColumnThree varChar(30)
)";
// Execute query
mysql_query($sql,$con);
mysql_query("INSERT INTO ExampleTable(ColumnOne, ColumnTwo, ColumnThree)
VALUES ('Data1', 'Data2', 'Data3')");
mysql_query("INSERT INTO ExampleTable(ColumnOne, ColumnTwo, ColumnThree)
VALUES ('Data_Data1', 'Data_Data2', 'Data_Data3')");
mysql_close($con);
?>
</body>
</html>
So, I have now created a MYSQL table with two three columns, and two rows of data. How can I get this table to show up on a web page?
Step 2: Saving / Retrieving saved data Is there some way that I can add data to a table, so that the data will be there permanently? - or is this how it works by default?
For example: Let's say that I have a form with a button on it. When the button is clicked a new row is added to the table 'ExampleTable'. The next time the user visits the page the table will be updated with his newly added data.
Thank you very much for any help. I understand that I am a beginner and do not fully understand these topics yet. Any responses will be greatly appreciated.
I'd recommend creating the table in a separate step, not from PHP; you don't want every time your web page is refreshed to create a new table.
Once your table is created, you can get the data from it by executing (within your PHP, using mysql_query) a query like "SELECT * from {tablename}". Once you've got that query result from mysql, then you can use the various PHP looping and mysql record reading methods to output the results from your query in the form you want into the page your PHP script will be serving to your client.
There are SELECT and UPDATE queries to do this. For example if you want to show data in a table, you would use query looking like this:
SELECT * FROM ExampleTable
In PHP, you can work with these data for example like this:
$query = mysql_query("SELECT * FROM ExampleTable");
while ($row = mysql_fetch_array($query)) {
echo $row["ColumnOne"];
}
And to the UPDATE query:
UPDATE ExampleTable SET ColumnOne = 'some value'
Usage in PHP is also with mysql_query. You can also use WHERE conditions in the query.
1 - Displaying All Data
<?php
// Conect to MYSQL
$con = mysql_connect("localhost", "My_Username", "My_Password") or die(mysql_error());
echo "Connected to MYSQL </br>";
mysql_select_db("My_Database") or die(mysql_error());
echo "Connected to Database";
// Get all the data from the "ExampleTable" table
$result = mysql_query("SELECT * FROM ExampleTable") or die(mysql_error());
echo "<table border='1'>";
echo "<tr> <th>ColumnOne</th> <th>CoumnTwo</th> <th>CoumnThree</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['ColumnOne'];
echo "</td><td>";
echo $row['ColumnTwo'];
echo "</td><td>";
echo $row['ColumnThree'];
echo "</td></tr>";
}
echo "</table>";
?>
From: http://www.tizag.com/mysqlTutorial/mysqlselect.php
2 - Data Storage
Anything INSERT
-ed into a database will remain* in the database until it is explicitly deleted.
*with few exceptions - but you'll learn about them a little later in your database journey :)
精彩评论