Update statement not working properly

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
pingeyeg
Forum Newbie
Posts: 10
Joined: Wed Oct 15, 2008 2:33 pm
Location: Atlanta, GA

Update statement not working properly

Post by pingeyeg »

I have run into a bit of a snag. I created this update statement, but I keep getting a return of "Query was empty" yet my SQL statement outputs to the screen what I am trying to do. Please take a look and let me know what I am doing wrong. Thanks

if($_POST['image-click']) {
$menuName = mysql_real_escape_string($menuName);
$menuLink = mysql_real_escape_string($menuLink);

$menuID = (int)$_POST['menuID'];
$menuName = $_POST['menuName'];
$menuLink = $_POST['menuLink'];

$updateMenu = "UPDATE menu
SET menuID = $menuID, menuName = '$menuName', menuLink = '$menuLink'
WHERE menuName = '$menuName'";
$menuUpdated = mysql_query($udpateMenu);
echo mysql_error();

$updatePage = "UPDATE pages SET pageID = $menuID, page = '$menuName' WHERE page = '$menuName'";
$pageUpdated = mysql_query($updatePage);
echo mysql_error();

if(mysql_affected_rows >= 1) {
$result = "The menu has been updated.";
} else {
$result = "There was an issue in completing your request.";
}
}

The page in question is at http://cms.goodboyweb.com/admin UN/ cmsuser PW/ cmsuser
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Update statement not working properly

Post by aceconcepts »

You are missing single quotes from the following:

Code: Select all

 
SET menuID = $menuID
 
SET pageID = $menuID
 
pingeyeg
Forum Newbie
Posts: 10
Joined: Wed Oct 15, 2008 2:33 pm
Location: Atlanta, GA

Re: Update statement not working properly

Post by pingeyeg »

Those are ID fields though, auto-increment. Aren't they supposed to not have single quotes around them since that is a number?
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Update statement not working properly

Post by aceconcepts »

Try using mysql_error.

Code: Select all

mysql_query($udpateMenu) or die(mysql_error());
pingeyeg
Forum Newbie
Posts: 10
Joined: Wed Oct 15, 2008 2:33 pm
Location: Atlanta, GA

Re: Update statement not working properly

Post by pingeyeg »

I already have that there:

$updateMenu = "UPDATE menu
SET menuID = '$menuID', menuName = '$menuName', menuLink = '$menuLink'
WHERE menuName = '$menuName'";
$menuUpdated = mysql_query($udpateMenu);
echo mysql_error();

That is where I am getting the "Query was empty" remark.
pingeyeg
Forum Newbie
Posts: 10
Joined: Wed Oct 15, 2008 2:33 pm
Location: Atlanta, GA

Re: Update statement not working properly

Post by pingeyeg »

Ok, I have figure out one thing, but I am still having an issue. I had misspelled the variable calling the first query string. Now I no longer get that "Query was empty" remark, but the db is still not changing.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Update statement not working properly

Post by aceconcepts »

Have you checked to see the values of your variables before you send your query?

Try echoing your variables "$menuName" etc...
pingeyeg
Forum Newbie
Posts: 10
Joined: Wed Oct 15, 2008 2:33 pm
Location: Atlanta, GA

Re: Update statement not working properly

Post by pingeyeg »

One step ahead of you. The following are my results for both queries:

UPDATE menu SET menuID = 3, menuName = 'Gallery', menuLink = 'gallery.php' WHERE menuName = 'Gallery'
UPDATE pages SET pageID = 3, page = 'Gallery' WHERE page = 'Gallery'
pingeyeg
Forum Newbie
Posts: 10
Joined: Wed Oct 15, 2008 2:33 pm
Location: Atlanta, GA

Re: Update statement not working properly

Post by pingeyeg »

Actually, that might be my issue. The field that I am actually changing is not being displayed I the query. The one that is being displayed is the last field in the db. The following is my form.

<?php
if(isset($_POST['image-click'])) {
echo $result;
} elseif(!isset($_POST['image-click'])) {

$grabInfo = "SELECT menuID, menuName, menuLink FROM menu ORDER BY menuID ASC";
$outputInfo = mysql_query($grabInfo);
echo mysql_error();

while($row = mysql_fetch_assoc($outputInfo)) {
?>
Info for page <?= (int)$row['menuID'] ?>:
<div style="margin-bottom: 20px">
<input type="text" name="menuID" style="width: 25px" value="<?= (int)$row['menuID'] ?>"> <> <input type="text" name="menuName" style="width: 200px" value="<?= $row['menuName'] ?>"> <> <input type="text" name="menuLink" style="width: 200px" value="<?= $row['menuLink'] ?>">
</div>
<?php
}
}
?>
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Update statement not working properly

Post by aceconcepts »

You're trying to send multiple form fields from within a while loop.

You need to send each field as an array:

Code: Select all

 
while($row = mysql_fetch_assoc($outputInfo)) {
?>
Info for page <?= (int)$row['menuID'] ?>:
<div style="margin-bottom: 20px">
<input type="text" name="menuID[]" style="width: 25px" value="<?= (int)$row['menuID'] ?>"> <> <input type="text" name="menuName[]" style="width: 200px" value="<?= $row['menuName'] ?>"> <> <input type="text" name="menuLink[]" style="width: 200px" value="<?= $row['menuLink'] ?>">
</div>
<?php
}
}
?>
 
Notice the square brackets i've placed at the end of each field name. This passes the form element/field as an array.
pingeyeg
Forum Newbie
Posts: 10
Joined: Wed Oct 15, 2008 2:33 pm
Location: Atlanta, GA

Re: Update statement not working properly

Post by pingeyeg »

Is there something I need to place in between those brackets? Right now I am getting this for the queries:

UPDATE menu SET menuID = 1, menuName = 'Array', menuLink = 'Array' WHERE menuName = 'Array'
UPDATE pages SET pageID = 1, page = 'Array' WHERE page = 'Array'
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Update statement not working properly

Post by aceconcepts »

When you submit the form you need to setup a for loop to get each of the fields submitted via the form:

Code: Select all

 
if($_POST['image-click']) {
$menuID = (int)$_POST['menuID'];
$menuName = $_POST['menuName'];
$menuLink = $_POST['menuLink'];
 
for($x=0; $x<count($menuID); $x++)
{
$menu_id=$menuID[$x];
$menu_name=$menuName[$x];
$menu_link=$menuLink[$x];
 
$updateMenu = "UPDATE menu
SET menuID = $menuID, menuName = '$menu_name', menuLink = '$menu_link'
WHERE menuName = '$menu_name'";
$menuUpdated = mysql_query($udpateMenu);
echo mysql_error();
 
$updatePage = "UPDATE pages SET pageID = '$menu_id', page = '$menu_name' WHERE page = '$menu_name'";
$pageUpdated = mysql_query($updatePage);
echo mysql_error();
}
 
if(mysql_affected_rows >= 1) {
$result = "The menu has been updated.";
} else {
$result = "There was an issue in completing your request.";
}
}
 
Post Reply