Connecting to and Workign with Mysql from PHP

I. How to connect to the MySql Server

The way to connect to a MySql Database from PHP is to use the mysql_connect() method. To conect on our computers with XAMPP, you use the lines below.

<?php
$result = mysql_connect("localhost","root","");
echo $result;

?>

II. How to Create MySql Database & MySql Table

The code below creates a database that you are going to store and use on the server. A database is made up of many tables.

<?php
$createDatabase ="CREATE DATABASE IF NOT EXISTS database1" ;
$resultOfCreation =	mysql_query($createDatabase) or  die(mysql_error()) ;
echo " resultOfCreation  "  . $resultOfCreation ;
?>

Now, let's create a table in our databse and print out the results.

<?php

$createTable = "CREATE TABLE IF NOT EXISTS firstTable(
				stringItem varchar(255) ,  
				id  INT  AUTO_INCREMENT, 
				PRIMARY KEY(id)
		)";
$resultOfCreation =	mysql_query($createTable) or die(mysql_error()) ;


?>	

---

If you need to delete a database or table, use this Query

Note: you must always first connect to the server as we did at the top. To Drop a Table, you use the same syntax

$querty = "DROP DATABASE   database1";
$dropQ =	mysql_query($querty) or  die(mysql_error()) ;
echo "dropped table " . $dropQ;

Connecting to an Already Established Table

After you have created the database as well as a table such as our aptly named table `firstTable` you use the code below to connect to that database's table and, presumably, stat doing stuff with the table

<?php 

$connection = mysql_connect("localhost","root","");
echo $connection;
echo '<br /> ';
$connectToDB = mysql_select_db('firstDB', $connection) or  die(mysql_error());
echo 'select db: ' . $connectToDB ;
?>

Part I. Create a table called 'apcs'

<?php


$connection = mysql_connect("localhost","root","");
$connectToDB = mysql_select_db('firstDB', $connection) or  die(mysql_error());
 
$createTableQuery = "CREATE TABLE IF NOT EXISTS apcs(
	firstName varchar(255) ,  
	lastName varchar(255)  ,
	gradeLevel INT ,    
	age  INT  , 
	id  INT  AUTO_INCREMENT, 
	PRIMARY KEY(id)	)";
	
$queryResults=	mysql_query($createTableQuery) or  die( mysql_error() ) ;
echo 'results of query ' . $queryResults;
 
 ?>

Part II. Inserting Data into the Table

The query below will insert a row for Andrew Grosser!

A more advanced and better way to insert data

<?php

$addStudentsQuery = "INSERT INTO apcs  SET 
			firstName ='Andrew' ,
			lastName = 'Grosser',
			gradeLevel = 10,
			age = 16";

$queryResults =	mysql_query($addStudentsQuery) or  die(mysql_error()) ;
echo 'results of query ' .$queryResults ;

?>

Run this same query until every student in the class is in your database. It would be wise to create a reusable function that takes parameters.

Welcome Guest
Please Log in or register
Who's online: 8 Guests, 0 Users :        Please welcome jsantiago, our newest member.