Solved

Php not Updating SQK Database

Posted on 2014-11-15
10
156 Views
Last Modified: 2014-11-15
This should be easy for experts.

The code below is from a tutorial and everything works except it is not UPDATING The data base. You can see the page here:

http://bbsidekick.com/bb/index_edit_test.php

<!DOCTYPE html>
<html>
<head>
<title></title>
<link href="style.css" rel="stylesheet" type="text/css">
<style>
@import "http://fonts.googleapis.com/css?family=Droid+Serif";
/* Above line is used to import Google font style */
.maindiv{
margin:0 auto;
width:980px;
height:500px;
background:#fff;
padding-top:20px;
font-size:14px;
font-family:'Droid Serif',serif
}
.title{
width:100%;
height:70px;
text-shadow:2px 2px 2px #cfcfcf;
font-size:16px;
text-align:center;
font-family:'Droid Serif',serif
}
.divA{
width:70%;
float:left;
margin-top:30px
}
.form{
width:400px;
float:left;
background-color:#f0f8ff;
font-family:'Droid Serif',serif;
padding-left:30px
}
.divB{
width:100%;
height:100%;
background-color:#f0f8ff;
border:dashed 1px #999
}
.divD{
width:200px;
height:480px;
float:left;
background-color:#f0f8ff;
border-right:dashed 1px #999
}
#form3{
color:green;
font-weight:700
}
p{
font-weight:700;
text-align:center;
color:#5678C0;
font-size:18px;
text-shadow:2px 2px 2px #cfcfcf
}
form h2{
text-align:center;
text-shadow:2px 2px 2px #cfcfcf
}
textarea{
width:250px;
height:60px;
border-radius:1px;
box-shadow:0 0 1px 2px #123456;
margin-top:10px;
padding:5px 0;
border:none
}
.input{
width:250px;
height:15px;
border-radius:1px;
box-shadow:0 0 1px 2px #123456;
margin-top:10px;
padding:5px 0;
border:none;
margin-bottom:20px
}
.submit{
color:#fff;
border-radius:3px;
background:#1F8DD6;
padding:5px;
margin-top:40px;
border:none;
width:100px;
height:30px;
box-shadow:0 0 1px 2px #123456;
font-size:16px
}
a{
text-decoration:none;
font-size:16px;
margin:2px 0 0 30px;
padding:3px;
color:#1F8DD6
}
a:hover{
text-shadow:2px 2px 2px #cfcfcf;
font-size:18px
}
.clear{
clear:both
}
</style>
</head>
<body>
<div class="maindiv">
<div class="divA">
<div class="title">
<h2>Update Data Using PHP</h2>
</div>
<div class="divB">
<div class="divD">
<p>Click On Menu</p>
<?php
	$host="localhost"; 					// Put host name in a variable
	$username="bb2014"; 				// Put mysql username in a variable
	$password="sidekick2014"; 			// Put mysql password in a variable
	$db_name="bbsidekick"; 			// Put database name in a variable
	$tbl_name="posts"; 				// Put table name in a variable
		
	$connection = mysql_connect("$host","$username","$password"); 		// VITAL - Connect info: host/user/password
	mysql_select_db("$db_name")or die("cannot select DB"); 		// Select proper DB
	
