Editing MySQL table data records

[JamesCliff]

Hi all,

Im making a page called edit.php to update table records. I have a page called list.php which passes the SaleID for each record through the URL and to the edit.php page.

Now ive done some searching and looking around the internet for help with this, however tutorials ive found are often wrong or incomplete and not working. Others ive found are not suitable for what i need.

My table consists of SaleID, Image_Ref, Title, Description, Date, Price fields. However i will only need to concentrate on Title, Description and Price fields as those are the only fields the user will be able to update, as Date is auto added and Image_Ref is non updatable and editable.

So basiclly how will i be able to create a php script that displays the fields: Title, Description, Price from the selected record using the SaleID passed in the URL, this record data is displayed in a form which is editable to the user. Then at the bottom there is an Update button. I need it all to be done on one php script page, so the select query and update query are all on the same script.

Ive seen examples of it but its alot more confusing as multiple queries are used eg. select and update.

I would be very greatful for some guidance in this.

Thanks alot

Jim

 

[kaancho12]

jim
This is one way to do it:
<?
if ($submit){
$j = 0;
$total_change = $_REQUEST["$total"];
while($j < $total_change){
$title1 = $_REQUEST["title" . $j];
$des1 = $_REQUEST["des" . $j];
$price1 = $_REQUEST["price" . $j];
$primary = $_REQUEST[primary_key" .$j];
$update = "UPDATE table SET Title = '$title1', Description='$des1', Price='$price1' WHERE primary = '$primary'";
$mysql_query($update);
}
?>
<form name="update" action="$PHP_SELF">
<? php
$sql = "select Title, Description,Price FROM table";
$qry = mysql_query($sql);
$i = 0;
while ($fetch=mysql_fetch_array($qry)){
?>
Title:<input type="textbox" name="title<?$i?>">
Description:<input type="textbox" name="des<?$i?>">
Price:<input type="textbox" name="price<?$i?>">
<input type="hidden" name="primary_key<?$i?>">
<?
$i++;
}
?>
<input type="hidden" name="total" value="<?$i?>">
<input type="submit" name="update">
</form>

ko12,

You need to pass the primary key field along with the form when it is submitted. It is the general idea.

 

[JamesCliff]

Thanks for the reply m8,

However, forgive me for saying this but where does the update query enter the script? Im fairly new to update querys and edit forms, so not to sure what to do with the above code u gave me.

The SaleID is already passed to the edit script through the other page which i have already made called list.php. Therefore would i need to pass more variables from the edit.php to another page? Is it not possible to have the whole edit script on one script page seen as the SaleID variable has already been passed to it using the following link from the list.php record:-

edit.php?SaleID=2

Thanks alot

Jim

 

[kaancho12]

hi,
"if ($submit)" in my reply should be "if ($update)". When the user user makes changes and hits the "submit" button then the "update" query is executed.
You could use "$_Request[]" to get all the SaleID for the page edit.php if it has been passed, but i would assume that you would have to use "select" to get all the other information (price, description) from your "table".
let me know, how it goes. may be put an example script to show what you want to do.
ko12

 

[Meekos]

I'm sure there is more than one way to do this, but this is how I do it. I use the same page to list, edit or add, but I've remove the add portion. Hope it helps.


function display_list()
{
global $message;
$sql = "Select saleid, title, price from yourtable order by title";
$result = @mysql_query($sql);
if ($result)
{
if (mysql_num_rows($result) > 0 )
{ // build table
echo '<table width="100%" border="1">';
echo '<tr><td colspan="3"><b>Click the "ID" to edit/delete the record.</b></td></tr>';
echo '<tr><td align="center"><b>ID</b></td><td align="left"><b>Title</b></td><td><b/>Price</b></td></tr>';
while ($rec = mysql_fetch_assoc($result)) {
// Set your link to return to the page calling it with mode as edit and Id as the sale ID.
echo '<tr><td align="center" valign="top" width="7%">
<a href="'.$_SERVER['PHP_SELF'].'?mode=e&id='.$rec['saleid'].'">'.$rec['saleid'].'</td>';
echo '<td valign="top">'.$rec['title'].'</td><td valign="top" width="20%">'.$rec['price'].'</td></tr>';
} // end of while loop
echo '</table>';
} else {
echo '<p>Nothing was found.</p>';
} // end of number of rows check
} else {
echo '<p>We are currently experiencing technical difficulties. Please try again later.</p>';
}
} // end of display list function


function display_edit_form ($id)
{
$sql = "select saleid, title, description, price from news where saleid=$id";
$result = @mysql_query($sql);
if ($result) {
$rec = mysql_fetch_assoc($result);
echo '<form method="post" action="'.$_SERVER['PHP_SELF'].'?mode=e&id='.$rec['saleid'].'">';
echo '<table width="100%"><tr><th colspan="2" align="left">Please make changes, then click "Save Changes" to update. Or, click "Delete" to remove from database.</th></tr>';

echo '<tr><td colspan="2"><hr width="100%"></td></tr>';
echo '<tr><td>Title: </td><td><input name="title" size="30" value="'.$rec['title'].'"/></td></tr>';

echo '<tr><td width="40%">Description: </td><td><input name="description" size="60" value="'.$rec['description'].'"/></td></tr>';

echo '<tr><td width="10%">Price: </td><td><input name="description" size="10" value="'.$rec['price'].'"/></td></tr>';
echo '<tr><td colspan="2" align="center"><input name="save" type="submit" value="Save Changes"/>&nbsp;&nbsp;<input name="delete" type="submit" value="Delete"/></td></tr></table>';
} else {
echo '<p>Nothing found in the database.</p>';
}
}

if ($_GET['mode']) { // Check to see if mode has been set
$message = NULL;
$mode = $_GET['mode']; //Store mode in a variable
switch ($mode)
{ // begin switch mode
case 'l' : display_list(); // display a list of records
break;
case 'e' : if (isset($_GET['id'])) { // Make sure id is set
$id = $_GET['id']; // Store ID into a variable.
if (isset($_POST['save'])) { // save button was clicked
//Validate the data in your form here.
//Set your select statement based on the data in your form.
$sql = "Update yourtable Set title={$_Post['title']}, description={$_POST['description']}, price={$_POST['price']} Where id=$id;";
$result = @mysql_query($sql);
if ($result) { // update went ok
$message .= '<p>The record was updated successfully.</p>';
} else { // update did not go ok
$message .= '<p>We are experiencing technical difficulties. Unable to update this record.</p>';
} // end of result check
} else { // // remove this if you don't want them to delete items.
if (isset($_POST['delete'])) { // Delete was selected
$sql = "delete from yourtable where id='$id'";
$result = @mysql_query($sql);
if ($result) {
$message .= '<p>The record was deleted successfully.</p>';
} else {
$message .= '<p>We are unable to delete this record.</p>';
}
}// end of isset delete check
} // end of save post
} // end of isset id check
break;
} // end switch mode

}

 

[JamesCliff]

Thanks alot for the above code m8.

I edited it slightly to remove the delete section and alter it so my database fields etc.

The SaleID variable is carried over from my list.php to your script, which i have called edit.php. The selected records details are dispalyed in the form fields. However there are some issues. When i click the update button after i have changed a record to what i want, i am taken back to the main page, and the record isnt updated at all. SO i changed the action section of the form to:

'.$_SERVER['PHP_SELF'].'?page=admin/sales/edit&amp;mode=e&SaleID='.$rec['SaleID'].'"

I thought that would help. However it dosnt, all that happens now is when i have changed a record to what i want and click the update button, the edit.php just seems to refresh to how it was before and any changes i have made to the records in the field boxes are returned to there previous state. It seems although the update section of the script is not functioning.

you can see all this here:-

http://www.briskfire.com/gb/index.php?page=admin/sales/edit&SaleID=1

Below is the full code i am using:

<?

$host = "127.0.0.1";
$user = "jim11";
$pass = "";
$db = "gbplantandmachinery";

$connection = mysql_connect($host, $user, $pass) or die ("Unable to
connect!");

$saleID = $_GET['SaleID'];

// select database
mysql_select_db($db) or die ("Unable to select database!");

 
     $sql = "select * from sales where SaleID = '$saleID'";
     $result = @mysql_query($sql);
     if ($result) {
       $rec = mysql_fetch_assoc($result);
       echo '<form method="post" action="'.$_SERVER['PHP_SELF'].'?page=admin/sales/edit&amp;mode=e&SaleID='.$rec['SaleID'].'">';
       echo '<table width="750"><tr><colspan="2" align="center">Please make your changes to the selected sale item, then click the update button to update the item.<br>To change a sale items picture you will need to delete this item and re-add the item with the specific picture.<br><br></tr>';
       
       echo '<tr><td colspan="2"><hr width="750"><br><br></td></tr>'; 
       echo '<tr><td>Title: </td><td><input name="Title" size="30" value="'.$rec['Title'].'"/></td></tr>';

echo '<tr><td width="340">Description: </td><td><input name="Description" size="60" value="'.$rec['Description'].'"/></td></tr>';

echo '<tr><td width="75">Price: </td><td><input name="Price" size="10" value="'.$rec['Price'].'"/><br><br></td></tr>';
       echo '<tr><td colspan="2" align="center"><input name="save" type="submit" value="Update Sale Item"/></td></tr></table>';
     } else {
       echo '<p>Nothing found in the database.</p>';
     }

if ($_GET['mode']) { // Check to see if mode has been set
  $message = NULL;
  $mode = $_GET['mode'];  //Store mode in a variable
  switch ($mode) 
  {  // begin switch mode
    case 'l' : display_list();  // display a list of records
               break;
    case 'e' : if (isset($_GET['SaleID'])) { // Make sure id is set
                 $SaleID = $_GET['SaleID'];  // Store ID into a variable.
                 if (isset($_POST['save'])) { // save button was clicked
                   //Validate the data in your form here.
                   //Set your select statement based on the data in your form.
                   $sql = "Update sales Set Title={$_Post['Title']}, Description={$_POST['Description']}, Price={$_POST['Price']} Where SaleID=$SaleID;";
                   $result = @mysql_query($sql);
                   if ($result) { // update went ok
                        $message .= '<p>The sale item was updated successfully.</p>';
                    } else {  // update did not go ok
                        $message .= '<p>We are experiencing technical difficulties. Unable to update this record.</p>';
                    }  // end of result check
                 }  // end of save post 
               } // end of isset id check
               break;
  }  // end switch mode

}


?>

Any help with this would be great.

Thanks alot for the time you have already given to helping me.

Jim

 

[Meekos]

Let's try to debug this with a couple of echo statements. Also there are too many ; in the UPDATE SQL statement. Try this code. Let me know what happens.

if (isset($_POST['save'])) { // save button was clicked
//Validate the data in your form here.
//Set your select statement based on the data in your form.
$t = $_Post['title];
$d = $_post['description'];
$p = $_post['price'];
echo "DEBUG: Title=$t, Description=$d, Price=$p";
$sql = "Update sales Set Title='$t', Description='$d', Price=$p Where SaleID=$SaleID";
$result = @mysql_query($sql);
if ($result) { // update went ok
echo '<p>The sale item was updated successfully.</p>';
} else { // update did not go ok
echo '<p>We are experiencing technical difficulties. Unable to update this record.</p>';
} // end of result check
} // end of save post

 

[JamesCliff]

Hi and thanks for the help.

I did what you said, but it still dosnt update the record, i cant understand why though.

http://www.briskfire.com/gb/index.php?page=admin/sales/edit&mode=e&SaleID=1

View the URL above and try editing the record and click the update button, as you can see its not updated.

The php scripting that im using is:

<?

$host = "127.0.0.1";
$user = "jim11";
$pass = "";
$db = "gbplantandmachinery";

$connection = mysql_connect($host, $user, $pass) or die ("Unable to
connect!");

$saleID = $_GET['SaleID'];

// select database
mysql_select_db($db) or die ("Unable to select database!");

 
     $sql = "select * from sales where SaleID = '$saleID'";
     $result = @mysql_query($sql);
     if ($result) {
       $rec = mysql_fetch_assoc($result);
       echo '<form method="post" action="'.$_SERVER['PHP_SELF'].'?page=admin/sales/edit&amp;mode=e&SaleID='.$rec['SaleID'].'">';
       echo '<table width="750"><tr><colspan="2" align="center">Please make your changes to the selected sale item, then click the update button to update the item.<br>To change a sale items picture you will need to delete this item and re-add the item with the specific picture.<br><br></tr>';
       
       echo '<tr><td colspan="2"><hr width="750"><br><br></td></tr>'; 
       echo '<tr><td>Title: </td><td><input name="Title" size="30" value="'.$rec['Title'].'"/></td></tr>';

echo '<tr><td width="340">Description: </td><td><input name="Description" size="60" value="'.$rec['Description'].'"/></td></tr>';

echo '<tr><td width="75">Price: </td><td><input name="Price" size="10" value="'.$rec['Price'].'"/><br><br></td></tr>';
       echo '<tr><td colspan="2" align="center"><input name="save" type="submit" value="Update Sale Item"/></td></tr></table>';
     } else {
       echo '<p>Nothing found in the database.</p>';
     }

if ($_GET['mode']) { // Check to see if mode has been set
  $message = NULL;
  $mode = $_GET['mode'];  //Store mode in a variable
  switch ($mode) 
  {  // begin switch mode
    case 'l' : display_list();  // display a list of records
               break;
    case 'e' : if (isset($_GET['SaleID'])) { // Make sure id is set
                 $SaleID = $_GET['SaleID'];  // Store ID into a variable.
				 if (isset($_POST['save'])) { // save button was clicked
    //Validate the data in your form here.
    //Set your select statement based on the data in your form.
    $t = $_Post['title'];
    $d = $_Post['description'];
    $p = $_post['price'];
    echo "DEBUG: Title=$t, Description=$d, Price=$p";
    $sql = "Update sales Set Title='$t', Description='$d', Price=$p Where SaleID=$SaleID";
$result = @mysql_query($sql);
if ($result) { // update went ok
echo '<p>The sale item was updated successfully.</p>';
} else { // update did not go ok
echo '<p>We are experiencing technical difficulties. Unable to update this record.</p>';
} // end of result check
} // end of save post 
               } // end of isset id check
               break;
  }  // end switch mode

}


?>

Any more ideas?

Thanks

Jim

 

[jpadie]

Jim

a few thoughts:
change your variable declarations thus

    $t = mysql_escape_string($_POST['Title']); // should be $_POST['Title'] variables and array keys are case sensitive.
    $d = mysql_escape_string($_POST['Description']); //
    $p = mysql_escape_string($_POST['Price']);

note that we have also escaped the variables. this is a MUST before doing any sql activity.

and your sql declaration to

$sql = "Update sales Set Title='$t', Description='$d', Price='$p' Where SaleID='$SaleID'"; //enclose everything in quotes for good measure.

 

[JamesCliff]

Thanks for that section of code jpadie.

I entered it into the script and there were no parse errors etc. However it still dosnt update the record. I typed changes to a record in and then clicked update. The message "The sale item was updated successfully." appeared successfully but then the record returned to what it was before and any changes i had made were lost.

Ive tried debugging it myself, basiclly i think that the update query isnt working how it should oviously lol, then after the update button is pressed, the form is refreshed and selects the data from that record, as the record hasnt been updated, it displays the original details. Weve been troubleshooting it for a while now and the update query seems ok, however there is a section of code that is causing me suspision, ill post the full script below and highlight the code of suspicion:

  <?

$host = "127.0.0.1";
$user = "jim11";
$pass = "";
$db = "gbplantandmachinery";

$connection = mysql_connect($host, $user, $pass) or die ("Unable to
connect!");

$saleID = $_GET['SaleID'];

// select database
mysql_select_db($db) or die ("Unable to select database!");

 
     $sql = "select * from sales where SaleID = '$saleID'";
     $result = @mysql_query($sql);
     if ($result) {
       $rec = mysql_fetch_assoc($result);
       echo '<form method="post" action="'.$_SERVER['PHP_SELF'].'?page=admin/sales/edit&amp;mode=e&SaleID='.$rec['SaleID'].'">';
       echo '<table width="750"><tr><colspan="2" align="center">Please make your changes to the selected sale item, then click the update button to update the item.<br>To change a sale items picture you will need to delete this item and re-add the item with the specific picture.<br><br></tr>';
       
       echo '<tr><td colspan="2"><hr width="750"><br><br></td></tr>'; 
       echo '<tr><td>Title: </td><td><input name="Title" size="30" value="'.$rec['Title'].'"/></td></tr>';

echo '<tr><td width="340">Description: </td><td><input name="Description" size="60" value="'.$rec['Description'].'"/></td></tr>';

echo '<tr><td width="75">Price: </td><td><input name="Price" size="10" value="'.$rec['Price'].'"/><br><br></td></tr>';
       echo '<tr><td colspan="2" align="center"><input name="save" type="submit" value="Update Sale Item"/></td></tr></table>';
     } else {
       echo '<p>Nothing found in the database.</p>';
     }

// CODE BELOW COULD BE DODGY!!!

if ($_GET['mode']) { // Check to see if mode has been set
  $message = NULL;
  $mode = $_GET['mode'];  //Store mode in a variable
  switch ($mode) 
  {  // begin switch mode
    case 'l' : display_list();  // display a list of records
               break;
    case 'e' : if (isset($_GET['SaleID'])) { // Make sure id is set
                 $SaleID = $_GET['SaleID'];  // Store ID into a variable.
				 if (isset($_POST['save'])) { // save button was clicked

// END OF CODE THAT COULD BE DODGY!!!

    //Validate the data in your form here.
    //Set your select statement based on the data in your form.
	$t = mysql_escape_string($_POST['Title']); // should be $_POST['Title'] variables and array keys are case sensitive.
    $d = mysql_escape_string($_POST['Description']); //
    $p = mysql_escape_string($_POST['Price']);    
$sql = "Update sales Set Title='$t', Description='$d', Price='$p' Where SaleID='$SaleID'"; //enclose everything in quotes for good measure.$result = @mysql_query($sql);
if ($result) { // update went ok
echo '<p>The sale item was updated successfully.</p>';
} else { // update did not go ok
echo '<p>We are experiencing technical difficulties. Unable to update this record.</p>';
} // end of result check
} // end of save post 
               } // end of isset id check
               break;
  }  // end switch mode

}


?>

The code that im suspicious of is the code thats within the //CODE THAT COULD BE DODGY notifications on the code above, however im not sure if that could effect it, is the highlighted code above really nessessary within the script? Could this effect the update query? How would i re-code the script without that code?

Thanks

Jim

 

[jpadie]

i think that the

$result = mysql_query($sql);

should be on a separate line. in the code above it is commented out.

i'd also remove the @ sign whilst you are still in debugging mode!

 

[JamesCliff]

That was it

Thanks alot for the help mate.
Im going to start customizing the form now its working.

Again thanks for all the help people.

Jim

 

[jpadie]

Jim

i noticed in an earlier post that your site (with images) was taking a long time to load. this seemed to be because you weren't serving up thumnails but full sized images scaled down to size.

the latter works ok from a presentation perspective but the image remains the same filesize.

when you get over the initial humps, have a look at the image handling functions that are available with php and its add-on libraries. i'd guess you want to serve up thumbnails (at a few k each) in your list display and then serve a higher res image in an individual display.

Justin

 

[JamesCliff]

Yeh m8,

Thanks for bringing this up, at school today i noticed about the loading times, they are awfully long, and yeh it is because the images are still full size and simply re-sized.

I am going to start addressing this issue very soon. At the minute im just finishing off the edit.php and index.php 100% height page scale. I am going to address this problem very soon m8. Im hoping php has a function to auto create thumbernails and link them to the main picture. However im still unsure. I am going to begin research tonight hopefully when i have addressed the current issues.

No doubt ill run into trouble when modifying the sales.php to display thumbernails and linking to images or trouble with the AddSale.php page, so i expect i will be posting a thread on it fairly soon hehe.

Thanks again for the help

Jim

 

[jpadie]

search for thumbnail in the php.net online documentation.

i've never played with this functionality but have used code snippets from php.net that have worked more or less out of the box.

you need the GD library installed in your php implementation - this should be the case if you are using a recent version. run phpinfo() to see whether it is there.

have fun.
Justin

 

[JamesCliff]

Yep i got GD support already installed on my server, i installed it when i compiled php with apache on my linux box.

Ill have alook now for thumbnail on php.net

Thanks alot for head start

Ill let you know how i get on hehe.

Jim

 

[jpadie]

Jim

Olav has posted some code in another thread that does what you want (thread434-1011339) on thumnail creation.

Justin