Seesions in the database

I found a helpful article showing how to use a class.
http://www.josephcrawford.com/php-articles/going-deep-inside-php-sessions/

It has problems and I spent time fixing all the problems and will post the code when its ready.

How to make and use MySQL stored functions using PHP

How to make a useful function in mysql
I am going to show how to make MySQL functions that insert data into a people table in MySQL. I will also show how to make a function that returns a value of the average age of the people in the table.
Log into MySQL with the following line:
mysql -u Username -p
Then open the database you will use like this:
use test
First create a table called people and run this in MySQL:
CREATE table people(id int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT
,name VARCHAR(50)
,age INT
);
You can insert a user in mysql or in PHP. It will use the same SQL code either way.
You can add people with their ages to a list in 2 different ways. You can either write static PHP code and pass the name and age into the SQL code via varables and execute it or you can write a MySQL function and pass it the name and age values to insert into the table via parameters.
Here is how you would insert using the last blogs method using the mysql_magic function:

$age = 15;
$name = 'Mike';
$insert = mysql_magic("INSERT INTO people VALUES(NULL,?,?)", $name, $age);
echo "insert".$insert;


Now we will make a MySQL function that will do our inserts for us.
type this in the MySQL prompt:

DROP FUNCTION IF EXISTS insert_people;
DELIMITER $
CREATE FUNCTION insert_people(name VARCHAR(50), age INT)
RETURNS INT
BEGIN
INSERT INTO people VALUES(NULL, name, age);
RETURN 1;
END$
DELIMITER ;


Here is how you would execute the insert statement from a PHP script:

$name = "Bill";
$age = 20;
$insert = mysql_magic('SELECT insert_people(?,?) AS the_insert', $name, $age);
echo $insert[0]['the_insert']."
";

If the value echoed is a 1 then it worked.
Now lets make a function that will average the ages of the people:

DROP FUNCTION IF EXISTS average_age;
DELIMITER $
CREATE FUNCTION average_age()
RETURNS INT
BEGIN
DECLARE lv_avg_age INT;
SELECT AVG(age) INTO lv_avg_age FROM people;
RETURN lv_avg_age;
END$
DELIMITER ;


Now get the average from php:

$average = mysql_magic('SELECT average_age() AS the_average');
echo "The average age is: ".$average[0]['the_average'];

If there is something you would like to see here let me know and I will add it.

PHP helper function for making SQL queries easy

Go get this function called mysql_magic at:
http://www.php.net/manual/en/ref.mysql.php#100626
This function listed in PHP's manual looks very promising for making query's easy to use but just passing the query plus the parameters to a function that uses php's mysql_bind function plus the function returns a resultset if its a select and will also work for a delete or update statement which exits the function sooner than normal.
to use the function use these:
$nb_affected = mysql_magic('delete from users');
$nb = mysql_magic('select count(*) from users');
$one_row = mysql_magic('select * from users limit 1');
$all_rows = mysql_magic('select * from users where name = ?', 'John');
$id = mysql_magic('insert into users(name,rank) values(?,?)', 'Vincent', 3);
http://www.php.net/manual/en/ref.mysql.php#100626


To make the function work with the example below make this change to the function.
global $con, $con_server, $con_username, $con_password, $con_database;

if (!$con)
{
$con = mysql_connect( $con_server, $con_username, $con_password );
@mysql_select_db( $con_database, $con );
}
I will show you how to use it next. Make a table called data in mysql.

CREATE TABLE data (age INT,name VARCHAR(50));

Next insert a couple rows.
INSERT INTO data VALUES(20,'Bill');
INSERT INTO data VALUES(18,'Mark');

I put the function mysql_magic in a file called mysql_helper.php into the include path, but you can put it in the same folder as your webpage that will be doing mysql queries. This is all I did to make it work:
include "helper_mysql.php";
$con_server = "localhost";
$con_username = "username";
$con_password = "password";
$con = @mysql_connect($con_server,$con_username,$con_password);
if (!$con){
die('Could not connect to Database');
}
$con_database = "test";
mysql_select_db($con_database, $con);
$all_rows = mysql_magic('select * from data');
echo "<br /><pre>".print_r($all_rows,true)."</pre>";


Now you will see the result will show all the rows from the table:
Array
(
[0] => Array
(
[age] => 20
[name] => Bill
)

[1] => Array
(
[age] => 18
[name] => Mark
)
)

Then you can use the following to insert more data into the table.
$insert = mysql_magic("INSERT INTO data VALUES(15,'Ron')");

Now for a more useful example. Use ?'s to signify a parameter to bind variables to.
$age = 25;
$name = "Sally";
$insert = mysql_magic("INSERT INTO data VALUES(?,?)",$age, $name);
//after inserting use the following to see what the table looks like now.
echo "<br /><pre>".print_r($all_rows,true)."</pre>";

In the next post I will show how to make a stored procedure or function in MySQL and call it using the magic_mysql function when you want to insert data into the data table.

How to get the current time in a timezone of choice

type this in a php page to get the time where you live. The -6 means go to 6 hours before greenwhich time.
$tz = -6;
$date = gmdate('m/d/Y', time()+($tz*3600));
echo $date;


Running php from the commandline

if you want to run a short snibit of code from the command line use this syntax:
php -r "echo 'test';"