if (isset($_GET['submit'])) {
$id = $_GET['id'];
$active = $_GET['active'];
$orderid = $_GET['orderid'];
$head = $_GET['head'];
$body = $_GET['body'];
$user = $_GET['user'];

$query = mysql_query('UPDATE posts SET
active="$active", orderid="$orderid", head="$head", body="$body", use="$user" where id="$id"', $connection);
}
$query = mysql_query('select * from posts', $connection);
while ($row = mysql_fetch_array($query)) {
echo '<b><a href="index_edit_test.php?update='.$row["id"].'">'.$row["head"].'</a></b>';
echo '<br />';
}
?>
</div><?php
if (isset($_GET['update'])) {
$update = $_GET['update'];
$query1 = mysql_query("select * from posts where id=$update", $connection);
while ($row1 = mysql_fetch_array($query1)) {
echo '<form class="form" method="get">';
echo '<h2>Update Form</h2>';
echo '<hr/>';
echo $row1["id"];
echo ' hdgfgkshdkjahdkahdsoiah ';
echo '<input class="input" type="hidden" name="did" value="'.$row1["id"].'" />';
echo '<br />';
echo '<label>' . 'ID:' . '</label>' . '<br />';
echo '<input class="input" type="text" name="id" value="'.$row1["id"].'" />';
echo '<br />';
echo '<label>' . 'User:' . '</label>' . '<br />';
echo '<input class="input" type="text" name="user" value="'.$row1["user"].'" />';
echo '<br />';
echo '<label>' . 'Active:' . '</label>' . '<br />';
echo '<input class="input" type="text" name="active" value="'.$row1["active"].'" />';
echo '<br />';
echo '<label>' . "Order:" . '</label>' . '<br />';
echo '<input class="input" type="text" name="orderid" value="'.$row1["orderid"].'" />';
echo '<br />';
echo '<label>' . 'Head:' . '</label>' . '<br />';
echo '<input class="input" type="text" name="head" value="'.$row1["head"].'" />';
echo '<br />';
echo '<label>' . "Address:" . '</label>' . '<br />';
echo '<textarea rows="15" cols="15" name="body">'.$row1["body"].'';
echo '</textarea>';
echo '<br />';
echo '<input class="submit" type="submit" name="submit" value="update" />';
echo '</form>';
}
}
if (isset($_GET['submit'])) {
echo '<div class="form" id="form3"><br><br><br><br><br><br>
<span>Data Updated Successfuly......!!</span></div>';
}
?>
<div class="clear"></div>
</div>
<div class="clear"></div>
</div>
</div><?php
mysql_close($connection);
?>
</body>
</html>

Open in new window

0
Comment
Question by:edavo
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40444224
Variables are not substituted in single quoted strings like one:
$query = mysql_query('UPDATE posts SET
active="$active", orderid="$orderid", head="$head", body="$body", use="$user" where id="$id"', $connection);
}

Open in new window

When I changed it to this, it started working.
$squery = "UPDATE posts SET active='$active', orderid='$orderid', head='$head', body='$body', user='$user' where id='$id'";
$result = mysql_query($squery);
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $squery;
    die($message);
}

Open in new window

0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 40444229
Always have error printed on this mysql command

$query = mysql_query('UPDATE posts SET
active="$active", orderid="$orderid", head="$head", body="$body", use="$user" where id="$id"', $connection);
}  or die ('query failed '.mysql_error())

Open in new window


more details http://php.net/manual/en/function.mysql-error.php
0
 

Author Comment

by:edavo
ID: 40444232
STill not working - I pasted in exactly as you have.

I am sure it has to do with the ' and the " being out of wack, but I have switched them so many times and couldn't get any version to work.

dM
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40444322
I set up the database with all the same login and fields on my systems and the change I showed you above works perfectly.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40444342
One of the unfortunate things about the internet is that old and obsolete code examples are not marked with expiration dates.  PHP's support for the MySQL extension expired about five years ago.  It's deprecated in modern PHP and so the example you've found in that ancient tutorial should not even be online any more, much less held out as a teaching example.  It's just plain wrong, full of coding errors and security holes.  Nobody does anything like that any more.  Discard it at once and never look at it again!  

If you're new to PHP and want some safe and dependable places to learn, this article can help you find them, and can also help you avoid getting tripped up by the old stuff.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

To understand why PHP is doing away with MySQL and what you must do to keep your scripts running, this article can help.  It's a lot to learn, but the examples will map the familiar but obsolete MySQL extension to the modern database extensions.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

I'll see if I can show you a safer way to write this script after I have a little while to digest the author's original intent.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40444345
A quick look at it tells me that this code smell could be part of the problem.

use="$user"

Should that be more like this?

user="$user"
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 40444354
OK, I think I understand a bit of the original intent.  It looks like a table maintenance script.  There are fairly well understood design patterns for that sort of work, but this original code snippet is so poorly written that I can't remediate it.  It's better to start over with a simpler example.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12335-PHP-and-MySQLi-Table-Maintenance.html

