best method for doing mysql update

Codes here !

Moderators: macek, egami, gesf

guitarizmo
New php-forum User
New php-forum User
Posts: 5
Joined: Sun Feb 19, 2012 9:16 pm

best method for doing mysql update

Postby guitarizmo » Mon Feb 20, 2012 8:35 pm

I have 3 pages. Basically the first (menu1) page pulls columns from my mysql menu table and creates them as hyperlinks for selection by user. When selected, it sends the table key to my second page (menu2), I run a query on this data and populate some text boxes with existing database information. I want the user to able to make modifications to menu data, which I want to then update that row of data in SQL. The first page works fine and the mysql select query runs and pulls the correct data. The update query also works fine when I plug in values manually. I need to know how to send the modified text on the second page, which represent changes to the restaurant menu, to the update query. I'm struggling to share PHP variables with HTML forms.

From the first form, I send the table key menu_id. I do a $Get on this and use it for my select query. I will also need this value to not change, so I can use it in my update query. The other 4 fields, menu_item, item_description, item_price and item_category. get changed or remain the same based on user selected manipulation. Can someone please help me understand, best method.

User avatar
egami
php-forum GURU
php-forum GURU
Posts: 2196
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Re: best method for doing mysql update

Postby egami » Tue Feb 21, 2012 5:24 am

You should probably look into using SESSIONS if you want variables to go from one script to another.

solarblitz
New php-forum User
New php-forum User
Posts: 1
Joined: Fri May 04, 2012 11:31 am

Re: best method for doing mysql update

Postby solarblitz » Fri May 04, 2012 11:56 am

Hey I'm relatively new to php and mysql, and teaching myself.

I have the following scripts:- The main Form page

<?php
session_start();
// Must have first line of page
?>
<!DOCTYPE HTML>
<html>
<?php include('../includes/mysqlconnection.php');
$becompany = $_SESSION['becompany'];
$customerid = $_SESSION['customerid'];
$data = mysql_query ("SELECT * FROM customer WHERE customerid ='$customerid'");
//$page = mysql_query("SELECT * FROM pages WHERE siteid = $siteid");
$info = mysql_fetch_array( $data ); ?>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<?php echo '<title> Welcome to '.$becompany.'s - ************ C.M.S.</title>';?>

<meta name="description" content="CMS for ********.co.uk - Web Design Firm"/>
<meta name="author" content="**********" />
<meta charset="UTF-8" />
<link rel="stylesheet" href="../css/site.css" type="text/css">
<link rel="shortcut icon" href="images/favicon.ico" >
</head>
<body>

