[SOLVED] Generic Mysql Application in PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dream2rule
Forum Contributor
Posts: 109
Joined: Wed Jun 13, 2007 5:07 am

[SOLVED] Generic Mysql Application in PHP

Post by dream2rule »

Hello Friends,

I have been given a task to code for a generic mysql application where in i need to use functions in PHP to insert, update, delete etc (MySQL querying) so that i can use this code as a reusability tool for designing any database in MYSQL.

I just started with it and i got it half way. I create the respective functions and within those i write the associated query to insert, update or delete records. The table name and the field name is being sent to a function via arguments.

I just include the file and call the respective functions where i need to perform the given tasks.

But I am stuck.. as to i am unable to figure out the same when there are multiple field names and multiple data items involved.

Here's my code in which i am using the functions only for a single record. How do i go aout when there are multiple field names and multiple records to be inserted or updated in a database???

Code: Select all

<?php

//function to insert a single value into the database
function insert($table_name,$column_name,$value)
{
	$sql_insert = "INSERT INTO $table_name ($column_name) VALUE ('$value')";
	echo $sql_insert."<br>";
	mysql_query($sql_insert);
}

//function to update a single value into the database
function update($table_name,$column_name_1,$new_value,$column_name_2,$original_value)
{
	$sql_update = "UPDATE $table_name SET $column_name_1='$new_value' WHERE $column_name_2='$original_value'";
	echo $sql_update."<br>";
	mysql_query($sql_update);
}

//function to delete a single value into the database
function delete($table_name,$column_name,$value)
{
	$sql_delete = "DELETE FROM $table_name WHERE $column_name='$value'";
	echo $sql_delete."<br>";
	mysql_query($sql_delete);
}

//function to find the maximum value
function maximum_value($column_name,$table_name)
{
	$sql_max = "SELECT MAX($column_name) FROM $table_name";
	$rs_max = mysql_fetch_row(mysql_query($sql_max));
	echo "Maximum value of $column_name is ".$rs_max[0]."<br>";
}


//function to count the number of rows in a database
function count_rows($table_name)
{
	$sql_count = "SELECT count(*) FROM $table_name";
	$rs_count = mysql_fetch_row(mysql_query($sql_count));
	echo "Total number of rows in $table_name is ".$rs_count[0]."<br>";
}

?>
Last edited by dream2rule on Thu Aug 02, 2007 11:33 pm, edited 1 time in total.
tansoft
Forum Newbie
Posts: 12
Joined: Sun Jul 29, 2007 1:04 am

Post by tansoft »

Send the full prepared query into the function instead of what you are doing ......

write query out side the function like this
$sql_insert = "INSERT INTO $table_name ($column_name) VALUE ('$value')";

and then call the function

insert($slq_insert);

//function to insert a single value into the database
function insert($sql_insert)
{
echo $sql_insert."<br>";
mysql_query($sql_insert);
}

INSTEAD OF THIS

//function to insert a single value into the database
function insert($table_name,$column_name,$value)
{
$sql_insert = "INSERT INTO $table_name ($column_name) VALUE ('$value')";
echo $sql_insert."<br>";
mysql_query($sql_insert);
}

Similarly you can change all other functions also...... :)
dream2rule
Forum Contributor
Posts: 109
Joined: Wed Jun 13, 2007 5:07 am

Post by dream2rule »

No my problem is that i need to pass multiple column names and multiple values (single value to its respective column_name) .

So the query would be something like

Code: Select all

function insert($table_name,$column_name[],$value[])
{
	$sql_insert = "INSERT INTO $table_name ($column_name[]) VALUE ('$value[]')";
	echo $sql_insert."<br>";
	mysql_query($sql_insert);
}
i.e, for a single table which contains multiple fields, i need to insert a specific value in a specific column_name (or field_name)

Any idea of accessing a function with arguments ( again in which the argument is an array) ??

Regards
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

You'll have to use foreach to loop over the keys and values (or similar stuff via explode)...

Anyway, since you're going to use these values in a query, you should prepare them for such use, by calling mysql_real_escape_string on each value...
dream2rule
Forum Contributor
Posts: 109
Joined: Wed Jun 13, 2007 5:07 am

@timvw

Post by dream2rule »

Using foreach statements, i am able to get the values to be inserted into the database but how do i correlate the function and its arguments.

Using Foreach:

Code: Select all

$column_name = array("abcd","abc123#","india");
for($i=0; $i < count($column_name); $i++)
{
	list($user_name,$password,$location) = split(",",$column_name[$i]);
	echo "$user_name "."$password "."$location ";
	//calling the insert function
	insert("table_name","user_name,password,location","$user_name,$password,$location");
}

Generic Insert Function:

Code: Select all

