Is there an editor that allows changes to data in MySQL?

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Is there an editor that allows changes to data in MySQL?

Postby OrcaSoul » 06. June 2014 00:22

I have a database with some of the fields on some of the product records that are empty.

I would like to find an existing editor that allows me to call up any individual record, and show me what is in that record.

An editor that can look at a MySQL database, show the existing data in the fields, and allow me to enter/change data into any of the fields.

PhpMyAdmin does this, but I have to select each record from the screen, click to edit it, then go to another form to make changes. Plus, long text fields need to be scrolled in a small box that does not show the entire text.

The ideal form would allow me to enter the product name, and it would use that to pull just the record for that product, and allow me to enter text in any field, then save it into the database, replacing the existing record.

I can write a form that will allow me to enter data in the fields, and use PHP to replace the existing record - but that would not show me the existing data in those fields before I make the changes. I want to call a record, see what is in it, then make changes as needed.

Thanks.

Edit to add that I do NOT want to be able to change the table structure of the database, just simply edit the data and fill in missing data.
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: Is there an editor that allows changes to data in MySQL?

Postby JJ_Tagy » 06. June 2014 01:05

Short of creating your own editor, phpmyadmin is your best bet. You can sort/filter in phpmyadmin to show specific entries or types of entries.
JJ_Tagy
 
Posts: 788
Joined: 30. January 2012 13:44
XAMPP version: 5.5.15
Operating System: Windows 10 Pro x64

Re: Is there an editor that allows changes to data in MySQL?

Postby OrcaSoul » 06. June 2014 02:07

JJ_Tagy wrote:Short of creating your own editor, phpmyadmin is your best bet. You can sort/filter in phpmyadmin to show specific entries or types of entries.


Yeah...but that isn't as fluid as I would like - you need to jump around, and getting back to the product you just updated takes some effort.

I'm thinking I can write a form that will call up the product based on the name or ID, which could safe the fields to a file. Then with that saved, open a separate form that would read the file, fill in the fields, and I could make the changes I need to and then submit it to a php that would save that into the database.

Still a bit cumbersome, but should serve.

One thing that comes to mind, is it possible to call the second html form from the first php after it writes the file?

The steps:
1: Open the first form.
2: Enter the single name/ID and submit to the first php.
3: That php gets the record from the DB, and saves the data in a temp file.
4: The php then opens the second form, which reads the temp file, puts that data in the form.
5: The form is used to enter/change data, then hit submit.
6: That opens th 2nd php, which saves the revised record into the DB.

The more that can be automated, the better.

Just a thought...
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: Is there an editor that allows changes to data in MySQL?

Postby Nobbie » 06. June 2014 10:12

OrcaSoul wrote:PhpMyAdmin does this, but I have to select each record from the screen, click to edit it, then go to another form to make changes. Plus, long text fields need to be scrolled in a small box that does not show the entire text.


If your records contain at least one unique field (and a well designed table should of course at least have a primary key), you also may click into the grid and edit the record directly, without the need of taking the circumvention to the appropriate form.

This feature is called "gridediting" (or inline editing), as far as i know it is realized with the help of AJAX, a JavaScript environment for HTTP calls, and it *should* be enabled by default. I never used this, but i know there is something like that. Give it a try, if it does not work, you may google for "phpmyadmin grid editing" and/or "phpmyadmin inline editing", you will have a lot of help there.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: Is there an editor that allows changes to data in MySQL?

Postby JJ_Tagy » 06. June 2014 14:16

OrcaSoul wrote:One thing that comes to mind, is it possible to call the second html form from the first php after it writes the file?

Yes, but you're going to need to dive into HTML and PHP to generate your own editor. That will take some time to create. That is why Nobbie and I agree on using phpMyAdmin.

If you are still dead set on creating your own editor, I would build one controlling PHP base file that provides your options. Click on the option and it can call itself with an option set. When the form reloads, it can pull the MySQL query and send the data array to a new form "template" for editing. Once the user makes the changes and hits submit, it directs back to the original form with the updated data which the first form commits the changes to the database. Again, this will take you some time (and a high learning curve) to finish.
JJ_Tagy
 
Posts: 788
Joined: 30. January 2012 13:44
XAMPP version: 5.5.15
Operating System: Windows 10 Pro x64

Re: Is there an editor that allows changes to data in MySQL?

Postby Altrea » 06. June 2014 17:01

We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 11926
Joined: 17. August 2009 13:05
XAMPP version: several
Operating System: Windows 11 Pro x64

Re: Is there an editor that allows changes to data in MySQL?

Postby OrcaSoul » 07. June 2014 01:02

I've looked at the phpmyadmin editing on the grid, and that would do - in a pinch.

