working with a database for the first time

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
mabufo
Forum Commoner
Posts: 81
Joined: Thu Jul 10, 2003 11:11 pm
Location: Orland Park, IL
Contact:

working with a database for the first time

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

There's a huge supply of examples available across the web. What have your searches turned up?
User avatar
mabufo
Forum Commoner
Posts: 81
Joined: Thu Jul 10, 2003 11:11 pm
Location: Orland Park, IL
Contact:

Post by mabufo »

Well, I'm asking for whatever has worked for you all.
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

http://www.ooer.com/index.php?section=php&id=8 ... not a bad place to start. But I would say that.
User avatar
mabufo
Forum Commoner
Posts: 81
Joined: Thu Jul 10, 2003 11:11 pm
Location: Orland Park, IL
Contact:

Post by mabufo »

Quick question: how do I check to make sure a table doesn't exist, before I create it?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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;
}
User avatar
mabufo
Forum Commoner
Posts: 81
Joined: Thu Jul 10, 2003 11:11 pm
Location: Orland Park, IL
Contact:

Post 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?
User avatar
mabufo
Forum Commoner
Posts: 81
Joined: Thu Jul 10, 2003 11:11 pm
Location: Orland Park, IL
Contact:

Post 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 :oops: .

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>>
Last edited by mabufo on Mon Jun 25, 2007 9:23 pm, edited 3 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You can also add "IF NOT EXISTS" into the "CREATE TABLE" call. I can't recall offhand what databases support it, however.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
mabufo
Forum Commoner
Posts: 81
Joined: Thu Jul 10, 2003 11:11 pm
Location: Orland Park, IL
Contact:

Post 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?
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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` (
...
);
User avatar
mabufo
Forum Commoner
Posts: 81
Joined: Thu Jul 10, 2003 11:11 pm
Location: Orland Park, IL
Contact:

Post 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.
Last edited by mabufo on Tue Jun 26, 2007 12:25 pm, edited 1 time in total.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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>
Post Reply