Updating mysql from php page

cracker

Hi

I have a php page which is displaying a number of rows from a mysql database. I want to add the functionality to update the values and save them by clicking a save button.
My question is what is the preferred way of doing this. Should I use javascript from an onclick event or call a php function to do the update?

I am only starting with PHP so I am not sure how to do the latter. Do I have to create a form and call the php function from the action event? I think this would mean that the whole form would be resubmitted where as if I use javascript it could be done asynchronously?

Thanks

Dave

lil_lisa

I did this for my project, to update it I used the following code, you can change it to what you want:

have the form in HTML sending to a page with the below on it, you know how to pass variables?

[PHP]$query = "UPDATE table SET field = $variable1 where field2 = $variable2;"; //variables are passed through POST on your form
$result = mysqli_query($g_dbc, $query); //the $g_dbc is your connection
[/PHP]

AARRRGH

Use a PHP function.

1. Display a HTML form where the data can be updated
2. User updates the data and clicks submit
3. Your next page catches the data, e.g. $_POST
4. [Important] You check the data to strip out any dodgy business, such as bits of javascript
5. You then do something like lil_lisa said

dmeehan

i would go with php when doing anything with mysql. not sure on the integration between javascript and mysql

lil_lisa

You'd really only use js if you're not moving pages or not using a submit button. To keep on the same page you can have the action of the form posting to the same page

Webmonkey

Javascript alone will not allow you to to perform what you need here.
Like AARRRGH says, use PHP.

However, if you are feeling adventurous you can use AJAX to call a PHP page in the background with the new values. It isn't all that complicated.

Do it with regular form methods first and then if you want to give it ago, try a little AJAX.

cracker

Thanks, bear with me. I only started doing PHP last week and web programming is also still quite new to me. It is a very different animal to client side and database programming.

I can see that if I use a submit button on the form I can have a php file as the action.

echo "<form action=\"db_updates.php\" method=\"POST\">";

Then in the db_updates.php I can access the variables and update the database. But does that mean I have to have a separate file for every action in the website. Is this standard practice or can I call a function in a file, I can't find an example of this.

My instinct from other languages is to use objects to store the information on the page and pass them to an update method somewhere. Is this possible if I go down the AJAX route?

Webmonkey

cracker wrote: »

Thanks, bear with me. I only started doing PHP last week and web programming is also still quite new to me. It is a very different animal to client side and database programming.

I can see that if I use a submit button on the form I can have a php file as the action.

echo "<form action=\"db_updates.php\" method=\"POST\">";

Then in the db_updates.php I can access the variables and update the database. But does that mean I have to have a separate file for every action in the website. Is this standard practice or can I call a function in a file, I can't find an example of this.

My instinct from other languages is to use objects to store the information on the page and pass them to an update method somewhere. Is this possible if I go down the AJAX route?

You can have a single file but you can pass a hidden field or pass it as a get parameter, ie set the action of the post to "db_update.php?update=customers", then in your db_update.php you could have:
[php]
if (isset($_POST))
{
$update = $_POST;
switch ($update)
{
case 'customers':
addCustomers();
break;
case 'suppliers':
addSuppliers();
break
}

function addCustomers()
{
if (isset($_POST))
$customerName = $_POST;
else
{
echo 'Error';
return;
}

$query = "INSERT INTO `customers` (`name`) VALUES ('$customerNmae')";
if (!mysql_query($query)
printf("Error executing query!");
}
[/php]

Only example code, obviously have to do proper validation etc

cracker

Ah, I see. Easy when you know how. Thanks.

I am going through the PHP AJAX tutorial on the netbeans site so I might give that a go yet.

D.

lil_lisa

You don't have to have a separate page, you can have a form posting to itself like so:

[PHP]<form method="post" action="<?php echo $_SERVER;?>">[/PHP]

and then check if the referral page is the current page and get the values, like so:

[PHP]
$refererPage = strtoupper(basename($_SERVER));
if($refererPage == "CURRENTPAGE.PHP"){
$variable = $_POST;[/PHP]

Then take the variables from there. I'd suggest turning the referrer page to uppercase to ensure you get the right page name.

Then you can set the values in your db like:

[PHP]$query = "UPDATE table SET field = ". $variable . ";";
$result = mysqli_query($dbc, $query);[/PHP]

Make sense?

cracker

Perfect sense.

I suppose it depends on whether you need access to the code from several pages. If you do then breaking it out to a separate file would make sense. If not then include it on the same page.

Webmonkey

Generally I'd separate business logic from presentation.

cracker

So put all the database access code (or any other business logic) in php files with no html and then just return data to the presentation page as a query result or some application defined object? So the presentation page would just contain logic for displaying the data and a load of echo statements.

Am I correct that if a page needs to be updated after some action then it is either the AJAX route or else the page needs to be submitted (via post or get) and then completely reloaded from the server. I think from the netbeans tutorial that this is the point of AJAX, it makes the web page more like a traditional thick client GUI.

Webmonkey

Yup AJAX allows you to do asynchronous calls to a PHP page using the javascript xmlHttp object without having to reload the page. You can then update the page content dynamically without a reload using javascript and the DOM model. like: document.getElementById(contentdiv).innerHTML = 'your new HTML for that div';
or document.getElementById(customerField).value = 'NewValue';

You can return the results back from PHP using XML and then use javascript to extract the new content and set the relevent fields.

It's nice and there are lots of tutorials. I've read AJAX and PHP: Building Responsive Web Applications and it is an excellent book. It's lots of fun

This combined with scriptaculous javascript framework for improving the user experience can result in lovely web applications

Of course this is way out of scope for what you want here, but just letting you know that it is available and if you've interest, no harm looking at it.

Book: http://www.amazon.co.uk/Ajax-PHP-Building-Responsive-Applications/dp/1904811825/ref=nosim/cristiandarie-21

http://script.aculo.us/

cracker

As you say it is not really required for what I want since I am not looking to make any changes to the page when the user clicks save on the table.

But say I wanted to make a minor change to the page, e.g. a message to say your data has been saved would AJAX be the preferred way to do this, rather than a whole page refresh with some logic about what to display?

Webmonkey

cracker wrote: »

As you say it is not really required for what I want since I am not looking to make any changes to the page when the user clicks save on the table.

But say I wanted to make a minor change to the page, e.g. a message to say your data has been saved would AJAX be the preferred way to do this, rather than a whole page refresh with some logic about what to display?

You can do a javascript alert to give messages or have status div, that updates the status of what is happening or just display a big Success.

AJAX has states, so state one, you send request. During this state you would place a animated gif and a loading message. Then when you get response. You can say Success/Failure depending on response and update HTML to say this.
Or you can just do a javascript alert message box.

But then again, this is a huge overkill - better off just do a post to it's self, then call your external data base functions from a included php file.