Either one, though, the text area is still empty, and I cannot get the game_id variable I'm trying to use to represent the extension /file.php?game_id=XX
I must be going about this the wrong way though I'm still trying to get it working
Here is the update form with the line you supplied me
PHP Code:
<?php
$link = mysqli_connect('mysql.powerhost.com','u195276486_comp','opl123','u195276486_comp');
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$tbl_name="opl_comp"; // Table name
?>
<?php
$sql="SELECT * FROM $tbl_name WHERE id='45'";
$result=mysqli_query($link,$sql) or die("Error: ".mysqli_error($link));
?>
<?php
if ($row = mysqli_fetch_array($result)) {
// You have data
}
You don't really need to use a while loop if you're just grabbing one row, that's why I used the if statement. After the $row variable is set, you should print it out to the screen just to see what data it has inside. Like var_dump($row);
I never see you assigning anything to the $game_id variable. You have to get the query variable from the url and assign it to the $game_id variable:
You don't really need to use a while loop if you're just grabbing one row, that's why I used the if statement. After the $row variable is set, you should print it out to the screen just to see what data it has inside. Like var_dump($row);
I never see you assigning anything to the $game_id variable. You have to get the query variable from the url and assign it to the $game_id variable:
Thanks, Prime! Huge help. Taking me forever to figure how to get that working.
It seems my update action has broken somehow with all the code I've been messing with, I cannot seem to find what I messed up here, as it refuses to update the database on submit now.
PHP Code:
<?php //Connect $link = mysqli_connect('mysql.powrhost.com','u195276486_comp','opl123','u195276486_comp'); if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } $tbl_name="opl_comp"; $notes=$_POST['notes']; //update notes column under specified primary key id if(isset($_POST['submit'])) { $sql="UPDATE $tbl_name SET notes=".addslashes($notes=$_POST['notes'])." WHERE id='".$game_id."'"; $result=mysqli_query($link,$sql) or die("Error: ".mysqli_error($link)); } //Thanks if($result){ echo "Thanks. You can view the compatibility list <a href='complist.php'>here</a>."; echo "<BR>"; echo "<a href='insert.php'><-- Back to submission form</a>"; }
else { echo "There was a problem. Please try again later."; } ?> <?php // close connection mysqli_close($link); ?>
Is there an obvious issue here I am not seeing? This is the same code I was using earlier when I had originally gotten it to edit specific columns using primary key values without using the variable.
You've lost some apostrophes along the way (the error message you're getting will say something about a syntax error just after notes=).
Incidentally, the $notes= bit there is redundant - you're already doing that two lines earlier (even before you've checked to see if anything was submitted, in fact).
Exactly what I was going to say. When an SQL statement isn't doing what you expect, print out your $sql variable so you can see the full SQL statement it's trying to execute. That usually gives you clues as to what's wrong.
//update notes column under specified primary key id
if(isset($_POST['submit'])) {
$sql="UPDATE $tbl_name SET notes='".addslashes($_POST['notes'])."' WHERE id='52'";
$result=mysqli_query($link,$sql) or die("Error: ".mysqli_error($link));
}
Haha I did, hmm.. funny. Stoner move, I guess. Okay, so I just copied the same code I posted on the previous page (when it was working) and still it wont update the column
Perhaps the problem is in the other page?
edit: I posted before refreshing the page xD How can I go about running a $sql statement to check the errors on the page? I tried
PHP Code:
$result=mysqli_query($link,$sql) or die("Error: ".mysqli_error($sql));
Solved everything. I was able to create a page that will call all the information from the columns to be edited with the game_id variable. At first, I only wanted to have the one column open to edit.. but after accomplishing this, I decided to go for more. Thank you all for the help and not spood feeding me that which I was seeking.
Here is the code for future reference by searches.
PHP Code:
<?php //CONNECT TO THE MYSQL DATABASE $link = mysqli_connect('server','username','password','database'); if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); }
//CREATE THE VARIABLES TO IDENTIFY THE DIFFERENT FORM INPUTS $tbl_name="opl_comp";
//VARIABLE TO ASSIGN PRIMARY KEY VALUE SO THE FORM KNOWS WHICH ROW IS BEING EDITED $game_id = isset($_GET['game_id']) ? (int)$_GET['game_id'] : 0;
<?php //ON SUBMIT, UPDATE THE MYSQL DATABASE WITH THE NEW DATA INSIDE THE FORM if(isset($_POST['submit'])) { $update="UPDATE $tbl_name SET notes='".addslashes($_POST['notes'])."', gamename='$gamename', region='$region', mode='$mode', smb='$smb', hdd='$hdd', usb='$usb', comp='$comp', vmc='$vmc', oplver='$oplver' WHERE id='".$game_id."'"; $result=mysqli_query($link,$update) or die("Error: ".mysqli_error($update)); } ?>
<?php //SELECT ALL FROM TABLE WHERE THE ID COLUMN IS THE PRIMARY KEY VALUE $sql = "SELECT * FROM $tbl_name WHERE id = '".$game_id."'";
//SUBMIT QUERY TO DATABASE AND CAPTURE RESULTS FOR THE FORM INPUTS $result = $link->query($sql) or die(mysqli_error($sql)); $query=getenv(QUERY_STRING); parse_str($query); ?>
Since you seem to want your code to be referenced in the future, or even if you plan on actually using the above code verbatim on a live website, you might want to take note of the several issues it has:
Outputting SQL error messages/info to the user is both dangerous and unhelpful. It's dangerous in that you're needlessly exposing the inner-workings of your database to the end user (who might be attempting to maliciously attack your DB in the first place and would be delighted to learn such details), and it's unhelpful in that it doesn't tell you what the problem is (after all, you are the one who's in a position to fix the errors in the first place).
Consider handling the errors gracefully and logging all of the relevant details instead.
blindly assumes that all of those POST'ed elements exist. You should always verify external data exists before you attempt to use it.
This SQL query:
PHP Code:
$update="UPDATE $tbl_name SET notes='".addslashes($_POST['notes'])."', gamename='$gamename', region='$region', mode='$mode', smb='$smb', hdd='$hdd', usb='$usb', comp='$comp', vmc='$vmc', oplver='$oplver' WHERE id='".$game_id."'";
uses unsanitized user-supplied data. You should never do this, else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors.
Speaking of the above query, addslashes() should never be used to prepare data for use in a SQL query. (At least, certainly not to a MySQL DBMS, since there are better suited functions for the job.)
PHP Code:
$query=getenv(QUERY_STRING);
parse_str($query);
What is the purpose of these lines? Not only do you not define a constant named QUERY_STRING (meaning an error message is going to be generated), but both of those lines seem rather pointless - why not just use the $_GET array?
What's worse, you're basically mimicking the dreaded register_globals behavior. If you're not aware why this behavior is one of the ugly mistakes of PHP's history, start here: security.globals.
Your HTML is littered with groups of elements that share the same "id" attribute value. This is not only meaningless but violates the W3C standard. You might want to make sure you're producing valid HTML if you actually expect various browsers to interpret it and behave correctly/reliably.
Of course, that's not even getting into the 1990s style <table>-is-awesome-for-design-layout! debacle.
is pointless since you never use any of those variables after those lines... not to mention the fact that you've already let the user inject whatever malicious SQL code (s)he desired several statements before these.
Thank you for the informative post. I was hoping that someone might come along and do just that. I have only been coding PHP for 2 days, so I know just by comparing to other code I have seen that mine is in no way legitimate for wide live use. Your language is a tad overly critical, but your post is definitely appreciated.
I wrote this to submit compatibility information between PS2 games and PS2 homebrew. I highly doubt that any of the issues with the code will effect it's health or purpose in any way. It is working 100% correctly, and I have tested it a lot. As far as displaying properly.... It's all of 1 form, a couple input fields and some checkboxes so I don't think it really matters
blindly assumes that all of those POST'ed elements exist.
All of those fields are required before a submission is made to the database, so.. I'm not sure if what you're saying on this matter still stands?
uses unsanitized user-supplied data. You should never do this, else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors.
Perhaps some constructive elaboration on your expertise is in need here...
Speaking of the above query, addslashes() should never be used to prepare data for use in a SQL query. (At least, certainly not to a MySQL DBMS, since there are better suited functions for the job.)
What functions are better suited for the job, and how would they be applied? Also, why "never"?
is pointless since you never use any of those variables after those lines.
So, you should use the escape immediately after using the variable? I was not aware that the location of the escapes matter, since nothing is submitted until you click submit.. In this case, would the escapes be used properly if they are placed after the UPDATE?
What functions are better suited for the job, and how would they be applied?
See posts #10 and #11 in this thread.
So, you should use the escape immediately after using the variable?
No, before you use the variable so that you used the escaped value - doing it after is, as bradgrafelman says, pointless (and, as he points out, you never used the escaped values).
Because addslashes() doesn't do the same thing as mysqli_real_escape_string(), for example. I guess I say "never" because it's similar to saying you should "never" use a brand new smartphone to sink a nail into a 2x4 when you've got a hammer at your disposal as well. It's not to say that you can't get the job done most of the time... just that it doesn't make sense to not use the better-suited tool instead.
Originally Posted by bemore
So, you should use the escape immediately after using the variable? I was not aware that the location of the escapes matter
Well the "location" certainly matters as far as order of operations goes. What's the point of making data safe for use inside a SQL query if you've already used the data in a SQL query? If you were shoveling manure all day for a living, would you come home and wash ("sanitize") your hands immediately after you had prepared and ate dinner?
you should "never" use a brand new smartphone to sink a nail into a 2x4
Hahaha, this had me laughing.
What's the point of making data safe for use inside a SQL query if you've already used the data in a SQL query?
After it was mentioned, no it doesn't make sense to use it after a query. Very silly to not assume this right away when learning PHP, but hey I know now and that is all I care about.
Here is the corrected code
PHP Code:
<?php
//CONNECT TO THE MYSQL DATABASE
$link = mysqli_connect('host','user','pass','database');
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
//CREATE THE VARIABLES TO IDENTIFY THE DIFFERENT FORM INPUTS
$tbl_name="opl_comp";
//VARIABLE TO ASSIGN PRIMARY KEY VALUE SO THE FORM KNOWS WHICH ROW IS BEING EDITED
$game_id = isset($_GET['game_id']) ? (int)$_GET['game_id'] : 0;
//VARIABLES TO ASSIGN THE FORM'S INPUT FIELD VALUES TO WHICH COLUMN THE DATA WILL BE INSERTED INTO
$region=$_POST['region'];
$vmc=$_POST['vmc'];
$smb=$_POST['smb'];
$hdd=$_POST['hdd'];
$usb=$_POST['usb'];
$notes=$_POST['notes'];
$comp=$_POST['comp'];
$oplver=$_POST['oplver'];
$gamename=$_POST['gamename'];
?>
//ON SUBMIT, UPDATE THE SQL DATABASE TABLE WITH THE NEW DATA INSIDE THE FORM INPUT FIELDS
if(isset($_POST['submit'])) {
$update="UPDATE $tbl_name SET notes='$notes', gamename='$gamename', region='$region', mode='$mode', smb='$smb', hdd='$hdd', usb='$usb', comp='$comp', vmc='$vmc', oplver='$oplver' WHERE id='".$game_id."'";
$result=mysqli_query($link,$update) or die("Error: ".mysqli_error($update));
}
?>
<?php
//SELECT ALL FROM SQL DATABASE TABLE WHERE THE id COLUMN IS THE PRIMARY KEY VALUE
$sql = "SELECT * FROM $tbl_name WHERE id = '".$game_id."'";
//SUBMIT QUERY TO DATABASE
$result = $link->query($sql) or die(mysqli_error($sql));
?>
Reply With Quote
Originally Posted by primehalo
though I'm still trying to get it working
and not spood feeding me that which I was seeking.
Bookmarks