There are a lot of things to learn about handling forms with PHP.  You need to understand the difference between HTTP GET and POST request methods.  You must never change a data base (or any other server-side data) on the basis of a GET request.  In the instant case, the script can change the database, so you must use a method="post" attribute and use $_POST instead of $_GET.  The PHP introductory tutorial has some guidance.
http://php.net/manual/en/tutorial.php

One of the important things to learn about PHP is how to get it to tell you what is going on in your programs.  You will probably want to raise the error_reporting() level to E_ALL.  You will also want to be able to see exactly what is in the data.  You will probably find that var_dump() is your friend.

The relationship between HTML forms and PHP request variables ($_GET, $_POST) works on the basis of the name= attribute.  The name= attribute in the form becomes the array key (index) into the GET OR POST array.  If you look at this script you will see that there is no element in the HTML form that is named update=.  As a result this if() statement can never be evaluated TRUE, so some parts of this script can never get executed.

if (isset($_GET['update']))

There are other things wrong too, and taken together it just makes me feel like you would be better off setting all of this aside and starting over with better and more modern examples.  The articles linked here and in the other comments can help you find those examples.
0
 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 250 total points
ID: 40444355
That is also included in the code I posted above.  Below are two different versions, one 'mysql' and one 'mysqli'.  Both work.

Here is the 'mysql' version I edited as 'index_edit_test.php'.
<!DOCTYPE html>
<html>
<head>
<title></title>
<link href="style.css" rel="stylesheet" type="text/css">
<style>
@import "http://fonts.googleapis.com/css?family=Droid+Serif";
/* Above line is used to import Google font style */
.maindiv{
margin:0 auto;
width:980px;
height:500px;
background:#fff;
padding-top:20px;
font-size:14px;
font-family:'Droid Serif',serif
}
.title{
width:100%;
height:70px;
text-shadow:2px 2px 2px #cfcfcf;
font-size:16px;
text-align:center;
font-family:'Droid Serif',serif
}
.divA{
width:70%;
float:left;
margin-top:30px
}
.form{
width:400px;
float:left;
background-color:#f0f8ff;
font-family:'Droid Serif',serif;
padding-left:30px
}
.divB{
width:100%;
height:100%;
background-color:#f0f8ff;
border:dashed 1px #999
}
.divD{
width:200px;
height:480px;
float:left;
background-color:#f0f8ff;
border-right:dashed 1px #999
}
#form3{
color:green;
font-weight:700
}
p{
font-weight:700;
text-align:center;
color:#5678C0;
font-size:18px;
text-shadow:2px 2px 2px #cfcfcf
}
form h2{
text-align:center;
text-shadow:2px 2px 2px #cfcfcf
}
textarea{
width:250px;
height:60px;
border-radius:1px;
box-shadow:0 0 1px 2px #123456;
margin-top:10px;
padding:5px 0;
border:none
}
.input{
width:250px;
height:15px;
border-radius:1px;
box-shadow:0 0 1px 2px #123456;
margin-top:10px;
padding:5px 0;
border:none;
margin-bottom:20px
}
.submit{
color:#fff;
border-radius:3px;
background:#1F8DD6;
padding:5px;
margin-top:40px;
border:none;
width:100px;
height:30px;
box-shadow:0 0 1px 2px #123456;
font-size:16px
}
a{
text-decoration:none;
font-size:16px;
margin:2px 0 0 30px;
padding:3px;
color:#1F8DD6
}
a:hover{
text-shadow:2px 2px 2px #cfcfcf;
font-size:18px
}
.clear{
clear:both
}
</style>
</head>
<body>
<div class="maindiv">
<div class="divA">
<div class="title">
<h2>Update Data Using PHP</h2>
</div>
<div class="divB">
<div class="divD">
<p>Click On Menu</p>
<?php
      $host="10.202.46.41";                               // Put host name in a variable
      $username="bb2014";                         // Put mysql username in a variable
      $password="sidekick2014";                   // Put mysql password in a variable
      $db_name="bbsidekick";                   // Put database name in a variable
      $tbl_name="posts";                         // Put table name in a variable
            
      $connection = mysql_connect("$host","$username","$password");             // VITAL - Connect info: host/user/password
      mysql_select_db("$db_name")or die("cannot select DB");             // Select proper DB
      
