Page 1 of 2
working with a database for the first time
Posted: Mon Jun 25, 2007 4:24 pm
by mabufo
I have recently installed 'apache2triad' on my local machine. I want to learn to work with a database through simple scripts.
At the moment, I have a database setup that I would like to use, however, when it comes to writing/reading from the database - I really don't know where to start. Could someone point me in the right direction?
Posted: Mon Jun 25, 2007 4:27 pm
by feyd
There's a huge supply of examples available across the web. What have your searches turned up?
Posted: Mon Jun 25, 2007 4:41 pm
by mabufo
Well, I'm asking for whatever has worked for you all.
Posted: Mon Jun 25, 2007 4:45 pm
by ReverendDexter
I'd suggest getting a MySQL server setup on your system, grab yourself one of the O'Reilly PHP books, and start reading and playing. The Database section here has good threads and lots of example code to recycle if you get stuck.
There's no substitute for reading the documentation.
Posted: Mon Jun 25, 2007 4:55 pm
by onion2k
http://www.ooer.com/index.php?section=php&id=8 ... not a bad place to start. But I would say that.
Posted: Mon Jun 25, 2007 5:08 pm
by mabufo
Quick question: how do I check to make sure a table doesn't exist, before I create it?
Posted: Mon Jun 25, 2007 5:18 pm
by volka
e.g. by testing for the specific
error code
In this case
Error: 1050 SQLSTATE: 42S01 (ER_TABLE_EXISTS_ERROR)
Message: Table '%s' already exists
Code: Select all
<?php
define('ER_TABLE_EXISTS_ERROR', 1050);
$mysql = mysql_connect( ... ) or die(mysql_error());
mysql_select_db(..., $mysql) or die(mysql_error());
$query = 'CREATE TABLE `user` ( `id` int(10), `name` varchar(64) )';
mysql_query($query, $mysql);
switch(mysql_errno()) {
case ER_TABLE_EXISTS_ERROR:
echo 'table already exists';
break;
case 0:
echo 'table created';
break;
default:
echo mysql_error();
break;
}
Posted: Mon Jun 25, 2007 5:44 pm
by mabufo
If the table exists, and a query is sent to create the same table, will the database write over the current table? Will it ignore the request?
Posted: Mon Jun 25, 2007 5:58 pm
by mabufo
my script seems to be failing. I want to add a string of text into a table called 'quotes', that will be ordered by ascending integers. When I click submit on my form, the process page(the one I post) outputs nothing, where is my code going wrong? I can't see the error

.
The script Obviously won't over write the table, but it won't add the new quote to the table. What's the deal?
Code: Select all
<html>
<body>
<?php
$quote = $_POST['quote'];
if ($quote == True){
$con = mysql_connect("localhost","root","zerocool");
if (!$con){
die('Could not connect to database: ' . mysql_error());
}
mysql_select_db("misc", $con);
$sql = "CREATE TABLE quotes(
number int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(number),
quote char(200))";
if (!mysql_query($sql, $con)){
die('Error with table creation: ' . mysql_error());
}
}
$sql2 = "INSERT INTO quotes(quote)
VALUES('$quote')";
if (!mysql_query($sql2, $con)){
die('Error adding data to table: ' . mysql_error());
}
mysql_close($con);
echo $quote;
}
else{
echo 'Please enter a Quote!';
include("form.php");
}
}
?>
</body>
</html>>
Posted: Mon Jun 25, 2007 6:16 pm
by feyd
You can also add "IF NOT EXISTS" into the "CREATE TABLE" call. I can't recall offhand what databases support it, however.
Posted: Mon Jun 25, 2007 6:29 pm
by volka
mabufo wrote:If the table exists, and a query is sent to create the same table, will the database write over the current table?
No.
mabufo wrote:Will it ignore the request?
It will not execute the request, if this is your question.
Posted: Mon Jun 25, 2007 8:53 pm
by mabufo
I still cannot get the above form processing code to work how it should. It's supposed to echo back the variable that was passed to it by the form, but it isn't happening when the script is executed.... No errors either. Is there a logic problem that I can't see?
Posted: Mon Jun 25, 2007 9:12 pm
by bdlang
feyd wrote:You can also add "IF NOT EXISTS" into the "CREATE TABLE" call. I can't recall offhand what databases support it, however.
MySQL does.
Code: Select all
CREATE TABLE IF NOT EXISTS `testMe` (
...
);
Posted: Mon Jun 25, 2007 9:31 pm
by mabufo
Mystery Solved.
Code: Select all
<html>
<body>
<?php
$quote = $_POST['quote'];
if ($quote){
//connects to database with given info
$con = mysql_connect("localhost","***","***") or die(mysql_error());
//selects database
mysql_select_db("misc", $con);
//infor for table creation
$sql = "CREATE TABLE IF NOT EXISTS `quotes`
(
numberID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(numberID),
quote varchar(200)
)";
//creats table
mysql_query($sql, $con) or die(mysql_error());
//data for table
$data = "INSERT INTO quotes(quote) VALUES('$quote')";
//sends data to table
mysql_query($data, $con) or die(mysql_error());
//closes connection
mysql_close($con);
echo $quote;
}
else{
echo 'Enter a quote!';
include("form.php");
}
?>
</body>
</html>
Looking at the phpmyadmin on my local machine, it works properly. Quotes entered on the form are added to the table. Hooray.
Posted: Tue Jun 26, 2007 3:46 am
by volka
and now please read
http://de3.php.net/security.database.sql-injection
Code: Select all
<html>
<head><title>quotes</title></head>
<body>
<?php
if ( isset($_POST['quote']) && 0<strlen(trim($_POST['quote'])) ) {
$con = mysql_connect("localhost","root","zerocool") or die(mysql_error());
mysql_select_db("misc", $con) or die(mysql_error());
// table creation
$sql = "CREATE TABLE IF NOT EXISTS `quotes`
(
numberID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(numberID),
quote varchar(200)
)";
// table creation
mysql_query($sql, $con) or die(mysql_error());
$quote = mysql_real_escape_string($_POST['quote'], $con);
$data = "INSERT INTO quotes(quote) VALUES('$quote')";
mysql_query($data, $con) or die(mysql_error());
echo htmlentities($_POST['quote']);
}
else {
echo 'Enter a quote!';
require 'form.php';
}
?>
</body>
</html>