Insert/Retrieve to/from MySQL Table in PHP Function

[freshJet]

OK, I ask too many questions...

I have modules in development involving MySQL tables. These often involve adding and getting data from these tables. However, I can never get them to work. I try copying from other modules but still nothing.

Submitting Data

Say I wanted to have a user complete a form and submit it. The data would then be inserted to a MySQL table. How does this work? I am aware I will need a module class file for this as well but I'm not sure on the exact code. What would the code be for the submit button to run the function that inserts the data, and what would the PHP function code look like? Is it as simple as the SQL followed by mysql_query($sql); ? (Where $sql contains the SQL code)

Retrieving Data

As for retrieving data, say I wanted an HTML table displaying the content. Sounds easy, but I can never get it to work (although this is probably because I can't get the insert to work). The way I did this is something along the lines of:

$data = ExampleData::getData();

where the function code is nothing more than SQL, but what is the correct code to go after the SQL? Say I have:

$sql = "SELECT * FROM table_name WHERE id=5";

What is the line following it?

[Tom]

Submitting Data

$sql = "INSERT INTO table (field, names, here) VALUES (1, 2, 3)";
$res = DB::query($sql);

Retrieving Data

$sql = "SELECT * FROM table WHERE id='5'";
return DB::get_results($sql);

Then you'd get the data as per your example and loop through the rows.

[Parkho]

Okay here is how you'll do:

1. create a module for you form.

2. create a class for you module.

3. create a tpl to show the form.

The module is pretty much the heart where you will get the data and pass it on to the class and there the data will be processed and returns the results back to the module. Now the module shows the results in a tpl or like message.

I assume you know how to create a module, so I go to class file,

the sample function to insert data into the table would be something like this:

public function insertdata($data)
{
 $sql = "INSERT INTO phpvms_data (column_name)VALUES($data)";
 DB::query($sql);
}

To get the data from the table you would write something like this:

public function getdata($data)
{
 $sql = "SELECT * FROM phpvms_data WHERE column_name ='$data'";
 return DB::get_results($sql);
}

[freshJet]

And what about the button on the form to call the function? And is $data an array?

[Parkho]

The button is in fact inside a form and basically triggers the form to be submitted, so inside your tpl you'll have it like this:

<form action="<?php echo url('/yourmodule') ;?>" method="post">
<input type="submit" name="submit" value="submit">
</form>

$data is a variable. You would use that the get data for one row of records for example:

$result = YourData::getdata($data);

echo $result->column_name;

[freshJet]

The button is in fact inside a form and basically triggers the form to be submitted, so inside your tpl you'll have it like this:

I know that but I am asking how it is triggered, surely the function name needs to be included somewhere as a hidden field?

[Sava]

You can create an index function(method) with just code for displaying the form, and then make the form's action /module/newmethod

And then do:

Public function newmethod()

{

//do insert code here

}

hope you follow by now.

The second option is to have the submit code in the index function as well but you need to include an if statement to see if the button has been pressed $this->post->submit should be what you are looking for.

Look at my Hub Transfer or LoA modules that are just pure CRUD code, and you'll get an idea.

Sorry for the format. Writing from the iPad.

[Tom]

Or for a more compact solution, within the same function:

if(!empty($this->post->submit)){
   MyClass::SubmitData($this->post->var1, $this->post->var2, $this->post->varn);
   // show success template?
} else {
   // Show form template
}

And then just have your form submit to itself.

[Sava]

The second option is to have the submit code in the index function as well but you need to include an if statement to see if the button has been pressed $this->post->submit should be what you are looking for.

[Tom]

Well I just wrote out an example then

[Sava]

I had those moments when I just needed to make a comment lol

Sorry, haha