Problems with that are that I have to search for the record, it may not be on the current page so I have to page through to find it, and setting the number of records on one page to 500 times out - I have over 800 records and I need to search by a bowl name, not my the index number (I may not have that if I am looking at a bowl and need to change/add some text to one or more fields). For instance, in the past I may have entered the new record with just the bowl name and perhaps the source wood it was made from - but not a description if it wasn't finished yet with the leather, carving, air brush work I add later. At that time, I did not record the index because I don't need it yet...

The result is, searching for the name isn't the fastest way to go - and yes, I can sort the list, but it's still a nuisance when dealing with many bowls.

It also requires shifting right/left to find the field, more time - and then when I click to edit the field the text is in a small box, and a bit hard for me to read.

I looked at MySQL Workbench - it would work, but is a bit more than what I want - and costs after the trial is done.

Anyway, I found one script that does some of what I need http://www.tutorialspoint.com/php/mysql_update_php.htm, a simple editor that allows changing a field using an input box, but it won't load the existing data, which I want to see so I can tell which of the 14 fields I need to change.

I have finished most of a script that allows me to enter a bowl name, it calls the record for that and shows a page with the fields with existing data - on submit it will open the record and update the entire record.

I have this done up to the point of the update - having some problem coding the actual update code for the 14 fields:
    txtProductBowlCode
    txtSpeciesSeq
    txtSourceWood
    txtNotes
    intProductID -------this is the index, so probably should not update this...
    txtProductHeading
    txtProductBowlDetails
    txtProductBowlImage
    intProductBowlStatus
    intProductBowlVenderID
    curProductBowlPriceAsked
    curProductBowlPriceSold
    curProductBowlProceeds
    txtVenue
into the tblSplintersBowlInventory table.

The examples of the UPDATE function seem to say I have to update each separately - is this true, or can I do it in a single query, similar to the INSERT function?
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: Is there an editor that allows changes to data in MySQL?

Postby OrcaSoul » 07. June 2014 04:59

After some digging, I found that the following SHOULD work - but it doesn't! :(

Code: Select all
// update data in mysql database
   $sql="UPDATE tblsplintersbowlinventory SET txtSpeciesSeq='$r_speciesseq', txtSourceWood='$r_sourcewood', ".
      "txtNotes='$r_notes', txtProductHeading='$r_heading', txtProductBowlDetails='$r_details', ".
      "txtProductBowlImage='$r_image', intProductBowlStatus='$r_status', intProductBowlVenderID='$r_venderID', ".
      "curProductBowlPriceAsked='$r_priceasked', curProductBowlPriceSold='$r_pricesold', curProductBowlProceeds='$r_proceeds', ".
       "txtVenue='$r_venue' WHERE txtProductBowlCode='$r_bowlcode'";


I've checked the syntax against several examples, and it looks the same...

Any suggestions?
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: Is there an editor that allows changes to data in MySQL?

Postby JJ_Tagy » 07. June 2014 05:40

What indications are there that it doesn't work? If you think the syntax is correct, then it must be something else. How are you populating the $r_ variables?
JJ_Tagy
 
Posts: 788
Joined: 30. January 2012 13:44
XAMPP version: 5.5.15
Operating System: Windows 10 Pro x64

Re: Is there an editor that allows changes to data in MySQL?

Postby OrcaSoul » 07. June 2014 06:02

JJ_Tagy wrote:What indications are there that it doesn't work? If you think the syntax is correct, then it must be something else. How are you populating the $r_ variables?


This is the output I get, showing the field data exists and is valid:

bowlcode: Cherry07-004
speciesseq: Cherry07-004
sourcewood:
notes: this is a note
idcode: 269
heading:
details:
image: BowlPhotos/Thumbs/NoBowltmb.jpg
status: 4
venderID: 1
priceasked: 0
pricesold: 4
proceeds: 0.0000
venue:

UPDATE tblsplintersbowlinventory SET txtSpeciesSeq='Cherry07-004', txtSourceWood='', txtNotes='this is a note', txtProductHeading='', txtProductBowlDetails='', txtProductBowlImage='BowlPhotos/Thumbs/NoBowltmb.jpg', intProductBowlStatus='4', intProductBowlVenderID='1', curProductBowlPriceAsked='0', curProductBowlPriceSold='4', curProductBowlProceeds='0.0000', txtVenue='' WHERE txtProductBowlCode='Cherry07-004'
ERROR


The error at the bottom tells me the update failed.

Here is the code:

Code: Select all
<?php
   $con = mysql_connect("localhost","xxxx","xxxxxxxx");
   if (!$con)
   {
      die('Could not connect: ' . mysql_error());
   }
   
   $r_bowlcode = $_POST['bowlcode'];
   $r_speciesseq = $_POST['speciesseq'];
   $r_sourcewood = $_POST['sourcewood'];
   $r_notes = $_POST['notes'];
   $r_idcode = $_POST['idcode'];
   $r_heading = $_POST['heading'];
   $r_details = $_POST['details'];
   $r_image = $_POST['image'];
   $r_status = $_POST['status'];
   $r_venderID = $_POST['venderID'];
   $r_priceasked = $_POST['priceasked'];
   $r_pricesold = $_POST['pricesold'];
   $r_proceeds = $_POST['proceeds'];
   $r_venue = $_POST['venue'];
   

   // update data in mysql database
   $sql="UPDATE tblsplintersbowlinventory SET txtSpeciesSeq='$r_speciesseq', txtSourceWood='$r_sourcewood', ".
      "txtNotes='$r_notes', txtProductHeading='$r_heading', txtProductBowlDetails='$r_details', txtProductBowlImage='$r_image', ".
      "intProductBowlStatus='$r_status', intProductBowlVenderID='$r_venderID', curProductBowlPriceAsked='$r_priceasked', ".
      "curProductBowlPriceSold='$r_pricesold', curProductBowlProceeds='$r_proceeds', txtVenue='$r_venue' WHERE txtProductBowlCode='$r_bowlcode'";
      
   echo "<br />" . $sql . "<br />";
   
   $result=mysql_query($sql);
   
   // if successfully updated.
   if($result){
      echo "Successful";
      echo $result;
   }
   
   else {
      echo "ERROR";
      echo $result;
   }
   
   
   
   
   
   //echo $_POST['bowlcode'] . " record updated";
   
   mysql_close($con);
   
?>
</body>
</html>


This is called from another PHP that loads the form where the data is displayed.

There are 3 files in this:
Form_EditBowl.html - that simply requests the bowl name (in this case Cherry07-004) and posts to:
Form_Edit Bowl.php - that finds the record with that name and gets the data, which is then placed in the appropriate form text box.And then posts to Form_WriteBowl.php, the code above.
Last edited by OrcaSoul on 07. June 2014 10:40, edited 1 time in total.
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: Is there an editor that allows changes to data in MySQL?

Postby JJ_Tagy » 07. June 2014 09:34

Perhaps you have a null or type issue. Under the error message, you could dump the mysql error instead of $result to get a hint of what is wrong.
JJ_Tagy
 
Posts: 788
Joined: 30. January 2012 13:44
XAMPP version: 5.5.15
Operating System: Windows 10 Pro x64

Re: Is there an editor that allows changes to data in MySQL?

Postby OrcaSoul » 07. June 2014 10:39

JJ_Tagy wrote:Perhaps you have a null or type issue. Under the error message, you could dump the mysql error instead of $result to get a hint of what is wrong.


I did that:

Code: Select all
$result=mysql_query($sql) OR die("Error:".mysql_error());


And it informed me that no database had been requested.... :oops:

Added
Code: Select all
mysql_select_db("scw-db", $con);
right after the
Code: Select all
if (!$con)
statement, and it works perfectly...

Sometimes I get too close to the code, and don't see the obvious... :roll:

Thanks.
OrcaSoul
 
Posts: 57
Joined: 24. August 2009 23:51

Re: Is there an editor that allows changes to data in MySQL?

Postby Nobbie » 07. June 2014 10:51

OrcaSoul wrote:Sometimes I get too close to the code, and don't see the obvious... :roll:


That is the "standard problem" if there is no usefull error handling. "echo ERROR" instead of mysql_error() is not very helpfull...
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: Is there an editor that allows changes to data in MySQL?

Postby Nobbie » 07. June 2014 10:57

OrcaSoul wrote:The result is, searching for the name isn't the fastest way to go - and yes, I can sort the list, but it's still a nuisance when dealing with many bowls.


Because you are searching "manually" (this is indeed a bad idea). Why dont you simply search with MySQL (Select.... Where bowlname = "...." or similar)? MySQL provides not only Metacharacters for Search, it also supports regular expressions.

You may also define an index for the column which holds the names, which makes the search very fast. For columns of type TEXT you may also define a "FULLTEXT" Index, which is a special MySQL Feature.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: Is there an editor that allows changes to data in MySQL?

Postby Nobbie » 07. June 2014 11:17

OrcaSoul wrote:The ideal form would allow me to enter the product name, and it would use that to pull just the record for that product, and allow me to enter text in any field, then save it into the database, replacing the existing record.


I still dont understand, what you are messing aroung - of course phpmyadmin provides this functionality as well. Instead of browsing thru all your records, click the "Search" button in the top menu, you will receive a form, which shows ALL fields of your choosen table, you may specify any values, any special functionts, whatever you want for each field and finally hit "Submit" and phpmyadmin will show you all records, which matches your criterias. YOu may then even use grid edit / inline edit or hit "edit" to enter data in an appropriate form.

You dont have to write your own code, phpmyadmin is a very sophisticated tool and you really can do everything with this tool, what you are looking for. It is the "swiss army knife" of MySQL.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Next

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 100 guests