?
Solved

MySQL Update Statement Syntax / PDO

Posted on 2010-03-23
12
Medium Priority
?
493 Views
Last Modified: 2013-12-12
I recently added PDO support to our server.  Not surprisingly, some php scripts that worked before this update are now glitching up.  I have a very simple script that updates a field in a database when a form is submitted.  This SQL statement works when I execute it in phpMyAdmin.  When the form is submitted, it does not work; however, I don't get an error message. Everything in the targeted field in the database gets erased.  Nothing gets updated.  

Here is my code.  Anyone have an idea what's going on?  I know this has to be something stupid that I'm missing.
$sqlone = "UPDATE specials SET content = '$content' WHERE label = 'specials'";
$queryone = mysql_query("$sqlone", $con);
if(!$queryone){
die(mysql_error($con));
}

Open in new window

0
Comment
Question by:rowdyj81
  • 7
  • 5
12 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 28357400
use

$queryone = mysql_query("$sqlone", $con);
-->
$queryone = mysql_query($sqlone, $con);
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 28357472
or

$sqlone = "UPDATE specials SET content = '".$content."' WHERE label = 'specials'";
$queryone = mysql_query($sqlone, $con);
0
 

Author Comment

by:rowdyj81
ID: 28362511
Tried both solutions, neither worked.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 61

Expert Comment

by:HainKurt
ID: 28368895
what does $content have?
post the value of $sqlone before running it...

$sqlone = "UPDATE specials SET content = '".$content."' WHERE label = 'specials'";
echo $sqlone;
$queryone = mysql_query($sqlone, $con);
0
 

Author Comment

by:rowdyj81
ID: 28444349
This is the SQL statement that is printed:
UPDATE specials SET content = '' WHERE label = 'specials'

For whatever reason, $specials seems to have no value.  Everything looks right in my form also.  I even tried passing the variable through the url.  The file is called poster.php so I tried this:

poster.php?&specials=test

Still nothing.  I'm going to attach the full code.
<?php
require("config.php");

$specials = $_GET['specials'];
$content = nl2br("$specials");

$con = mysql_connect("$dbhost", "$dbuser", "$dbpass");
if (!con){
	die(mysql_error($con));
}

mysql_select_db("$dbname", $con);

$sqlone = "UPDATE specials SET content = '".$content."' WHERE label = 'specials'";
echo $sqlone;
$queryone = mysql_query($sqlone, $con);
if(!$queryone){
	echo "Query failed.<br />";
	die(mysql_error($con));
}
else{
	echo "Thank you.  Your changes have been made successfully.<br /><a href=\"admin.php\">Click here</a> to go back to the admin panel.";
}
?>

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 28452630
$sqlone = "UPDATE specials SET content = '".$content."' WHERE label = 'specials'";
-->
$sqlone = "UPDATE specials SET content = '".$content."' WHERE label = '".$specials."'";

what is nl2br?

$content = nl2br("$specials");
-->
$content = nl2br($specials);
0
 

Author Comment

by:rowdyj81
ID: 28454679
Still no luck.  This is my SQL now:
UPDATE specials SET content = '' WHERE label = ''

nl2br adds a <br /> tag before each new line in the string.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 28458480
what happens if you use this

$sqlone = "UPDATE specials SET content = '$content.' WHERE label = '$specials'";
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 28458534
oops, forgot to delete one .

$sqlone = "UPDATE specials SET content = '$content' WHERE label = '$specials'";

also , check the value of $content & $special at this moment...
0
 

Author Comment

by:rowdyj81
ID: 28462786
This is the output I get:
content =
specials =
UPDATE specials SET content = '' WHERE label = ''

It's weird.  I have no idea why it's behaving this way.  Another, thing it's no longer erasing the field in the database.  It's not affecting the database at all now.
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 28464321
thats good ;) somehow you are not passing the values to the page... code looks ok, but you need to pass the values...

so you used this

$sqlone = "UPDATE specials SET content = '$content' WHERE label = '$specials'";

and got these

content =
specials =
UPDATE specials SET content = '' WHERE label = ''

now replace these lines with

$specials = $_GET['specials'];
$content = nl2br("$specials");
-->
$specials = $_REQUEST['specials'];
$content = nl2br("$specials");

and see what you get...
0
 

Author Comment

by:rowdyj81
ID: 28464943
Woot, you're awesome man.  Thank you so much.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
The viewer will learn how to dynamically set the form action using jQuery.
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.

588 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