ricocheting.com - Free CGI and PHP scripts
Need something changed? I can do script customization
Active Scripts
ricosend Image Gallery PHP MySQL Wrapper
x Abandon Scripts x Contact x HOME

PHP MySQL wrapper

Description

Download

Terms of Use

What's new

Instructions

Calling the Class, config settings

Examples of how to call things

Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
// every page needs to start with these basic things

// I'm using a separate config file. so pull in those values
require("config.inc.php");

// pull in the file with the database class
require("database.class.php");

// create the $db ojbect
$db = new Database($config['server'], $config['user'], 
                   $config['pass'], $config['database'], $config['tablePrefix']);

// connect to the server
$db->connect();

#####
// your main code would go here
#####

// and you're done, remember to close connection
$db->close();

Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
// update an existing record using query_update()

$data['count'] = 3536;
$data['ename'] = "NULL";// it knows to convert NULL and NOW() from a string

// query_update() parameters
//     table name (no prefix)
//     assoc array with data (doesn't need escaped)
//     where condition
$db->query_update("logs", $data, "url='google.com'");

// would create the query:
// UPDATE `logs` SET `count`='3536', `ename`=NULL WHERE url='google.com'

Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
// insert a new record using query_insert()

$data['count'] = 1;
$data['ename'] = "you're"; // query_insert() will auto escape it for us
$data['url'] = "ricocheting.com";

// query_insert() parameters
//     table name (no prefix)
//     assoc array with data (doesn't need escaped)
$db->query_insert("logs", $data);

// would create the query:
// INSERT INTO `logs` (`count`,`ename`,`url`) VALUES ('1', 'your\'re', 'ricocheting.com')

Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
// escape() query() and fetch_array()

// pullout the first 10 entries where url came from google
//     $db->pre is my table prefix
//     $db->escape() escapes string to make it safe for mysql

$url = "http://www.google.com/";

$sql = "SELECT id, url, count FROM ".$db->pre."logs
          WHERE url LIKE '". $db->escape($url) ."%'
          ORDER BY count DESC
          LIMIT 0,10";

$countRows = $db->query($sql);

while ($countRow = $db->fetch_array($countRows)) {
    echo "<tr><td>$countRow[id]</td>
          <td>$countRow[url]</td>
          <td>$countRow[count]</td></tr>";
}

Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// using escape() and fetch_all_array()

// pullout the first 10 entries where url came from google
//     $db->pre is my table prefix
//     $db->escape() escapes string to make it safe for mysql

$url = "http://www.google.com/";

$sql = "SELECT id, url, count FROM ".$db->pre."logs
          WHERE url LIKE '". $db->escape($url) ."%'
          ORDER BY count DESC
          LIMIT 0,10";

// feed it the sql directly. it will return all records
$all_rows = $db->fetch_all_array($sql);


// print out array later on when we need the info on the page
foreach($all_rows as $key=>$val){
    echo "<tr><td>$val[id]</td>
          <td>$val[url]</td>
          <td>$val[count]</td></tr>";
}

Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
// using escape() query() and fetch_array()

// sort by rank which is (premium*hits) from table
//     $db->pre is my table prefix
//     $db->escape() escapes my category string to make it safe for mysql

$category = 'Keyboard "THE GREAT" Typer';

$sql = "SELECT id, ename, SUM(premium*hits) AS rank FROM ". $db->pre ."logs
          WHERE category='". $db->escape($category) ."' 
          GROUP BY id 
          ORDER BY rank DESC";

$rankrows = $db->query($sql);

while ($rank_row = $db->fetch_array($rankrows)) {
    echo "<tr><td>$rank_row[id]</td>
          <td>$rank_row[ename]</td>
          <td>$rank_row[rank]</td></tr>";
}

Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// using query_first() 

// get patient's name using their unique ID
//     $db->pre is my table prefix

$sql = "SELECT first_name FROM ". $db->pre ."medical
          WHERE patient_id=". $p_id ."";

// since patient_id is unique, only one record needs returned
//     I use $db->query_first() instead of $db->query() and fetch_array()
//     $db->query_first() will return array with first record found
$row = $db->query_first($sql);

// since it only returned one record, i don't need to fetch. I can print off
echo $row['first_name'];

Code:
1
2
3
4
// delete a specific entry

$sql = "DELETE FROM ".$db->pre."news WHERE id=$my_id";
$db->query($sql);

Code:
1
2
3
4
// updates/replaces if exists. adds if doesn't

$sql = "REPLACE INTO ".$db->pre."news VALUES (1, 'first text'),(2, 'second text');";
$db->query($sql);

Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
// snippet of code to see if a user has downloaded too much

$config['download_limit'] = 3;

// check database for their IP
$sql = "SELECT count(*) AS number FROM ".$db->pre."downloads
          WHERE ip='$_SERVER[REMOTE_ADDR]'
          AND category='games'";

$iplocked = $db->query_first($sql);

