How to connect c# to remote mysql database

In this quick tutorial I’m going to show you how you can connect your c# application to a remote mysql database. There’s no additional coding involve in this tutorial all you need to do is to configure your connection string a little bit and configure the privileges on your mysql database.

 

Requirements

  • phpmyadmin

 

Privileges

First you have to access your mysql database using phpmyadmin.

http://localhost:8080/phpmyadmin/

Click on the privileges tab then change the host to Any host.

image

 

Connection String

Finally you just have to change the server you’re connecting to. This time its not localhost since you’re not connecting to the database on your local computer.

string con_string = "Server=192.168.218.10;

Database=cake_beginner;Uid=root;Pwd=1234";

In the example above I used the internal ip address. If you don’t know your internal ip address. Just go to cmd and type ipconfig/all and copy the ip version 4 preferred address. Well that’s just internal ip address, which means that only the people who are inside your home network can access. If you want to connect to a remote mysql database you have to determine your external ip address, you can do so by going to cmyip.com. But remember that the ip address of the computer you’re trying to access should be static so that you won’t need to change your connection string every time you need to connect to the remote mysql database.

I have written a tutorial before on how to connect to a remote computer. You might want to read it if you don’t know how to configure your router to make remote access possible.

How to connect c# with mysql

In this tutorial I’m going to show you how you can connect your c# application with a mysql database. Were also going to create a simple CRUD application.

 

Requirements

 

Database

For this tutorial I’m going to use a sample database called users. You can also use it if you want. Just create a database in phpmyadmin and execute the following query.

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(80) NOT NULL,
  `middlename` varchar(80) NOT NULL,
  `lastname` varchar(80) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

Once you have the table input some data into it since the first thing that were going to do is to select the data from the database.

 

MySQL Namespace

I won’t go ahead and discuss the whole namespace thing. I’ll just discuss it briefly so that you will have an idea what it is. Namespace is basically just a container for classes, interface, and all sorts of object oriented stuff that you can think of. Here’s a link if you want to learn more about namespaces.

Back to our main topic, the MySQL Namespace. First you have to import it into your application, make sure that already installed the mysql .net connector on your computer before doing anything else. To import the MySQL Namespace into your application, just go to Project->Add Reference and then click on the Browse tab and browse to the following location:

C:\Program Files\MySQL\MySQL Connector Net 6.4.4\Assemblies\v2.0

Once you are there just select the MySql.Data.dll file. To make sure that you have really added the namespace you can go to your solution explorer, expand the references folder and make sure that the MySql.Data namespace is in there.

image

We still can’t go ahead and connect to a mysql database with what we just did. We still have to include it on our application. Just line it up with all the using stuff that you see on your code by default. This enables you to use all the classes and methods on the MySql.Data namespace. 

using MySql.Data.MySqlClient;

 

Database Class

Were here at the exciting part. For this tutorial I’m going to use a class to connect and do some operations on the mysql database. 

To create a new class, just go to Project->Add new item and then choose class.

Include the MySql.Data namespace.

using MySql.Data.MySqlClient;

Declare the connection string. For this tutorial I’m using the cake_beginner database. The default user for mysql is root, and the default password is none but in here I have 1234 as my password. For security sake don’t use these kinds of password on a live server.

string con_string = "Server=localhost;Database=cake_beginner;

Uid=root;Pwd=1234";

Next is the mysqlconnection class. I’m declaring it in a global scope so any method on our class can just use it. Its basically used for representing mysql database connections.

MySqlConnection con;

Lastly the mysqlcommand class. It’s a class whose methods are used for executing queries in a mysql database. More like a container for a bunch of methods which you can use to execute different queries.

MySqlCommand command;

Next we create our constructor. If you’re new to programming, the constructor is a special type of method which is automatically executed once we declare an object of the class. Sounds like a good place to define the mysqlconnection, all we have to do is to supply the connection string as an argument once we declare an object of the MySqlConnection class.

public database_class() {
            con = new MySqlConnection(con_string);
}

Next we define a method which reads a single row from our table. It has a single parameter which is the user_id and we will use it on the select query since we only want a single record to be returned.

 public string[] read_row(string user_id) {

}

Inside of the read_row() method we define an array which will store the firstname, middlename, and lastname:

string[] name =  new string[3];

Create a new command:

command = con.CreateCommand(); command.CommandText = "SELECT * FROM users

WHERE id='" + user_id + "'";

Here’s what you have to remember before or after you execute any command or query. Remember to always open the connection before you execute any query and close it after execution.
In the code below we used the MySqlDataReader to read the rows returned from our select query. Then we used a while statement together with the read() method to go through the stream of rows as long as there is something to read. In this case we are only selecting one record so this loop only executes once and then return the array which contains the firstname, middlename and lastname.

con.Open(); MySqlDataReader dr = command.ExecuteReader(); //execute select query while (dr.Read()) //continue reading while there is something to select {

