PHP AND MySQL EXAMPLES

PLEASE DONATE AS WORKING OUT THIS CODE TAKES TIME AND HOSTING WEB SITE ALSO COSTS HARD CASH. MOST HOSTS WON'T GIVE YOU THIS CODE. AND MOST WEBSITES HAVE ERRORS BECASUE PHP HAS BEEN UPDATED .

Step 1 - go into you control panel on web Host and create a MySQL database - this will have database_name, user_name, your_password, host will tell you server address.

STEP 2. GO INTO PHP MY ADMIN

RUN A SIMPLE MY SQL QUERY TO CREAT A SIMPLE TABLE - REPLACE DB_NAME WITH NAME OF YOUR DB

USE DB_NAME;

CREATE TABLE contacts
(user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
first VARCHAR(15) NOT NULL,
last VARCHAR (15) NOT NULL,

PRIMARY KEY(user_id)
);

EXAMPLE OF WHY YOU DONT SENT NULL VALUES TO DATABASE

AUTO-INCREMENT VALUES IN A SQL TABLE

SQL CODING TIPS > EXTERNAL LINK

write a simple php file to test connection to the server. STEP 3

<?php

// This file contains the database access information.
// This file also establishes a connection to MySQL
// and selects the database.
// procedural style
// Set the database access information as constants:
//DEFINE ('DB_USER', 'fred');
//DEFINE ('DB_PASSWORD', 'Gol');
//DEFINE ('DB_HOST', 'host.ipagemysql.com');
//DEFINE ('DB_NAME', 'acid');

 

$link = mysqli_connect("server-hostname", "db-username", "password", "db-name");

/* check connection */
if (!$link) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

printf("Host information: %s\n", mysqli_get_host_info($link));

/* close connection */
mysqli_close($link);
?>

LETS NOW GET INFO FROM USER OF WEB SITE AND PUT IT INTO THE TABLE WE CREATED IN STEP 2. HTML FILE BELOW.

<body>

<h1>A small example page to insert some data in to the MySQL database using PHP</h1>

<form action="insert.php" method="post">

Firstname: <input type="text" name="fname" /><br><br>

Lastname: <input type="text" name="lname" /><br><br>

<input type="submit" />

</form>

</body>
</html>

<body>
<html>


PHP INSERT INTO DATABASE FILE BELOW. USE SAME HOST, PASSWORD AND USER NAMES AS IN STEP 3.

<body>

test page

 

<?php

$con = mysql_connect("DB_HOST","DB_USER","DB_PASSWORD");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

 

mysql_select_db("acid", $con);

 

$sql="INSERT INTO nametable (firstname, lastname)

VALUES

('$_POST[fname]','$_POST[lname]')";

 

if (!mysql_query($sql,$con))

{

die('Error: ' . mysql_error());

}

echo "1 record added";

 

mysql_close($con)

?>

</body>

</html>

 

</body>
</html>


DISPLAY THE DATABASE WE HAVE CREATED. STEP 4 ANOTHER PHP FILE - again put in your values for db_host, db_user and db_password.

<?

$con = mysql_connect("db_host","db_user","db_password");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

 

mysql_select_db("acid", $con);

 

 

$query="SELECT * FROM nametable";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

$i=0;
while ($i < $num) {

$firstname=mysql_result($result,$i,"firstname");
$lastname=mysql_result($result,$i,"lastname");

echo "<b>$firstname $lastname</b><br>";

$i++;
}

?>


HOW TO SEARCH THE DATABASE

<?php

/*
localhost - it's location of the mysql server, usually localhost
root - your username
third is your password

if connection fails it will stop loading the page and display an error
*/


/* tutorial_search is the name of database we've created */

$con = mysql_connect("DB_HOST","DB_USER","DB_PASSWORD");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

 

mysql_select_db("acid", $con);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Search results</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="style.css"/>
</head>
<body>
<?php
$query = $_GET['query'];
// gets value sent over search form

$min_length = 3;
// you can set minimum length of the query if you want

if(strlen($query) >= $min_length){ // if query length is more or equal minimum length then

$query = htmlspecialchars($query);
// changes characters used in html to their equivalents, for example: < to &gt;

$query = mysql_real_escape_string($query);
// makes sure nobody uses SQL injection

$raw_results = mysql_query("SELECT * FROM nametable
WHERE (`lastname` LIKE '%".$query."%') OR (`firstname` LIKE '%".$query."%')") or die(mysql_error());

// * means that it selects all fields, you can also write: `id`, `title`, `text`
// articles is the name of our table

// '%$query%' is what we're looking for, % means anything, for example if $query is Hello
// it will match "hello", "Hello man", "gogohello", if you want exact match use `title`='$query'
// or if you want to match just full word so "gogohello" is out use '% $query %' ...OR ... '$query %' ... OR ... '% $query'

if(mysql_num_rows($raw_results) > 0){ // if one or more rows are returned do following

while($results = mysql_fetch_array($raw_results)){
// $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop

echo "<p><h3>".$results['lastname']."</h3>".$results['firstname']."</p>";
// posts results gotten from database(title and text) you can also show id ($results['id'])
}

}
else{ // if there is no matching rows do following
echo "No results";
}

}
else{ // if query length is less than minimum
echo "Minimum length is ".$min_length;
}
?>
</body>
</html>


DISPLAYING A FLICKR PHOTOSTRAM - HTML DOCUMENT. USES JAVASCRIPT.

<html>
<head>
<title>Flick Test</title>

<script src="http://code.jquery.com/jquery-latest.js"></script>

<script>

$(function(){

var id='51214730@N02';
var limit ='20';

// Flickr Photostream feed link.
$.getJSON("http://api.flickr.com/services/feeds/photos_public.gne?id=" + id + "&lang=en-us&format=json&jsoncallback=?",

function(data){$.each(data.items,

function(i,item){

// Number of thumbnails to show.
if(i < limit){

// Create images and append to div id flickr and wrap link around the image.
$("<img/>").attr("src", item.media.m.replace('_m', '_s')).appendTo("#flickr").wrap("<a href='" + item.media.m.replace('_m', '_z') + "' name='"+ item.link + "' title='" + item.title +"'></a>");

}

});

});

});

</script>
<style type="text/css">
#flickr{
width:340px;
overflow:hidden;
}
#flickr a{
width:75px;
float:left;
margin:10px 10px 0 0;
padding:0;
border:0;
}
</style>

</head>
<body>
<h1> MY FLICKR PHOTOSTREAM </h1>
<div id="flickr"></div>
</body>
</html>


DISPLAYING A FLICKR PHOTOSTRAM USING PHP. DOWNLOAD PhpFlickr from the Internet.

Put file below in the PhpFlickr folder it is a PHP FILE .PHP

<?PHP
require_once("phpFlickr.php");

$phpFlickrObj = new phpFlickr('YOUR-API-KEY-FROM-FLICKR-APP-GARDEN-FOR-YOUR-APP');

$user = $phpFlickrObj->people_findByUsername('Adrian Jones');
$user_url = $phpFlickrObj->urls_getUserPhotos($user['id']);
$photos = $phpFlickrObj->people_getPublicPhotos($user['id'], NULL, NULL, 20);

foreach ($photos['photos']['photo'] as $photo)
{
echo '<a href="'.$user_url.$photo['id'].'" title="'.$photo['title'].' (on Flickr)" target="_blank">';
echo '<img alt="'.$photo['title'].'" src="'.$phpFlickrObj->buildPhotoURL($photo, "square").'" />';
echo '</a>';
}
?>