<div id="wrap">
<header id="page_header">
<nav></nav>
</header>
<div id="sectop"></div>
<section id="controlpanel">
<p>Below is your Customer Details - Some of these you can change and some you will need to send an email to request a change. </p>
<?php echo $info['name'];?>
<table>
<form method="post" action="">
<input type="hidden" name="id" value="<? echo "$info[$customerid]"?>">
<tr>
<td>Name</td>
<td>
<input type="text" name="name"
size="20" value="<? echo "$info[name]"?>">
</td>
</tr>
<tr>
<td>Address</td>
<td>
<input type="text" name="address1" size="40"
value="<? echo "$info[address1]"?>">
</td>
</tr>
<tr>
<td></td>
<td>
<input type="text" name="address2" size="40"
value="<? echo "$info[address2]"?>">
</td>
</tr>
<tr>
<td></td>
<td>
<input type="text" name="address3" size="40"
value="<? echo "$info[address3]"?>">
</td>
</tr>
<tr>
<td>Town</td>
<td>
<input type="text" name="town" size="40"
value="<? echo "$info[town]"?>">
</td>
</tr>
<tr>
<td>County</td>
<td>
<input type="text" name="county" size="40"
value="<? echo "$info[county]"?>">
</td>
</tr>
<tr>
<td>Postcode</td>
<td>
<input type="text" name="postcode" size="40"
value="<? echo "$info[postcode]"?>">
</td>
</tr>
<tr>
<td>Telephone</td>
<td>
<input type="text" name="telephone" size="40"
value="<? echo "$info[telephone]"?>">
</td>
</tr>
<tr>
<td>Mobile</td>
<td>
<input type="text" name="mobile" size="40"
value="<? echo "$info[mobile]"?>">
</td>
</tr>
<tr>
<td>Longitude</td>
<td>
<input type="text" name="longitude" size="40"
value="<? echo "$info[longitude]"?>">
</td>
</tr>
<tr>
<td>Latitude</td>
<td>
<input type="text" name="latitude" size="40"
value="<? echo "$info[latitude]"?>">
</td>
</tr>
<tr>
<td>Email (Default)</td>
<td>
<input type="text" name="email1" size="40"
value="<? echo "$info[email1]"?>">
</td>
</tr>
<tr>
<td>Email (Other)</td>
<td>
<input type="text" name="email2" size="40"
value="<? echo "$info[email2]"?>">
</td>
</tr>
<tr>
<td align="right">
<input type="submit"
name="submit value" value="Submit Changes">
</td>
</tr>
</form>
</table>
</section>
<div id="secbottom"></div></div>
<?php
echo $becompany."<br>";
echo $customerid; // name class and mark will be printed with one line break
if(isset($_POST['edit']))
{
$name=addslashes($_POST['name']);
$address1=addslashes($_POST['address1']);
$address2=addslashes($_POST['address2']);
$address3=addslashes($_POST['address3']);
$town=addslashes($_POST['town']);
$county=addslashes($_POST['county']);
$postcode=addslashes($_POST['postcode']);
$telephone=addslashes($_POST['telephone']);
$mobile=addslashes($_POST['mobile']);
$latitude=addslashes($_POST['latitude']);
$longitude=addslashes($_POST['longitude']);
$email1=addslashes($_POST['email1']);
$email2=addslashes($_POST['email2']);

//$data = "UPDATE 'customer' SET name='$name', address1='$address1',address2='$address2', address3='$address3', town='$town', county='$county', postcode='$postcode', telephone='$telephone', mobile='$mobile', latitude='$latitude', longitude='$longitude', email1='$email1' email2='$email2', WHERE customerid='$customerid'";

// Insert data into data
mysql_query("UPDATE 'customer' SET name='$name', address1='$address1',address2='$address2', address3='$address3', town='$town', county='$county', postcode='$postcode', telephone='$telephone', mobile='$mobile', latitude='$latitude', longitude='$longitude', email1='$email1' email2='$email2', WHERE customerid='$customerid'") or die (mysql_error());

}
?>
</body>
</html>

And the following page validating it -


<?
session_start();
//edit_data.php
include('../includes/mysqlconnection.php');
$becompany = $_SESSION['becompany'];
$customerid = $_SESSION['customerid'];


// Get values from form
if(isset($_POST['edit']))
{
$name=addslashes($_POST['name']);
$address1=addslashes($_POST['address1']);
$address2=addslashes($_POST['address2']);
$address3=addslashes($_POST['address3']);
$town=addslashes($_POST['town']);
$county=addslashes($_POST['county']);
$postcode=addslashes($_POST['postcode']);
$telephone=addslashes($_POST['telephone']);
$mobile=addslashes($_POST['mobile']);
$latitude=addslashes($_POST['latitude']);
$longitude=addslashes($_POST['longitude']);
$email1=addslashes($_POST['email1']);
$email2=addslashes($_POST['email2']);

//$data = "UPDATE 'customer' SET name='$name', address1='$address1',address2='$address2', address3='$address3', town='$town', county='$county', postcode='$postcode', telephone='$telephone', mobile='$mobile', latitude='$latitude', longitude='$longitude', email1='$email1' email2='$email2', WHERE customerid='$customerid'";

// Insert data into data
mysql_query("UPDATE 'customer' SET name='$name', address1='$address1',address2='$address2', address3='$address3', town='$town', county='$county', postcode='$postcode', telephone='$telephone', mobile='$mobile', latitude='$latitude', longitude='$longitude', email1='$email1' email2='$email2', WHERE customerid='$customerid'") or die (mysql_error());

}


//$info = mysql_query($data);
// if successfully updated.
//if($info){
//echo "Successful";
//echo "<BR>";
//echo "<a href='details.php'>View result</a>";
//}

//else {
//echo "ERROR";
//}
header("location:details.php");
?>

As you will see from my note out comments' I have tried various technic's illistrated on various tutorial sites, and have very little success. It just appears to be not updating the database, even though it's pulling the data from it. I'm sure it's a newbie error,missing bracket or quotes, but after 3 hrs of trial and error, I thought I'd ask.

Thanks in advance

Rich

User avatar
freshnet
php-forum Active User
php-forum Active User
Posts: 283
Joined: Tue Feb 22, 2011 8:19 am
Location: Canada

Re: best method for doing mysql update

Postby freshnet » Fri May 04, 2012 12:02 pm

echo back the query to your browser, then try running it and see if it updates correctly. If you're not getting any error the problem is likely in the code you're using for the update.


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 1 guest