if (isset($_GET['submit'])) {
$id = $_GET['id'];
$active = $_GET['active'];
$orderid = $_GET['orderid'];
$head = $_GET['head'];
$body = $_GET['body'];
$user = $_GET['user'];

$squery = "UPDATE posts SET active='$active', orderid='$orderid', head='$head', body='$body', user='$user' where id='$id'";
$result = mysql_query($squery);
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $squery;
    die($message);
}

}
$query = mysql_query('select * from posts', $connection);
while ($row = mysql_fetch_array($query)) {
echo '<b><a href="index_edit_test.php?update='.$row["id"].'">'.$row["head"].'</a></b>';
echo '<br />';
}
?>
</div><?php
if (isset($_GET['update'])) {
$update = $_GET['update'];
$query1 = mysql_query("select * from posts where id=$update", $connection);
while ($row1 = mysql_fetch_array($query1)) {
echo '<form class="form" method="get">';
echo '<h2>Update Form</h2>';
echo '<hr/>';
echo $row1["id"];
echo ' hdgfgkshdkjahdkahdsoiah ';
echo '<input class="input" type="hidden" name="did" value="'.$row1["id"].'" />';
echo '<br />';
echo '<label>' . 'ID:' . '</label>' . '<br />';
echo '<input class="input" type="text" name="id" value="'.$row1["id"].'" />';
echo '<br />';
echo '<label>' . 'User:' . '</label>' . '<br />';
echo '<input class="input" type="text" name="user" value="'.$row1["user"].'" />';
echo '<br />';
echo '<label>' . 'Active:' . '</label>' . '<br />';
echo '<input class="input" type="text" name="active" value="'.$row1["active"].'" />';
echo '<br />';
echo '<label>' . "Order:" . '</label>' . '<br />';
echo '<input class="input" type="text" name="orderid" value="'.$row1["orderid"].'" />';
echo '<br />';
echo '<label>' . 'Head:' . '</label>' . '<br />';
echo '<input class="input" type="text" name="head" value="'.$row1["head"].'" />';
echo '<br />';
echo '<label>' . "Address:" . '</label>' . '<br />';
echo '<textarea rows="15" cols="15" name="body">'.$row1["body"].'';
echo '</textarea>';
echo '<br />';
echo '<input class="submit" type="submit" name="submit" value="update" />';
echo '</form>';
}
}
if (isset($_GET['submit'])) {
echo '<div class="form" id="form3"><br><br><br><br><br><br>
<span>Data Updated Successfuly......!!</span></div>';
}
?>
<div class="clear"></div>
</div>
<div class="clear"></div>
</div>
</div>
<?php
mysql_close($connection);
?>
</body>
</html>

Open in new window


And here is a 'mysqli procedural' version as 'index_edit_test2.php'.
<!DOCTYPE html>
<html>
<head>
<title></title>
<link href="style.css" rel="stylesheet" type="text/css">
<style>
@import "http://fonts.googleapis.com/css?family=Droid+Serif";
/* Above line is used to import Google font style */
.maindiv{
margin:0 auto;
width:980px;
height:500px;
background:#fff;
padding-top:20px;
font-size:14px;
font-family:'Droid Serif',serif
}
.title{
width:100%;
height:70px;
text-shadow:2px 2px 2px #cfcfcf;
font-size:16px;
text-align:center;
font-family:'Droid Serif',serif
}
.divA{
width:70%;
float:left;
margin-top:30px
}
.form{
width:400px;
float:left;
background-color:#f0f8ff;
font-family:'Droid Serif',serif;
padding-left:30px
}
.divB{
width:100%;
height:100%;
background-color:#f0f8ff;
border:dashed 1px #999
}
.divD{
width:200px;
height:480px;
float:left;
background-color:#f0f8ff;
border-right:dashed 1px #999
}
#form3{
color:green;
font-weight:700
}
p{
font-weight:700;
text-align:center;
color:#5678C0;
font-size:18px;
text-shadow:2px 2px 2px #cfcfcf
}
form h2{
text-align:center;
text-shadow:2px 2px 2px #cfcfcf
}
textarea{
width:250px;
height:60px;
border-radius:1px;
box-shadow:0 0 1px 2px #123456;
margin-top:10px;
padding:5px 0;
border:none
}
.input{
width:250px;
height:15px;
border-radius:1px;
box-shadow:0 0 1px 2px #123456;
margin-top:10px;
padding:5px 0;
border:none;
margin-bottom:20px
}
.submit{
color:#fff;
border-radius:3px;
background:#1F8DD6;
padding:5px;
margin-top:40px;
border:none;
width:100px;
height:30px;
box-shadow:0 0 1px 2px #123456;
font-size:16px
}
a{
text-decoration:none;
font-size:16px;
margin:2px 0 0 30px;
padding:3px;
color:#1F8DD6
}
a:hover{
text-shadow:2px 2px 2px #cfcfcf;
font-size:18px
}
.clear{
clear:both
}
</style>
</head>
<body>
<div class="maindiv">
<div class="divA">
<div class="title">
<h2>Update Data Using PHP</h2>
</div>
<div class="divB">
<div class="divD">
<p>Click On Menu</p>
<?php
      $host="10.202.46.41";                               // Put host name in a variable
      $username="bb2014";                         // Put mysql username in a variable
      $password="sidekick2014";                   // Put mysql password in a variable
      $db_name="bbsidekick";                   // Put database name in a variable
      $tbl_name="posts";                         // Put table name in a variable