// check to see if IP and/or cookie is over the limit
if($iplocked['number'] >= $config['download_limit'] || 
$_COOKIE['download_limit'] >= $config['download_limit']){
    echo "over user limit error";
    // YOU: exit or redirect them
}
// they're good to go. allow download
else{
    // insert their ip into database
    $data = array('ip'=>$_SERVER['REMOTE_ADDR'], 'time'=>time(), 'category'=>"games");
    $db->query_insert("downloads", $data); 

    // set a cookie too
    setcookie("download_limit", ($_COOKIE['download_limit']+1), time() + 24 * 3600);

    // YOU: send the user to the file
}


// deletes "expired" entries each time script is run
$lockTime = time()-24*3600; // 24hrs
$sql = "DELETE FROM ".$db->pre."downloads
          WHERE category='games'
          AND time < $lockTime";
$db->query($sql);

Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// involved process getting certain "pages" of info from mysql using LIMIT
$config['page']=3;  //current page to pull up
$config['items']=4; //items to display per page

//if they are looking past page one, it's expired news
//  get expired news, but the page of X 'item' records specified above
if($config['page'] > 1){
    $sql = "SELECT author, header, description FROM ".$db->pre."news
              WHERE expires < ".date("Y-n-j")."
              ORDER BY expires DESC, id DESC
              LIMIT ".(($config['page']-1)*$config['items']).",$config[items]";
}else{
//they are on page on (which displays all the "current" news)
//  get that current news that hasn't expired yet
    $sql = "SELECT author, header, description FROM ".$db->pre."news
              WHERE expires >= ".date("Y-n-j")."
              ORDER BY expires DESC, id DESC";
}

$newsRows = $db->query($sql);
while ($newsRow = $db->fetch_array($newsRows)) {
    // etc
}

Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$sql = "CREATE TABLE ".$db->pre.$table." (
         `id` smallint(6) NOT NULL auto_increment,
         `expires` date NOT NULL default '0000-00-00', 
         `author` varchar(20) NOT NULL, 
         `header` varchar(100) default NULL, 
         `description` text, 
         PRIMARY KEY (id),
         INDEX (expires)
         )";

$sql = "CREATE TABLE ".$db->pre.$table." (
         `user` varchar(20) NOT NULL default '',
         `pass` varchar(20) NOT NULL default '',
         `email` varchar(255) default NULL,
         `count` smallint(6) unsigned default '0',
         `html` enum('N','Y') NOT NULL default 'N', 
         PRIMARY KEY (user),
         UNIQUE KEY user (user)
         )";

$db->query($sql);

Code:
1
2
3
4
5
6
$sql = "SELECT a.auction_id, a.title, p.file, p.caption 
    FROM ".$db->pre."auctions a
    LEFT JOIN ".$db->pre."photos p on a.primary_photo_id = p.photo_id
    WHERE a.featured = 1";

$db->fetch_all_array($sql);

And now because I'm lazy and tend to look here all the time myself when i need a reference.
Data Type Column Type Range or Description Storage
Numeric TinyInt Signed values from -128 to 127
Unsigned values from 0 to 255		 1 byte
Numeric SmallInt Signed values from -32768 to 32767
Unsigned values from 0 to 65535		 2 bytes
Numeric MediumInt Signed values from -8388608 to 8388607
Unsigned values from 0 to 16777215		 3 bytes
Numeric Int Signed values from -2147683648 to 2147483647
Unsigned values from 0 to 4294967295		 4 bytes
Numeric BigInt Signed values from -9223372036854775808 to 9223372036854775807
Unsigned values from 0 to 18446744073709551615		 8 bytes
Numeric Float Minimum non-zero values: 1.175494351E-38
Maximum non-zero values: 3.402823466E+38		 4 bytes
Numeric Double Float Minimum non-zero values: 2.2250738585072014E-308
Maximum non-zero values: 1.7976931348623157E+308		 8 bytes
Numeric Decimal Varies Maximum width + 2 bytes
String Char Range 1-255 characters Always filled max width
String VarChar Range 1-255 characters Length of string + 1 byte
String TinyBlob, TinyText Max length 255 characters Length of string + 1 byte
String Blob, Text Max length 65535 characters (~64KB of text) Length of string + 2 bytes
String MediumBlob, MediumText Max length 16777216 characters (16MB of text) Length of string + 3 bytes
String LongBlob, LongText Max length 4294967295 characters (4GB of text) Length of string + 4 bytes
String Enum ('value','value2',...) String object that can have only one set of allowed values 1 or 2 bytes
String Set ('value','value2',...) String object that can have one or many values of a set of allowed values. 1, 2, 3, 4, or 8 bytes
Date/Time Date 'YY-MM-DD', 'YYYY-MM-DD', 'YYMMDD'
Range "1000-01-01" to "9999-12-31"		 3 bytes
Date/Time Time 'HH:MM:SS', 'HHMMSS', 'HHMM', 'HH'
Range "-838:59:59" to "838:59:59"		 3 bytes
Date/Time DateTime 'YYYY-MM-DD HH:MM:SS'
"0000-01-01 00:00:00" to "9999-12-31 23:59:59"		 8 bytes
Date/Time TimeStamp 19700101000000 to sometime in the year 2037 4 bytes
Date/Time Year 'YYYY', 'YY' Range "1901" to "2155" 1 byte
Scripts & design created 2000-2008 ricocheting.com   ::   Use Policy   ::   Contact