function insert($table_name,$column_name,$value)
{
	$sql_insert = "INSERT INTO $table_name ($column_name) VALUE ('$value')";
	mysql_query($sql_insert);
	echo $sql_insert."<br>";
}
If i take the values (retrieved from the foreach statements) as arguments to the function, there is a mismatch for the arguments.

How should i proceed?? I am really confused with this. :(
Last edited by dream2rule on Wed Aug 01, 2007 6:30 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

Code: Select all

foreach ($columns  as $key => $value)
$key is the name of the field and $value is the value of the field ...
There are 10 types of people in this world, those who understand binary and those who don't
Jello
Forum Newbie
Posts: 16
Joined: Mon Jul 16, 2007 1:53 pm

Post by Jello »

personally I wouldn't give myself a headache re-inventing the wheel. Nip over to phpclasses.org and grab one of their numerous DB abstraction classes...
dream2rule
Forum Contributor
Posts: 109
Joined: Wed Jun 13, 2007 5:07 am

Post by dream2rule »

the classes in phpclasses.org are too confusing atleast for me as i am a newbie.

Can anyone provide me with an easy solution or at least suggest some ways to proceed.

It would be really helpful.

Regards
Jello
Forum Newbie
Posts: 16
Joined: Mon Jul 16, 2007 1:53 pm

Post by Jello »

well if you store your column names and values in an array of key/value pairs you can then use vladsuns foreach loop to get them back out again:

Code: Select all

$data = array("column1"=>"value", "column2"=>"value");
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

The point is that you need to create a query like:

INERT INTO $table ( $column1, $column2, ... ) VALUES ( $value1, $value2, ... )

Here is a possible approach:

Code: Select all

$pairs = new array();
$pairs['column1'] = 'val1';
$pairs['column2'] = 'val2';

$sql1 = "INSERT INTO $table ( ";
$sql2 = ") VALUES ( ";

foreach($pairs as $key => $value)
{
 $sql1 .= "``$key`, ";
 $sql2 .= "'" . mysql_real_escape_string($value) . "', ";
}

....
Completing the code is considered homework ;)
dream2rule
Forum Contributor
Posts: 109
Joined: Wed Jun 13, 2007 5:07 am

Post by dream2rule »

yeah i am trying real hard to complete my homework though :wink: :wink:

but i need to use functions in my code for different mysql queries like a separate generic function to insert etc. So when i call any particular function anywhere in my script, it should be able to perform the particular query.

I am stuck in passing the arguments to the function. I am able to get the individual values to be inserted using foreach but when passing them as function arguments, i am stuck up. :( :(

Generic function code:

Code: Select all

function insert($table_name,$column_name=array(),$value=array())
{
	$sql_insert = "INSERT INTO $table_name ($column_name) VALUES ('$value')";
	mysql_query($sql_insert);
	echo $sql_insert."<br>";
}
The page where the function insert() is being called:

Code: Select all

$data = array("user_name" => "abcd","password" => "abc123#","email_id" => "abcd@yahoo.com","location" => "india");
$count = count($data);
echo $count."<br>";

foreach($data as $key => $value)
{
	echo "$key => $value\n"."<br>"; //this generates all the required elements t be inserted into the database
	insert(generic_application,$key,$value); //This does not work - I am really unable to proceed from here 
}
Can the arguments in a function be defined as an array. I have searched a lot in google but in vain :(

Please help.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Please think about what you're doing... Foreach pair (key, value) you're going to insert a rows... This is clearly NOT what your itention was..



INERT INTO $table ( $column1, $column2, ... ) VALUES ( $value1, $value2, ... )
// One query, appending the keys and values to the same query..
// NOT building multiple queries....
dream2rule
Forum Contributor
Posts: 109
Joined: Wed Jun 13, 2007 5:07 am

Post by dream2rule »

Yes i rectified the mistake. Thanks.

Finally i could find a script that would insert, update records into the database.

Thanks all for the required help.

And Jello, thanks for your help too.. I could find a class in phpclasses.org which meets my requirements. And i struggled hard and customized it even :) :)

Thanks all :) :) :)
User avatar
boo
Forum Commoner
Posts: 42
Joined: Mon Jul 02, 2007 11:30 am
Location: NY

Post by boo »

dream2rule wrote:Yes i rectified the mistake. Thanks.

Finally i could find a script that would insert, update records into the database.

Thanks all for the required help.

And Jello, thanks for your help too.. I could find a class in phpclasses.org which meets my requirements. And i struggled hard and customized it even :) :)

Thanks all :) :) :)
So what did you end up using?
dream2rule
Forum Contributor
Posts: 109
Joined: Wed Jun 13, 2007 5:07 am

Post by dream2rule »

I used a class written by Genilhu

Here's the link for that: http://www.phpclasses.org/browse/package/1551.html
Post Reply