//$link = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');            
$connection = mysqli_connect($host, $username, $password, $db_name);
      
if (isset($_GET['submit'])) {
$id = $_GET['id'];
$active = $_GET['active'];
$orderid = $_GET['orderid'];
$head = $_GET['head'];
$body = $_GET['body'];
$user = $_GET['user'];

$squery = "UPDATE posts SET active='$active', orderid='$orderid', head='$head', body='$body', user='$user' where id='$id'";
$result = mysqli_query($connection, $squery);
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $squery;
    die($message);
}

}
$query = mysqli_query($connection,'select * from posts');
while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
echo '<b><a href="index_edit_test2.php?update='.$row["id"].'">'.$row["head"].'</a></b>';
echo '<br />';
}
?>
</div><?php
if (isset($_GET['update'])) {
$update = $_GET['update'];
$query1 = mysqli_query($connection, "select * from posts where id=$update" );
while ($row1 = mysqli_fetch_array($query1, MYSQLI_ASSOC)) {
echo '<form class="form" method="get">';
echo '<h2>Update Form</h2>';
echo '<hr/>';
echo $row1["id"];
echo ' hdgfgkshdkjahdkahdsoiah ';
echo '<input class="input" type="hidden" name="did" value="'.$row1["id"].'" />';
echo '<br />';
echo '<label>' . 'ID:' . '</label>' . '<br />';
echo '<input class="input" type="text" name="id" value="'.$row1["id"].'" />';
echo '<br />';
echo '<label>' . 'User:' . '</label>' . '<br />';
echo '<input class="input" type="text" name="user" value="'.$row1["user"].'" />';
echo '<br />';
echo '<label>' . 'Active:' . '</label>' . '<br />';
echo '<input class="input" type="text" name="active" value="'.$row1["active"].'" />';
echo '<br />';
echo '<label>' . "Order:" . '</label>' . '<br />';
echo '<input class="input" type="text" name="orderid" value="'.$row1["orderid"].'" />';
echo '<br />';
echo '<label>' . 'Head:' . '</label>' . '<br />';
echo '<input class="input" type="text" name="head" value="'.$row1["head"].'" />';
echo '<br />';
echo '<label>' . "Address:" . '</label>' . '<br />';
echo '<textarea rows="15" cols="15" name="body">'.$row1["body"].'';
echo '</textarea>';
echo '<br />';
echo '<input class="submit" type="submit" name="submit" value="update" />';
echo '</form>';
}
}
if (isset($_GET['submit'])) {
echo '<div class="form" id="form3"><br><br><br><br><br><br>
<span>Data Updated Successfuly......!!</span></div>';
}
?>
<div class="clear"></div>
</div>
<div class="clear"></div>
</div>
</div>
<?php
mysqli_close($connection);
?>
</body>
</html>

Open in new window

0
 

Author Closing Comment

by:edavo
ID: 40444468
I appreciate your feedback
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now