//assign each field with a different index in the name array name[0] = dr["firstname"].ToString(); name[1] = dr["middlename"].ToString(); name[2] = dr["lastname"].ToString(); } con.Close();

return name;

Next is updating a single row. Define a new method and call it update_row() which has 4 parameters as you can see from the code below.

public void update_row(string fname, string mname, string lname, string user_id) {


}

Inside it we execute the update query.

string update_command = "UPDATE users SET firstname='" + fname + "', middlename='" + mname + "', lastname='" + lname + "' WHERE id='" + user_id + "'";
command.CommandText = update_command;
con.Open();
command.ExecuteNonQuery();
con.Close();

Lastly we need to create the method for creating new rows.

 public void create_row(string fname, string mname, string lname) {

}

Inside it we just execute an insert query.

command = con.CreateCommand();
command.CommandText = "INSERT INTO users SET firstname='" + fname + "', middlename='" + mname + "', lastname='" + lname + "'";
con.Open();
command.ExecuteNonQuery();
con.Close();

You might have noticed that I didn’t include a method which will delete a record from our database. Yes you’re right, and that’s because I’m lazy and I just embedded the code on the form itself so please bear with me.

 

Creating the forms

For you to have an idea what the form might look like, here’s a screenshot. The big blank space is for the datagrid, the refresh button is used for fetching the data from the mysql database and loading it into the datagrid, the create button pops out a new form which allows the user to create a new record, the edit button pops out a new form which will allow the user to update an existing row, and the delete button is used to delete a selected row from the datagrid.

image

Note that the form above is our main form so you might want to change the startup form if you don’t already have it as the startup form. To set a form as the startup form you just have to open up the Program.cs file from the solution explorer and under the Main() method just change the name of the Form supplied as the argument. In this case my default form is Form2 so just change it to whatever the name of the form you want to become the startup form.

static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form2()); //set startup form here
        }

 

Calling new forms

Calling forms is on its full object-oriented syntax on c#. You first need to create an object of the form that you’re trying to call and then use the show() method to show it. The example below calls for a form called Form1.

Form1 form1 = new Form1();
 form1.Show();

 

Populating the Datagrid

For populating the datagrid I’m not going to use the class that we built earlier. I’m going to paste the code for mysql connection on the form. Why? because I’m just a beginner in c#.

string con_string = "Server=localhost;Database=cake_beginner;Uid=root;Pwd=1234";
MySqlConnection con;
MySqlCommand command;
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter();
int current_id = 0; //changes when the user clicks on the cell

Then create a function that will load the data into the datagridview. First we clear the contents of the dataset. In simple terms the dataset is buffered data similar to that of the buffered video when you’re watching on youtube. It’s a temporary storage of data from the mysql database. Next we specify the query to be used by the data  adapter, the data adapter as defined by msdn serves as a bridge between a dataset and a data source for retrieving and saving data.
Next we filled the dataset with the results returned from the mysql command. Then finally using the data from the dataset as a data source for the datagridview.

 public void load_data() {
            ds.Clear(); //clear the dataset
            
            da.SelectCommand = new MySqlCommand("SELECT * FROM users", con);
            da.Fill(ds); //fill the dataset 
            dataGridView1.DataSource = ds.Tables[0]; //specify the data source for the datagridview
        }

On form load you just need to create an object of the MySqlConnection class and then call the method that we created earlier. This will load the data into the datagridview when the form loads.

con = new MySqlConnection(con_string); //initialize mysqlconnection
load_data(); //load data into datagridview

The refresh button just calls the load_data() method. This effectively updates the data shown in the datagridview.

 

Creating a new row

For creating a new row were also going to use the database class that we created earlier. I just had a little difficulty on passing the data from the dataset into the datagridview that’s why I showed you the evil lazy way of doing things. Having your connection strings and database operations all over the place is not a good practice, always remember that.

Here’s a screenshot of the form for creating new rows. We just have to call the create_row() method when the user clicks on the save button.

image

Here’s the code for the save button:

//housekeeping

string fname = str_cleaner(textBox1.Text);
string mname = str_cleaner(textBox2.Text);
string lname = str_cleaner(textBox3.Text);

 

db_class.create_row(fname, mname, lname); //insert data
cleaner(); //clear textboxes
this.Close(); //close the form

 

For the str_cleaner() method I just used some built in methods for processing a string in c#. I combined the ToUpper() and the Trim() method. This method returns the trimmed UPPERCASE version of the string.

public string str_cleaner(string raw_str) {
    return raw_str.ToUpper().Trim(); //remove whitespaces, all caps
}

You probably know what the cleaner() method body is but here it is anyway:

public void cleaner() {
    textBox1.Clear();
    textBox2.Clear();
    textBox3.Clear();
}

