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
// using $db->affected_rows
//     returns the number of rows in a table affected by your query
//     can be used after UPDATE query (to see how many rows are updated)
//     can be used after SELECT query (to see how many rows will be returned)

$sql = "SELECT name FROM ". $db->pre ."medical WHERE wtype='1'";

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

if($db->affected_rows > 0){
    echo "Success!";
}
else{
    echo "Error: No record";
}

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
require("Database.class.php");

// creating and connecting to two database
$db_1 = new Database("localhost", "user", "pass", "deadwood_public");
$db_2 = new Database("localhost", "user", "pass", "deadwood_internal");

// NOTE: this is the main difference
// when I connect using connect(true) it will force open a new connection
// otherwise connect() uses the existing connection if it contains the the same info
$db_1->connect(true);
$db_2->connect(true);

// insert data into both database
$data['member_name']="John Smith";

$db_1->query_insert("members", $data);
$db_2->query_insert("members", $data);

// close the second connection
$db_2->close();


// we can still run a query to the first connection
$rows = $db_1->query("SELECT * FROM members LIMIT 0,5");

while($row = $db_1->fetch_array($rows)){
    echo "<pre>".print_r($row,true)."</pre>";
}

// close the first connection
$db_1->close();

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-2009 ricocheting.com   ::   Use Policy   ::   Contact