Interactive Database Editing?

General PHP Help
#1

Greetings.

My specific issue is not one I’ve seen addressed in the many tutorials and threads here. I will try to explain what I want to do as thoroughly as possible, but I need to make it known that I am no expert. I have only a very basic knowledge of web languages, and part of the problem is that I cannot find specific enough instruction online to help guide me to the answers I need.

What I’m Trying To Accomplish
For years, I have been working on an adventure game. I’ve researched several programming languages, but I don’t have the funds to gain access to anything which will work for my needs. From what I’ve seen, database management and web-based data manipulation will be flexible and effective enough to meet those needs.

Unfortunately, my initial experiences with PHP have only given me one method for data retrieval (PHP SELECT). The code I am using is a direct copy from W3Schools.com. The specific problem I have encountered is that there appears to be no way to actually manipulate the result of a SELECT query; the only code they offer appears to capable only of printing a result to a web page. For my work to continue, I need to be able to retrieve the current value of any given trait, calculate its cost through an equation, and increment/decrement the trait in real-time.

Essentially, I need interactive data editing which can retrieve current values from a table, edit those values, then save the new values back to the database.

I understand that PHP is server-side. What I need to be able to do is client-side. To my knowledge, this plants me firmly in JavaScript territory. I have already studied methods for passing a PHP variable to JavaScript, but I can not figure out how to do this based on a database query; all available examples are demonstrated by directly entering the information which will be passed to JavaScript, not by retrieving data.

I’ve been through pages and pages of information on W3Schools.com, but nothing I’ve found even addresses real-time altering of database information. I’m hoping I can learn more here.

Any assistance is appreciated. I can provide specific details on request, but really what I’m looking for is an article or document explaining this to me in a way I can understand.

#2

Once the data is retrieved, you can setup some inputs to allow the user or yourself to edit that information, then submit it again using an update query. There are tons of tutorials out there on how to do a select query, edit it and then send it back to the database.

#3

Thanks for the reply, Richei.

I’ve seen a few tutorials, but none yet that actually detail the kind of work I need to do. 90% of them are still using my_sql_* commands, which are deprecated and vulnerable. In the event that I actually find a tutorial which does appear to address my concerns, it ends up being an example which can only be used for something extremely basic, or non-applicable to my situation. The example of the PHP SELECT function is a perfect one: It only tells me how to retrieve and echo, not how to modify in real-time. Without that information, I’m just blindly typing and hoping for an accidental success.

I’m just hoping I can find some more detailled information here, because my internet searches have turned up zero.

#4

What you need to do is find tutorials on Ajax and understand JSON with a good knowledge of JavaScript (I personally the jQuery library instead of JavaScript). This way of doing still using MySQL and introduces a little more of a security challenge by having JavaScript into the mix; however, as long as you use prepared statements in PHP and knowing that writing script a certain way in JavaScript will prevent people from accessing valuable data then it’s relatively secure as writing it in straight PHP ( or whatever server language you write it in). The only downside to doing this way is people have to have JavaScript enable, but I say a vast majority have JavaScript enable.

Another route you could possibly go and I’m no expert by any means is learning Ruby on Rails (or another server-side language), from what I understand is Ruby is more robust than PHP. However, that is something you will have to check out or maybe someone here could give you better info if that is something that can solve your problem.

#5

[member=46186]Kevin Rubio[/member] may be able to point u in right direction.

#6

I had seen some tutorials on AJAX and JSON, but hadn’t delved that deeply into them. I was certain a solution with just PHP and JavaScript was possible, and wanted to keep the list of languages I had to learn to a minimum. The more parts in a system, the more there is to break. I will take a second look.

#7

If you can give an exact detailed example of what you want to do you will get better answers.

You said you wanted to select and modify in real time. What exactly are you wanting to do?

#8

The simplest way to say it is that I need to be able to retrieve a database entry, and use a pair of buttons to increment or decrement that value. For example, let’s say that I want to calculate the price of a crate of peaches. The database holds entries for the price of each peach, the number of peaches, and the total cost of a given order. What I need to be able to do is call a query to the database entry for the number of peaches, allow the user to click the buttons to alter the number, and calculate the total price with each click. As it stands now, I have no idea how to do anything with a query other than simply display it.

I could directly write to the database using the UPDATE command, but this would require a complete page load with each click. Client-side calculation of each change is the only way to avoid this (that I’m aware of).

It should be noted that I have had no trouble manipulating database information through more direct means (UPDATE, for example). My page loads the data correctly every time, so I can’t believe it’s a connection issue.

I’m going to try to include the code I’m using, but please bear with me as I’ve never used code tags in a forum before.


<?php
$con=mysqli_connect("host","username","password","database");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT Quantity FROM testdb WHERE PID='1'");


while($row = mysqli_fetch_array($result)) {
  echo $row['Quantity'];
}


mysqli_close($con);
?>

While this code does allow me to retrieve information, any changes I make to it result in an error. If there is a way to write to variable instead of displaying it on the screen, I’ve not found it.

I also found this code, which, while it does allow me to pass a PHP variable into JavaScript, still doesn’t give me enough power to insert a database entry. It will accept information I manually enter, but I still can’t point it to the database.


<?php
    $php_var = "Hello world from PHP";
?>
 
<html>
  <head>
    <title>Pass variable from PHP to JavaScript - Cyberster's Blog'</title>
  </head>
  <body>
    <script>
      var js_var = "<?php echo $php_var; ?>";
        alert(js_var);
    </script>
  </body>
</html>

The goal I have in mind seems like it shouldn’t be that complex. I must have missed something, or not found the correct tutorial, because I’ve seen websites which can calculate cost from increment buttons before. I just don’t have the knowledge of how to accomplish it.

The end result I’m looking for is to be able to update the database with any changes, through a simple submit button. I just have to be able to see the results of these changes prior to that submission. Otherwise, I force my users to reload the page on every alteration.

Can you help? I’ve spent two weeks on this problem and I’m still no closer to a solution.

#9

You’re definitely going to need to use jquery and ajax for that. I would avoid the hassle of buttons and just allow the user to enter the numbers (with a max number if needed).

Sponsor our Newsletter | Privacy Policy | Terms of Service