I’m interested to know if there is a better way of clearing multiple textboxes in a single execution. Please comment if you have a way to do this on one line of code.

 

Cell Click

Before we proceed with the methods for updating and deleting the records you first need to know how to track the currently selected record from the datagridview. Remember that earlier we created a global variable called user id, I made it global so that the methods for updating and deleting records will also be able to access its value. The user_id variable will store the unique user id which we will get once the user clicks on any row for the user id.
I enclosed it on a try catch statement so that the program won’t die on us once we try to convert the corresponding cell value into a string. As you can see from the code below I used Convert.ToInt32() to convert the user id from a string to an integer. If the user clicks on any of the rows under the names fields, we will get an error for trying to convert that string to an integer. Because common sense tells us that “Name” has no integer equivalent. Only “5” or “8” or any number being treated as a string can be converted to an integer.

 String current_cell = dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].FormattedValue.ToString();
    try
    {
       int int_val = Convert.ToInt32(current_cell); 
       if (int_val >= 0) //checks if value is a valid integer
        {
          current_id = int_val; //assigns the user id from datagrid view into the current id
         }
     }
     catch (Exception es) { //this pops out if user clicks on a name
         MessageBox.Show("You can only click on the id if you want to update or delete the current record!");
     }

 

Deleting a row

Now that we have the current user id we can just use it on our query. Yup I got lazy again so please bear with me. In the code below its mandatory that the user has already selected something from the datagridview. To implement this I just checked if the current_id is equal to 0 since row count in any database starts with 1 and we initially gave the current_id a value of 0 so if its still 0 then it means that the user hasn’t selected anything yet. On the other hand if the current_id is not 0 then we just output a confirmation message box, if the user clicks on yes then the delete query executes and then we reload the data on the datagridview this effectively removes any traces of the deleted data.

   if (current_id == 0)
            {
                MessageBox.Show("Please select a record before clicking on delete");
            }
            else {
                if (MessageBox.Show("Are you sure you want to delete ' "+ current_id +"'", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    MySqlConnection con = new MySqlConnection(con_string );
                    MySqlCommand command = con.CreateCommand();

                    command.CommandText = "DELETE FROM users WHERE id='" + current_id + "'";
                    con.Open(); 
                    command.ExecuteNonQuery();
                    con.Close();
                    load_data();
                }
            }

 

Updating a row

Updating a row is a 2-step process, first we call up the form for updating a single row then we perform the update query from that form. As you might expect this is what the update form will look like.

image

Here’s the code for calling up the update form if the current_id is not equal to 0.

if (current_id == 0)
  {
    MessageBox.Show("Please select an id before clicking edit!");
  }
  else {
    Form3 update_form = new Form3(current_id );
    update_form.Show();
  }

If you have a good eyesight you might have noticed that I used the current_id as an argument when I created an object of Form3. And for that you need to do a little object-oriented trick on the form for updating a row.

First you need to declare a private variable and call it user_id.

private int user_id;

On the constructor for the update form you just supply a parameter called user id. Then assign this to the private variable user_id. What were doing here is simply passing the current_id(form for datagridview) to the user_id(form for update). I don’t know if that’s clear so you can comment if that wasn’t clear to you. I’m glad to answer comments.

public Form3(int user_id)
{
   InitializeComponent();
   this.user_id = user_id;
}

So once the update form loads, you just use the user_id passed to this form and convert it to string so that it can be outputted on a textbox.
I suppose there are no longer problems here since we already created the read_row() method earlier in this tutorial. To refresh your mind the read_row() method is a method which takes up a single argument which is the user id. It then uses it as a value for the select query. If a result is returned the firstname, middlename, and lastname are assigned into a different index in the name array. The name array is then returned by the method which we access from this form.

 string str_user_id = user_id.ToString();
 textBox1.Text = str_user_id;
 textBox4.Focus(); //set focus to firstname
 string[] name = db_class.read_row(str_user_id ); 
 textBox4.Text = name[0];
 textBox2.Text = name[1];
 textBox3.Text = name[2];

Finally, the code for updating.

string fname = textBox4.Text;
string mname =  textBox2 .Text;
string lname = textBox3 .Text;
db_class.update_row(fname, mname, lname, user_id.ToString ());
cleaner(); //empty the textboxes
this.Close(); //close the form

How to create a raffle program

In this tutorial I’m going to show you how to create a raffle program which will randomly select records from a mysql database.

 

Requirements

  • jQuery
  • Wampserver

 

Initial Script

First, create a new file and call it raffle.php. This is where we will put the codes for randomly selecting records using javascript.
Declare an array which will temporarily store the names of the participants and winners. Then initialize the winner_index to 0. If you have already spent some time in programming, you know that winner_index is a way to keep track how many winners were already selected on the current session. This is also a way of making sure that we store each of the winners on a different index in the array.

var participants = [];
var winners = [];
var winner_index = 0;

Once the document has been fully loaded, we initialize the total number of names to be displayed to 30. Then we create an event handler once btn_random is clicked by the user. We haven’t created the btn_random yet since where still on the script so hold your horses and keep reading.
Clicking the btn_random button will cause a post request to the server and this is through the $.post() function in jQuery. It’s a convenience function primarily used to post or load data from the server. In this case, were loading the data from loader.php a script which we will create later. But what it does is select random records from the database and then returns a JSON string which is then used by our raffle program. As you can see in the code, we used $.parseJSON() function before assigning the data to the array of participants. This is used to convert the JSON String into a Javascript Object. This is to prevent the data from being treated as a string. Finally, we execute the RunRaffle() function which takes up 2 arguments: the total number of names to raffle and then the array of names to raffle.

$(function(){
	var total_count = 30;	
	$('#btn_random').click(function(){
		$.post('loader.php', function(data){
		participants = $.parseJSON(data);
			
		RunRaffle(total_count, participants);
	                 });
	});				
});

 

Loading the data

I guess its only proper to first show you how to load the names that were going to raffle from the database before I go through the meat of the program.

Create a new file and name it loader.php. Include your database configuration. Then set the character set to UTF8, this makes sure that we don’t get any errors when dealing with names which are made up of characters which are not very friendly when you retrieve them from the database.
Nex, we select the records randomly from the database and that is by using the RAND() function in mysql. If you’re interested with the technicalities just visit the link. But basically the RAND() function is used to randomize the results which are being returned from a query but its only effective if there are a hundred records or more. Its almost always certain that this function will select the same records over and over again but only in a different order. If say you only have 30-100 records. And note that RAND() function is actually a very expensive operation when it comes to performance so don’t use it on production. But its perfectly fine if you use it on just raffle programs or similar operations.

<?php

require_once('seminar_config.php'); $db->query("SET CHARACTER SET utf8"); $select_random = $db->get_results("SELECT participant FROM tbl_participants

WHERE state = 1 OR state = 4 ORDER BY RAND() LIMIT 30");

 

?>

We then loop through the records and assign it to an array. As you can see we don’t actually need to assign an index to the current record since php does this automatically for us, that’s just sweet! After were done looping through all the records we just use the json_encode() function to convert the $data array into its JSON String representation. There’s also a note from the php.net site that this function only works for UTF8 encoded data so setting the character set to UTF8 in mysql is  actually really useful. Yes you can try not setting the character set to UTF8 and the function will still work, but not for all characters. If json_encode() doesn’t understand it, it will probably be set to null and you’ll get some null values on your JSON string.

if(!empty($select_random)){
	foreach($select_random as $k=>$v){
		if($v->participant != null){
			$data[] = $v->participant;
		}
	}
	echo json_encode($data);
}

 

Meat

After loading up the data that we need its now time to do the code for the presentation of the raffle. The visual signal that will let the audience know that this is actually a raffle program. Because you can’t just pull out random names from the database without any visual impact. And were using jQuery for that purpose, all we have to do is to present a specified number of names. In this case we want 30 names to show up on the interface, then we just change the background color of its container in a successive fashion. There are 3 elements which makes this program tick:

  • Math.random() – although we already randomize what were selecting from the database, we also do this on the client side for presentation purposes.
  • .next() – jQuery’s .next() function which is basically used to determine the next element after the current element that is being selected.
  • setTimeout() – a Javascript function which is used for delaying the execution of another function. In the raffle program it is used to achieve the effect of slowly decreasing the speed in which the light is looping through the names while time passes by.

Declare the RunRaffle() function:

function RunRaffle(intCount, people){
   //magic happens here
}

Inside it we first initialize where we are going to append the list of names. In this case were appending it into an element which has the id of raffle. This element is an ordered list. After that, we just empty the current contents of the ordered list. This is to make sure that we don’t populate the whole interface with a bunch of names from the database.  Don’t worry we’ll also be writing all the html later.

var jRaffle = $("#raffle");
jRaffle.empty(); //this clears all the contents of the ordered list

Next, we just fill the ordered list with the names that we have fetched earlier from the database. But before we actually append it into the list we first check whether its null and that its not an existing winner. This is some sort of an assurance that we don’t put the names of the winners on the list of names to raffle in case the query failed us.

for (var intI = 0 ; intI <= intCount ; intI++){
			
if(people[intI] != null && check_existing(winners, people[intI]) == false){
	jRaffle.append("<li><div class='grey'>" + people[intI] + "</div></li>");
}
}

Next, we change the background color of the first name in our list.  Then Initialize the pause time, the pause time is the amount of time that we are going to run the program. Finally, the intDelay is the amount of time used to jump from one name to another.

var jCurrentLI = jRaffle.find("li:first").addClass("on");  //find the first name that was appended on our list
var intPause = 40; //set initial value of pause time
var intDelay = (4500 + (Math.random() * 2000)); //get time to wait before chaining pause time

Next, create an anonymous function and assign it to a variable called Ticker.

var Ticker = function(){

}

Inside it we define the next name to be selected, if we don’t have anything else on the list we go back to the first name on our list.

var jNextLI = jCurrentLI.next("li"); //next name to highlight
if (!jNextLI.length){ //check whether jNextLI has something in it     jNextLI = jRaffle.find("li:first"); //go back to the first name
}

Then we remove the highlight from the current name and add the highlight to the next name.

jCurrentLI.removeClass("on"); //remove highlight from current
jjNextLI.addClass("on"); //add highlight to next

Then check  whether to make the chaining slower. Remember that the higher the number of the variable intPause, the slower the movement from the current name to the next name will execute. This means that the intPause variable effectively determines the next winner after we reach a certain value.

jCurrentLI = jNextLI; //for the next iteration
if (intDelay > 0){
	intDelay -= intPause; //intDelay = intDelay – intPause
} else {
	intPause *= (1 + (Math.random() * .1)); //intPause = intPause * random value
}

Finally, when the intPause variable reaches a value of 400 or above we stop the current session of iterations. What we do here is to append the current winner to the list of winners and that is if the current name isn’t already stored in the array of winners, for this we used the check_existing() function which we will create later. We also submitted the name of the current winner to winners.php which basically just saves the name of the current winner into our database and changes its state so that it won’t be selected again. If for some reason a name is selected twice, we just clear out the container which stores the name of the current winner and we don’t add anything to the list.

if (intPause >= 400){
	iif(check_existing(winners, jCurrentLI.text()) == false){
		$('#winners').append('<li>' + jCurrentLI.text() + '</li>'); //append current winner to list of winners
		winners[winner_index] = $.trim(jCurrentLI.text()); //housekeeping  $.post('winners.php', {'winner' : $.trim(jCurrentLI.text())}); //save winner to database
		$('#divider').html(jCurrentLI.text()); //current winner
		
		winner_index++; //add 1 to the index
					
	}else{
		$('#divider').html(''); //clear the container for current winner
	}
				
} else { //while intPause is not >=400 increase delay for Ticker
	setTimeout(Ticker, intPause); //set delay for the ticker to be called
}

Then we just call the anonymous function through the variable Ticker. Note that were calling it just like a normal function.

Ticker();

 

Check Existing

For the check_existing() function it returns true if it found the name of the current winner in the array and false if it didn’t find it. It takes up two arguments, the winners array and the selected name.

function check_existing(arr,obj){
	 return (arr.indexOf(obj) != -1);
}

 

Commit Selection

The last thing that we have to do is to commit the selected winners to our database. Just change the state of whoever gets selected into something that’s not specified on the select statement.

$winner = mysql_real_escape_string($_POST['winner']); 

$db->query("UPDATE tbl_participants SET state=4 WHERE participant='$winner'");
$db->query("INSERT INTO tbl_winners SET winner='$winner'");

Yup I know what I said above might be confusing so here’s an explanation. For example on your query for selecting records you only select those whose state is 1:

SELECT * FROM tbl_participants WHERE state=1 ORDER BY RAND();

All you have to do in your update is to set a different state:

UPDATE tbl_participants SET state=4 WHERE participant=’something’

Then finally insert the name of the winner into another table. That is if you want to keep track of those who are selected. But you can probably just select all records with the state of 4 if you don’t want to do this extra step.

 

Markup

I explicitly delayed the html for the last part since this is the most boring part.

<div id="header">
	<img src="images/raffle_header.png" />
</div>
<div id="container">
<input type="button" id="btn_random" value="Select"> <!-execution->
<ol id="raffle"> <!-for viewing the names to iterate from–>
	
</ol>
</div>

<div id="bottom">
<div id="current">

	<p>Current:</p>
		<div id="divider"> <!—for viewing the current ->
		
		</div>

</div>
<div id="list">

<p>Winners:</p>
	<div id="winners"> <!—for viewing the list of winners->

	</div>
</div>
</div>

 

Sources

http://www.bennadel.com/resources/presentations/jquery/raffle.htm

How to convert html tables to sql files

I know why you’re here, you want to convert a table such as the one below into full-fledged database table which can be manipulated using queries.

image

The image above is from w3schools of course.

Requirements

  • phpmyadmin / heidisql
  • Microsoft Excel or any spreadsheet application

 

Procedure

Converting an html table into an sql file can be done in 3 steps. First you need to have a source. Where are you getting the data from? In this case we are getting it from the fruits tables at w3schools. Highlight the whole table and paste it on Microsoft Excel, just right-click on the first cell of the sheet and select paste(Match Destination Formatting). After that, go ahead and save the file as an excel 2003 file(xls) or excel 2007 file(xlsx). Finally you just have to import the excel file using phpmyadmin or heidisql. Once you’re done you’ll end up with this.

image

Just remember that the name of the table depends on the name of the sheet. Sheet1 is the default so you have to rename the sheet if you’re planning to import multiple excel files on a single database. But you can just import it all at once, just use a different sheet for a different table structure.

CodeIgniter Beginner Tutorial 3– Playing with the Database

In this tutorial were going to play around some of the functions that are available in CodeIgniter that will allow us to manipulate the database ninja-style. Remember that you can always use the awesome codeIgniter user guide if you want to take a look at what other functions are available since I’m not going to tackle all of it in this tutorial. What I’m going to show is how to use some of those functions by implementing MVC.

And remember to check out the locally available user guide, it will be very helpful if you’re doing most of these offline. Which I also prefer since being connected to the internet tempts you to ask Google for everything.

 

Create the Model

First you need to create the Model which we will be working with. Nothing new here we just declared a new class for the mods model. If you don’t know what I’m doing then check out the first two tutorials on this codeIgniter beginner series.

class mods extends ci_Model{

}

Inserting Records

Inside the mods model create a function and call it insert_records(). In this function we specify a single parameter called $name. This argument for this parameter is going to be supplied in the controller in which we will load the mods model later on.

function insert_records($name){ $sql = "INSERT INTO testing SET name=?"; //query string //executes the query

//first argument->query string

//second argument, third argument,etc..->data bindings

$this->db->query($sql, $name); }

In this function were using something called the Query Bindings, its basically a safer way to execute queries on the database since the values which you supply to it are automatically escaped. This means that any malicious values supplied to queries won’t affect our database.

Next create a new controller and call it mods_controller.

class mods_controller extends ci_Controller{


}

Inside the mods_controller class create a function and call it inserts(). Inside the inserts() function we load the mods class. After the mods class has been loaded we can readily access its methods through the name of the class. And the method that were calling here is the insert_records() method, we just supplied a dummy name called blossom so that it will actually insert something on our table.

function inserts(){
	$this->load->model('mods'); //load the mods model class
	$this->mods->insert_records('blossom'); //call the insert_records() method
}

 

Updating Records

Updating records is pretty much the same with the inserting of records, but this time you’ll going to need a minimum of 2 arguments for your function. On the mods class create a new function called update_records, then supply 2 paramaters $name and $id. As usual we declare the sql query as a string. The only difference between the insert that we did earlier is that the second argument that you’re going to supply is actually an array composed of the values which will be supplied on the mods_controller controller.

function update_records($name, $id){
	$sql = "UPDATE testing SET name=? WHERE id=?";
	$this->db->query($sql, array($name, $id));
}

On the mods_controller all you have to do is to load up the mods model and call up the update_records() function. Remember to supply the values $id and $name. Supply a record which actually exist so that you can see if its really updating.

function updates(){
	$this->load->model('mods');
	$this->mods->update_records('jade', 2);
}

 

Deleting Records

Deleting records is still the same with inserting and updating records in the sense that were not using views for all of them. Though we can actually use views if we want to display the status of the query or something. Nothing new here, we just changed the sql statement to a delete query.

function delete_records($id){
	$sql = "DELETE FROM testing WHERE id=?";
	$this->db->query($sql, $id);
}

There’s also nothing new if we try to call the delete_records() function from the mods class. In here were trying to delete record with the id of 4 from our testing table.

function deletes(){
	$this->load->model('mods');
	$this->mods->delete_records(4);
}

 

Single Row Fetching

This time were going to use the Active Record Class to fetch a single row from our table.  First you have to create another function on the mods class and call it ar_getwhere() short for active record get where. The get_where() method accepts two arguments the first one is the name of the table, and the second is an array which stores the table field and value pair. In this case our table field is ‘id’ and the value is the argument which we will supply when we call the ar_getwhere() method.

function ar_getwhere($id){
	$where = array('id'=>$id);
	$query = $this->db->get_where('testing', $where);
	$row = $query->row();
		
	return $row->name;
}

If you want to include more fields in the where clause you can do so by adding more items on the $where array. In the example below we are including both the id field and the name field in our where clause.

$where = array('id'=>$id, 'name'=>$name);

This simply translates to the following query.

SELECT * FROM testing WHERE id='$id' AND name='$name'

Next we just have to call the function from our controller inside the mods_controller class.

function getname(){ $this->load->model('mods'); //load the mods class $row['name'] = $this->mods->ar_getwhere(1); //select * from testing where id is equal to 1 $this->load->view('mods_view', $row); //load the view in which we will display the name

//use the value returned from the query as the second argument }

And from mods_view we just simply echo out the name of the person with the id of 1.

<?php
echo $name; 
?>

 

Joins

You can just directly perform joins on your queries but the Active Record Class actually allows you to do this by using the join() function which takes 2 arguments, first is the table that you wish to join and the second is the common key between the table your selecting from and the table that you wish to join. Remember that you can use the join() function as many times as you would like but this ultimately depends on how many tables you wish to join together in a single query. After joining we just call the get() function. I can only guess what this does because I haven’t actually read about it. Maybe the get() function just puts the query and joins together in a single query. We then check if the number of rows returned is greater than 0. We won’t actually do anything if it returned 0 results. Next we just loop through the results and assign the rows returned to an array key.

function joins(){
	$this->db->select('name, clubname');
	$this->db->from('testing');
	$this->db->join('clubs', 'testing.id = clubs.person_id');
	$this->db->join('clubnames', 'clubs.club_id = clubnames.club_id');
	$query = $this->db->get();
	if($query->num_rows() > 0){
		foreach($query->result() as $v){
			$data['name'][] = $v->name;
			$data['clubname'][] = $v->clubname;
		}
	return $data;
	}
}

On the controller side of things we do what we have always done: load model, call function. The only thing that’s new in here is that we actually load a view called mods_view.

function joins_controller(){
	$this->load->model('mods');
	$data['info'] = $this->mods->joins();
	$this->load->view('mods_view', $data);
}

In the mods_view file we just loop through all the data stored in the $data array.

<table border="1"> <tr>   

<th>Student</th>   

<th>Club</th> </tr> <?php foreach($info['name'] as $k=>$v){ ?> <tr>   

<td><?php echo $v; ?></td>   

<td><?php echo $info['clubname'][$k]; ?></td> </tr> <?php } ?> </table>

 

Conclusion

That’s it for this tutorial. You’ve learned how to perform CRUD operations on your database using CodeIgniter. I hope you’re already getting comfortable on using the CodeIgniter user guide. It’s the ultimate reference believe me.

CodeIgniter Beginner Tutorial 2–Introduction to MVC

In this tutorial I’m going to show you the basics of implementing MVC using CodeIgniter. MVC stands for Model View Controller and its commonly used to separate logic from presentation. The Model handles the database manipulation, the Controller handles the logic and the View handles the presentation. That’s all I can share to you since I’m not an expert in MVC.

 

Controllers

Let’s start playing with the controller. The controller is the guy which we will be call from the url and that is through the class name and functions. If that didn’t make sense, don’t worry we’ll work through an example so that you’ll understand things better. First you have to create a new php file, name it whatever you like and save it on the controllers folder which can be found in the following path.

 codeigniter/application/controllers

Then create a new class inside the php file that you have just created, just make sure that the filename and the class name is the same. For example if you have named your file x.php your class name should be x. And that class should extend the code igniter controller.

<?php
class x extends ci_Controller{


}
?>

Next create a function called index(), this function is the default function that’s going to be called if you access the x controller.

function index(){
	$data['name'] = 'Yoh Asakura';
	$data['course'] = 'BSIT';
	$this->load->view('viewdata', $data); //load viewdata
}

What we just did was to pass an array into the view called viewdata. This simply means that whenever we try to access the x controller, the viewdata page is being loaded.

 

Views

We still haven’t created the viewdata file so create a new file called viewdata or whatever you like just make sure to match it with the name of the view that you are loading in your index() function. So that if you are loading the view called viewdata, then you must also name your file viewdata.php. Be sure to save your file in the views folder. Here’s the path.

 codeigniter/application/views

Inside the viewdata.php file check if the name and course is actually set. If its set then we output both of them.

if(isset($name) && isset($course)){
	echo $name.'<br/>'.$course;
}

And that is the beauty of MVC, we are actually making our code more organized by separating the logic and the presentation through the use of controllers and views.

Back to our x controller. Declare another function and call it whatever you like. I’ll call mine zup(). This function simply echoes out What’s up world.

function zup(){
	echo "What's up world!";
}

You can access it by supplying the function name after the name of the controller. Something like the one below if your controllers class name is earth and your function name is zup().

codeigniter/index.php/earth/zup

 

Models

As I have said earlier models do the talking with the database, it is where you will perform the queries, it is where you will assign the results to arrays. Okay I guess I’m not making any sense again so here’s an example.

First create a new php file and save it inside the models folder.

 codeigniter/application/models

Then create a class called mods and make it extend the code igniter model class. In simple terms extending the model class is a way to make an identification that this file is actually a model. Well aside from the fact that you saved it inside the models folder.

Next, create a method called get_records() which we will be using to fetch the records from the testing table. In this example I used the active record class, a built in class in codeIgniter which makes it easier to insert, update and fetch records from a database.

function get_records(){
	$db_query = $this->db->get('testing'); //select all records from testing table
	if($db_query->num_rows > 0){ //check if the query returned anything
		foreach($db_query->result() as $v){ //loop through the results that was returned
			$records[] = $v->name; //create a new array item and assign it to the name field
		}
			
	}
	return $records;//return the values accumulated by the $records array
}

Here’s the sql query for creating the testing table. Just add some sample data after you have imported it to the database that you have specified to be used by CodeIgniter.

CREATE TABLE IF NOT EXISTS `testing` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Next, create the controller which will utilize the model that we have just created. Name it mods_controller and extend the Controller class just like what we did on the first example.

class mods_controller extends ci_Controller{


}

Inside the class, declare the index() function as I have said earlier this function is the default function that is being loaded once you access the class where it was declared in this case the mods_controller class.

function index(){
	$this->load->model('mods'); //load the mods model
	$data['names'] = $this->mods->get_records(); //call the get_records() function and assign its returned value to an array
	$this->load->view('mods_view', $data); //load the view and supply the data that was returned from the model
}

Finally, create the view in which we will display the records that were returned. Name it mods_view.php. Simply print the whole array for now.

<?php
print_r($names);
?>

 

Conclusion

That’s it for the second tutorial in this CodeIgniter beginner series. You have learned how to use Models, Views, and Controllers in CodeIgniter.

CodeIgniter Beginner Tutorial 1–Configuring CodeIgniter

In this tutorial I’m going to show you how to configure codeIgniter.


Requirements

 

Resources

Here are some of the links that you might want to check out in the process of learning how to do things in CodeIgniter.

  • Nettuts – Everything you need to know to get started with CodeIgniter – this is a pretty good tutorial. Though its already getting old, most of the concepts, methods and configurations that you need to do is still the same with the current version of CodeIgniter(v2.1.0)
  • CodeIgniter User Guide – A very good reference for the different methods and helper classes available in CodeIgniter. This is a very valuable resource once you know basics of CodeIgniter.
  • Nettuts CodeIgniter From Scratch – A series of screencast which discusses how to work with CodeIgniter. I recommend it if you’re not low on bandwidth.
  • Stackoverflow – If you get stuck in your quest on learning CodeIgniter you can just search for your problem on Stackoverflow. There’s a big probability that you’re problem has already been answered before.

 

Base URL

First you have to download CodeIgniter then unzip it on your web folder. If you’re using Wampserver its on c:\wamp\www if you haven’t configured anything when you installed Wampserver. If you’re using XAMPP your web folder is somewhere in c:\xampp\htdocs. But don’t take my word for it, I’m not a XAMPP user so you might as well do your research.

After unzipping you’ll have a folder structure similar to this.

image

Next open up the application folder and then the config folder.

application > config

Inside the config folder you will see the config.php file, open it up and set the base url. The base url is the root location that you will be working on. It looks similar to this.

$config['base_url']	= 'http://localhost:8080/tester/codeigniter/index.php/';

 

Database Configuration

Still on the same folder open up the database.php file. This is where you will set what database codeIgniter is going to use. Here are the stuff that you’re going to edit.

$db['default']['hostname'] = 'localhost';

$db['default']['username'] = 'root';

$db['default']['password'] = '';

$db['default']['database'] = 'database_name';

$db['default']['dbdriver'] = 'mysql'; //optional, you can use mysqli if you want

If you don’t know what you’re going to set just use localhost for the hostname, root for the username and an empty password. The database is up to you to fill out, just create a database for testing purposes if you’re just starting out with codeIgniter. You can create it in phpmyadmin or the mysql console.

 

Autoload

Next you need to specify the libraries, packages, helper files, custom configuration files, language files and models that you want to autoload in your application. This means that anything that you autoload in your application doesn’t need to be called every time you need to use it. To edit the libraries, helper files, and models that you want to autoload in your application just open up the autoload.php file which is in the application/config folder.
Below is an example where I included the database library in the libraries that will be autoloaded in the application. To do that just add database as an item to the libraries array.

$autoload['libraries'] = array('database');

There are other libraries available in codeIgniter which you can find on codeigniter/system/libraries folder. You can also add them to the libraries array but be sure you know how to use them and make sure not to load more than what you need.

image

You might also want to autoload some of the helpers that you might use later on. The url helper and the form helper. Helpers are used to make your life easier, if you translate it into coding this means that you get to write less code and do more.

$autoload['helper'] = array('url', 'form');

 

Default Controller

This is actually an optional configuration so you can just skip it if you want. But if you want to setup a default controller then read along.
The default controller is the controller that’s going to be loaded when you access the root of your application. In this case the root of the application points to the address below.

http://localhost:8080/tester/code_igniter/codeigniter/

To setup the default controller open up the routes.php file, still under the application/config folder and find the line similar to the following.

$route['default_controller'] = "site";

The default controller on the example above is the site controller, the site controller must be on the application/controllers folder otherwise this will fail. If you have a different name for your default controller, for example your default controller is index.php then change the default controller to index.

 

I guess that’s all you need to configure for now. Be sure to check out the resources that I’ve mentioned earlier.